Dynamic SQL for Enhanced Replication Server Performance

Dynamic SQL in Replication Server enhances replication performance by allowing Replication Server Data Server Interface (DSI) to prepare dynamic SQL statements at the target user database and to execute them repeatedly.

Instead of sending SQL language commands to the target database, only the literals are sent on each execution, thereby eliminating the overheads brought by SQL statement syntax checks and optimized query plan builds.

You can use dynamic SQL in a user database connection for a language command if:

The command is insert, update, or delete.
There are no text, image, unitext, or java columns in the command.
There are no NULL values in the where clause of an update or delete command.
There are no more than 255 parameters in the command:
- insert commands can have no more than 255 columns.
- update commands can have no more than 255 columns in the set clause and where clause combined.
- delete commands can have no more than 255 columns in the where clause.
The command does not use user-defined function strings.

Limitations

Dynamic SQL has these limitations:

If a table is replicated to a standby or MSA connection using an internal replication definition and dynamic SQL is enabled for the connection, any new replication definition for the table should define the column order consistent with the column order in the primary database. Otherwise, the existing prepared statements may be invalidated, and may require the standby or MSA connection to be restarted.
Dynamic SQL requires ASE or DirectConnect™ 12.6.1 ESD#2 for UDB as target database.