Using Dynamic SQL with Sybase Replication Server

November 24, 2010

By 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. 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.

admin config,"dynamic_sql"  

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
    • More than 255 columns in the insert command or in the where clause of the delete command
    • 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.

Server 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:

admin config,"dynamic_sql"

Connection Level:

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:

admin config,"connection",”dataserver”,”database","dynamic_sql"

Table Level:

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
      sp_setreptable my_table, true 

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" 

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:

     admin stats,reset,dsi,TEST_ASE15,trgdb

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

  admin stats,dsi,TEST_ASE15,trgdb

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
      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.

      admin stats,dsi,TEST_ASE15,trgdb

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
      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.