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 run them repeatedly.
Dynamic SQL now supports heterogeneous replicate databases including Oracle, Universal Database (UDB), DB2, and Microsoft SQL. 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
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.
Dynamic SQL commands support the data within Sybase range. 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 dynamic_sql off:
alter connection... set dynamic_sql off – turns dynamic SQL off for all commands replicating to 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.
