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.
Configure dynamic SQL at a server or a connection level by issuing the following commands:
configure replication server
set { dynamic_sql |
dynamic_sql_cache_size |
dynamic_sql_cache_management }
to value
alter connection to server.db
set { dynamic_sql |
dynamic_sql_cache_size |
dynamic_sql_cache_management }
to value
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 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 keeps the most recently used statements and deallocates the rest to make room for the new statements if the value is mru.
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.
