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.
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.
Dynamic SQL is off by default at a server and connection level. Configure dynamic SQL at a server or a connection level by issuing these 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
create/alter replication definition commands allow you to control the application of dynamic SQL on each table through replication definition.
See the Replication Server Reference Manual for information about create/alter replication definition commands.
You can change the dynamic SQL execution at the table level for a specific replicate database by using:
set dynamic_sql {on | off}
for replication definition with replicate at
data_server.database
At the replication definition level, the default is to use dynamic SQL. You only need to use these commands to change the dynamic SQL usage if you want to exclude tables from dynamic SQL. To check for dynamic SQL usage, turn on stats_sampling and run admin stats, dsi command and look for DSIEDsqlPrepared, DSIEDsqlExecuted and other dynamic SQL related counters.
Use stored procedures rs_helprep, rs_helpsub, and rs_helppubsub to display dynamic SQL setting for each replication definition.
See “rs_helprep”, “rs_helpsub”, and “rs_helppubsub,” in Chapter 6, “Adaptive Server Stored Procedures” in the Replication Server Reference Manual for information about using these stored procedures.
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.
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.
Replication Server converts user-defined datatypes to Open Client/Server™ (OCS) datatype in a dynamic SQL command.
If data falls outside Sybase ranges that cause dynamic SQL to fail, DSI logs an error message and resends dynamic SQL using the language command. DSI shuts down only if the language command also fails.
If this condition happens frequently, disable dynamic SQL from the table replication definition or use the set dynamic_sql off command.
Use any of these commands to turn off dynamic_sql:
alter connection... set dynamic_sql off – turns dynamic SQL off for all commands in this connection.
create/alter replication definition...without dynamic_sql – turns dynamic SQL off for all commands using this replication definition.
set dynamic_sql off for replication definition with replicate at... – turns dynamic SQL off for all commands using this replication definition at this replicate connection.
