Syntax diagram for DB2® and Oracle database job types

Purpose

Syntax diagram for the DB2® and Oracle database job types. The following syntax rules apply:

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 or Oracle, you can set CMDTYPE to SQL.
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 specified STATEMENT3.

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 specify STATEMENT3.

If you set DBTYPE to (/DB2) or to (/ORACLE), you can set CMDTYPE to SQL.

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 the JavaExt\cfg directory in your HCL Workload Automation installation directory. Define the jdbcDriversPath 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.

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

The following is an example of a JOBREC statement to select columns country_id and country_name FROM table countries:

//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