Syntax diagram for MSSQL database job type

Purpose

This section provides detailed information about the syntax diagram for the MSSQL database job type. 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 (/MSSQL), you can set CMDTYPE to either JOB or SQL and specify the related job or SQL query in the STATEMENT keyword. If you set CMDTYPE to JOB, you can specify a job saved on the server; if you set CMDTYPE to SQL, you can specify a statement in the native language of the database that you selected.
If you set CMDTYPE to JOB, you can optionally define the SYNCTYPE and POLLINGIVL keywords. This information is not visible in the syntax diagram.
If you set CMDTYPE to SQL, you cannot define the SYNCTYPE and POLLINGIVL keywords. This information is not visible in the syntax diagram.
You can specify any number of CMDTYPE, SYNCTYPE, and POLLINGIVL keywords, according to the corresponding STATEMENT keyword. The STATEMENT keyword must be specified before the corresponding CMDTYPE, SYNCTYPE, and POLLINGIVL keywords. For example, you can specify CMDTYPE3, SYNCTYPE3, and POLLINGIVL3 only if you have previously specified STATEMENT3.

Format

Parameters

CMDTYPEid(SQL|JOB)

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 (/MSSQL), you can set CMDTYPE to either JOB or SQL and specify the related job or SQL query in the STATEMENT keyword. If you set CMDTYPE to JOB, you can specify a job saved on the server; if you set CMDTYPE to SQL, you can specify a statement in the native language of the database that you selected.

DBNAME(database name)

The name of the database. This keyword is required.

DBTYPE(MSSQL)

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.

POLLINGIVLid(polling int|10000)

The polling interval in milliseconds. This keyword is valid only if you set CMDTYPE to (/JOB). You can specify a series of POLLINGIVLid keywords, where id is a unique numeric value, including null and zero. The id of the POLLINGIVL keyword must match the id of the related STATEMENT keyword. For example, you can specify POLLINGIVL3 only if you specify STATEMENT3.

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.

SYNCTYPEid(YES|NO)

Specifies if synchronization is required. This keyword is valid only if you set CMDTYPE to (/JOB). You can specify a series of SYNCTYPEid keywords, where id is a unique numeric value, including null and zero. The id of the SYNCTYPE keyword must match the id of the related STATEMENT keyword. For example, you can specify SYNCTYPE3 only if you specify STATEMENT3.

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 run job SPMTEST_BACKUP without synchronization and with a polling interval of 100 milliseconds, and to select all records from the DBO.TWSRECORDS table:

//JOBREC                
JOBTYPE(/database) 
DBNAME(SCVTEST) 
DBTYPE(MSSQL) 
SERVER(SQLCON008) 
PORT(1278) 
STATEMENT1(SPMTEST_BACKUP)
CMDTYPE1(JOB) 
SYNCTYPE1(NO)
POLLINGIVL1(100)
STATEMENT2(SELECT * FROM DBO.TWSRECORDS)
CMDTYPE2(SQL) 
JOBUSR(PWR_USER) 
JOBPWD(YES) 
//END JOBREC