HCL Workload Automation, Version 9.4

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.
The following table lists the required and optional attributes for DB2, Oracle, and Netezza jobs:
Table 1. Required and optional attributes for the definition of a database job
Attribute Description and value Required
application name database ✓
dbms The database type where you want the job to run. Supported values are:
db2
For the DB2 databases
mssql
For the Microsoft SQL Server databases
oracle
For the Oracle databases
IBM Netezza
For the Netezza database
BigSQL
For the InfoSphere BigInsights BigSQL database
Hive
For the Apache Hive database
 ✓
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. Required for DB2 databases only.
driverClass The name of the JDBC driver class Required if you specify a custom database.
connectionUrl The string that is used to connect to the database, containing database URL, username and password Required if you specify a custom database.
driverPath 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.  
dbStatement The name of the database job to be run. Required if you specify an SQL statement
storedProcedure 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:
TEST.STORE_PROC1(VARCHAR,?)
TEST.STORE_PROC1(VARCHAR,VARCHAR,?)
TEST.STORE_PROC1(VARCHAR,?,?)
TEST.STORE_PROC1(VARCHAR,VARCHAR,?,?)
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.
ParameterTableValue key The name and values of the procedure expressed complying with the following syntax:
Stored procedure variable type
Supported values are:
  • IN
  • OUT
  • INOUT
Variable name
The name of the variable as defined in the stored procedure.
Variable type
Type of variable. Supported SQL types are:
  • DATE
  • DECIMAL
  • INTEGER
  • VARCHAR
0...n
Position of each variable as defined in the stored procedure.
For example:
Name	                      Value
IN VARIN DATE 0           2012-01-01
OUT VAROUT DATE 1         ?
 Required if you specify a stored procedure.
ParameterTableValue 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.  
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:
  • Enter a username specified in the database with the user definition (it is applicable to all operating systems on this job type) and key the statement:
    <jsdl:password>${password:username}</jsdl:password>
    The password is retrieved from the username user definition in the database and resolved at runtime. See Using user definitions on job types with advanced options for further details.
    You can also specify the user of a different workstation and use the following syntax for the password:
    <jsdl:password>${password:workstation#username}</jsdl:password>
  • Enter a user and password defined with the param utility command locally on the dynamic agent that will run the job (if the job is to be submitted to a pool or to a dynamic pool, the definition must be present on all the agents of the pool). Provided you defined the user name with the variable user and a password, the corresponding credential statements would be:
    <jsdl:userName>${agent:user}</jsdl:userName>
<jsdl:password>${agent:password.user}</jsdl:password>
    The user and password variables are resolved on the agent at runtime. See Defining variables and passwords for local resolution on dynamic agents for further details.
  • When integrated security is used for Microsoft SQL server, the sqljdbc_auth.dll library (that is part of the Microsoft JDBC Driver 4.0 for SQL Server package) must be placed in the same directory where the JDBC driver is located, and this path must be added to the PATH system environment variable.
  
The schema for DB2, Oracle, and Netezza databases is very similar, therefore the following example shows a job that runs a query on a DB2 database:
$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
Note: (1) the user name was defined on the agent that runs the job with a variable named 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

This section describes the required and optional attributes for MSSQL jobs. Each job definition has the following format and arguments:
Table 2. Required and optional attributes for the definition of an MSSQL job.
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:
  • Enter a username specified in the database with the username user definition (it is applicable to all operating systems on this job type) and key the statement:
    <jsdl:password>${password:username}</jsdl:password>
    The password is retrieved from the username user definition in the database and resolved at runtime. See Using user definitions on job types with advanced options for further details.
  • Enter a user and password defined with the param utility command locally on the dynamic agent that will run the job (if the job is to be submitted to a pool or to a dynamic pool, the definition must be present on all the agents of the pool). Provided you defined the user name with the variable user and a password, the corresponding credential statements would be:
    <jsdl:userName>${agent:user}</jsdl:userName>
<jsdl:password>${agent:password.user}</jsdl:password>
    The user and password variables are resolved on the agent at runtime. See Defining variables and passwords for local resolution on dynamic agents for further details.
  • When integrated security is used for Microsoft SQL server, the sqljdbc_auth.dll library (that is part of the Microsoft JDBC Driver 4.0 for SQL Server package) must be placed in the same directory where the JDBC driver is located, and this path must be added to the PATH system environment variable.
  
The following example shows a job that runs a job on an MSSQL database:
$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
Note: (1) The password for user 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 Table 20.

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

Creating job definitions.

For more information about how to create and edit scheduling objects, see

Designing your Workload.