/* Job Scheduler Template */
/*
** Messages for sp_jst_get_datachange
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Generic stored procedure used by Sybase Job Scheduler Template jobs'
go
-- Remove the sp_jst_get_datachange if it exists before attempting to
-- create it
if exists( select 1 from sysobjects 
           where name = 'sp_jst_get_datachange'
             and type = 'P' 
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_datachange
go
/*
** sp_jst_get_datachange
** Version: 3.0
**
** This procedure takes the data needed by the datachange function and
** calculates the current datachange level. 
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that 
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @table_name   - the name of the table for which we want index names
** @ndxform_flag - value of 0 indicates non ndx form, value of 1 indicates
**                 index form of update statistics command
** @colndx_flag  - value of 0 indicates column names, value of 1 indicates
**                 index names
** @colndx_names - a comma-separated list of index or column names
** @ptn_name     - a partition name if data change is limited to a partition
** @dc_value     - the current level of datachange for this specified objects 
*/
create procedure sp_jst_get_datachange( @table_name varchar(255),
                                        @ndxform_flag int=0,
                                        @colndx_flag int=0,
                                        @colndx_names varchar(1280),
                                        @ptn_name varchar(255),
                                        @dc_value float out)
as
begin

    print 'sp_jst_get_datachange( @table_name = %1!, @ndxform_flag = %2!, @colndx_flag = %3!, @colndx_names = %4!, @ptn_name = %5! )', @table_name, @ndxform_flag, @colndx_flag, @colndx_names, @ptn_name

    declare @col_names varchar(2560) 
          , @sp_name varchar(30)
          , @max_dc float
          , @cur_dc float
          , @len int
          , @end_pos int
          , @cur_pos int
          , @indid int
          , @table_id int

    --mjm testcode
    --declare @cur_db varchar(30)
    --select @cur_db = db_name()
    --print 'get datachange db is %1!', @cur_db

    -- Initialize variables
    select @sp_name = 'sp_jst_get_datachange'
         , @max_dc = 0.0
         , @table_id = id from sysindexes where name = @table_name

    --print 'table id is: %1!', @table_id

    -- check for required input values
    -- must have a table name
    select @table_name = ltrim(rtrim(@table_name))
    select @len = isnull(datalength(@table_name),0)
    if( @len = 0 )
      begin
          raiserror 19048, @sp_name, '@table_name'
          return -@@error
      end

    -- strip blanks
    select @colndx_names = ltrim(rtrim(@colndx_names))
    select @len = isnull(datalength(@colndx_names),0)
    if( @len = 0 )
      begin
        select @colndx_names = NULL
      end

    select @ptn_name = ltrim(rtrim(@ptn_name))
    select @len = isnull(datalength(@ptn_name),0)
    if( @len = 0 )
      begin
        select @ptn_name = NULL
      end

    --
    -- first check if we are checking datachange for the index form of
    -- the update statistics command.
    --
    -- for index form 
    --    but no index name supplied, retrieve all indexes and check 
    --    the leading column for each.  
    --
    -- for non index form 
    --    if no column or index supplied, check table
    --
    --    if column supplied, get max datachange for all columns
    --
    --    if index supplied, get datachange for index' leading column
    --

    if( @ndxform_flag = 1 ) --index case
      begin
        declare @i int
              , @column_name varchar(255)
              , @ndx_name varchar(255)

        select @column_name = ""
             , @i = 1
        --print "processing index form of datachange"

        -- if no ndx is provided, get the indexes for the table
        -- then for each one we will get the leading column
        if( @colndx_names = NULL )
          begin
            --print "no indexes provided"
            exec sp_jst_get_index_names @table_name, @colndx_names out
            --print 'index names retrieved are: %1!', @colndx_names

            select @len = isnull(datalength(@colndx_names),0)
            while @len > 0
              begin
                --get the index name, only, next or last
                select @end_pos = patindex('%[,]%', @colndx_names )
                if @end_pos <= 0
                  begin
                    -- only or last ndx_name
                    select @ndx_name = ltrim(@colndx_names)
                    select @len = 0
                  end
                else
                  begin
                    -- get the next name
                    select @ndx_name = substring(@colndx_names,1,@end_pos-1)
                    select @ndx_name = ltrim(rtrim(@ndx_name))
                    -- remove the index currently being processing
                    select @colndx_names = stuff(@colndx_names,1,@end_pos,NULL)
                    select @len = isnull(datalength(ltrim(@colndx_names)),0)
                  end

                select @indid = indid from sysindexes 
                where name = @ndx_name and id = @table_id

                select @column_name = index_col(@table_name, @indid, 1)

                if( @col_names = NULL )
                  select @col_names = @column_name
                else
                  select @col_names = @column_name + "," + @col_names
              end --end while more indexes

          end --if no ndx name

        else --ndx provided
          begin
              --print 'index names %1! provided', @ndx_name
              select @indid = indid from sysindexes 
              where name = @ndx_name and id = @table_id

              select @column_name = index_col(@table_name, @indid, 1)
              select @col_names = @column_name
          end 
      end --ndx form

    else -- not ndx form
      begin
         --print 'not datachange index form'
         -- if not ndx form, we could have column, ndx or table request
        if( @colndx_names = NULL ) --table case
          begin
            select @col_names = NULL
            --print 'not ndx form, col_names set to NULL'
          end

        else -- col or index case
          begin
            if( @colndx_flag = 0 ) -- col case
              begin
                select @col_names = @colndx_names
                --print 'not ndx form, col case, col_names set to colndx_names'
              end

            if( @colndx_flag = 1 ) -- index case
              begin
                select @indid = indid from sysindexes 
                where name = @colndx_names and id = @table_id

                select @column_name = index_col(@table_name, @indid, 1)
                select @col_names = @column_name
                --print 'not ndx form, ndx case, col_names set to column_name'
              end
          end
      end -- not ndx form

    --print 'getting ready to start datachange checks'
    --print 'using %1! column(s) to determine datachange', @col_names
    --if( @col_names = NULL )
    --  begin
    --    print "column names is null" 
    --  end
    --else
    --  begin
    --    print 'col_names are *%1!*', @col_names
    --  end

    --loop through each column name and find max datachg for all
    if( @col_names = NULL )
      begin
         --declare @dc_cmd3 varchar(700)
         --select @dc_cmd3 = 'datachange(' + @table_name + ',' 
         --                      +  @ptn_name + ', NULL )'
        select @max_dc = datachange(@table_name, @ptn_name, NULL) 
        --print 'dc_cmd3 is: %1!, max_dc is: %2!', @dc_cmd3, @max_dc
      end
    else
      begin
        --print 'found column names, %1!', @col_names
        select @len = isnull(datalength(ltrim(@col_names)),0)
        --print 'col_names length is %1!', @len
        while( @len > 0 )
          begin
            --print 'getting dc value from column names'
            --get the name, only, next or last
            select @end_pos = patindex('%[,]%', @col_names )
            --print 'end_pos is %1!', @end_pos
            if @end_pos <= 0
              begin
                -- only or last name case
                select @column_name = ltrim(rtrim(@col_names))
                     , @len = 0
                --print 'only or last name is %1!', @column_name
              end
            else
              begin
                -- get the next name
                select @column_name = substring(@col_names,1,@end_pos-1)
                select @column_name = ltrim(rtrim(@column_name))

                -- remove the name we are currently processing
                select @col_names = stuff(@col_names,1,@end_pos,NULL)
                select @len = isnull(datalength(ltrim(@col_names)),0)
              end

            --mjm - test code
            declare @dc_cmd2 varchar(700)
            select @dc_cmd2 = 'datachange(' + @table_name + ',' 
                               +  @ptn_name + ', ' + @column_name + ')'
            select @cur_dc = datachange(@table_name, @ptn_name, @column_name) 
            --print 'dc_cmd2 is: %1!, cur_dc is: %2!', @dc_cmd2, @cur_dc
            --print 'max_dc is %1!', @max_dc
            if( @cur_dc > @max_dc )
              begin
                --print 'replacing max_dc with cur_dc'
                select @max_dc = @cur_dc
              end
          end  --while 
        --print "max_dc is %1!", @max_dc
      end  --while 

    select @dc_value = @max_dc
    return 0
end
go
if exists( select 1 from sysobjects 
           where name = 'sp_jst_get_datachange'
             and type = 'P' 
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_get_datachange has been created'
    grant execute on sp_jst_get_datachange to public
end
go
