/* Job Scheduler Template */
/*
** Messages for sp_jst_reclaim_table_space
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19135, "NUMBER OF MINUTES is required when RESUME option is specified."
** 19138, "REORG RECLAIM SPACE for table %1! failed."
** 19139, "REORG RECLAIM SPACE for table %1! succeeded."
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reclaim Table Space Template '
go
-- Remove the sp_jst_reclaim_table_space if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_reclaim_table_space'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_reclaim_table_space
go
/* 
** sp_jst_reclaim_table_space
** Version: 3.0
**
** This stored procedure runs the reorg reclaim space command. It
** will reclaim unused space left on a page as a result of deletions and
** row-shortening updates. It can reclaim space for one or more tables. 
**
** 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 Table
** 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_names   - name of one or more tables to be reorganized.
** @ptn_name      - partition name
** @resume_flag   - indicates if command should resume after a prior
**                  run, resume can not be used without a number of
**                  minutes value, true = 0, false = 1
** @num_minutes   - number of minutes to run the reorg command
*/
create procedure sp_jst_reclaim_table_space( @table_names varchar(2560),
                                             @ptn_name varchar(255),
                                             @resume_flag int = 1,
                                             @num_minutes int = 0 )
as
begin
    print 'sp_jst_reclaim_table_space(table_names = %1!, ptn_name = %2!, resume_flag = %3!, num_minutes = %4! )', @table_names, @ptn_name, @resume_flag, @num_minutes 

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

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

    -- must have at least one table name
    select @table_names = rtrim(ltrim(@table_names))
    if( @table_names = NULL )
      begin
          raiserror 19048, @sp_name, 'Table Name'
          return -@@error
      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

    --create output file names for each db specified
    declare @table_name varchar(255)
    declare @cntr int, @end_pos int, @cur_pos int, @len int

    --loop through each table specified
    select @len = isnull(datalength(@table_names),0), @cur_pos=1

    while  @len > 0
      begin
        --get the filename, only, next or last
        select @end_pos = patindex('%[,]%', @table_names )
        if @end_pos <= 0
          begin
            -- only or last table name
            select @table_name = ltrim(@table_names)
            select @len = 0
          end
        else
          begin
            -- get the next name
            select @table_name = substring(@table_names,1,@end_pos-1)
            select @table_name = ltrim(rtrim(@table_name))

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

        -- build the execution string
        select @exec_str = 'reorg reclaim_space ' + @table_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 'reclaim space command: %1!', @exec_str
        exec( @exec_str )
        select @err = @@error
        if( @err != 0 )
          begin
              raiserror 19138, @table_name
              -- status will report error if any execution fails
              select @status = -@@error
          end
        else
          begin
              raiserror 19139, @table_name
          end
      end --end while

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

