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