Before SQL and After SQL statements

You can run custom SQL statements on the target database before and after data is processed.

Before SQL: Custom SQL statements are run when the job starts and before any data is processed by the stage.
After SQL: Custom SQL statements are run after all the data has been processed by the stage and just before ending the job.

If multiple statements are specified as Before SQL or After SQL statements, they need to be separated by a semicolon character.

The stage runs Before SQL statements in a one database transaction and After SQL statements in another. If a pair of semicolons is encountered in the statements, the stage interprets it as the user-defined commit point. It commits the current transaction and starts a new one.

To configure the DRS Connector stage to run Before SQL and After SQL statements you must set the Before/After SQL category in Usage section to Yes. The Before SQL and After SQL statements are then specified in the Before SQL and After SQL properties located under the Before/After SQL category.

You can specify Before SQL and After SQL statements in a file located on the InfoSphere® DataStage® Server machine. To point the stage to this file, instead of typing in the actual statements in the stage you must type FILE= filepath or {FILE} filepath, where filepath is the fully-qualified path to the file with the actual SQL statements.

The stage rolls back the current transaction and fails the job when any statement in the Before SQL and After SQL lists of statements fails to run successfully. You can change this default behavior.

To cause the DRS Connector stage to continue processing statements when this happens, you must set the properties Continue if Before SQL failed and Continue if After SQL failed under the Before/After SQL category in the Usage section to Yes.