/* Job Scheduler Template */
/*
** Messages for sp_jst_reconf_mdcache
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19123, "%1! value of %2! is invalid."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reconfigure Metadata Cache Template'
go
-- Remove sp_jst_reconf_mdcache if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_mdcache'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reconf_mdcache
go
/* 
** sp_jst_reconf_mdcache
** Version: 1.0
**
** This stored procedure 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 Metadata
** Cache 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
** @cache_type      - type of cache object, 0=db,1=indexes,2=objects,3=all
** @pct_over_active - percent by which number of cache object(s) should change
** @num_md_objects  - override value to force an exact change in number
**                    of objects
*/
create procedure sp_jst_reconf_mdcache ( @cache_type int=NULL,
                                         @pct_over_active int=10,
                                         @num_md_objects int=NULL )
as
begin

    print 'sp_jst_reconf_mdcache(cache_type = %1!, pct_over_active = %2! percent, num_md_objects = %3!)', @cache_type, @pct_over_active, @num_md_objects

    -- number of open databases, config = 105
    -- number of open indexes, config = 263
    -- number of open objects, config = 107

    -- if number of connections is specified, ignore other parameter
    -- values and set configuration parameter to this value
    declare @flag int
          , @sp_name varchar(30)
          , @err int
          , @status int

    -- Initializations
    select @sp_name = 'sp_jst_reconf_mdcache'
         , @status = 0

    -- Determine metadata object type
    if( @num_md_objects <> NULL and @num_md_objects > 0 )
    begin
        --if user specified "All," set value for each md cache type
        if( @cache_type = 3 )
        begin
            select @flag = 2
            while( @flag >= 0 )
            begin
                --print 'cache flag is %1!', @flag
                exec @err = sp_jst_reconf_mdcache_type @flag, @num_md_objects
                if( @err < 0 )
                    select @status = @err
                select @flag = @flag-1
            end
        end
        -- set value for specified md cache type
        else
        begin
            exec @status = sp_jst_reconf_mdcache_type @cache_type, 
                                                      @num_md_objects
        end
    end --if

    -- calculate the desired amount, if any
    else
    begin
        if( @pct_over_active is NULL or @pct_over_active = 0 )
        begin
            raiserror 19048, @sp_name, 'percent over active'
            return -@@error
        end
        if( @pct_over_active < 0 )
        begin
            raiserror 19123, 'Percent Over Active', @pct_over_active
            return -@@error
        end

        declare @config int
              , @num_active int

        --if user specified "All," calc & set value for each md cache type
        if( @cache_type = 3 )
        begin
            select @flag = 2
            while( @flag >= 0 )
            begin
                exec sp_jst_get_mdcache_type_config @flag, @config out 
                --print 'cache flag is %1!; config value is %2!', 
                --      @flag, @config
                select @num_active = 
                           config_admin( 22, @config, 2, 0, NULL, NULL )
                --print 'number active is %1!', @num_active
                select @num_active = @num_active + 
                          ( convert( int, ( ( @pct_over_active * .01 )
                                             *   @num_active ) ) )
                --print 'sum number active is %1!', @num_active
                exec @err = sp_jst_reconf_mdcache_type @flag, @num_active
                if( @err < 0 )
                    select @status = @err
                select @flag = @flag-1
            end
        end --end all types

        else --one object type specified
        begin
            exec @status = sp_jst_get_mdcache_type_config @cache_type, 
                                                          @config out 
            select @num_active = config_admin( 22, @config, 2, 0, NULL, NULL )

            select @num_active = @num_active + 
                      ( convert( int, ( ( @pct_over_active * .01 )
                                             *   @num_active ) ) )
            -- set value for specified md cache type
            exec @status = sp_jst_reconf_mdcache_type @cache_type, @num_active
        end --end
    end --else
 
    return @status
end --end sp
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_mdcache'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created procedure sp_jst_reconf_mdcache'
    grant execute on sp_jst_reconf_mdcache to public
end
go
