Specifying SQL to control refresh operations

By default, CDC Replication deletes all rows in the target table in response to a table-level clear or refresh operation. You can specify a SQL WHERE clause that restricts the rows you want CDC Replication to delete in response to a table clear or refresh operation.

CDC Replication only deletes or truncates the rows for which the condition is true. For example, you may want to use the WHERE clause to delete rows from the supplier table. The SQL statement SupplierName= 'IBM' would delete all rows from the supplier table where the SupplierName is IBM®.

You can also specify additional SQL statements that execute after CDC Replication applies a table refresh or truncate/clear operation to the target table.

For all CDC Replication replication engines (except for the CDC Replication Engine for Db2® for z/OS® and the CDC Replication Engine for Db2 Database ), you need to run the DMSQL command to enable the Additional SQL feature in Management Console. For more information, see the Commands section in the documentation for the appropriate CDC Replication replication engine.

If you have installed the CDC Replication Engine for Db2 for z/OS, then you need to set the keyword ALLOWSQL to YES to enable theAdditional SQL feature in Management Console. You can add this keyword to the CHCDBMxx Configuration Control data set member.For more information about this keyword, see ../../com.ibm.cdcdoc.cdcforzos.doc/refs/allowsql.html

If you have installed the CDC Replication Engine for Db2 Database, then you need to create a metadata table to enable the Additional SQL feature in Management Console. For more information about this feature, see ../../com.ibm.cdcdoc.cdcfordb2luw.doc/tasks/enablesqlstatementsinmanagementconsole.html

Prior to issuing SQL statements in Management Console, consider the following:
  • If you are referencing a database, table, or column name that contains spaces, then you must enclose the name in square brackets. For example, to reference the table name EMP NY, you must enter it as [EMP NY].
  • If your delete WHERE clause references a character column, the specified value must be enclosed in single quotation marks. For example, MGR = 'Anna Kim'.
  • You cannot reference LOB columns in delete WHERE clauses.
  • Specify only SQL INSERT, UPDATE, and DELETE statements.
  • SQL statements must be 4,000 bytes or less in length. If you specify SQL statements that do not fit on one line, do not press the Enter key to break the statements over more than one line. The text will feed automatically to the next line.
  • No support for logical branching and iteration.
  • The database running on the target server must recognize the syntax of the SQL statements.
  • Separate multiple SQL statements by semicolons (;). If a character string in a SQL statement contains a semicolon, specify two semicolons consecutively (;;) in the string.
  • Management Console does not verify SQL statements for syntactical correctness. The target DBMS verifies statements during replication.