Example 1: Outbound Queue after create subscription

Interpret an outboud queue containing a create subscription transaction.

The create subscription command at the replicate Replication Server starts the materialization process by selecting from the primary database:

create subscription emp_queue_sub
for emp_queue
 with replicate at NYDS.nydb

Queue Dump

Dump the entire queue:

sysadmin dump_queue, 103, 0,-1,1,-2

Output:

I. 2006/07/05 08:20:20. QUEUE DUMP FOR 103:0
I. 2006/07/05 08:20:20. BLOCK BEGIN
q_number=103 q_type=0 blk=0:1 cnt=1
I. 2006/07/05 08:20:20. Begin Transaction
Origin User= Tran Name=
I. 2006/07/05 08:20:20. ENTRY ver=1100 len=188 orig=102
lorig=0 oqid=00000000000016c7000004ff0004000004ff0000
000097f50087a0720000000000000000 lqid=0:1:0 st=21
tr='00000000000016c70000' NYDSnydb comlen=69
activate subscription 0x0100006580000065 0
I. 2006/07/05 08:20:20. END QUEUE DUMP FOR 103:0

Explanation

BLOCK BEGIN

The beginning of a 16K block.
q_number=103

The queue ID.
q_type=0

The queue type (0 = outbound, 1 = inbound).
blk=0:1

Block identifier (segment 0: block 1).
cnt=1

The number of entries (commands) in this block.
Begin Transaction Origin User= Tran Name=

Beginning of a transaction with originating user and the transaction name. Tran Name is empty when the original transaction at the primary database was explicitly started with a begin transaction without supplying a transaction name.
ENTRY

The actual message.
len=188 orig=102

The message length and the originating site ID.
oqid=00000000000016c7000004ff0004000004ff0000000097f50087a072 0000000000000000

The origin queue ID: the queue ID assigned to the command string at the place the command originated from. This ID is used for duplicate detection.
Lqid=0:1:0

Local queue ID: Commands logged by an SQM are assigned a local queue ID. This queue ID is sent to the next queue as the sender queue ID and is used by the destination to acknowledge the command. Lqid consists of segment number, block number and row number. Row numbers start at 0.
St=21

Status and command type. The field is a bitmask which has status and command type set. This bit’s settings are 0x01 and 0x20, a commit and materialization command.

0x01 = status of a commit

0x02 = status of a rollback

0x04 = status of a begin

0x08 = status of a orphan statement

0x10 = status of a materialization statement

0x20 = status of a materialization statement

0x40 = status of a tran going to primary

0x80 = ignore duplicates for DDL commands

0x0100 = status of a materialization command

0x0200 = dump/load message

0x0400 = routing message

0x0800 = flag for DDL generation

0x1000 = DDL generation at DSI flag

0x2000 = special SQM message

0x4000 = SQT mini-abort

0x8000 = message has been zapped
tr= '00000000000016c70000' NYDSnydb comlen=69 activate subscription 0x0100006580000065 0

Transaction ID and command, comlen indicates the length of the command.