The ASE Server is a featured part of the Real-Time Database Platform (RTDP) offered by SAP. This blog supports the RTDP and focuses you on getting the best functionality from your ASE software.
One of the least understood and perhaps least utilized ASE server options that come with the base install of the ASE Server is the Job Scheduler. In my mind I believe this feature of the SAP ASE Server is a great advantage to DBAs. I highly recommend it be installed and operational in 100% of the ASE Server installations I am involved with.
What is Job Scheduler?
Job Scheduler is a method that allows database administrators to run unattended jobs according to a set time and date schedule. Being unattended allows the database admin to be free for other functions as well allowing us, as developers, an opportunity to think outside the box and plan for a "proactive" not "reactive" monitoring and response.
What are the benefits of Job Scheduler?
- proactive design can be applied
- no extra licence required, comes with the base installation of the ASE 15.X Server.
- certain update statistics job templates have been supplied by the developers
- easily customizable and transportable across multiple server instances
- hands-off operation
| Technical Sidebar: in-depth architecture of Job Scheduler |
|---|
Reference: Adaptive Server Enterprise 15.0 Job Scheduler's user Guide. Job Scheduler consists of the following components: • An internal ASE task called the JS Task: The JS Task determines when scheduled jobs should run and creates a historical record of jobs that are run. It starts the JS Agent process and feeds it the necessary information to retrieve job information and run the job on the specified ASE. • An external process called the JS Agent: When the JS Agent retrieves the job information from Job Scheduler’s own database, called sybmgmtdb, it logs into the target ASE and issues the job commands. When the job completes, JS Agent logs any result or output to the log tables in the sybmgmtdb database. • The sybmgmtdb database and stored procedures: All of the information about jobs, schedules, scheduled jobs, and data needed by the JS Task for internal processing is stored in the sybmgmtdb database, where data is usually accessed through stored procedures. The stored procedures make the data available to the GUI, the JS Agent, and the command-line interface. Only the JS Task accesses data directly from the sybmgmtdb database. • The graphical user interface (GUI) using the ASE plug-in in Sybase Central: Using the data it receives, the GUI helps you create and schedule jobs, view job status and job history, and control jobs. It also provides an administration feature that turns the ASE internal task on and off and, therefore, allows Job Scheduler to process and execute scheduled jobs. • Predefined templates from which the database administrator can create and schedule useful, time-saving jobs: Templates are an important tool in defining parameterized tasks for self-management of the database, such as database backups, reorganization rebuilds, modification of configuration parameters, and statistics updates and monitoring. They are implemented as batch T-SQL commands for which you provide parameter values. Database administrators can use templates to generate jobs schedule them to run at specific times.
|
What are Job Scheduler moving parts for creating a scheduled job?
Essentially we have two moving parts: the Job and the Schedule. Both the Job and the Schedule are created separately; one is the WHAT and the other is the WHEN. We can create these two parts but nothing is going to happen until we bring the two together. This is known as a SCHEDULED JOB. I am afraid the name does really give it away. A JOB can only be run if it is married to a SCHEDULE. A SCHEDULED JOB is our third moving part; the part or job that gets executed according to the schedule.
Job Scheduler captures the results and output of jobs and records that information in log tables which are contained in tables in the Job Scheduler database. This data can be viewed at a later date. In addition, Job Scheduler keeps a history of scheduled jobs; however, to keep a limit on the size of the history table, Job Scheduler monitors it and removes outdated, unnecessary history records.
How do I create a job and a schedule?
If you can type or code an SQL statement, you can create a job and/or a schedule. It's that easy. We can create jobs a number of ways:
- From scratch using the command line or GUI (Sybase Central)
- From a SQL batch file
- From a template
Using Sybase Central is definitely the way to go. This GUI administration tool part of the Sybase Client that comes with the ASE Server software. For our talk I will be using the command line only. Not that I prefer using the command line, but in some cases such as rebuilding the Job Scheduler on another Server, the ability to run a script sometimes has its advantages.
How do I create my first job?
Lets take something really easy, something that everybody is going to recognize. For my job I want to output the line: "I am alive!"
I will use a stored procedure to do this and I will put this into the Job Scheduler database. Here's the code:
/********************************
* create a procedure in the Job Schedule database (named sybmgmtdb) and call this procedure js_ping
* this procedure when called will output the line "I am alive! from the Job Scheduler"
********************************/
use sybmgmtdb
go
if exists(select 1 from sysobjects where name = 'js_Ping')
drop proc js_Ping
go
create proc js_Ping
as
print "I am alive! from the Job Scheduler"
return
go
/************************************
** let it be accessable or executable for any user in the sybmgmtdb database
************************************/
grant exec on js_Ping to public
go
/************************************
** create job and call it "ping"
************************************/
exec sp_sjobcreate @name='jname=Ping', @option='jcmd=exec sybmgmtdb..js_Ping'
go
/********************************
** End of Job Name: Ping
********************************/
The last line is where the magic happens. The sp_sjobcreate @name='jname=Ping', @option='jcmd=exec sybmgmtdb..js_Ping' line tells us to use the stored procedure sp_sjocreate (job create) call the newly created job 'Ping' and when the Job is run we want to actually execute the procedure we created earlier named js_Ping that is located in the sybmgmtdb database. Think of this simple example as a template you can duplicate many times. Only the name and the called procedure will change. Now onto the second part, creating a new schedule.
How do I create a schedule?
Creating a job was easy, right?
Schedules are a bit more complicated but only because we have a choice of dates and times and any combination in between. Lets make it simple so we can use this as a template for future schedules.
/*********************************
** Create Schedules
*********************************/
use sybmgmtdb
go
/********************************
* Schedule "Every ten minutes", every day
********************************/
exec sp_sjobcreate @name='sname=Every ten minutes', @option='repeats=10minute,starttime=00:01, endtime=23:59'
go
The stored procedure sp_sjobcreate (job create) is now used to create a schedule named 'Every ten minutes' and we will start this at 1 minute past midnight and end it at 11:59 PM. We are going to have a repeat of every 10 minutes and as we did not mention the days to run or any dates it is assumed that this will run all day, every day until eternity.
How do I create a scheduled job?
A scheduled job is the only way we can get our job to run. We have our job named 'Ping' and our schedule named 'Every ten minutes'. In our scheduled job example we want to run Ping every ten minutes. The code to do this is:
exec sp_sjobcreate @name='Ping:Every ten minutes', @option='server=<<ASE_SERVER_NAME>>, jname=Ping, sname=Every ten minutes'
go
Note that we are still using the procedure sp_sjobcreate and we have chosen the name 'Ping:Every ten minutes' so we can immediately recognize the scheduled job what it does and when it runs. It's a good idea to adopt the same naming convention.
Once we create this scheduled job - its already running. That's all that is required for this simple example. Besides being a learning tool, unfortunately this example by itself is not useful. We want our jobs to do something; if its a check or monitor, then take action and do something. We want Job Scheduler to be hands off so lets have our program make decisions that we might normally do. Lets make it a true Database Administrator Assistant. This is the design behind the Black Box DBA program; a proactive monitoring and decision tool for the ASE Server.
Sample scheduled Jobs
Some of the tasks undertaken by our newly minted DBA Assistant are listed by their scheduled job names. Its a very varied list but the underlying design to all of these is to make our life easier with less effort and manpower. These are actual jobs that have been and are now in Production Systems.
High_average_CPU_tripwire:Every ten minutes - Monitors CPU percent busy every 10 minutes and if its over our threshold issue a performance report to detail what the server is doing at that moment in time.
Update_Stats_Daily:M-F at 23:59 - does the update stats command Monday to Friday at 23:59 hours only on those tables that need this command
Update_Stats_Weekly:Sat at 23:59- a more inclusive update stats command every Saturday at 23:59 hours because our maintenance window is larger.
Dynamic_Engine_Allocation:Every ten minutes - Monitors CPU % busy between a high water mark and a low water mark every 10 minutes. If the levels are above or below it dynamically ads/subtracts a CPU Dynamic_Engine_Allocation.
Dump_System_Databases:Every day at 18:00- Quite common, just a simple dump of the databases every day at 18:00
Reorg_Compact_Stealth:M-F at 10:00- reorganizes tables that are disorganized but its in stealth mode meaning it only does this when the server is under a certain % of CPU busy.
Reorg Compact:Sat at 9:00- reorganizes the tables in all databases every Saturday at 9:00 AM.
I hope you can see that the adage of 'if you can code it in SQL you can put it into Job Scheduler' rings true. Over the next couple of blogs we are going to examine why the Update_Stats_Daily and Update_Stats_Weekly jobs are an integral part of your education in the SAP ASE Server.
Help is click away......
Follow the “Database Services Content Library” to access the entire series of Database Services Blogs and join the conversation on Twitter @SAPServices
Learn more about SAP Database Services here
http://www.sap.com/services-and-support/data-and-technology/database-services.epx
5 Comments
