Managing and scheduling database jobs with the Data Studio Web Console
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.
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
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.
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 type||Communication 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
Figure 2. Opening the Job Manager from the Task Launcher
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
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:
- In the Getting Started section of the Task Launcher, click Add database connections.
- 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
- 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
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:
- Open the Job Manager in the web console.
- 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
Figure 5b. Entering basic job properties
- Click OK to create a new job entry.
- 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:
- From the Job List tab, select the job you want to add a script to and click Edit.
- In the Job Components menu, click Script.
- 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;
- Click Save All to save your changes to the job.
Figure 6. Adding a script to a 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:
- From the Job List tab, select the job you want to run and click
Figure 7. Running a job from the job list tab
- 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
- Click OK to run the job on the selected databases.
- 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
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
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:
- From the Job List tab, select the job that you want to add a schedule to and click Edit.
- In the Job Components menu, click Schedules. Then, click Add Schedule.
- 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.
- Specify one or more databases against which you want to run the job.
- Click Save All to save your changes to the job.
Figure 11. Scheduling a job
Alternatively, you can add a schedule from the Schedules tab of the Job Manager and associate the schedule with an existing job.
- From the Schedules tab, click Add Schedule.
Figure 12. Adding a schedule from the Schedules tab
- 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
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
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
To specify a chain of jobs:
- From the Job List tab, select the main job you want to add a chain to and click Edit.
- In the Job Components menu, click Chain.
- Select the additional jobs that will run conditionally when the main job has completed.
- Click Save All to save your changes to the job.
Figure 14. Specifying a chain of 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:
- From the Job Manager, open the Notifications tab, then click
Figure 15. Adding a notification
- Select a job that you want to associate with a notification and
Figure 16. Associating a job with a notification
- 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.
- 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
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:
<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
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
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
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
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.
We would like to thank Sriram Srinivasan for reviewing the technical contents of this article and for providing valuable insight and comments.
- To learn more about IBM Data Studio, see the IBM Data Studio 3.1 Information Roadmap.
- Get the latest product information about IBM Data Studio.
- In the developerWorks Data Studio and InfoSphere Optim pureQuery Runtime section, get the resources you need to advance your skills on IBM Data Studio.
- Download Data Studio, available at no charge, and try it for yourself.
- 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.