Run before and after SQL statements property

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, UPDATE, or DELETE 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, or enter the fully qualified path to the file that contains the SQL statement. 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 reside on the engine tier host, and you must set the Read Before SQL statement from file, Read Before SQL (node) statement from file, Read After SQL (node) statement from file or Read After SQL 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 message and continue processing the remaining statements in the set. To configure the connector to report a warning message, set the Stop the job when Before SQL statement fails, Stop the job when Before SQL (node) statement fails, Stop the job when After SQL (node) statement fails, or Stop the job when After SQL statement fails property to No.

When the connector is used to write records to the database and is configured to perform a table action on the target table before writing data, you can use the Perform table action first property to control what is performed first, the Before SQL statement or the table action statements.