Sybase ASE 15.0.2 Job Scheduler Setup

JOB SCHEDULER IN SYBASE


Step 1 Create a raw device for sybmgmtdb database


/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev
aloncvc1004:opctlsy2 >/opt/VRTSvxfs/sbin/qiomkfile -s 75m /OracleDumps/opctlsy2/sybmgmtdev

Step 2 Initialise unix raw device to create allocation units for dataserver

1> disk init
2> name=sybmgmtdev,
3> physname='/OracleDumps/opctlsy2/sybmgmtdev',
4> size='75M'


Step 3 Run the installjsdb script in $SYBASE/$SYBASE-ASE/scripts which will create the database sybmgmtdb on the device sybmgmtdev

aloncvc1004:opctlsy2 >isql -Usa -SGALLARDO -iinstalljsdb -oinstalljsdb.out
Password:*****

Starting the setup of sybmgmtdb.
Using device sybmgmtdev for the sybmgmtdb database.
CREATE DATABASE: allocating 33280 logical pages (65.0 megabytes) on disk
'sybmgmtdev'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
'sybmgmtdev'.
Caution: You have set up this database to include space on disk 55 for both
data and the transaction log. This can make recovery impossible if that disk
fails.
Database 'sybmgmtdb' is now online.
Database option 'trunc log on chkpt' turned ON for database 'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'trunc log on chkpt' to
take effect.
(return status = 0)
Database option 'select into/bulkcopy/pllsort' turned ON for database
'sybmgmtdb'.
Running CHECKPOINT on database 'sybmgmtdb' for option 'select
into/bulkcopy/pllsort' to take effect.
(return status = 0)
Executing checkpoint in sybmgmtdb
Database log is 5120 pages, 30% log free is at 1536 pages, the last chance
threshold is at 376 pages.
installing sp_js_logthreshold at 1536 pages.
Adding threshold for segment 'logsegment' at '1536' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
Created js_jobs.
Created js_commands.
Created js_schedules.
Created js_scheduledjobs.
Created js_seqsql.
Created js_callouts.
Created js_history.
Created js_output.
Created js_keys.
Created js_templates.
Created js_xml.
Created js_daynames.

Step 4 Add the below entry into interfaces file, note that port you choose should be avaialable and not in use


GALLARDO_JSAGENT
master tcp ether opctlsy2 5030
query tcp ether opctlsy2 5030

Step 5 Add the server into sysserver table

1> sp_addserver SYB_JSAGENT,null,GALLARDO_JSAGENT
2>
Adding server 'SYB_JSAGENT', physical name 'GALLARDO_JSAGENT'
Server added.
(return status = 0)

Step 6 Add the local server as loopback server onto sysservers as this will be referred by JS Scheduler and JS Agent


1> sp_addserver loopback,ASEnterprise,GALLARDO
2>
Adding server 'loopback', physical name 'GALLARDO'
Server added.
(return status = 0)

1> sp_helpserver
2>
name network_name class status id cost
----------- ---------------- ------------ ------------------------------------------------------------------------ -- ----
FERRARI FERRARI ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 4 1000
GALLARDO GALLARDO ASEnterprise 0 0
GALLAREP GALLAREP sql_server no timeouts, no net password encryption, writable , rpc security model A 5 1000
GALLARIBO GALLARIBO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 6 1000
SYB_BACKUP GALLARDO_BS ASEnterprise timeouts, no net password encryption, writable , rpc security model A 1 0
SYB_JSAGENT GALLARDO_JSAGENT ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 7 1000
SYB_JSTASK GALLARDO ASEnterprise timeouts, no net password encryption, writable , rpc security model A 8 1000
local GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 3 1000
loopback GALLARDO ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 2 1000
(return status = 0)



Step 7 Set up Sybase configuration parameters to start Job Scheduler

1> sp_configure 'job'
2>
Msg 17411, Level 16, State 1:
Server 'GALLARDO', Procedure 'sp_configure', Line 278:
Configuration option is not unique.

Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 0 0 switch dynamic
job scheduler interval 1 0 1 1 seconds dynamic
job scheduler tasks 32 0 32 32 number dynamic
maximum job output 32768 0 32768 32768 bytes dynamic

(1 row affected)
(return status = 1)
1> sp_configure 'enable job scheduler', 1
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable job scheduler 0 0 1 1 switch dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable job scheduler' to '1' increases the amount of memory ASE uses by 18694 K.
(return status = 0)
1>


Step 8 Start up Sybase Job Scheduler

Even a server recycle will start the JS with following message in sybase log

00:00000:00013:2009/08/03 14:39:18.94 kernel Initializing Job Scheduler Task
00:00000:00013:2009/08/03 14:39:19.01 kernel Installed Job Scheduler sequencer code version 0.27 - 27 tokens
00:00000:00013:2009/08/03 14:39:19.12 kernel Job Scheduler Task started with a clean 'js_history' table
00:00000:00013:2009/08/03 14:39:20.20 kernel Job Scheduler Task state set to running, startcount 1.
00:00000:00013:2009/08/03 14:39:21.20 kernel Job Scheduler Task connected with Agent on port 5030

OR


1> sp_js_wakeup 'start_js', 1
2>
(1 row affected)
(return status = 1)
1>

Step 9 Add a login with js_admin_role, js_user_role, js_client_role which you will use to kick and create jobs

1> sp_addlogin job_scheduler, job_scheduler, sybmgmtdb,NULL,'JS ADMIN ROLE'
2>
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1> use sybmgmtdb
2>
1> sp_adduser job_scheduler,job_scheduler
2>
New user added.
(return status = 0)
1>

1> sp_role 'grant',js_admin_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1> sp_role 'grant',js_user_role,job_scheduler
2>
Authorization updated.
(return status = 0)
1>


Step 10 Need to register the login as a remote login to all the target servers where you need to run jobs out of JS


1> sp_addexternlogin FERRARI,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'FERRARI'.
(return status = 0)
1>

For Local server add the following

1> sp_addexternlogin loopback,job_scheduler,job_scheduler,job_scheduler
2>
User 'job_scheduler' will be known as 'job_scheduler' in remote server 'loopback'.
(return status = 0)
1>


Step 11 If you use Sybase Central you can install the forllowing sps and templates using which you can create jobs for dump, reorg, statistics, rebuild etc


TEMPLATES

aloncvc1004:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/jobscheduler/Templates/sprocs
aloncvc1004:opctlsy2 >


aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created

TEMPLATE PROCS

aloncvc1004:opctlsy2 >./installTemplateProcs 'GALLARDO' 'sa' '*****'
Creating generic stored procedures used by Sybase Job Scheduler Template jobs
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_check_reorg_space has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_database_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_index_names has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datachange has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_get_datestr dropped
sp_jst_get_datestr has been created
Generic stored procedure used by Sybase Job Scheduler Template
jobs
sp_jst_get_freespace has been created
Creating support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_get_mdcache_type
sp_jst_get_spaceused has been created
Creating Support Stored Procedure for Sybase Reconfigure Metadata Cache Template
created procedure sp_jst_reconf_mdcache_type
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_verion_num has been created
Generic stored procedure used by Sybase Job Scheduler Template jobs
sp_jst_purge_alphachars has been created
sp_jst_valid_ase_version has been created
Creating job-specific stored procedures used by Sybase Job Scheduler Templates
Creating Sybase Delete Statistics Template stored procedures
sp_jst_delete_stats has been created
sp_jst_dump_db_to_disk has been created
Creating Stored Procedures for Sybase Backup Database to Disk Template
sp_jst_dump_dbs_to_disk has been created
Creating Sybase Back-up Log Template stored procedures
Creating Stored Procedures for Sybase Dump Logs Template
sp_jst_dump_tran_log_to_disk has been created
sp_jst_chk_row_threshold has been created
sp_jst_chk_time_threshold has been created


Step 12 Go ahead and create jobs and schedule them :)

Create job

declare @jobcmd varchar(255)
select @jobcmd='jcmd=YOUR COMMAND OR PROC,server=GALLARDO'
exec sp_sjobcreate 'sjname=3_job', @jobcmd

Attach a schedule

1> sp_sjobmodify @name='first_job', @option='sname=mysched'
2>
(return status = 0)
1> sp_sjobmodify 'sname=mysched' , @option='repeats=1day, starttime=03:00pm'
2>
(return status = 0)
1>

sp_sjobhelp


sjob_id: 6 name: 'first_job'
owner : sa
created : Aug 4 2009 2:15PM
state : enabled
job name : 7 - 'job_7'
schedule name : 7 - 'mysched'
server : GALLARDO
-- job --------:
description :
owner : sa
created : Aug 4 2009 2:15PM
-- schedule ---:
description :
owner : sa
created : Aug 4 2009 2:15PM
repeating : every day
starttime : 15:00
startdate : 04 Aug 2009


(1 row affected)
(return status = 0)
1> sp_sjobcmd 'first_job', 'list'
2>
job_id job_name jcmd_seqno jcmd_text
7 job_7 0 use master
7 job_7 1
7 job_7 2 go
7 job_7 3 exec sp_myproc
7 job_7 4
7 job_7 5 go



Default log location is the server log location :

GALLARDO_JSAGENT.log