Introduction to Dynamic SQL
Instead of sending SQL language commands, dynamic SQL sends only literals to the target database and thereby improves replication performance while eliminating overheads of SQL statement syntax checking. Dynamic SQL was introduced in Sybase Replication Server version 15.0.1 and later enhanced in version 15.1 to support heterogeneous replicate databases including Oracle, Universal Database (UDB), DB2, and Microsoft SQL. In Replication Server version 15.5, dynamic SQL was further enhanced to support the “replicate minimal column” clause along with a few more performance related enhancements. Let’s have a close look at how dynamic SQL works with Replication Server version 15.5.
Parameters affecting dynamic SQL settings
There are 3 parameters affecting dynamic SQL usages within Replication Server.
dynamic_sql:- Enables or disables dynamic SQL for Replication Server.
dynamic_sql_cache_size:- Number of database objects that can use dynamic SQL for a database connection; the default is 100.
dynamic_sql_cache_management:- Manages dynamic SQL caches for a connection. There are two possible values “fixed” and “mru”. If the value is set to “fixed”, Replication Server stops allocating new dynamic SQL statements once the number of dynamic SQL statements reaches dynamic_sql_cache_size. If values is set to “mru”, the most recently used statements are kept and the rest are deallocated by the Replication Server. The value of these configuration parameters can be queried using the below mentioned command in Replication Server.
Rules of using Dynamic SQL
Replication Server will make use of dynamic SQL only if the command is insert, update or delete. In the case of any of the below mentioned conditions, dynamic SQL will not be used by Replication Server.
* Text, image, unitext, or java columns in command
* NULL values in the where clause of delete and update command
* More than 255 parameters in the command
o More than 255 columns in the insert command or in the where clause of the delete command
o More than 255 columns in the set clause or the where clause of the update command
* The command, which uses user-defined function strings.
Enabling dynamic SQL
Dynamic SQL can be configured at different levels of replication sever component. There are three such levels; server level, connection level and table level.
To configure dynamic SQL at server level execute the following:
configure Replication Server set dynamic_sql to ‘on’
To verify if dynamic SQL is enabled execute the following:
To configure dynamic SQL at database connection level execute the following:
alter connection to server.db set dynamic_sql to ‘on’
To verify if dynamic SQL is enabled at connection level, execute the following:
To configure dynamic SQL at table level, execute the following:
set dynamic_sql on for replication_definition_name with replicate at REPLICATE_DATASERVER.database
To verify if dynamic SQL is enabled at the table level, execute rs_helprep and rs_helpsub for specific replication definition and subscription in RSSD database.