/* JobScheduler Templates */
use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Self Management stored procedure for automatic update of statistics'
if exists ( select 1 from sysobjects
            where name = 'sp_jst_default_autoupdtstats'
              and type = 'P'
              and (sysstat & 7) = 4)
    drop proc sp_jst_default_autoupdtstats
go
/*
** Internal Job Scheduler Self Management stored procedure (ASE Installer)
**
** Warning this procedure is not a published interface; do not use it.
*/
create procedure sp_jst_default_autoupdtstats( @dstartdate varchar(20)=NULL, 
                                               @serverName varchar(30) ) as
begin
--Create the default schedule
declare @sched_id int
      , @status int

if( @dstartdate = NULL )
    select @dstartdate = convert(varchar(19),getDate() )

exec @sched_id = sybmgmtdb..sp_createschedule @sname='defaultSchedule', 
          @sdesc='default schedule that runs jobs between midnight and 6 a.m.',
          @properties='shared',
          @startdate=@dstartdate,
          @starttime='Jan 1, 1970 12:00 AM',
          @enddate='',
          @endtime='Jan 1, 1970 6:00 AM',
          @repeats=1,
          @units="d",
          @days=0,
          @dates=0
print 'sched id is %1!', @sched_id

--Create the server update stats job
declare @job_id int
      , @jname varchar(256)
      , @jdesc varchar(200)
      , @timeout int
      , @properties varchar(200)
      , @jtext varchar(1800)

select @jname = 'defaultServerUpdateStats'
     , @jdesc = 'this job executes update statistics on all server tables with at least 10000 rows and a datachange value greater than or equal to 100'
     , @properties = 'run_as_owner=true,multi_task=false,shared=false'
     , @timeout = 360

exec @job_id = sybmgmtdb..sp_createjob @jname, @jdesc, @properties, @timeout
print 'job id is %1!', @job_id

select @jtext = 'declare @sp_str varchar(64)
      , @user_code int
      , @index_flag int
      , @datachg_threshold float
      , @rowcnt_threshold int
      , @pagecnt_threshold int
      , @step_value int
      , @consumers int
      , @spercent int

select @index_flag = 0
     , @datachg_threshold = 100
     , @rowcnt_threshold = 0
     , @pagecnt_threshold = 0
     , @step_value = NULL
     , @consumers = NULL
     , @spercent = NULL

exec sp_setpsexe @@spid, "priority", "LOW"
select @sp_str = "sybsystemprocs..sp_jst_svr_update_statistics"

exec @user_code = @sp_str @index_flag, @datachg_threshold, @rowcnt_threshold,
                          @pagecnt_threshold, @step_value, @consumers,
                          @spercent
if( @user_code <  0 )
begin
    print "ase_js_cmd: sp_sjobsetstatus @name=''%1!'',
                                        @option=''exit_code=2, user_code=%2!''",
                                        @js_runid, @user_code
end
else
begin
    print "ase_js_cmd: sp_sjobsetstatus @name=''%1!'',
                                        @option=''exit_code=1, user_code=0''",
                                        @js_runid
end'

declare @job_id_str varchar(6)
select @job_id_str = convert( varchar(6), @job_id )
exec sybmgmtdb..sp_addjobtext @job_id_str, @jtext

--create scheduled job
select @properties = 'shared_run=false'
exec sybmgmtdb..sp_createscheduledjob @job_id, @sched_id, @jname, @properties, @serverName, @timeout

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

