/* Job Scheduler Template */
/*
** Messages for sp_jst_reconf_usr_conns
**
** 19123, "%1! value of %2! is invalid."
** 19141, "Adaptive Server version %1! does not support dynamically changing
**         the memory allocation method."
** 19142, "Cannot reconfigure user connections."
** 19143, "UPPER BUFFER PERCENTAGE value for number of user connections was
**         not supplied, NULL, or empty."
** 19144, "LOWER BUFFER PERCENTAGE value for number of user connections was
**         not supplied, NULL, or empty."
** 19145, "User connections reconfigured to $1!."
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reconfigure User Connections Template'
go
-- Remove sp_jst_reconf_usr_conns if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_usr_conns'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reconf_usr_conns
go
/* sp_jst_reconf_usr_conns
** Version: 2.2
**
** This stored procedure runs sp_configure to adjust the number of user
** connections based on the threshold values provided here.  The number
** of user connections may be changed to a specific value provide to
** this SP or may be calculated based on the upper and lower thresholds
** provided.  User connections values will not change if not change is
** warranted.
**
** sp_jst_reconf_usr_conns also allows the user to specify a new memory
** allocation method.
**
** This procedure was developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running a job created from the "Sybase Reconfigure User
** Connections Template"
**
** This stored proc is the called at job execution.  The Job Scheduler
** GUI will gather the values for each parameter and form the job
** SQL when a job is created from the associated template.
**
** Parameter definitions
** @upper_threshold   - maximum user connections allowed at one time
** @lower_threshold   - minimum user connections allowed at one time
** @alter_pct         - percent by which num connections should change
** @upper_buf_pct     - upper percent threshold for allocating conns
** @lower_buf_pct     - lower percent threshold for deallocating conns
** @mem_alloc_method  - indicates type of memory allocation method
** @num_connections   - override to all other params and sets number of
**                      connections to this value.
*/
create procedure sp_jst_reconf_usr_conns( @upper_threshold int=NULL,
                                          @lower_threshold int=NULL,
                                          @alter_pct int=10,
                                          @upper_buf_pct int=10,
                                          @lower_buf_pct int=20,
                                          @mem_alloc_method int=2,
                                          @num_connections int=NULL ) 
as
begin

    print 'sp_jst_reconf_usr_conns(upper_threshold = %1!, lower_threshold = %2!, alter_pct = %3! percent, upper_buf_pct = %4! percent, lower_buf_pct = %5!
percent, mem_alloc_method = %6!, num_connections = %7! )', 
          @upper_threshold, @lower_threshold, @alter_pct, @upper_buf_pct,
          @lower_buf_pct, @mem_alloc_method, @num_connections  

    declare @conns_chgd varchar(5)
          , @sp_name varchar(30)
          , @version_num varchar(32)
          , @status int

    select @conns_chgd = 'false'
          , @sp_name = 'sp_jst_reconf_usr_conns'
          , @status = 0

    --determine if user wants to change the allocation method, if so check 
    --db version to see if this is allowed
    if( @mem_alloc_method <> NULL and @mem_alloc_method < 2 )
      begin
        declare @ase_version_ok int
        exec @ase_version_ok = sp_jst_valid_ase_version '12.5'
        if( @ase_version_ok = 0 )
          begin
            if( (@mem_alloc_method = 0) or (@mem_alloc_method = 1) )
              begin
                exec sp_configure 'dynamic allocation on demand', 
                                  @mem_alloc_method
              end
            else
              begin
                raiserror 19123, 'Memory Allocation Method',
                                 @mem_alloc_method
                return -@@error
              end
          end
        else
          begin
            exec sp_jst_version_num @version_num 
            raiserror 19141, @version_num
            return -@@error
          end
      end

    -- if number of connections is specified, ignore other parameter
    -- values and set configuration parameter to this value
    if( @num_connections <> NULL and @num_connections > 0 )
      begin
        exec @status = sp_configure 'number of user connections', 
                                        @num_connections
        if( @status != 0 ) --and @conns_chgd = 'true' ) 
          begin
            raiserror 19142
            return -@@error
          end
        return @status
      end
 
    -- get the current user connections info
    declare @cur_config_cnt int 
          ,  @actual_conns int 
    select @cur_config_cnt = value from master..syscurconfigs where config = 103 
    select @actual_conns = count(*) from master..sysprocesses where suid != 0 

    -- check necessary values
    if( @upper_buf_pct = NULL )
      begin
        raiserror 19143
        return -@@error
      end

    if( @lower_buf_pct = NULL )
      begin
        raiserror 19144
        return -@@error
      end

    -- determine if we need to increase the number of connections
    declare @new_num_conns int
    if( ( @actual_conns + 
         ( convert( int,((@upper_buf_pct * .01) * @cur_config_cnt)) ) ) >
                                                   @cur_config_cnt )
      begin
        select @new_num_conns = @cur_config_cnt + 
                      convert( int, ((@alter_pct*.01) * @cur_config_cnt) )

        if( @upper_threshold != NULL and @upper_threshold != 0 )
          begin
            if( @new_num_conns <= @upper_threshold )
              begin
                exec @status = sp_configure 'number of user connections', 
                                        @new_num_conns
                select @conns_chgd = 'true'
              end
          end
        else
          begin
            exec @status = sp_configure 'number of user connections', 
                                        @new_num_conns
            select @conns_chgd = 'true'
          end
      end --end increase condition

    -- determine if we need to decrease the number of connections
    else
      begin
        if( (@cur_config_cnt - @actual_conns) > 
              convert( int, ((@lower_buf_pct*.01) * @cur_config_cnt) ) )
          begin
            select @new_num_conns = @cur_config_cnt - 
                      convert( int, ((@alter_pct*.01) * @cur_config_cnt) )
            if( @lower_threshold != NULL and @lower_threshold != 0 )
              begin
                if( @new_num_conns >= @lower_threshold )
                  begin
                    exec @status = sp_configure 'number of user connections', 
                                      @new_num_conns
                    select @conns_chgd = 'true'
                  end
              end
            else
              begin
                exec @status = sp_configure 'number of user connections', 
                                            @new_num_conns
                select @conns_chgd = 'true'
              end
          end
      end --else lower condition
          
    if( @status != 0 )
      begin
        if( @conns_chgd = 'true' ) 
          begin
            raiserror 19142
            return -@@error
          end
      end
    else
      begin
        if( @conns_chgd = 'true' ) 
          begin
            raiserror 19145, @new_num_conns
          end
        --no need to change user connections
        return @status
      end
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_usr_conns'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'Created sp_jst_reconf_usr_conns'
    grant execute on sp_jst_reconf_usr_conns to public
end
go
