JDBC Job Definitions

To create a JDBC Job Definition:

  1. Navigate to Configure > Automate > Job Definitions.

  2. Click New.

  3. Choose a Partition and enter a Name.

  4. Choose JDBC Definition from the Type dropdown list.

  5. Enter the username of a database user with a JDBC Credential in the Run As User field. The syntax for this field is [{virtual}:]<user>[@<endpoint>]. If you leave this field empty, the value of the DefaultRunAsUserJob Server Parameter is used. Alternatively, you can specify that the Job Definition should execute as the Job owner.

  6. Enter the desired commands in the Source field.

Note: See Configuring JDBC Support for information on setting up RunMyJobs for JDBC.

Parameters

You can add the following JDBC-specific Job Parameters to override the Job Server parameters with the same name. Parameter names that contain the name of a database vendor are specific to that database vendor. Array Parameters are supported and will be passed on as such to the target database. Arrays must have unique elements.

Parameter Description
JDBCBindMode The parameter bind mode. If this parameter is set to OracleNamed (Oracle only), parameters are bound by name and a single PL/SQL block is expected. Otherwise they will be either bound by position (with PARAMETER<n>, where <n> is the order) or by name using the syntax (?{name}).
JDBCEnableParameterSubstitution Enables substitution variables when set to Yes. A process parameter named <parameter> referenced in the process source as &{<parameter>} will be replaced with the value of this Job Parameter. This syntax is deprecated in favor of the ?{} syntax, because the &{} syntax is vulnerable to code injection.
JDBCOutputFormat The output format to use. Valid values are HTMLText, XMLText, and CSV.
JDBCMultiStatementSeparator A regular expression used to delimit multiple queries in the process source. For example: ;\n. This cannot be used in conjunction with JDBCBindMode set to OracleNamed. Set this to *; and you can enter comments in the source after the queries terminated with a ;, provided each query is on its own line.
JDBCOracleDBMSOutput (Oracle only) Boolean parameter that enables/disables Oracle dbms_output. Equivalent to SET SERVEROUTPUT ON/OFF The default is false.
JDBCOracleDBMSOutputDestination (Oracle only) stderr.log or stdout.log. Defaults to stdout.log for XML and HTML, and stderr.log for all others.
JDBCOracleDBMSOutputHeader (Oracle only) Header to print at beginning of DBMS output. Defaults to <h3>Output</h3><hr><pre> for HTML, <output> for XML, and nothing for all others.
JDBCOracleDBMSOutputFooter (Oracle only) Footer to print at end of DBMS output. Defaults to </pre></hr> for HTML, </output> for XML, and nothing for all others.
PARAMETER<n> Parameter(s) for the stored procedure. The name will be used as bind position. These are also made available as variables in the source. Note that the name is case-sensitive.
JDBCProcedureName The name of a stored procedure or function to execute. If this parameter is set, the contents of the Source field are ignored.
JDBCReturnValue A Parameter used to store the return value of the function specified in JDBCProcedureName. This Parameter is ignored for stored procedures.
JCS_USER The database user to use for the connection.
JCS_ENDPOINT The database identifier to use for the connection.
JCS_PASSWORD The password of the database user to use for the connection (not recommended; use Credentials instead).
JDBCStatementFeedbackFormat The verbosity of the SQL statement feedback in JDBC jobs. The options are Simple, Normal, and Extended. For example, if you create a table named "MyTable" in the Source field, Simple results in Statement processed, Normal results in Table created, and Extended results in Table MyTable created.
JDBCStatementTimingFormat Should the duration of SQL statements in JDBC jobs be printed? And if so, in what format? The options are None, MilliSeconds, and Pretty (the default).

Depending on the bind mode, you can specify Parameters as follows:

JDBCBindMode set to Ordinal (default):

  • ?: Bound by position. The ?'s are counted from left to right. For each ?, a Parameter PARAMETER<n> must be defined, where <n> is the position of the ?. Note that you cannot re-use a ? when you use multiple SQL statements; each ? will be bound in the order it appears in the Source field.
  • <native_database_syntax>: Bound by position using the native database syntax for positional binding placeholders (for example :<n> on Oracle, where <n> matches <n> from the Parameter name). For each placeholder, a Parameter PARAMETER<n> must be defined.
  • ?{<name>}: Bound by name, where <name> is the name of the Parameter.

JDBCEnableParameterSubstitution set to Yes:

  • &{<name>}: Substituted by name. The placeholder is replaced with the value of the Parameter in the Source field. This potentially exposes you to SQL injection, so Redwood recommends using Parameter constraints to validate input.

JDBCBindMode set to OracleNamed:

  • <name>: Bound by name, where <name> is the name of the Parameter. Redwood recommends using uppercase Parameter names, because Oracle defaults to uppercase variable names. If you use lower or mixed-case names, you will have to quote your Parameter names with double quotes (") accordingly in the Source field.

Running the Job Definition as the Job Owner

To specify the Job owner in the Run As User field, use the {forcejobowner} or {tryjobowner} syntax. The RunMyJobs username of the submitter must match the username or the virtual username of a valid JDBC Credential.

The syntax is as follows:

  • [{virtual}:]{forcejobowner}[@<endpoint>]
  • [{virtual}:]{tryjobowner}[@<endpoint>]
  • [{virtual}:]{tryjobowner}[@<endpoint>]:[{virtual}]<user>[/<password][@<endpoint>]

The following table contains examples for using the Job owner and the potential consequences of a missing Credential.

Run As User field Consequence
{forcejobowner}@PS_JDBC_ORCL If there is no Credential with the username of the Job submitter, the Job fails.
{tryjobowner} If there is no Credential with the username of the Job submitter, the value of Job Server parameter DefaultRunAsUser is used. The Job Server where the Job runs specifies the Endpoint.
{tryjobowner}@PS_JDBC_ORCL:SYSJCS If there is no Credential with the username of the Job submitter, the SYSJCS Credential is used.

Note: The {forcejobowner} and {tryjobowner} syntaxes are not supported in the JCS_USER Job Definition Parameter.

For example, assume you have a Credential for a virtual user named oracle_user for the database. You can specify the virtual user as follows in the Run As User field:

{virtual}:oracle_user

You can also just specify the username:

scott

If you have a valid Credential for user scott for the target database, the connection should succeed.