Database jobs
A database job interfaces with a number of databases, including custom ones to perform queries, SQL statements, and jobs. You can also create and run stored procedures on DB2, Oracle, Microsoft SQL Server, and Netezza databases.
A description of the job properties and valid values are detailed in the context-sensitive help in the Dynamic Workload Console by clicking the question mark (?) icon in the top-right corner of the properties pane.Attribute | Description and value | Required |
---|---|---|
application name | database | ✓ |
DBMS | The database type where you want the job to
run. Supported values are:
|
✓ |
Database name | The name of the database. | ✓ |
Server | The hostname of the server where the database is located. | ✓ |
Port number | The port number for the database job. | ✓ |
JDBC driver class name | The name of the JDBC driver class | Required if you specify a custom database. |
JDBC connection string | The string that is used to connect to the database, containing database URL, username and password | Required if you specify a custom database. |
JDBC jar class path | Path to the database client jar files. This value overrides the value specified in the DatabaseJobExecutor.properties configuration file, if any. If you select the Microsoft SQL Server database, version 4 of the JDBC drivers is required. | |
JDBC custom properties | The JDBC connection string properties specified as name=value | |
Credentials | The user name and the password for accessing the database (domain users are
supported in the form DOMAIN\USER). As an alternative to hard-coding actual values, you can
parametrize in one of the following ways:
|
|
SQL | Use this section to type an SQL statement. Database-specific commands, such as db2 describe table, are not supported. To separate instructions, use an empty line. | Required if you specify a standard SQL |
Procedure name | The name of the procedure stored on DB2, Oracle,
or MSSQL databases. The procedure cannot be stored on
DB2 if the database already contains one or more stored procedures
with the same name and schema. For example, if the database has more
than one stored procedure called TEST.STORE_PROC1,
with different parameters as in the following: then the database
job cannot be created and the following message is returned: AWKDBE033E
The stored procedure name provided matches more then one stored procedure
definition in the database, to disambiguate specify also the schema. Note: This
attribute is not supported for BigSQL and Hive databases. |
Required if you specify a stored procedure. |
Procedure Parameters | The name and values of the procedure expressed
complying with the following syntax:
|
Required if you specify a stored procedure. |
Procedure Parameters content | Variable value. For output variables the value must be: ?. To enter a date variable, use the following format: yyyy-mm-dd. If no value is specified for a parameter, then the value is considered a NULL value in the database. | Required if you specify a stored procedure. |
Output file | The fully qualified path where you want to save your SQL query output. | Required if you specify an autocommit. |
$JOBS
AGENT#DATABASE
TASK
<?xml version="1.0" encoding="UTF-8"?>
<jsdl:jobDefinition xmlns:jsdl="http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdl"
xmlns:jsdldatabase="http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdldatabase" name="database">
<jsdl:application name="database">
<jsdldatabase:database>
<jsdldatabase:sqlActionInfo>
<jsdldatabase:dbms>db2</jsdldatabase:dbms>
<jsdldatabase:server>localhost</jsdldatabase:server>
<jsdldatabase:port>50000</jsdldatabase:port>
<jsdldatabase:database>TWS32</jsdldatabase:database>
<jsdldatabase:statements>
<jsdldatabase:dbStatement>SELECT * FROM DWB.ARE_ABSTRACT_
RESOURCES</jsdldatabase:dbStatement>
</jsdldatabase:statements>
<jsdldatabase:credentials>
<jsdl:userName>${agent:dbvars..dbtwouser}</jsdl:userName>
<jsdl:password>${agent:password.${agent:dbvars..dbtwouser}}</jsdl:password>1
</jsdldatabase:credentials>
</jsdldatabase:sqlActionInfo>
</jsdldatabase:database>
</jsdl:application>
</jsdl:jobDefinition>
DESCRIPTION "Defined using composer."
RECOVERY STOP
dbtwouser
through the param
utility command. So, the value
defined for dbtwouser
will be retrieved at runtime from the
dbvars
variables file located in the agent. Likewise, the password for the
value represented by dbtwouser
was defined on the agent with the
param
command and will be resolved at runtime from the same variables
file.Job definition - MSSQL jobs
Attribute | Description/value | Required |
---|---|---|
application name | database | ✓ |
dbms | The database type where you want the job to run. Because this job is specific for the Microsoft SQL Server database, the only supported value is mssql. | ✓ |
driverPath | Path to the database client jar files. This value overrides the value specified in the DatabaseJobExecutor.properties configuration file, if any. Version 4 of the JDBC drivers is required. | |
server | The host name of the server where the database is located. | ✓ |
port | The port number for the database job. | ✓ |
database | The name of the database. | ✓ |
dbStatement | The SQL statement. To separate instructions, use an empty line. | ✓ |
credentials | The user name and the password for accessing
the database (domain users are supported in the form DOMAIN\USER).
As an alternative to hard-coding actual values, you can parametrize
in one of the following ways:
|
$JOBS
AGENT#MSSQLJOB
TASK
<?xml version="1.0" encoding="UTF-8"?>
<jsdl:jobDefinition xmlns:jsdl="http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdl"
xmlns:jsdldatabase="http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdldatabase" name="database">
<jsdl:application name="mssqljob">
<jsdldatabase:database>
<jsdldatabase:sqlActionInfo>
<jsdldatabase:dbms>mssql</jsdldatabase:dbms>
<jsdldatabase:server>localhost</jsdldatabase:server>
<jsdldatabase:port>111</jsdldatabase:port>
<jsdldatabase:database>MYDATABASE</jsdldatabase:database>
<jsdldatabase:statements>
<jsdldatabase:dbStatement type="job">sada</jsdldatabase:dbStatement>
</jsdldatabase:statements>
<jsdldatabase:credentials>
<jsdl:userName>mssqluser</jsdl:userName>
<jsdl:password>${agent:password.mssqluser}</jsdl:password>1
</jsdldatabase:credentials>
</jsdldatabase:sqlActionInfo>
</jsdldatabase:database>
</jsdl:application>
</jsdl:jobDefinition>
DESCRIPTION "Defined using composer."
RECOVERY STOP
mssqluser
was defined with the
param
utility command in the variables file on the agent that is to run the
job. It will be resolved at run time with the defined value.Scheduling and stopping a job in HCL Workload Automation
You schedule HCL Workload Automation database jobs by defining them in job streams. Add the job to a job stream with all the necessary scheduling arguments and submit it.
You can submit jobs using the Dynamic Workload Console or Application Lab.
After submission, when the job is running and is reported in EXEC status in HCL Workload Automation , you can stop it if necessary, by using the kill command. The database job is deleted and an automatic rollback is performed. When the rollback operation completes, a new HCL Workload Automation database job is started automatically.
Job properties
You can see the job properties by running conman sj
<job_name>;props
, where<job_name> is the
database job name.
You can export some of the database job properties that you see in the Extra Information section of the output command, to a successive job in the same job stream or in a different job stream. For more information about the list of job properties that you can export, see Properties for database jobs.
Job log content
You can see the job log content by running conman sj
<job_name>;stdlist
, where <job_name> is the
database job name.
See also
From the Dynamic Workload Console you can perform the same task as described in
For more information about how to create and edit scheduling objects, see