/* Job Scheduler Template */
/*
** Messages for sp_jst_reconf_mdcache_type
**
** 19133, "Procedure %1!, invalid metadata cache type, value must be 0, 1, or 2."
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Support Stored Procedure for Sybase Reconfigure Metadata Cache Template'
go
-- Remove sp_jst_reconf_mdcache_type if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_mdcache_type'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reconf_mdcache_type
go
/* 
** sp_jst_reconf_mdcache_type
** Version: 1.0
**
** This stored procedure builds the sp_configure command to reconfigure
** a specific metadata cache type. It also runs the sp_configure
** command.
**
** 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.  It is a support
** stored procedure and all values for it come from 
** sp_jst_reconf_mdcache_type
**
** Parameter definitions
** @cache_type      - type of cache object, 0=db,1=indexes,2=objects
** @num_md_objects  - new number of this object type
*/
create procedure sp_jst_reconf_mdcache_type ( @cache_type int=NULL,
                                              @num_md_objects int=NULL )
as
begin

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

    -- set the metadata cache object string
    declare @md_string varchar(100)
          , @sp_name varchar(30)
          , @status int

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

    -- check ASE config param string for cache type
    if( @cache_type = 0 )
      begin
        --reconfigure db
        select @md_string = 'number of open databases'
      end
    else
      begin
        if( @cache_type = 1 )
          begin
            --reconfigure indexes
            select @md_string = 'number of open indexes'
          end
        else
          begin
            if( @cache_type = 2 )
              begin
                --reconfigure objects
                select @md_string = 'number of open objects'
              end
            else
              begin
                -- report invalid cache type
                raiserror 19133
                return -@@error
              end
          end
      end

    --reconfigure the command
    exec @status = sp_configure @md_string, @num_md_objects
    if( @status != 0 )
        return -@status
    else
        return @status
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_reconf_mdcache_type'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created procedure sp_jst_reconf_mdcache_type'
    grant execute on sp_jst_reconf_mdcache_type to public
end
go
