Syntax diagram for DB2® and Oracle database job types
Purpose
- You can define multiple statements in the same job by specifying a series of STATEMENTid keywords, where id is a unique numeric value, including null and zero. For example, you can define STATEMENT, STATEMENT0, STATEMENT1, STATEMENT2, and so on.
- If you set DBTYPE to
DB2
orOracle
, you can set CMDTYPE toSQL
. - You can specify any number of CMDTYPE keywords, according to
the corresponding STATEMENT keyword. The STATEMENT keyword must be
specified before the corresponding CMDTYPE keyword. For example, you
can specify
CMDTYPE3
only if you have previously specifiedSTATEMENT3
.
Format
Parameters
- CMDTYPEid(SQL)
- The type of command to be run. You
can specify a series of CMDTYPEid keywords, where id is
a unique numeric value, including null and zero. The id of
the CMDTYPE keyword must match the id of the related STATEMENT
keyword. For example, you can specify
CMDTYPE3
only if you specifySTATEMENT3
.If you set DBTYPE to
(/DB2)
or to(/ORACLE)
, you can set CMDTYPE toSQL
. - DBNAME(database name)
- The name of the database. This keyword is required.
- DBTYPE(DB2|ORACLE)
- The database type. This keyword is required.
- JOBPWD(YES|NO|AGENT)
- Specifies if the user name entered
in JOBUSR or set by using the job-submit exit EQQUX001 is associated
with a password.
If you set JOBPWD to
YES
, HCL Workload Automation for Z searches for the user password in the USRPSW keyword of the USRREC statement (for details, see Customization and Tuning).If you set JOBPWD to
AGENT
, this means that the password is resolved locally on the agent. The password must have been defined on the agent by means of the param command. This feature is available independently of the operating system of the workstation.Typically, the password is required for users who schedule jobs to run on Windows™ workstations. Set JOBPWD to
NO
if the user works with UNIX™ workstations and if no password is required. - JOBTYPE(/xajob/access method name|/j2ee/jms|/web service|/file transfer|/database|/java)
- To run a database job, specify /database.
For information about the other types of job, see the specific section
for each job type. This keyword is required.
- /database
- Set this value to submit a database job. Before you can run a
query on a database, download the JDBC drivers for your database client
to each HCL Workload Automation Agent on
which you want to submit database jobs. Specify the path to the database
client jar files in the
DatabaseJobExecutor.properties
file, located in theJavaExt\cfg
directory in your HCL Workload Automation installation directory. Define thejdbcDriversPath
property to point to the JDBC jar files directory, for example,jdbcDriversPath=c:\\mydir\\jars\\jdbc
. The JDBC jar files must be located in the specified directory or its subdirectories. Ensure that you have list permissions for the directory and its subdirectories.
- JOBUSR(user name)
- The user name for accessing the database. If the user schedules jobs to run on Windows™ workstations, ensure that a user password is also defined (see the JOBPWD keyword).
- If you are defining a Windows™ domain
user, use the following format:
JOBUSR(domainName\user1)
- If you are defining a Windows™ user
in the username@internet_domain format, use the
following format:
JOBUSR('administrator@mywindom.com')
To specify the user name, you can also use the job-submit exit EQQUX001. This user name overrides the value specified for JOBUSR. In turn, the value specified for JOBUSR overrides the value specified for USRNAM in the USRREC statement.
- If you are defining a Windows™ domain
user, use the following format:
- PORT(port number)
- The port number for the database job. This keyword is required.
- SERVER(host name)
- The host name of the server where the
file transfer is to be performed or where the database is located.
This keyword is required if you set JOBTYPE to
(/file transfer)
or to(/database)
. - STATEMENTid(string)
- The string defining the SQL query or job. This keyword is required.
You can define multiple statements in the same job by specifying a series of STATEMENTid
keywords, where id is a unique numeric value, including null and zero. For example, you can
define STATEMENT, STATEMENT0, STATEMENT1, STATEMENT2, and so on.
When running stored procedures, ensure that the returned result is in tabular format. Results in any other format are not supported.
- WSNAME(workstation_name)
- The name of the workstation from which user name and password must be retrieved. User name and password can be specified in the statement, using the JOBUSR and JOBPWD keywords, or can be associated to a workstation with the USRREC statement. With the WSNAME keyword, you can specify a workstation where the user name and password are stored, which is different from the workstation where the job runs.
Examples
The following is an example of a JOBREC statement to select all records from table SYSTOOLS.POLICY:
//JOBREC
JOBTYPE(/database)
STATEMENT20(SELECT * FROM SYSTOOLS.POLICY)
DBNAME(DBWEB)
DBTYPE(DB2)
SERVER(9.168.99.87)
PORT(50000)
JOBUSR(Administrator)
JOBPWD(YES)
CMDTYPE20(SQL)
//END JOBREC
//JOBREC
JOBTYPE(/database)
STATEMENT1(SELECT country_id, country_name FROM countries)
DBNAME(SAMPLE)
DBTYPE(ORACLE)
SERVER(9.168.115.37)
PORT(70000)
CMDTYPE1(SQL)
JOBUSR(ZCENTUMB)
JOBPWD(YES)
//END JOBREC