Using Dynamic SQL with Sybase Replication Server
November 24, 2010By Manish Negandhi
Manish Negandhi explains how dynamic SQL works with Sybase Replication Server and discusses various methods of setting up dynamic SQL with Sybase Replication Server, including a step-by-step example.
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. Lets 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.
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.
Replicating with dynamic SQL Step-by-Step
The example below explains how to set up dynamic SQL step-by-step. In the example, the source database name is srcdb and the target database name is trgdb, table name is my_table. The test is carried out on Replication Server version 15.5.
1. Mark source and target database for replication using rs_init utility.
2. Create test table in source and target ASE databases.
create table my_table (col1 int,col2 varchar(20),col3 datetime default getdate() null )
3. Create replication definition and subscription in Replication Server.
create replication definition my_table_rdf with primary at TEST_ASE15.srcdb with all tables named my_table (col1 int, col2 varchar (20), col3 datetime) primary key (col1) create subscription my_table_sub for my_table_rdf with replicate at TEST_ASE15.trgdb without materialization
4. Mark table for replication, in primary ASE database execute:
use srcdb go sp_setreptable my_table, true go
5. Enable Replication Server trace flag to verify what the Data Server Interface (DSI) thread is trying to apply on the target database. Execute this in Replication Server. After turning the trace, the transaction applied by DSI will be displayed in the Replication Server error log. You can turn off the trace by replacing on with off at the end of your testing.
trace "on","dsi","dsi_buf_dump" go
6. Verify how DSI stats are accumulated in different dynamic SQL counters. Check the status for stats_sampling by executing the following:
admin stats, status
If stats_sampling is not turned on, turn it on by executing:
configure Replication Server set stats_sampling to 'on'
Setting stats_sampling parameter to on turns on sampling for an indefinite period; you can turn it off after your testing.
7. Now reset DSI counters; this command will reset counters for target database:
If you are already accumulating any counters for DSI, you might want to save it to RSSD with the save option of the admin stats command before resetting it.
8. Verify counters are reset by executing
9. We will first execute a few transactions without enabling dynamic SQL and see if and how transactions are replicated and stats are accumulated in counters.
use srcdb go insert into my_table (col1,col2) values(1,"1st row") go 5
10. Execute the below mentioned command in Replication Server and look for counters DSIEDsqlExecuted and DSIEDsqlPrepared. You might not see the value of these counters if the counters have 0 value and if parameter stats_show_zero_counters is not turned on.
11. Check how transactions are applied by DSI on the target database by looking at the Replication Server error log. You will see five insert statements with values that will look like the following:
T. 2010/10/24 12:19:07. (33): 'begin transaction [0a] insert into my_table (col1, col2, col3) values (1, '1st row', '20101024 12:19:05:000') [0a] insert into my_table (col1, col2, col3) values (1, '1st row', '20101024 12:19:05:013') [0a] insert into my_table (col1, col2, col3) values (1, '1st row', '20101024 12:19:05:013') ' T. 2010/10/24 12:19:07. (33): '[0a] insert into my_table (col1, col2, col3) values (1, '1st row', '20101024 12:19:05:013') [0a] insert into my_table (col1, col2, col3) values (1, '1st row', '20101024 12:19:05:013') '
12. Turn on dynamic_sql at the connection level and verify if dynamic_sql is turned on.
suspend connection to TEST_ASE15.trgdb alter connection to TEST_ASE15.trgdb set dynamic_sql to 'on' resume connection to TEST_ASE15.trgdb admin config,"connection","TEST_ASE15","trgdb","dynamic_sql"
13. Insert a few transactions again in primary and see how the dsi counters are affected.
use srcdb go insert into my_table (col1,col2) values(1,"1st row") go 10
14. Execute admin stats,dsi,TEST_ASE15,trgdb and check for counters DSIEDsqlPrepared and DSIEDsqlExecuted. Here is the output:
Observer Obs Rate x/sec DSIEDsqlPrepared 1 0 DSIEDsqlExecuted 10 0
Note that statement is prepared only once while it is executed 10 times. Also in the Replication Server error log you will not see SQL command language. Thus, Sybase Replication Server improves replication performance by letting DSI prepare dynamic SQL statements at the target user database once and executing them repeatedly.
Manish Negandhi has extensive experience and expertise in software engineering, team management and leading DBA teams for mission critical database environments. Manish has been working with Sybase ASE,Replication Server and Sybase IQ products, is a Sybase certified professional and member of TeamSybase.Manish can be reached via email.