User-defined SQL statements for writing data to the database

In some cases, the INSERT, UPDATE and DELETE statements that are generated automatically might not be suitable for the write operation that you want the connector to complete on the database. In these cases, you can enter the INSERT, UPDATE and DELETE statements manually.

To configure the DRS Connector stage to run user-specified SQL statements instead of auto-generated statements you must set the Write mode property in the Usage section to User-defined SQL.

In the DRS Connector stage the statements need to be specified in the Statement property in the SQL category in the Usage section.

The specified statements need to use bind parameters that the stage associates with the columns on the input link. The bind parameters are specified as question marks. The stage then internally translates the question marks to comply with the bind parameter syntax used by the backend database. When multiple statements are specified, the association between question marks and link columns is performed separately for each statement.

The DRS Connector stage also supports InfoSphere® DataStage® syntax for bind parameters which makes use of the ORCHESTRATE keyword to denote bind parameters. In this case the parameters directly reference columns on the link. Each parameter is specified in form ORCHESTRATE. param_name where param_name corresponds to the column on the link that should be associated with that parameter.

You can specify the statements in one of the following combinations:

A single INSERT statement

The stage associates question mark parameters in the statement with the columns on the link in the same order. The first question mark is associated with first column on the link, the second question mark with the second column on the link and so forth. In this case the stage operates in just like it was configured in Insert mode except the INSERT statement is specified manually.

A single UPDATE statement

The stage associates question marks in the WHERE clause of the statement with the columns on the link marked as Key columns. The first question mark in the WHERE clause is associated with the first key column on the link, the second question mark with the second key column and so forth. The question marks in the SET clause of the statement are associated with columns on the link that are not marked as Key columns. Again the first question mark in the SET clause is set with the first non-Key column on the link, the second question mark with the second non-Key column and so forth. In this example, the stage operates similarly to Update matching rows mode except the UPDATE statement is specified manually.

A single DELETE statement

The stage associates question marks in the WHERE clause of the statement with the columns on the link marked as Key columns. The first question mark in the WHERE clause is associated with the first key column on the link, the second question mark with the second key column and so forth. The link columns that are not marked as Key columns are ignored. In this example, the stage operates just like it was configured in Delete matching rows mode except the DELETE statement is specified manually.

An INSERT statement followed by an UPDATE statement

The statements are separated by a semicolon character. In this case, the stage operates just like it was configured in Insert then update write mode except the statements are specified manually.

An UPDATE statement followed by an INSERT statement

The statements are separated by a semicolon character. In this case, the stage operates like it was configured in Update then insert write mode except the statements are specified manually.

A DELETE statement followed by an INSERT statement

The statements are separated by a semicolon character. In this case, the stage operates like it was set in Delete then insert write mode except the statements are specified manually.

Any combination of statements

The stage runs each statement separately.

However, if the DRS Connector stage is configured for the Oracle database type, this combination is supported, but the stage does not run the statements individually. Instead the stage runs the statements as a single anonymous PL/SQL block on every input row. Also in this case array write mode is disabled and the input rows are sent to the database one by one where the PL/SQL block statement runs for them.

In addition to specifying SQL statement directly in the stage, it is possible to store statements in a file on the InfoSphere DataStage Server machine and configure the stage to reference this file. The full path to the file is specified in the stage.

To configure the DRS Connector stage to read statements from a file you must set the Write mode property in the Usage section to User-defined SQL file. The file path can then be provided through the Statement property in the Usage section to User-defined SQL file. If a FILE= or {FILE} prefix is specified for the Statement property in the DRS Connector stage, then the stage treats the remainder of the value as the path to the file that contains the user-defined SQL statements. This is true if the Write mode property in DRS Connector is set to User-defined SQL file, but also when it is set to User-defined SQL.