/* Job Scheduler Template */
/*
** Messages for sp_jst_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!."
** 19406, "Current datachange value of %1! for table %2!, columns/indexes %3!, 
**         partition %4! does not meet or exceed the datachange threshold.
** 19407, "Datachange threshold is only applicable for ASE 15.0 and later.
**         This value is being ignored."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
--use sybsystemprocs
--go
print 'Creating Sybase Update Statistics Template stored procedures'
go
-- Remove the sp_jst_chkupdtstat_thresholds if it exists before 
-- attempting to create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_chkupdtstat_thresholds'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_chkupdtstat_thresholds
go
/* 
** sp_jst_chkupdtstat_thresholds 
** Version: 3.0
**
** If the user sets thresholds for row and page counts, then
** we should only run update statistics if one or both of those
** thresholds are met or surpassed.  
** If no thresholds are provided we will always run update statistics
**
** 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.  It is a support
** stored procedure and all values for it come from 
** sp_jst_update_statistics
**
** Parameter definitions
** @rowcnt_threshold   - row count threshold required to run update 
**                       stats command
** @pagecnt_threshold  - page count threshold required to run update 
**                       stats command
** @table_name         - name of the table for which statistics should be
**
** Return Value: 0 if one or both thresholds set and either is equaled or 
**                 succeeded
**               1 if both thresholds are 0, and should be ignored
**              -1 if neither threshold test is met, but at least one is set 
*/
create procedure sp_jst_chkupdtstat_thresholds( @rowcnt_threshold int = 0,
                                                @pagecnt_threshold int = 0,
                                                @table_name varchar(255) )
as
begin

    declare @rowcnt int
          , @pagecnt int
          , @db_id int
          , @table_id int

    -- Mark the thresholds as ignore until tested
    select @rowcnt = -1
         , @pagecnt = -1

    -- if no thresholds set, always run update statistics command
    if( (@rowcnt_threshold = 0) and (@pagecnt_threshold = 0) )
        return 1

    -- need to flush stats to table before checking
    --exec sp_flushstats @table_name
    select @db_id = db_id()
    select @table_id = object_id( @table_name )

    --check row and page counts to determine if we should preceed
    if( @rowcnt_threshold > 0 )
    begin
        select @rowcnt = row_count(@db_id, @table_id)
          --from systabstats ss, sysobjects so
         --where so.name = @table_name and so.id = ss.id and
         --                (ss.indid = 0 or ss.indid = 1)
    end

    if( @pagecnt_threshold > 0 )
    begin
        select @pagecnt = data_pages( @db_id, @table_id )
        --select @pagecnt = sum(ss.pagecnt) 
        --  from systabstats ss, sysobjects so
        -- where so.name = @table_name and so.id = ss.id
        --                 and (ss.indid = 0 or ss.indid = 1)
    end

--    print 'rowcnt = %1!', @rowcnt
--    print 'rowcnt_threshold = %1!', @rowcnt_threshold
--    print 'pgcnt = %1!', @pagecnt
--    print 'pgcnt_threshold = %1!', @pagecnt_threshold

    -- if the current page and row counts are greater than or equal to
    -- their respective thresholds, update statistics.
    -- if one of the thresholds is set to 0, ignore that threshold
    if( @rowcnt = -1 )
    begin
        if( @pagecnt >= @pagecnt_threshold )
        begin
            return 0
        end
    end
    else
    begin
        if( @pagecnt = -1 )
        begin
           if( @rowcnt >= @rowcnt_threshold )
           begin
               return 0
           end
           else
           begin
               return -1
           end
        end
        else
        begin
            if( (@rowcnt >= @rowcnt_threshold) or 
                   (@pagecnt >= @pagecnt_threshold) )
            begin
                return 0
            end
            else
            begin
                return -1
            end
        end
    end
end
go
if exists( select 1 from sysobjects 
           where name = 'sp_jst_chkupdtstat_thresholds'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created stored procedure sp_jst_chkupdtstat_thresholds'
    grant execute on sp_jst_chkupdtstat_thresholds to public
end
go
-- Remove the sp_jst_update_statistics if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_update_statistics'
             and type = 'P'
             and (sysstat & 7) = 4 )

    drop proc sp_jst_update_statistics
go

/* 
** sp_jst_update_statistics 
** Version: 3.0
**
** This stored procedure runs the update statistic command using the
** input provided to determine the form of the command to run and
** if it is necessary to run the 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 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
** @table_name  - name of the table for which statistics should be
**                updated
** @table_owner - name of the table owner
** @columns     - one or more (comma-separated) column names
** @index_name  - name of an index associated with the table
** @ptn_name    - partition name to use in determining datachange value
**                and where the update stats will occur, when provided
** @index_flag  - indicates which form of update statistics will run
**                true = 0, index form; false = 1, non index form
** @step_value  - the number of histogram steps
** @consumers   - the number of consumer processes
** @spercent    - the sampling rate, if any
** @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
*/
create procedure sp_jst_update_statistics ( @table_name varchar(255) = NULL,
                                            @table_owner varchar(30) = NULL,
                                            @columns varchar(2560) = NULL,
                                            @index_name varchar(255) = NULL,
                                            @ptn_name varchar(255) = NULL,
                                            @index_flag int = 1,
                                            @step_value int = NULL,
                                            @consumers int = 0,
                                            @spercent int = NULL,
                                            @datachg_threshold float = 0,
                                            @rowcnt_threshold int = 0,
                                            @pagecnt_threshold int = 0 )
as
begin

    print 'sp_jst_update_statistics( table_name = %1!, table_owner = %2!, columns = %3!, index_name = %4!, ptn_name = %5!, index_flag = %6!, step_value = %7!, consumers = %8!, sampling_rate = %9!,  datachg_threshold = %10!, rowcnt_threshold = %11!, pagecnt_threshold = %12! )',
               @table_name, @table_owner, @columns, @index_name, 
               @ptn_name, @index_flag, @step_value, @consumers, 
               @spercent, @datachg_threshold, @rowcnt_threshold, 
               @pagecnt_threshold  

    -- supported syntax:  
    --  update statistics <table_name> 
    --             [ [partition <data_partition_name>] [(column_list)]  |
    --               <index_name> [partition <index_partition_name>] ] 
    --             [using step values]
    --             [with consumers = consumers], 
    --             [sampling = spercent percent]
    --
    --  update index statistics <table_name> 
    --             [ [partition <data_partition_name>] [(column_list)]  |
    --               <index_name> [partition <index_partition_name>] ] 
    --             [using step <values>]
    --             [with consumers = <consumers>], 
    --             [sampling = <sampling percent>]
    --
    -- Example:
    --  update statistics authors(auth_id) with sampling = 5 percent
    --

    declare @updt_cmd varchar(900)
          , @ase_version varchar(50)
          , @status int
          , @with_flag varchar(5)
          , @ptn_flag int
          , @rel_15 int
          , @index_len int
          , @cols_len int
          , @rel_12503 int
          , @datachg_ok int
          , @sp_name varchar(30)
          , @err int
          , @msgno int
          , @colndx_flag int
          , @colndx_names varchar(1280)
          , @dc_value float

    select @with_flag = 'true'
         , @rel_12503 = -1
         , @ptn_flag = 0
         , @rel_15 = -1
         , @datachg_ok = 0
         , @dc_value = 0.0
         , @colndx_flag = -1 --neither columns nor index
         , @status = 0
         , @err = 0
    
    -- check for required input values
    -- must have a table name
    select @table_name = ltrim(rtrim(@table_name))
    if( @table_name is NULL )
    begin
        raiserror 19048, @sp_name, 'Table Name'
        return -@@error
    end

    -- strip blanks
    select @index_name = ltrim(rtrim(@index_name))
    select @columns = ltrim(rtrim(@columns))

    exec @rel_15 = sp_jst_valid_ase_version '15.0'
    if( @rel_15 = 0 )
    begin
        select @ptn_name = ltrim(rtrim(@ptn_name))

        if( @ptn_name is not NULL and @ptn_name != "" )
        begin
            -- ptn_flag is only 1 if release 15.0 or later server and 
            -- partition name was supplied.
            --print 'partition is %1!', @ptn_name
            select @ptn_flag = 1
        end
    end

    -- start building the command
    if( @index_flag = 0 )
    begin
        select @colndx_flag = 1
        select @index_flag = 1
        select @updt_cmd = 'update index statistics ' + @table_name
        if( @index_name is not NULL )
        begin
            select @updt_cmd = @updt_cmd + " " + @index_name
        end
        if( @ptn_flag = 1 )
        begin
            select @updt_cmd = @updt_cmd + ' partition ' + @ptn_name
        end
        --print 'updt_cmd is: %1!', @updt_cmd
    end
    else
    begin
        select @updt_cmd = 'update statistics ' + @table_name
        select @index_flag = 0
        --  do columns option over index option
        if( @columns is not NULL )   
        begin
            select @colndx_flag = 0
            select @colndx_names = @columns
            if( @ptn_flag = 1 )
            begin
                select @updt_cmd = @updt_cmd + ' partition ' + @ptn_name
            end
            select @updt_cmd = @updt_cmd + ' (' + @columns + ')'
        end
        else
        begin
            if( @index_name is not NULL )   
            begin
                select @colndx_flag = 1
                select @colndx_names = @index_name
                select @updt_cmd = @updt_cmd + ' ' + @index_name
            end
            -- we are updating stats on the table only
            else
            begin
                select @colndx_flag = -1
                select @colndx_names = NULL
            end

            if( @ptn_flag = 1 )
            begin
                select @updt_cmd = @updt_cmd + ' partition ' + @ptn_name
            end
        end
    end
    --print 'updt_cmd is: %1!', @updt_cmd

    -- check the data change threshold
    if( @rel_15 = 0 )
    begin
        if( @datachg_threshold is not NULL and @datachg_threshold != 0 )
        begin
            if( @datachg_threshold < 0 )
            begin
                raiserror 19123, '@datachg_threshold', @datachg_threshold
                return -@@error
            end
            else  
            begin
                exec @status = sp_jst_get_datachange @table_name, @index_flag,
                                                     @colndx_flag, 
                                                     @colndx_names, 
                                                     @ptn_name, @dc_value out
                select @err =  @@error 
                if( @err <> 0 )
                    return -@err
                if( @status < 0 )
                    return @status
                --print '@dc_value is: %1!', @dc_value
                --print '@datachg_threshold is: %1!', @datachg_threshold

                --if threshold not met, check the other thresholds
                --if met, other thresholds don't matter as this is an ||
                --process.
                if( @dc_value <  @datachg_threshold )
                begin
                    raiserror 19406, @dc_value, @table_name, @colndx_names, 
                                     @ptn_name
                    exec @status = sp_jst_chkupdtstat_thresholds 
                                                     @rowcnt_threshold,
                                                     @pagecnt_threshold,
                                                     @table_name
                    if( @status < 0 or @status = 1 )
                    begin
                        raiserror 19130, @table_name
                        return 0
                    end
                end
            end
        end --end datachange threshold provided

        -- datachange threshold not provided, check other thresholds
        else
        begin
            exec @status = sp_jst_chkupdtstat_thresholds @rowcnt_threshold,
                                                         @pagecnt_threshold,
                                                         @table_name
            if( @status < 0 )
            begin
                raiserror 19130, @table_name
                return 0
            end
        end
    end --end >= 15.0 release

    -- not 15.0 or later release
    else
    begin
        -- report unused datachange value, but continue on
        if( @datachg_threshold is not NULL and @datachg_threshold != 0 )
        begin
            raiserror 19407
        end

        -- check that we meet the minimum values for performing an update
        exec @status = sp_jst_chkupdtstat_thresholds @rowcnt_threshold,
                                                     @pagecnt_threshold,
                                                     @table_name
        if( @status < 0 )
        begin
            raiserror 19130, @table_name
            return 0
        end
    end

    -- add step value 
    if( @step_value is not NULL )
    begin
        select @updt_cmd = @updt_cmd + " using " + 
                           convert( varchar(8),@step_value) + " values"
    end
    
    -- add consumers if specified
    if( @consumers > 0 )
    begin
        select @updt_cmd = @updt_cmd + " with consumers = " + 
                                    convert(varchar(8),@consumers)
        select @with_flag = 'false'
    end

    -- ensure valid sampling rate
    if( @spercent is not NULL )
    begin
        if( @spercent < 0 or @spercent > 100 )
        begin
             raiserror 19123, '@spercent', @spercent
             return -@@error
        end
        else
        begin
            if( @spercent > 0 )
            begin
                --determine if sampling is viable by checking the version number
                exec @rel_12503 = sp_jst_valid_ase_version '12.5.0.3'  
                if( @rel_12503 = 0 )
                begin
                    if( @with_flag = 'false' )
                    begin
                        select @updt_cmd = @updt_cmd + ", sampling = " 
                    end
                    else
                    begin
                        select @updt_cmd = @updt_cmd + " with sampling = " 
                    end

                    select @updt_cmd = @updt_cmd + convert(varchar(8),@spercent)
                                             + " percent"
                end
            end
        end
    end --sampling check

    --set the user is one is provided
    select @table_owner = rtrim(ltrim( @table_owner ))
    if( @table_owner is not NULL )
    begin
        setuser @table_owner
    end

    --ready to run the update stats command
    print @updt_cmd
    execute( @updt_cmd )

    --Log success or failure for update stats command
    select @err = @@error
    if( @err <> 0 )
    begin
        --print 'err is %1!', @err
        if( @index_name is NULL )  
        begin
            raiserror 19126, @table_name
        end
        else
        begin
            raiserror 19127, @table_name, @index_name
        end
        return -@@error
    end
    else
    begin
        if( @index_name is NULL )  
        begin
            raiserror 19128, @table_name
        end
        else
        begin
            raiserror 19129, @table_name, @index_name
        end
    end

    --for recompile of procs and triggers referencing the table
    exec @err = sp_recompile @table_name
    if( @err <> 0 )
    begin
        return -@err
    end
    else
    begin
        return @err
    end
end
go
if exists( select 1 from sysobjects 
           where name = 'sp_jst_update_statistics'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'created stored procedure sp_jst_update_statistics'
    grant execute on sp_jst_update_statistics to public
end
go

