Sybase ADDICT

This is an External End of Life Notice for Replication Server Version 15.0.x
See more here

Watch this short demo to learn how to use ASE’s newest self-management feature, the Job Scheduler, to automate common database administration tasks.
See it in action here

Job scheduler allows DBA to create and schedule administration tasks. Since ASE v15.0.3, lot of bugs are solved, making job scheduler to be workable. By the way, I will suggest to heavily test it before to go in production environment !

So how to install it ?

Job scheduler is a separate external process running as an agent (JS Agent), and an internal ASE task (JS Task) that need a specific database to run. We will need to setup a database and create a directory services entry in interface file.

Create the database sybmgmtdb:

Let’s assume the dataserver you created is named SYBPARFRUAT01_DS and you created 2 separate devices named sybmgmtdat for data and sybmgmtlog for log segment.
Connect to the dataserver you want to install the job scheduler and create the database sybmgmtdb:

create database sybmgmtdb on sybmgmtdat = 100 log on sybmgmtlog = 25
go
sp_dboption sybmgmtdb,"trunc log",true
go
sp_dboption sybmgmtdb, "select into", true
go
use sybmgmtdb
go
checkpoint
go

Then, back to Unix command line, you must run the installjsdb script located in $SYBASE/$SYBASE_ASE/scripts, it will install the job scheduler componants into database sybmgmtdb:

isql -Usa -Ppassword -SSYBPARFRUAT01_DS \
-i $SYBASE/$SYBASE_ASE/scripts/installjsdb

I previously mentionned job scheduler is a separate server process running as an agent. So we need to create a directory services entry in interface file.
Let’s call the job scheduler server process SYBPARFRUAT01_JS.

SYBPARFRUAT01_JS
master tcp ether vm.sybnux.com 5999
query tcp ether vm.sybnux.com 5999

Now, let’s connect to the dataserver and add the job scheduler agent using sp_addserver to create an entry in the dataserver’s sysservers table:

sp_addserver SYB_JSAGENT, null, SYBPARFRUAT01_JS
go

While executing a job, job scheduler consider all dataservers as remote servers, including the server where Job Scheduler is installed.
Job scheduler use CIS mechanism. So to execute jobs locally, we need to create an entry in sysservers (SYBPARFRUAT01_RPC) to alias the local dataserver (SYBPARFRUAT01_DS):

sp_addserver SYBPARFRUAT01_RPC, ASEnterprise, SYBPARFRUAT01_DS
go

Setup job scheduler users:

To create, manage, or execute jobs and schedules, we need to create at least one job scheduler admin account one job scheduler user account.

exec sp_addlogin 'jobsadm',
'password',
@defdb='sybmgmtdb',
@deflanguage='us_english'
go
exec sp_addlogin 'jobsusr',
'password',
@defdb='pubs2',
@deflanguage='us_english'
go
use sybmgmtdb
go
sp_adduser jobsadm
go
use pubs2
go
sp_adduser jobsusr
go
sp_addexternlogin 'SYBPARFRUAT01_RPC','jobsadm','jobsadm','password'
go
sp_addexternlogin 'SYBPARFRUAT01_RPC','jobsusr','jobsusr','password'
go

Grant the appropriate roles to logins:

sp_role 'grant', js_admin_role, jobsadm
go
sp_role 'grant', js_user_role, jobsadm
go
sp_role 'grant', js_user_role, jobsusr
go
sp_modifylogin jobsadm, 'add default role', js_user_role
go
sp_modifylogin jobsadm, 'add default role', js_admin_role
go
sp_modifylogin jobsusr, 'add default role', js_user_role
go

Activate and start the jobscheduler agent:

sp_configure "enable job scheduler", 1
go
use sybmgmtdb
go
sp_js_wakeup "start_js", 1
go

You are now ready to use the jobsheduler.
To stop the jobscheduler proceed as follow:

use sybmgmtdb
go
sp_js_wakeup "stop_js", 1
go