Syntax diagram for MSSQL database job type
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
(/MSSQL)
, you can set CMDTYPE to eitherJOB
orSQL
and specify the related job or SQL query in the STATEMENT keyword. If you set CMDTYPE toJOB
, you can specify a job saved on the server; if you set CMDTYPE toSQL
, 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
, andPOLLINGIVL3
only if you have previously specifiedSTATEMENT3
.
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 specifySTATEMENT3
.If you set DBTYPE to
(/MSSQL)
, you can set CMDTYPE to eitherJOB
orSQL
and specify the related job or SQL query in the STATEMENT keyword. If you set CMDTYPE toJOB
, you can specify a job saved on the server; if you set CMDTYPE toSQL
, 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 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:
- 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 specifyPOLLINGIVL3
only if you specifySTATEMENT3
. - 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 specifySYNCTYPE3
only if you specifySTATEMENT3
. - 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
//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