/* Job Scheduler Template */
/*
** Messages for sp_jst_reconf_locks
**
** 19123, "%1! value of %2! is invalid."
** 19140, "number of locks changed to %1!"
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reconfigure Locks Template'
go
-- Remove sp_jst_reconf_locks if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_locks'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reconf_locks
go
/* 
** sp_jst_reconf_locks
** Version: 1.0
**
** This stored procedure allows users to set-up automatic
** reconfiguration of the number of locks.  It runs the sp_configure 
** stored procedure using the input provided to this procedure.
**
** 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 Locks
** 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
** @grab_size          - number of new locks to allocate
** @grab_pct_threshold - the percentage of locks that can be used before
**                       more locks need to be added. 
** @free_factor        - used to determine if the number of locks is too
**                       large
** @num_locks          - override value to force an exact change in number
**                       of locks
*/
create procedure sp_jst_reconf_locks ( @grab_size int=5000,
                                       @grab_pct_threshold int=95,
                                       @free_factor int=3,
                                       @num_locks int=NULL )
as
begin

    print 'sp_jst_reconf_locks(grab_size = %1!, grab_pct_threshold = %2! percent, free_factor = %3!, num_locks = %4!)', @grab_size, @grab_pct_threshold, @free_factor, @num_locks

    declare @err int
          , @status int
          , @sp_name varchar(32)
          , @locks_chgd varchar(5)
          , @locks_in_use int
          , @configd_locks int
          , @grab_threshold int
          , @free_threshold int

    --Initializations
    select @sp_name = 'sp_jst_reconf_locks'
         , @locks_chgd = 'false'
         , @status = 0

    -- if number of locks is specified, ignore other parameter
    -- values and set configuration parameter to this value
    if( @num_locks <> NULL and @num_locks > 0 )
      begin
         exec @status = sp_configure 'number of locks', @num_locks
         select @locks_chgd = 'true'
      end --if
    -- calculate the increase amount or decrease amount, if any
    else
      begin
        --check for required values
        if( @grab_size = NULL or @grab_size <= 1000 )
          begin
            -- invalid grab_size
            raiserror 19123, 'Grab Size', @grab_size
            return -@@error
          end
        if( @grab_pct_threshold = NULL or @grab_pct_threshold <= 0 
                                       or @grab_pct_threshold > 100 )
          begin
            -- invalid grab_pct_threshold
            raiserror 19123, 'Grab Percent Threshold', 
                            @grab_pct_threshold
            return -@@error
          end

        --get server lock info
        select @locks_in_use = count(*) from master..syslocks sl,
                                             master..spt_values v1,
                                             master..spt_values v2
               where sl.type = v1.number 
                 and v1.type = "L" 
                 and (sl.context+2049) = v2.number 
                 and v2.type = "L2"

        select @configd_locks = value from master..syscurconfigs 
                                      where config = 106 

        -- check for need to increase locks first; if no increase
        -- needed, check for need to decrease locks.

        -- check if more locks are needed
        select @grab_threshold = ( convert( int, (
                                     ( @grab_pct_threshold * .01) * 
                                      @configd_locks) ) )
        if( @locks_in_use >= @grab_threshold )
          begin
            --get more locks
            select @num_locks = (@grab_size + @configd_locks)
            exec @status = sp_configure 'number of locks', @num_locks
            select @locks_chgd = 'true'
          end
        -- check if lock count should be reduced
        else
          begin
            if( @free_factor = NULL or @free_factor <= 0 )
              begin
                -- invalid free_factor
                raiserror 19123, 'Free Factor', @free_factor
                return -@@error
              end

            select @free_threshold = @configd_locks - @locks_in_use

            if( @free_threshold > (@free_factor * @grab_size) )
              begin   
                --reduce locks
                select @num_locks = @configd_locks-@grab_size 
                exec @status = sp_configure 'number of locks', @num_locks
                select @locks_chgd = 'true'
              end   
          end
      end 

    --Log lock changes
    if( @status = 0 and @locks_chgd = 'true' )
      begin
        raiserror 19140, @num_locks
      end

    return -@status
end --end sp
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_locks'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created procedure sp_jst_reconf_locks'
    grant execute on sp_jst_reconf_locks to public
end
go
