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_check_reorg_space if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_check_reorg_space'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_check_reorg_space
go
/*
** sp_jst_check_reorg_space
** Version: 3.0
**
** This procedure gets the available database space and the size of the
** table to reorg and makes a comparison to ensure there is enough
** space to perform the reorg rebuild command. True is returned if
** there is space and false if there is not enough space
**
** 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
** @db_name - name of database that needs freespace
** @table_name - name of table for which space is required
** @enough_space - out parameter that indicates if there is enough space
**                 to perform a reorg rebuild command
*/
create procedure sp_jst_check_reorg_space( @db_name varchar(30),
                                           @table_name varchar(255),
                                           @enough_space varchar(5) out )
as
begin

    declare @sp_name varchar(30)
          , @usedspace_KB int
          , @freespace_KB int
          , @spaceneeded_KB numeric(20,2) 
          , @sp_str varchar(62)

    --initializations
    select @sp_name = 'sp_jst_check_reorg_space'

    --Get the amount of freespace associated with the database
    select @sp_str = @db_name + '..sp_jst_get_freespace'
    exec @sp_str @db_name, @freespace_KB out

    --Get the size of the space used by the database
    select @sp_str = @db_name + '..sp_jst_get_spaceused'
    exec @sp_str @table_name, @usedspace_KB out

    --Multiple the table space used by 1.6 
    select @spaceneeded_KB = @usedspace_KB * 1.6

    print 'usedspace %1!, freespace %2!, spaceneeded %3!', 
          @usedspace_KB, @freespace_KB, @spaceneeded_KB

    --Do we have enough space
    if( @spaceneeded_KB < @freespace_KB )
      begin
        select @enough_space = 'true'
      end
    else
      begin
        select @enough_space = 'false'
      end

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

