Using SQL Statement Replication for Warm Standby

SQL statement replication complements log-based replication and addresses performance degradation caused by batch jobs.

Prerequisites

You can perform SQL statement replication only with Adaptive Server 15.0.3 and later.

Task

In step 4 of the task, "Marking the active database for replication," the threshold for the database is set to 10. Therefore, SQL statement replication is used only when there are more than 10 rows. Insert 10 rows in to the table “t1”:

insert into t1 values (2,'first row',getdate())
insert into t1 values (3,'first row',getdate())
insert into t1 values (4,'first row',getdate())
insert into t1 values (5,'first row',getdate())
insert into t1 values (6,'first row',getdate())
insert into t1 values (7,'first row',getdate())
insert into t1 values (8,'first row',getdate())
insert into t1 values (9,'first row',getdate())
insert into t1 values (10,'first row',getdate())
insert into t1 values (11,'first row',getdate ())

Update a number of rows less than or equal to the threshold value.

update t1 set b = 'no SQL' where a < 3
go
(2 rows affected)
T. 2009/10/28 22:18:55. (138): Command(s) to
'wingak1505i.pubs2':
T. 2009/10/28 22:18:55. (138):
'begin transaction [0a]
update dbo.t1 set a=1, b='no SQL',
c='20091028 22:10:42:383'
where a=1 [0a] update dbo.t1
set a=2, b='no SQL', c='20091028
22:12:24:093' where a=2 '

The trace statements show the individual SQL updates to each row.

Update all the rows:

update t1 set b = 'yes SQL'
go
(11rows affected)

The trace output shows the SQL statement, not the individual statements for each row.

T. 2009/10/28 22:23:35. (138): Command(s) to
'wingak1505i.pubs2':
T. 2009/10/28 22:23:35. (138): 'begin transaction
[0a] update dbo.t1 set b = 'yes SQL' '

Note: To turn off the tracing function in Replication Server, use:

trace "off",dsi,dsi_buf_dump
go