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. In addition, DSI optimizes dynamic SQL statements by generating the language command only when the dynamic SQL command fails, and generating the prepared statement only once when the prepared statement is used for the first time.
If turned on, dynamic SQL will be used in a user database connection instead of a language command if:
The command is insert, update, or delete.
There are no text, image, java or opaque columns in the command.
There are no NULL values in the where clause for 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 clauses combined.
delete commands can have no more than 255 columns in the where clause.
The command does not use user-defined function strings.
The server-level configurations provide the default values for the connections created or started in the future. For database-level configurations:
dynamic_sql – turns dynamic SQL on or off for a replicate connection. Other dynamic SQL related configuration parameters take effect only if this parameter is set to on.
dynamic_sql_cache_size – tells the Replication Server how many database objects may use the dynamic SQL for a connection. This parameter is provided to limit the resource demand on the data server.
dynamic_sql_cache_management – manages the dynamic SQL cache for a connection. Once the dynamic SQL statements reaches dynamic_sql_cache_size for a connection, it either stops allocating new dynamic SQL statements if the value is fixed, or it keeps the most recently used statements and deallocates the rest to allocate new statements if the value is mru.
