How to check Sybase Replication Queue sizes!!

Space monitoring or Space used in repserver queues is administered in a similar way
to ASE transaction logs, But all the completed transactions will be removed automatically from stable queues. We can also find the latency or Replication performance based on the Queues size. rs_ticket is the best way to find/analyze latency but knowing the queue size also important in replication system for quick understandings.

Replication stable device – Use “admin disk_space” to monitor all stable devices.

admin disk_space

Displays use of each disk partition accessed by the Replication Server.

isql –U<user> -S<RepSrv> -P<pwd>

admin disk_space
go
 

Partition	Logical	Part.Id	Total Segs	Used Segs	State
/dev/vx/rdsk/sybase2/raw2g14	SQM14	112	2000	10	ON-LINE//
/dev/vx/rdsk/sybase2/raw2g13	SQM13	111	2000	6	ON-LINE//

Total Segs -  Total number of 1MB segments on a partition.

 We can also query the RepServer's RSSD to get full info:

select 'ASE/DB'=convert(char(40),d.dsname+'.'+d.dbname), 'Q_Type'=case q_type when 0 then 'OBQ' else 'IBQ' end, 'Size_Mb'=count(used_flag)

from rs_segments s, rs_databases d
where used_flag = 1
and s.q_number = d.dbid
group by d.dsname+'.'+d.dbname, q_type, used_flag

UNION

select 'ASE/DB'=convert(char(40),name), 'Q_Type'='RSI', 'Size_Mb'=count(used_flag)

from rs_segments s, rs_sites t
where used_flag = 1
and s.q_number = t.id
group by name, q_type, used_flag
order by 'DB/Site', Q_Type

ASE/DB                                Q_Type   Size_Mb    

-------                               ------   -----------
ASE1.bobj                                OBQ              1
ASE1.livedr                              OBQ              1
ASE1.rssd_drp                            IBQ              1
ASE1.rssd_drp                            OBQ              1

We can also try using admin who, sqm to find the queue size.

1> admin who, sqm

2> go

 Spid State                Info                                     Duplicates  Writes      Reads       Bytes       B Writes    B Filled    B Reads     B Cache     Save_Int:Seg

         First Seg.Block                 Last Seg.Block                  Next Read                       Readers     Truncs

 ---- -------------------- ---------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------------------

         ------------------------------- ------------------------------- ------------------------------- ----------- -----------

   48 Awaiting Message     133:0 ASE.user_store                             0           0           0           0           0           0           0           0 0:0

        0.1                             0.0                             0.1.0                                     0           1

   47 Awaiting Message     120:0 UTF_ASE.Bnk                                6    28140377     3305582 -1620639873      623325      386004       71758       71006 0:52468

         52468.1                         52468.1                         52468.2.0                                 1          

11 Awaiting Message 105:1 FSSOLTP.TMW 1662 41058993 43665390 -1496848285 7224036 6684446 7928857 6162835 0:196052 196052.16 196052.25 196052.26.0 1 1

The last segment and block values move as new data stored in the queue. First, the block advances until the segment is full (at block # 64); then, a new segment is assigned, and this segment may get the next segment number or not.

The first segment and block contain the oldest data to be handled by RepServer. It is a concept similar to the truncation point in ASE's syslogs. As data are used, oldest blocks and segments are no longer needed and the first segment-block marker advances.

The bold shown a queue with 10 blocks used (from 196052.16 to 196052.25).

Sybase Replication Quick HELP!!

~

Replication Server is Sybase's multiplatform system for distributing data over networks and maintaining server synchronization. Replication Server increases the flexibility and lowers the costs of managing multiple data management platforms. Sybase RepServer can replicate data across Sybase databases to Sybase/MS SQL databases, Sybase database tables to Any RDBMS scema tables - vice versa, and also from Any RDBMS schema tables to Any RDBMS schema tables.

Replication can improve your data infrastructure:

· High availability – Sales associates, branch offices, chain retail locations and more are able to exchange and share data from point-of-sale applications through regional and district offices to the corporate level and back again.

· Highly distributed – Consider sales and service companies where users are disconnected during the day but need to update orders/inventories and other information automatically after normal working hours. Advantage Replication provides an easy solution when data must be highly distributed.

· Offline Reporting or Data Warehousing – Moving data from production servers to reporting servers removes the processing loads of complex reports or data mining operations from production environments. Increase the speed and efficiencies of reporting without impacting day-to-day operations.

It's not so easy to start with Replication as it involves multiple Servers/Databases, but little Querying would give you overview and once you got the flow/architecture you can tune in.

You should know the ID Server (The 1st Replication Server installed on the System) and the ASE of the RepServer RSSD to start understanding the replication topology. you can use following T-SQL to start with. Many DBAs have doubts to know what kind of replication is in their environment (like W/S, MSA or table).

Query the RSSD to get info:

To find all the Replication Server names:

select id, name from rs_sites

To find all the warm standby databases in the replication including the Server name:

select distinct dsname, dbname from rs_databases

where ltype='L'

To find all the table-level/MSA databases in the replication including the Server name:

select distinct dsname, dbname from rs_databases

where ltype='P'

To find all the database repdefs list:

select dsname, dbname,dbrepname from rs_dbreps rd, rs_databases rs

where rs.dbid=rd.dbid

order by dsname, dbname

To find all the table repdefs list:

select dsname, dbname, objname, phys_tablename,deliver_as_name from rs_objects ro, rs_databases rd

where ro.dbid=rd.dbid

order by dsname, dbname

To find all the subscriptions list:

select dsname, dbname,subname from rs_subscriptions rs, rs_databases rd

where rs.dbid=rd.dbid

order by dsname, dbname

I hope with this information you can identify the replication flow and also type. Keep replicating this stuff too...

Thank you all..

Your Qs and Tips are highly appreciated and welcome...

~