Skip to Content

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.

Best Practices for Migrating to SAP Sybase ASE - Job Scheduler basics.jpg

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

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Mahesh Gadgil

    Thanks for the very good article that highligths relatively unknown functionality of ASE. What may not jump out to a reader is that ‘job scheduling’ is an optional feature which requires you to create ‘sybmgmtdb’ database.

    To install this feature on a pre-installed instance of ASE, ‘installjsdb’ scripts needs to be executed. ‘installjsdb’ is found under $SYBASE_HOME/ASE-15_0/scripts.

    The script runs halfway through but aborts after creation ‘sybmgmtdb’ database, when it tries to create a user ‘jstask’ with a password generated by ‘password_random()’ function.

    The script requires us to create manually a ‘jstask’ user with a password of our choosing, before completing successfully on the next attempt.

    Alternatively, before you attempt to run the script please check the setting of ASE for minimum width required for a password, by executing sp_configure ‘minimum password length’.

    If the run value is zero as it happened to me when I tried this, change it any value equal to or greater than 6 by executing sp_configure ‘minimum password length’, 7.

    (0) 
  2. Darryl Griffiths

    We are implementing BPA (Redwood job scheduler).
    With an Oracle DB, all DBA jobs are run from the DBA calendar and can easily be controlled by the external scheduler.
    For ASE, we would need to pull out all the ASE ATM jobs and create custom jobs in BPA that call a command line to create and execute the ASE job.

    Hopefully SAP will remove the need for the ASE job scheduler and migrate to the DBA calendar.

    (0) 
    1. Maria Victoria NORMAND

      Hi Darryl,

      This article was oriented to non-SAP applications running on ASE.
      If you are talking about DBA Planning Calendar (DB13 tcode), it is a DBACockpit functionality, and DBACockpit means you are having SAP applications running on ASE. DBA Planning Calendar (as well ATM) is using in background the ASE Job Scheduler anyway to get the jobs executed.

      Best regards,
      Victoria.

      (0) 
      1. Darryl Griffiths

        Hi Victoria,

        Thanks for the clarification.

        However, because the ASE job scheduler is used, it makes it difficult to migrate these ASE controlled jobs into Redwood.
        The idea is that the entire SAP estate is controlled from one central job scheduler, Redwood.

        If we can’t see the ASE controlled jobs in Redwood then it makes it difficult to properly plan administrative tasks.  An example being a REORG or UPDATE STATISTICS starting on the DB, when all other jobs are paused in Redwood, ready for a system copy.

        By having the ASE controlled jobs scheduled directly from SAP (i.e. a background job that calls the required ASE stored procedure), then it makes it transparent to the Redwood administrators (operations centre personnel).

        In this instance, the ASE job scheduler could then be removed, simplifying the solution landscape.

        Regards,

        Darryl

        (0) 
        1. Maria Victoria NORMAND

          Hi Darryl,

          Thanks for your comment.
          Interesting actually I didn’t know about BPA and that it was integrated with Solman. As per I could read there is still a limitation regarding the repository as it should be Oracle. But that might be out of the scope of this discussion 🙂

          Best regards,
          Victoria.

          (0) 

Leave a Reply