Using Before/After SQL Statements in DataStage

Use this property to define SQL statements that are run before or after data processing occurs.

If you set Before/After SQL to Yes, the subproperties are available for you to use. If you set Before/After SQL to No, the subproperties are not available to you. The default value is No.

When the connector runs SQL statements, it reports an error and stops running if a statement fails. You can configure the connector to report a warning message and continue running the remaining SQL statements. To make the connector report a warning for a particular SQL statement, deselect the Fail on error property under that statement.

Before SQL and After SQL are not intended to establish transient session states. For example, if you need to apply a schema name to a query, it should be specified as a part of the interaction properties. A schema name should not be specified dynamically as a part of Before/After SQL properties.

There can be subtle differences in the behavior across different database connectors, underlying client or driver dependencies and the internal implementation.

Setting the Before/After SQL option

  • If you use a connector as a source, in the connector properties, open the Output > Usage tab, and check the Run before and after SQL statements.
  • If you use a connector as a target, in the connector properties, open the Input > Usage tab, and check the Run before and after SQL statements.

Supported connectors

The following connectors and their optimized versions support SQL statements.

  • Amazon Redshift
  • Amazon RDS for MySQL
  • Amazon RDS for Oracle
  • Amazon RDS for PostgreSQL
  • Apache Derby
  • Apache Hive
  • Apache Impala
  • DataStax Enterprise
  • Exasol
  • Generic JDBC
  • Google BigQuery
  • Greenplum
  • IBM Cloud Databases for MySQL
  • IBM Cloud® Databases for PostgreSQL
  • IBM Db2®
  • IBM Db2 for DataStage
  • IBM® Db2 Big SQL
  • IBM Db2 for i
  • IBM Db2 for z/OS®
  • IBM Db2 on Cloud
  • IBM Db2 Warehouse
  • IBM Informix®
  • IBM Netezza® Performance Server
  • IBM Netezza Performance Server for DataStage
  • MariaDB
  • Microsoft Azure Databricks
  • Microsoft Azure SQL Database
  • Microsoft Azure PostgreSQL
  • Microsoft SQL Server
  • MySQL
  • ODBC
  • Oracle
  • Oracle Database for DataStage
  • PostgreSQL
  • Presto
  • SAP ASE
  • SAP IQ
  • SingleStoreDB
  • Snowflake
  • Teradata
  • Teradata database for DataStage
  • IBM watsonx.data