/* Job Scheduler Template */
/*
** Messages for stored procedures in this file
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19123, "%1! value of %2! is invalid."
** 19147, "Transaction log backup for database %1! failed on %2!."
** 19148, "Transaction log backup for database %1! succeeded on %2!."
** 19240, "Transaction log dump for database %1! was skipped because row and
**           time thresholds of %2! and %3! were not met."
*/

print 'Creating Sybase Back-up Log Template stored procedures'
go
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating Stored Procedures for Sybase Dump Logs Template'
go
-- Remove the sp_jst_dump_tran_log_to_disk if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_tran_log_to_disk'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_dump_tran_log_to_disk
go
/*
** sp_jst_dump_tran_log_to_disk
** Version: 1.0
**
** This stored procedure forms the dump filename and dump tran command
** for the log to be dumped.  It then executes the dump command.
**
** 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 Backup Transaction
** Log Template"
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from 
** sp_jst_dump_tran_logs_to_disk
**
** Parameter definitions
** @db_name        - name of database whose log should be dumped
** @dump_location  - device name or path to location where dump file
**                   will be created.
** @dump_filename  - flag indicating if server name should be included
** @compress_flag  - indicates if compression should be used
** @compress_value - indicates compression level
** @truncate_flag  - indicates if the "no_truncate" option should be
**                   specified.  A value of true adds this option to 
**                   the dump command.
*/
create procedure sp_jst_dump_tran_log_to_disk( @db_name varchar(30),
                                       @dump_location varchar(128),
                                       @dump_filename varchar(250),
                                       @compress_flag varchar(5),
                                       @compress_value int,  
                                       @truncate_flag int )
as
begin

print 'in sp_jst_dump_tran_log_to_disk'
    print 'syb_dump_tran_log_to_disk(db_name = %1!, dump_location = %2!, dump_filename = %3!, compress_flag = %4!, compress_value = %5!, truncate_flag = %6! )', @db_name, @dump_location, @dump_filename, @compress_flag, @compress_value, @truncate_flag

    declare @tmp_filename varchar(128)
          , @dump_params varchar(500)
          , @dump_time varchar(8)
          , @err int
          , @sp_name varchar(30)
          , @path_separator char(1)
          , @str_len int
          , @end_char char(1)


    -- Initialize variables
    select @sp_name = 'sp_jst_dump_db_to_disk'

    --  use dump location if provided
    select @dump_location = rtrim(ltrim(@dump_location))
    if( @dump_location != NULL and char_length(@dump_location)>0)
      begin
        -- make sure that the dump_location has a / or \ at the end
        -- question: what do we do if no path separator in dump_location
        select @str_len = char_length( @dump_location )
        select @end_char = substring( @dump_location, @str_len, @str_len)
        if( @end_char != '/' and @end_char != '\' )
          begin
            declare @pos int
            select @pos = charindex( '/', @dump_location )
            if( @pos > 0 )
              begin
                select @dump_location = @dump_location + '/'
              end
            else
              begin
                select @pos = charindex( '\', @dump_location )
                if( @pos > 0 )
                  begin
                    select @dump_location = @dump_location + '\'
                  end
              end
          end
      end -- if dump_location != NULL

    -- all values are expected to be filled in by previously
    -- stored procedures
    select @tmp_filename = ltrim(rtrim(@dump_filename)) + "Log.dmp"  
 
    --Build the dump filename
    if( @compress_flag = 'true' )
      begin
        select @dump_params = "compress::" +
                                 convert(char(1),@compress_value) + "::"
        if( @dump_location != NULL )
          begin
              select @dump_params = @dump_params + @dump_location 
                                                   + @tmp_filename 
          end
        else
          begin
              select @dump_params = @dump_params + @tmp_filename 
          end
      end
    else  --no compression
      begin
        if( @dump_location != NULL )
          begin
              select @dump_params =  @dump_location + @tmp_filename 
          end
        else
          begin
              select @dump_params = @tmp_filename 
          end
      end

    if( (@db_name = 'master') or (@truncate_flag = 1) )
      begin
        print '**dump transaction %1! to %2! with no_truncate**', 
              @db_name, @dump_params 
        if( @dump_params != NULL )
            dump transaction @db_name to @dump_params with no_truncate
      end
    else 
      begin
        print '**dump transaction %1! to %2!**', @db_name, @dump_params 
        if( @dump_params != NULL )
            dump transaction @db_name to @dump_params
        else
            dump transaction @db_name to ""
      end 
 
    --Log success or failure for each db
    if( @@error != 0 )
      begin
          select @dump_time = convert( char(8), getdate(), 3 )
          raiserror 19147, @db_name, @dump_time
          return -@@error
      end
    else
      begin
          select @dump_time = convert( char(8), getdate(), 3 )
          raiserror 19148, @db_name, @dump_time
          return @@error
      end

    --return 0
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_tran_log_to_disk'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_dump_tran_log_to_disk has been created'
    grant execute on sp_jst_dump_tran_log_to_disk to public
end
go
-- Remove the sp_jst_chk_row_threshold if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_chk_row_threshold'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_chk_row_threshold
go
/*
** sp_jst_chk_row_threshold
** Version: 1.0
**
** Parameters definitions
** @db_name        - name of the database whose transaction log should 
**                   be dumped
** @row_threshold  - indicates how many rows must be in the log before
**                   it should be dumped.  This value is not used if the
**                   time threshold is exceeded.
*/
create procedure sp_jst_chk_row_threshold( @db_name varchar(30),
                                          @row_threshold int )
as
begin

    print 'sp_jst_chk_row_threshold(db_name=%1!, row_threshold=%2! )', 
               @db_name, @row_threshold

    declare @rows int

    select @rows = count(*) from syslogs

    print 'comparing num rows and row threshold'
    if( @rows > @row_threshold )
        return 0
    else
        return -1
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_chk_row_threshold'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_chk_row_threshold has been created'
    grant execute on sp_jst_chk_row_threshold to public
end
go
-- Remove the sp_jst_chk_time_threshold if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_chk_time_threshold'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_chk_time_threshold
go
/*
** sp_jst_chk_time_threshold
** Version: 1.0
**
** Parameters definitions
** @db_name        - name of the database whose transaction log should 
**                   be dumped
** @time_threshold - indicates how maximum amount of time that should
**                   pass between log dumps. This value is not used if the
**                   row threshold is exceeded. Time is measured in
**                   minutes or hours. Format for time string is a
**                   number followed by a unit identifier, where valid 
**                   identifiers are "hh" and "mi"
*/
create procedure sp_jst_chk_time_threshold( @db_name varchar(30),
                                            @time_threshold varchar(5) )
as
begin

    print 'sp_jst_chk_time_threshold(db_name=%1!, time_threshold=%2! )', 
               @db_name, @time_threshold

    declare @timediff int
          , @msg varchar(255)
          , @dumpdate datetime
          , @min_threshold int
          , @unit_pos int  
          , @min_ind  varchar(2)  
          , @hr_ind  varchar(2)  

    select @min_ind = 'mi', @hr_ind = 'hh'

    --check that we have a threshold to surpass
    select @unit_pos = charindex( @min_ind, @time_threshold )
    if( @unit_pos > 0 )
      begin
        --threshold in minutes
        select @min_threshold = convert( int, 
                                    substring( @time_threshold,
                                               1, @unit_pos-1 ) )
      end
    else
      begin
        select @unit_pos = charindex( @hr_ind, @time_threshold )
        if( @unit_pos > 0 )
          begin
            --threshold in hour
            select @min_threshold = ( convert( int, 
                                       substring( @time_threshold,
                                       1, @unit_pos-1 ) ) * 60 )
          end
        else
          begin
            --threshold in minutes
            select @min_threshold = convert( int, 
                                     substring( @time_threshold, 1, 
                                      datalength(@time_threshold)-1 ) )
          end
      end

    select @dumpdate = dumptrdate from master..sysdatabases 
                       where name = @db_name

    --if not previous dumpdate, dump the log
    if( @dumpdate = NULL )
        return 0

    select @timediff = datediff( mi,
                                 @dumpdate, 
                                 getdate() ) 

    if( @timediff >= @min_threshold )
        return 0
    else
        return -1
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_chk_time_threshold'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_chk_time_threshold has been created'
    grant execute on sp_jst_chk_time_threshold to public

end
go
-- Remove the sp_jst_dump_tran_logs_to_disk if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_dump_tran_logs_to_disk'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_dump_tran_logs_to_disk
go
/*
** sp_jst_dump_tran_logs_to_disk
** Version: 3.1
**
** This stored procedure backs up the transaction log for one or
** more databases.
**
** 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 Dump Logs
** 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.
**
** Parameters definitions
** @db_names       - one or more names of databases for which 
**                   transaction logs should be dumped
** @dump_location  - device name or path to location where dump file
**                   will be created.
** @use_srvr_name  - flag indicating if server name should be included
**                   in the dump filename; true=0, false=1
** @date_fmt       - indicates what date format to use in dump filename
** @compress_value - indicates compression level
** @truncate_flag  - indicates if the log will be truncated after the
**                   dump.  Default is true (0) -- log will be truncated
** @row_threshold  - indicates the minimum number of rows in the syslogs
**                   table before the transaction log should be dumped.
**                   This value works in conjunction with the
**                   time_threshold value so that if either of the thresholds 
**                   are exceeded at job runtime, the transaction log will be 
**                   dumped.
**                   time threshold is exceeded.
** @time_threshold - indicates how minimum amount of time that should
**                   elapse between log dumps. This value works in conjunction 
**                   with the row_threshold value so that if either of the 
**                   thresholds are exceeded at job runtime, the transaction 
**                   log will be dumped.
**                   Time is measured in minutes or hours. Format for time 
**                   string is a number followed by a unit identifier, where 
**                   valid identifiers are "hh" and "mi"
**
*/
create procedure sp_jst_dump_tran_logs_to_disk ( @db_names varchar(500),
                                    @dump_location varchar(128)=NULL,
                                    @use_srvr_name int = 1,
                                    @date_fmt int=0,
                                    @compress_value int = 0,
                                    @truncate_flag int = 0,
                                    @row_threshold int = 0,
                                    @time_threshold varchar(5)='0mi')
as
begin

    print 'sp_jst_dump_tran_logs_to_disk( db_names = %1!, dump_location = %2!, use_srvr_name = %3!, date_fmt = %4!, compress_value = %5!, truncate_flag = %6!, @row_threshold = %7!, @time_threshold = %8! )', @db_names, @dump_location, @use_srvr_name, @date_fmt, @compress_value, @row_threshold, @time_threshold, @truncate_flag  

    declare @dump_filename varchar(225)
          , @ase_version_ok int
          , @ase_version varchar(50)
          , @tmp_string varchar( 128)
          , @compress_flag varchar(5)
          , @date_str varchar(25)
          , @err int
          , @sp_name varchar(30)
          , @status int


    select @ase_version_ok=1
         , @sp_name = 'sp_jst_dump_tran_logs_to_disk' 
         , @status=0

    --check for required input values
    --  must have at least one DB name
    select @db_names = ltrim(rtrim(@db_names))
    if( @db_names = NULL )
      begin
        raiserror 19048, @sp_name, 'database name'
        return -@@error
      end

    --  check for valid compession level, 0 - 9
    if( @compress_value < 0 or @compress_value > 9 )
      begin
        raiserror 19123, 'Compression', @compress_value 
        return -@@error
      end

    --  check for "all" option in database names
    if( lower(@db_names) = "all" )
      begin
        -- we need to dump logs for all databases on the server except 
        -- model & tempdb
        -- call method to get all db_names
        exec @err = sp_jst_database_names @db_names out  
        if( @err < 0 )
          return @err
      end

    --determine if user selected compression, if so check db version to
    --see if it is available
    exec @ase_version_ok = sp_jst_valid_ase_version '12.5'

    if( (@ase_version_ok=0) and (@compress_value > 0) )
        select @compress_flag = 'true'
    else
        select @compress_flag = 'false'

    --  set truncation flag to 1 (false) if not specified
    if( @truncate_flag = NULL )
       select @truncate_flag = 1

    --create output file names for each db specified
    declare @base_names varchar(500)
          , @db_name varchar(200)
          , @cntr int
          , @end_pos int
          , @cur_pos int
          , @len int

    select @base_names = @db_names 

    select @len = isnull(datalength(@base_names),0), @cur_pos=1

    if( @use_srvr_name = NULL )
        select @use_srvr_name = 1

    if( @date_fmt = NULL )
        select @date_fmt = 0
    if( @date_fmt > 0 )
        exec @err = sp_jst_get_datestr @date_fmt, @date_str out
    if( @err = -1 )
        return @err
    if( @date_str = NULL )
        select @date_fmt = 0  

    --loop through each db specified
    while  @len > 0
      begin
        --get the filename, only, next or last
        select @end_pos = patindex('%[,]%', @base_names )
        if @end_pos <= 0
          begin
            -- only or last db name
            select @db_name = ltrim(rtrim(@base_names))
            select @len = 0
          end
        else
          begin
            -- get the next db name
            select @db_name = substring(@base_names,1,@end_pos-1)
            select @db_name = ltrim(rtrim(@db_name))
            select @base_names = stuff(@base_names,1,@end_pos,NULL)
            select @len = isnull(datalength(ltrim(@base_names)),0)
          end

        select @dump_filename = @db_name

        --check to see if a log dump is necessary
        exec @err = sp_jst_chk_row_threshold @db_name, @row_threshold
        if( @err = -1 ) --row threshold not surpassed
          begin
            exec @err = sp_jst_chk_time_threshold @db_name, @time_threshold
            if( @err = -1 ) --time threshold not surpassed
              begin
                raiserror 19240, @db_name, @row_threshold, 
                                 @time_threshold
                continue
              end
          end

        -- if use_srvr_name is true, 0
        if @use_srvr_name = 0
            select @dump_filename = @db_name + @@servername

        if @date_fmt > 0
            select @dump_filename = @dump_filename + @date_str   

        --dump the current db using the dump command
        exec @err = sp_jst_dump_tran_log_to_disk @db_name, 
                                                 @dump_location, 
                                                 @dump_filename,
                                                 @compress_flag,
                                                 @compress_value,
                                                 @truncate_flag  
        if( @err < 0 )
            select @status = @err
      end --end while

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