/* Job Scheduler Template */
/*
** Messages for sp_jst_svr_update_statistics
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19123, "%1! value of %2! is invalid."
** 19126, "UPDATE STATISTICS command for table %1! failed."
** 19127, "UPDATE STATISTICS command for table %1!, index %2! failed."
** 19128, "UPDATE STATISTICS command for table %1! succeeded."
** 19129, "UPDATE STATISTICS command for table %1!, index %2! succeeded."
** 19130, "Threshold values not exceeded so UPDATE STATISTICS did not run for
           table %1!."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
use sybsystemprocs
go
print 'Creating Sybase Server Update Statistics Template stored procedures'
go
-- Remove the sp_jst_db_update_statistics if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_db_update_statistics'
             and type = 'P'
             and (sysstat & 7) = 4 )

    drop proc sp_jst_db_update_statistics
go
/* 
** sp_jst_db_update_statistics 
** Version: 3.0
**
** This stored procedure checks the number of rows in each table in the 
** specified database and for those tables with more than 10000 rows, it 
** builds and executes a request for the JS update statistics stored 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 Update Statistics
** 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
** @db_name  - name of the database for which statistics should be updated
** @index_flag  - indicates if updates stats should be run on indexes
**                false = 1, true = 0
** @datachg_threshold - datachange threshold required to run update statistics
** @rowcnt_threshold  - row count threshold required to run update stats command
** @pagecnt_threshold - page count threshold required to run update stats command
** @step_value  - the number of histogram steps
** @consumers   - the number of consumer processes
** @spercent    - the sampling rate, if any
*/
create procedure sp_jst_db_update_statistics ( @db_name varchar(255),
                                               @index_flag int = 1,
                                               @datachg_threshold float = 100.0,
                                               @rowcnt_threshold int = 10000,
                                               @pagecnt_threshold int = 0, 
                                               @step_value int = NULL,
                                               @consumers int = 0,
                                               @spercent int = NULL )
as
begin

    print 'sp_jst_db_update_statistics( @db_name = %1!, @index_flag = %2!, datachg_threshold = %3!, rowcnt_threshold = %4!, pagecnt_threshold = %5!, step_value = %6!, consumers = %7!, sampling_rate = %8! )', @db_name, @index_flag, @datachg_threshold, @rowcnt_threshold, @pagecnt_threshold, @step_value, @consumers, @spercent 

    -- supported syntax:  
    --  update statistics <table_name> 
    --             [using step values]
    --             [with consumers = consumers], 
    --             [sampling = spercent percent]
    --
    --  update index statistics <table_name> 
    --             [using step values]
    --             [with consumers = consumers], 
    --             [sampling = spercent percent]
    -- Example:
    --  update statistics authors with sampling = 5 percent
    --

    declare @updt_cmd varchar(600)
          , @status int
          , @err int
          , @sp_name varchar(30)
          , @msgno int
          , @table_name varchar(255)
          , @tbl_id int
          , @max_id int
          , @type char(2)
          , @row_cnt int
          , @db_id int

    -- check for required input values
    -- must have a db name
    select @db_name = ltrim(rtrim(@db_name))
    if( @db_name is NULL )
      begin
          raiserror 19048, @sp_name, 'Database Name'
          return -@@error
      end

    -- Find all the tables in this database and check each table to see
    --  if it's statistics need updating.
    select @tbl_id = 100
         , @status = 0
         , @db_id = db_id(@db_name)

    select @max_id = MAX(id)
    from sysobjects
    where type in ("U ","S ")

    while( @tbl_id <= @max_id )
    begin
        if( @tbl_id = @max_id )
        begin
            break
        end

        select @tbl_id = MIN(id)
        from sysobjects
        where id > @tbl_id and ( type = "U " 
                           and name not like 'spt_%') 
                            --or (type = "S " and name not like 'spt_%') )
        --select @row_cnt = rowcnt from systabstats where id = @tbl_id 
        --                                            and indid=0
        select @row_cnt = row_count( @db_id, @tbl_id )
        select @table_name = name from sysobjects where id = @tbl_id
        --print 'table %1! has %2! rows', @table_name, @row_cnt
        if( @row_cnt >= @rowcnt_threshold )
        begin
            select @table_name = name from sysobjects where id = @tbl_id
            --call the update statistic stored proc
            select @updt_cmd = @db_name 
                            + "..sp_jst_update_statistics "
            print 'executing:  %1!,%2!,%3!,%4!,%5!,%6!,%7!,%8!,%9!', 
                                @updt_cmd,
                                @table_name,
                                @datachg_threshold,
                                @rowcnt_threshold,
                                @pagecnt_threshold,
                                @consumers,
                                @step_value,
                                @spercent,
                                @index_flag
            exec @err = @updt_cmd @table_name=@table_name 
                         , @datachg_threshold=@datachg_threshold 
                         , @rowcnt_threshold=@rowcnt_threshold
                         , @pagecnt_threshold=@pagecnt_threshold
                         , @consumers=@consumers
                         , @step_value=@step_value
                         , @spercent=@spercent
                         , @index_flag=@index_flag
            if( @err != 0 )
                select @status = @err
        end
    end
    --print 'status is %1!', @status
    return @status
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_db_update_statistics'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created stored procedure sp_jst_db_update_statistics'
    grant execute on sp_jst_db_update_statistics to public
end
go

-- Remove the sp_jst_svr_update_statistics if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_svr_update_statistics'
             and type = 'P'
             and (sysstat & 7) = 4 )

    drop proc sp_jst_svr_update_statistics
go
/* 
** sp_jst_svr_update_statistics 
** Version: 3.0
**
** This stored procedure runs the update statistic command on all the
** tables in all server databases if it is necessary to run the command.
**
** This stored procedure runs the update statistic command on all the
** database tables when the conditions for running the command are met.
** 
** 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 Update Statistics
** 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
** @index_flag  - indicates which form of update statistics will run
**                false = 1, true = 0
** @datachg_threshold - datachange threshold required to run update statistics
** @rowcnt_threshold - row count threshold required to run update stats command
** @pagecnt_threshold - page count threshold required to run update stats command
** @step_value  - the number of histogram steps
** @consumers   - the number of consumer processes
** @spercent    - the sampling rate, if any
*/
create procedure sp_jst_svr_update_statistics ( @index_flag int = 1,
                                               @datachg_threshold float = 100.0,
                                               @rowcnt_threshold int = 10000,
                                               @pagecnt_threshold int = 0, 
                                               @step_value int = NULL,
                                               @consumers int = 0,
                                               @spercent int = NULL )
as
begin

    print 'sp_jst_svr_update_statistics( index_flag = %1!, datachg_threshold = %2!, rowcnt_threshold = %3!, pagecnt_threshold = %4!, step_value = %5!, consumers = %6!, sampling_rate = %7! )', @index_flag, @datachg_threshold, @rowcnt_threshold, @pagecnt_threshold, @step_value, @consumers, @spercent 

    declare @db_name varchar(255)
          , @sp_name varchar(30)
          , @err int
          , @status int
          , @msgno int
          , @cmd_str varchar(300)
          , @max_id int
          , @db_id int

    select @db_id = 1
         , @status = 0

    -- Find all the databases on the server
    select @max_id = MAX(dbid)
    from master..sysdatabases
    where name not in ("tempdb","model","sybsystemprocs",
                       "sybsystemdb", "master", "sybmgmtdb")
                      and (status3 & 256) != 256

    while(@db_id <= @max_id )
    begin
        if( @db_id = @max_id )
        begin
            break
        end
        select @db_id = MIN(dbid)
          from master..sysdatabases
         where dbid > @db_id
           and name not in ("tempdb","model","sybsystemprocs",
                            "sybsystemdb", "master", "sybmgmtdb")
           and (status3 & 256) != 256
        select @db_name = name from master..sysdatabases where dbid = @db_id

        if( @db_name is not NULL )
        begin
            print 'processing db: %1!', @db_name
            select @cmd_str = @db_name + "..sp_jst_db_update_statistics"
            exec @err = @cmd_str @db_name,
                                 @index_flag,
                                 @datachg_threshold,
                                 @rowcnt_threshold,
                                 @pagecnt_threshold,
                                 @step_value,
                                 @consumers,
                                 @spercent
            if( @err < 0 )
                select @status = @err
        end
    end

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