/* Job Scheduler Template */
/*
** Messages for sp_jst_rebuild_indexes
**
** 17733, "There is no table named '%1!'."
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19146, "Not enough space to run Reorg Rebuild command on table %1!."
*/
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Reorg Rebuild Indexes Template '
go
-- Remove the sp_jst_rebuild_indexes if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_rebuild_indexes'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_rebuild_indexes
go
/* 
** sp_jst_rebuild_indexes
** Version: 3.1
**
** This stored procedure runs the reorg rebuild command using 
** the table index form of the command. It will back-up the database
** if the dump_flag is set to true.
**
** 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 Reorg Rebuild
** Indexes 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.
** @ndx_ptn_name  - name of partition index
** @db_name       - name of the database containing the table, this
**                  value is required to check the size of the table
**                  and verify if there is enough space for the reorg
** @dump_flag     - indicates if database should be dumped after reorg
**                  true = 0, false = 1
** @dump_location - device name or path where db dump will occur.
*/
create procedure sp_jst_rebuild_indexes( @table_name varchar(255)=NULL,
                                         @index_names varchar(2560)=NULL,
                                         @ndx_ptn_name varchar(255)=NULL,
                                         @db_name varchar(30) = NULL,
                                         @dump_flag int = 1,
                                         @dump_location varchar(128)=NULL )
as
begin

    print 'sp_jst_rebuild_indexes(table_name = %1!, index_names = %2!, ndx_ptn_name = %3!, db_name = %4!, dump_flag = %5!, dump_location = %6! )', @table_name, @index_names, @ndx_ptn_name, @db_name, @dump_flag, @dump_location  

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

    select @sp_name = 'sp_jst_rebuild_indexes'  
         , @reorg_cnt = 0
         , @ptn_ok = -1
         , @ptn_flag = 0
         , @status = 0
         , @err = 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 Name'
        return -@@error
    end

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

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

    --  check for "all" option in index names
    if( lower(@index_names) = "all" )
    begin
        -- we need to get all the indexes associated with the table
        exec @err = sp_jst_get_index_names @table_name, @index_names out  
        if( @err < 0 )
            return @err
    end

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

    -- Check if table exists in database
    if not exists( select 1 from sysobjects where name = @table_name )
    begin
        raiserror 17733, @table_name
        return -@@error
    end

    -- Check if there is enough room for the reorg
    declare @enough_space varchar(5)
    exec sp_jst_check_reorg_space @db_name, @table_name,
                                  @enough_space out
    if( @enough_space != 'true' )
    begin
        raiserror 19146, @table_name
        return -@@error
    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

        select @cmd =  "reorg rebuild " + @table_name + " " + @index_name 
        if( @ptn_flag = 1 )
            select @cmd = @cmd + " partition " + @ndx_ptn_name

        --execute the reorg rebuild command
        print "reorg rebuild command: %1!", @cmd
        exec( @cmd )
        select @err = @@error
        if( @err != 0 )
        begin
            select @status = -@err
        end
        else
        begin
            select @reorg_cnt = @reorg_cnt + 1
        end

    end --end while

    -- should we dump the db? 
    if( @dump_flag = NULL )
        select @dump_flag = 1

    if( @dump_flag = 0  and @reorg_cnt > 0 )
    begin
        --  check for dump location
        select @dump_location = rtrim(ltrim(@dump_location))
        exec @err = sp_jst_dump_dbs_to_disk @db_name, @dump_location
        if( @err != 0 )
            select @status = @err
    end

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