Run before and after SQL statements property
Use the Run before and after SQL statements property to configure the connector to run semicolon-separated SQL statements before or after processing data. You can configure the connector to run SQL statements before or after processing any data in a job or to run SQL statements once before or after processing the data on each node.
Usage
Running SQL statements before or after processing data is useful when you need to perform operations that prepare data source objects for data access. For example, you might use an SQL statement to create a target table and add an index to it. The SQL statements that you specify are performed once for the whole job, before any data is processed.
After the connector runs the statements that are specified in the Before SQL statement property or After SQL statement property, the connector explicitly commits the current transaction. For example, if you specify a DML statement such as INSERT in the Before SQL statement property, the results of the DML statement are visible to individual nodes.
To run SQL statements on each node that the connector is configured to run on, use the Before SQL (node) statement property or the After SQL (node) statement property. The connector runs the specified SQL statements once before any data is processed on each node or once after any data is processed on each node. Then, the connector explicitly commits the current transaction.
When you specify the statement to run before or after processing, enter the SQL statements. Do not include input bind variables or output bind variables in the SQL statement. If the statement contains these types of variables, the connector logs an error message, and the operation stops.
If you specify a file name, the file must be on the computer where the InfoSphere® Information Server engine tier is installed, and you must set the corresponding Read statement from file property to Yes.
When the connector runs a set of statements that are specified in any of the Before SQL statement, Before SQL (node) statement, After SQL (node) statement, or After SQL statement properties, it reports an error and stops the job if any of the statements in the set fail. You can configure the connector to report a warning or informational message and continue processing the remaining statements in the set. To configure the connector to continue the job when a statement fails, set the Fail on error property for the Before SQL statement, Before SQL (node) statement, After SQL statement, or After SQL (node) statement property to No. When a statement fails and the Fail on error property is set to No, you can use the Log statement error as property to define the severity of the error message to log.
The order of the SQL statements that the connector runs before or after processing data differs from the normal operation when the write mode is insert and a staging table is not used. For these jobs, the connector writes directly to the target table, and the statements are run in the following order:
- The statements that are specified for the Before SQL statement property
- On each node, the statements that are specified for the Before SQL (node) statement property
- The INSERT statements that write to the target table
- On each node, the statements that are specified for the After SQL (node) statement property
- The statements that are specified for the After SQL statement property
However, when the Staging table mode property is set to Automatic or Existing, the connector writes to the target table after the statements that are specified for the After SQL (node) statement property are run. The statements are run in the following order:
- The statements that are specified for the Before SQL statement property
- On each node, the statements that are specified for the Before SQL (node) statement property
- The INSERT statements that write to the staging table
- On each node, the statements that are specified for the After SQL (node) statement property
- The statements that write from the staging table to the target table
- The statements that are specified for the After SQL statement property