Managing and scheduling database jobs with the Data Studio Web Console

As the number of databases increase in many organizations, many DBAs are facing a major challenge in automating and scheduling their database operations. The new job management capability in IBM® Data Studio provide DBAs with a simple and flexible way to create and manage database jobs, and to schedule command scripts to run automatically.

Share:

Dimple Bhatia (dimple@us.ibm.com), Advisory Software Engineer, IBM  

Photo of author Dimple BhatiaDimple Bhatia is an advisory software engineer at the IBM Silicon Valley Lab in San Jose, Calif. She is currently the lead on the Data Studio Web Console which is a web-based tool, including health monitoring and job management. She is also working on the web tooling infrastructure used for all web-based tools in the Optim umbrella. Before joining the InfoSphere Warehouse tooling team, she worked as the lead on the Federation Catalog and on migration in WebSphere Federation Server. She holds a master's degree in computer engineering from Syracuse University in New York.



Hassi Norlén (hnorlen@us.ibm.com), Optim Tools Senior Information Developer , IBM China

Hassi NorlenHassi Norlen is an information developer with IBM's Costa Mesa lab. He has worked extensively with database management and monitoring software such as InfoSphere Optim Performance Manager, IBM Data Studio, and IBM Data Studio web console. His subject of expertise is up-and-running and installation documentation, as well as UI development using the progressive-disclosure methodology. He started his IBM career 10 years ago in Enterprise Content Management, working on IBM FileNet P8 Application Engine/Workplace XT and IBM Enterprise Records. He holds a master's degree in physics and a degree in journalism from Sweden's Uppsala University, and worked as a science and technology journalist and a science teacher before joining IBM.



Quddus Chong (qchong@us.ibm.com), Optim Tools Information Developer, IBM China

Quddus ChongQuddus Chong joined IBM in 2003 after an internship with IBM Extreme Blue. He worked with the Informix team in Lenexa for three years as an infrastructure software engineer. In 2006, he joined IBM Silicon Valley Lab to work as a software accessibility specialist, then as the lead programming writer for IMS application development. Currently, he is an information developer for InfoSphere Optim Query Tuner products and the IBM Data Studio web console.



12 January 2012

Before you start

About this tutorial

Scheduling and automating database operations by using existing solutions such as cron, batch programming, or script automation programs can be a complex operation for many DBAs. Rather than creating and maintaining the scripts and schedules separately, it is easier and more reliable for a DBA to group these database operations into jobs, then manage and execute the jobs from a single tool.

A job is a container that contains all the necessary information required to run a script or command against one or more databases according to a user-defined schedule. The job does not directly contain any information about which databases that the job will run against. Instead, you associate database information when you create one or more schedules for a job. This flexible approach allows you to run the same scripts or commands against different databases and schedule them for execution at different times.

The Job Manager in the Data Studio web console provides the tools needed to create and schedule script-based jobs on your DB2® for Linux®, UNIX®, and Windows® and DB2 for z/OS® databases. When you create a job, you can schedule it to run on one or more databases. A job can also be run manually from the Job Manager user interface. You can also configure multiple jobs to run sequentially by adding a chain of jobs to the job you are creating.

Objectives

After completing this tutorial, you will have learned how to:

  • Create and manage script-based jobs
  • Set up schedules to run jobs against DB2 databases
  • Monitor the success or failure of jobs
  • Check the job execution history
  • Manage privileges for job scheduling and execution

Prerequisites

To use the new job-management capability, you will need to have one of the following software components installed and configured:

  • The IBM Data Studio web console
  • An IBM Data Studio Eclipse-based client (either the full or administration client); if you use the Data Studio client to access the Job Manager, you must install and configure the web console first

To learn about installing these components and getting started with them, see the Getting Started topic in the Data Studio Information Center.

You also need a compatible web browser, such as Firefox V3.6 or higher, or Internet Explorer V8 or higher.


Job types

Overview

The Data Studio web console supports these job types for DB2 for Linux, UNIX, and Windows: SQL-only script, DB2 CLP script, and Executable/Shell script. For DB2 for z/OS databases, only the SQL-only script job type is supported.

Table 1. Summary of job types
Job typeCommunication method
SQL-only script The job manager connects to the database and runs the SQL commands included in the job script directly against the database via JDBC.
DB2 CLP script The job manager remotely executes the script by invoking the DB2 command-line processor (CLP) on the database server system via SSH, by using the associated database connection user credentials.
Executable/Shell script The job manager remotely executes the script on the database server system via SSH, by using the associated database connection user credentials.

If you want to run DB2 CLP or Executable/Shell scripts in your jobs, make sure the SSH server is active on the system where the script will be executed, and the user credentials associated with the database connection in the Data Studio web console has SSH access permission. Also, remember to allow for SSH port access in your firewall rules.


Getting started with the Job Manager

This section covers the basics of using the Job Manager, including how to open the Job Manager, add a database connection, create a new job, add a script to a job, and run a job without scheduling.

Opening the Job Manager in the web console

To open the Job Manager in the browser, log in to the Data Studio web console, then click on Job Manager under the Open menu of the web console or click on the Manage database jobs link on the Task Launcher page.

Opening the Job Manager from the Open menu
Screenshot shows open menu
Figure 2. Opening the Job Manager from the Task Launcher
Screenshot shows task launcher with focus on Manage database jobs

Overview of the Job Manager user interface

The Job List tab displays all the enabled and disabled jobs. You can add, edit, delete and run jobs manually from this page. The Schedules tab allows you add, edit, and delete schedules on jobs. The Notifications tab allows you to add, edit, and delete notifications on jobs. The History tab shows the status of the jobs that ran. The job history displays jobs that ran according to a schedule, in addition to jobs that you ran manually. The History tab shows the overview status such as the Start Time, End Time, Status, and the Database Name.

Figure 3. The Job Manager user interface
Screenshot shows job manager tab

Adding a database

Before running or scheduling a job, you must specify which databases to run the job against. To add a database in the web console:

  1. In the Getting Started section of the Task Launcher, click Add database connections.
  2. From the Open menu, click Databases to bring up the Databases page, then click Add.
    Figure 4. Adding a new database connection from the Databases page
    Screenshot shows Databases tab with Add button
  3. In the Add Database Connections form, enter the database connection information. The credentials you specify will be used to connect to the remote database host via SSH, and to execute the script associated with the job.
    Figure 5. Specifying the database connection properties
    Screenshot shows fields for database connection

Note that once you add a database in the Databases page, the web console automatically enables health monitoring for that database. To learn more about health monitoring, see the developerWorks article titled "Monitor DB2 for Linux, UNIX, and Windows databases with Data Studio Health Monitor."

Creating a job from the Job Manager

To create a new job:

  1. Open the Job Manager in the web console.
  2. From the Job List tab, click Add Job and enter the basic properties of the new job.
    • Name: A descriptive name for the job. The name does not have to be unique. The Job Manager will automatically generate a Job ID as a unique identifier.
    • Type: Valid job types are SQL-only script, DB2 CLP script, or Executable/Shell script. The job type determines how the Job Manager connects to the associated database to run the script.
    • Enabled for scheduling: Select this option box to enable the job for scheduling. If this option is not selected, you cannot schedule the job but you can still manually run the job from the Job List tab.
    • Description: An optional short description of the job.
    Figure 5a. Adding a new job entry from the Job Manager
    Screenshot shows Job Manager tab
    Figure 5b. Entering basic job properties
    Screenshot shows job properties
  3. Click OK to create a new job entry.
  4. To define job components, such as schedules, scripts, and notifications, click on the job entry in the job list grid, and then click on the corresponding section under the Job Component bar.

Adding a script to a job

The script is the executable part of a job and defines the actions to be performed against the database server when the job is run. Each script consists of one or more statements that are executed in order.

The Job Manager does not verify that the scripts you enter are valid. Before you schedule the job in the job manager, you should test the script against a database, by using the Run Job button, or use other methods to verify that the script is correct and that it produces the expected results.

To add a script to a job:

  1. From the Job List tab, select the job you want to add a script to and click Edit.
  2. In the Job Components menu, click Script.
  3. In the text field, enter or paste the script to run against the database server when you run the job. For this tutorial, you can use the following sample SQL-only script to create a new table and immediately drop the table.
    	create table employee(c1 int, c2 int);
    	drop table employee;
  4. Click Save All to save your changes to the job.
Figure 6. Adding a script to a job
Screenshot shows script added to job

Running a job without scheduling

You do not have to add a schedule for a job to run it. You can run your jobs directly from the job list tab to verify that they work correctly before you schedule them.

To run a job directly:

  1. From the Job List tab, select the job you want to run and click Run Job.
    Figure 7. Running a job from the job list tab
    Screenshot shows run-job button
  2. Select one or more databases on which to run the job. If you select to run the job on a single database, you can use the default user ID associated with that database connection or specify an alternative user credential to run the job against the selected database. If you select to run the job on multiple databases, the job is run against each database using the user ID that is defined in the database connection for that database.
    Figure 8. Selecting the databases on which to run the job
    Screenshot shows database options
  3. Click OK to run the job on the selected databases.
  4. Optionally, you can open the History tab to see the job status details and the log file for the job.

Key job management tasks

This section covers how to view the job history, schedule a job, chain jobs, set up job execution notifications, and manage security privileges for jobs.

Viewing the job history

To see the status of a job you ran, open the History tab from the Job Manager.

Figure 9. Viewing the job status from the History tab
Screenshot shows job status

To view more details about a job execution or troubleshoot the problem if a job execution failed, select the job entry in the history grid and click View log in browser. The log contains the output of the job script and lists any exceptions or other messages related to the job. You can use your web browser to save or print the job execution log. You can also bookmark or share the URL of the log.

Listing 1. Sample output from a job execution log
Job ID:		 	1320258529323
Job Name:		testscript
Commands Executed:
		 	    Create table newtab(c1 int, c2 int);

Start Time:		2011-11-02 11:32:11.792
End Time:		2011-11-02 11:32:17.323
Result:		 	The job executed successfully.

By default, the job manager keeps the history of a job for three days. You can configure how long the job history records are kept in the job history settings. You can also set the type of job results you want to keep. By default, both successful and failed job records are kept. To change the job history settings for the Data Studio web console, from the job history tab, click Job History Settings.

Figure 10. Specifying the job history settings
Screenshot shows how long to keep job history

Scheduling a job

A schedule defines when a job will be run, whether the job is repeating, and whether the schedule is limited in number of runs or in time. The schedule is also associated with one or more databases against which to run the job. A job can have any number of schedules attached to it, but each schedule entry only applies to one job.

When you schedule a job on a single database, you can define the user ID that will run the job. If you schedule a job to run against more than one database, the job is run against each database by the user ID that is stored in the database connection for that database. If the user ID that is used to run the job does not have the required permissions to perform the commands defined by the script for the database, the job will fail with a permissions error.

To schedule a job:

  1. From the Job List tab, select the job that you want to add a schedule to and click Edit.
  2. In the Job Components menu, click Schedules. Then, click Add Schedule.
  3. In the Schedule Details section, select a start date and start time for the job. If you want the job to repeat, select the Repeats checkbox, and set the repetition parameters for the job. A schedule must be active to run the job.
  4. Specify one or more databases against which you want to run the job.
  5. Click Save All to save your changes to the job.
Figure 11. Scheduling a job
Screenshot shows schedule ddetails

Alternatively, you can add a schedule from the Schedules tab of the Job Manager and associate the schedule with an existing job.

  1. From the Schedules tab, click Add Schedule.
    Figure 12. Adding a schedule from the Schedules tab
    Screenshot shows add-schedule button
  2. In the Add Schedule wizard, select a job you want to schedule and click OK. The job opens with the Schedules component selected. Follow steps 3-5 above.
    Figure 13. Associating a job with a new schedule
    Screenshot shows choosing a job to associate with a schedule

Chaining jobs in a sequence

Chains are optional and can be used as a convenient way to handle errors or to perform cleanup after a job execution. In a chain, the main job is followed by a secondary job that is dependent on the outcome of the main job, then followed by a finishing job that performs cleanup operations, such as RUNSTATS and BACKUP. The secondary jobs are run against the same databases as the primary job. You can add a chain of subsequent jobs that run depending on the outcome of the primary job.

To specify a chain of jobs:

  1. From the Job List tab, select the main job you want to add a chain to and click Edit.
  2. In the Job Components menu, click Chain.
  3. Select the additional jobs that will run conditionally when the main job has completed.
  4. Click Save All to save your changes to the job.
Figure 14. Specifying a chain of jobs
Screenshot shows selecting additional jobs

Setting up notifications of job executions

You can set up notifications to help you keep track of the execution results for your jobs across multiple databases and schedules. You can define any number of notifications for a job. You can specify whether the Job Manager should send out notifications based on whether a job execution was successful, or you can choose to be notified that a job was executed regardless of its end result. You can also customize the set of users to notify, and restrict notifications for groups of databases that the job has been run against.

To set up a job notification:

  1. From the Job Manager, open the Notifications tab, then click Add Notification.
    Figure 15. Adding a notification
    Screenshot shows Notifications tab
  2. Select a job that you want to associate with a notification and click OK.
    Figure 16. Associating a job with a notification
    Screenshow shows adding a notification
  3. On the Notifications component form, enter one or more email addresses in the Email Recipients field (separated by commas), then select the criteria for which the notification will be sent.
  4. Click Save All to save your changes to the job.

Managing security privileges for jobs

By default, all web console users can create and manage jobs and can run jobs as the default user. You can control the ability for users to manage and run jobs by enabling or disabling privilege restrictions in the repository database.

When a privilege restriction is enabled, the user must be explicitly granted the privilege in order to perform the restricted operation. Privilege restrictions apply to all databases that are connected to the web console. Use the Manage Privileges page to configure privilege restrictions and to grant privilege to users.

Figure 17. Configuring privilege restrictions from the Manage Privileges page
Screenshows enabling Manage Privileges

The following privilege restrictions can be configured for job management and execution:

  • Can Manage Jobs— Only users granted this privilege can create and schedule jobs. If the Can Manage Jobs privilege restriction is enabled, users without this privilege can still open existing jobs in view-only mode.
  • Can Run As Default User— When multiple databases are targets for a scheduled job, the job will be run as the default user ID stored with the database connection for each database. If the Can Run As Default User privilege restriction is enabled, only users granted this privilege can run a scheduled job.

Scheduling jobs from the SQL Script editor in the Data Studio Eclipse-based client

If you are using the Data Studio full or admin Eclipse-based clients, you can run and schedule jobs from the SQL script editor in the client. After creating or modifying your script, you can schedule the script to run as a job in the Job Manager.

Before you start, you must configure the web console in the Data Studio client. In the Data Studio client, select Window > Preferences and click Data Management > Data Studio Web Console, and provide the following connection information for the web console server.

  • Data Studio web console URL— This is the URL that you use to connect to the web console. The URL is of the form: http://<server>:<port>/datatools, where <server> is the name or IP address of the computer on which you installed Data Studio web console, and <port> is the HTTP or HTTPS port specified when you installed the product.
  • User name— The name of a user that has login rights on the web console. If you have not configured Data Studio web console for multi-user login you must log in as the default administrative user you created when you installed the product.
  • Password— The password of the user that you specified.
Figure 18. Specifying the connection properties to the web console server
Screenshot shows Preferences screen

To schedule a job from the SQL script editor, click on the Job Manager icon. The launchpoint for the Job Manager appears as a clock icon in the upper right of the script query editor panel. The Job Manager creates a new unique job with the script prefilled.

Figure 19. Creating a job for a script in the SQL script editor
Screenshot shows SQL script editor

If the script was created against a database connection, and the same database connection has been added to the Databases page in the web console, the database will also be preselected in the job entry. Otherwise, you must select one or more databases to run the job against. You can also schedule the job to run at a specific time, or save the job without a schedule if you want to run the job manually.

Figure 20. Job properties in the Data Studio Eclipse-based client
Screenshot shows Eclipse-based client

To manage jobs, you can launch the Job Manager view from the menu by clicking Window > Show View > Other > Job Manager.

Figure 21. Job Manager view in the Data Studio Eclipse-based client
Screenshot shows list of jobs

Conclusion

The job management feature in IBM Data Studio web console provides a flexible and simple way for DBAs to schedule and automate database operations. The Job Manager is a unified tool you can use to create, schedule, and execute script-based jobs on your DB2 for Linux, UNIX, and Windows and DB2 for z/OS databases from the web console.

Acknowledgement

We would like to thank Sriram Srinivasan for reviewing the technical contents of this article and for providing valuable insight and comments.

Resources

Learn

Get products and technologies

  • Download Data Studio, available at no charge, and try it for yourself.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=784320
ArticleTitle=Managing and scheduling database jobs with the Data Studio Web Console
publish-date=01122012