/* Job Scheduler Template */
/*
** Messages for sp_jst_reclaim_index_space
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19134, "There are no indexes associated with table %1! for which to
**         reclaim space."
** 19135, "NUMBER OF MINUTES is required when RESUME option is specified."
** 19136, "REORG RECLAIM SPACE for table %1!, index %2! failed."
** 19137, "REORG RECLAIM SPACE for table %1!, index %2! succeeded."
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reclaim Index Space Template '
go
-- Remove the sp_jst_reclaim_index_space if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reclaim_index_space'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reclaim_index_space
go
/* 
** sp_jst_reclaim_index_space
** Version: 3.1
** 
** This stored procedure runs the reorg reclaim space command to 
** reorganize an index' data pages. The is no checking in this SP
** to determine if space needs to be reclaimed.  For now the reorg
** reclaim space will run each time the Job Scheduler job is run.
**
** 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 Reclaim Index
** Space 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 table to be reorganized.
** @index_names   - name of one or more indexes to be reorganized.
** @ptn_name      - partition name
** @resume_flag   - indicates if command should resume after a prior
**                  run, true = 0, false = 1
** @num_minutes   - number of minutes to run the reorg command
*/
create procedure sp_jst_reclaim_index_space( @table_name varchar(255)=NULL,
                                             @index_names varchar(2560)=NULL,
                                             @ptn_name varchar(255),
                                             @resume_flag int = 1,
                                             @num_minutes int = 0 )
as
begin
    print 'sp_jst_reclaim_index_space(table_name = %1!, index_names = %2!, ptn_name = %3!, resume_flag = %4!, num_minutes = %5! )', @table_name, @index_names, @ptn_name, @resume_flag, @num_minutes

    declare @sp_name varchar(30)
          , @err int
          , @status int
          , @exec_str varchar(900)
          , @ptn_ok int
          , @ptn_flag int

    -- Initialize variables
    select @sp_name = 'sp_jst_reclaim_index_space'
         , @ptn_ok = -1
         , @ptn_flag = 0
         , @status = 0

    -- check for required input values

    -- must have at least one table name
    select @table_name = rtrim(ltrim(@table_name))
    if( @table_name = NULL )
      begin
          raiserror 19048, @sp_name, 'Table Name'
          return -@@error
      end

    --  must have at least one index name
    select @index_names = rtrim(ltrim(@index_names))
    if( @index_names = NULL )
      begin
          raiserror 19048, @sp_name, 'Index Names'
          return -@@error
      end

    -- check for "all" in index_name variable
    if( lower(@index_names) = "all" )
      begin
        -- we need to get the names of all indexes associated with
        -- the specified table
        exec @err = sp_jst_get_index_names @table_name, @index_names out  
        if( @err < 0 )
          return @err
        if( @index_names = NULL )
          begin
              raiserror 19134, @table_name
              return -@@error
          end
      end

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

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

    --loop through each index specified
    declare @index_name varchar(255)
    declare @cntr int, @end_pos int, @cur_pos int, @len int

    select @len = isnull(datalength(@index_names),0), @cur_pos=1
    while  @len > 0
      begin
        --get the index name: only, next or last
        select @end_pos = patindex('%[,]%', @index_names )
        if @end_pos <= 0
          begin
            -- only or last index name
            select @index_name = ltrim(@index_names)
            select @len = 0
          end
        else
          begin
            -- get the next name
            select @index_name = substring(@index_names,1,@end_pos-1)
            select @index_name = ltrim(rtrim(@index_name))

            select @index_names = stuff(@index_names,1,@end_pos,NULL)
            select @len = isnull(datalength(ltrim(@index_names)),0)
          end

        -- build the execution string
        select @exec_str = 'reorg reclaim_space ' + @table_name + " "
                           + @index_name

        if( @ptn_flag = 1 )
            select @exec_str = @exec_str + " partition " + @ptn_name

        -- check for resume indicator
        if( @resume_flag = 0 )
          begin
            if( @num_minutes > 0 )
              begin
                select @exec_str = @exec_str + ' with resume, time = ' 
                                   + convert( varchar(4), @num_minutes)
              end
            else
              begin
                --invalid set of options
                raiserror 19135
                return -@@error
              end

          end
        else
          begin
            if( @num_minutes > 0 )
              begin
                select @exec_str = @exec_str + ', time = ' 
                                   + convert( varchar(4), @num_minutes)
              end
          end

        print 'executing:  %1!', @exec_str
        exec( @exec_str )
        select @err = @@error
        if( @err != 0 )
          begin
              raiserror 19136, @table_name, @index_name
              -- status will report error is any one execution fails.
              select @status = -@@error
          end
        else
          begin
              raiserror 19137, @table_name, @index_name
          end


      end --end while

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