ER queue question

Hi, we are using ER to replicate some tables from one to another database server. Version on both is 11.70.FC5. Now I see some replicates where the QUEUE SIZE does not change. My experience is that the QUEUE SIZE always tends to zero. cdr list repl | grep QUEUE QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 122494 QUEUE SIZE: 209272 QUEUE SIZE: 0 QUEUE SIZE: 34243 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 33321020 Hours later: cdr list repl | grep QUEUE QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 122494 QUEUE SIZE: 209272 QUEUE SIZE: 0 QUEUE SIZE: 47332 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 0 QUEUE SIZE: 33321020 There is one replicate with current activity on the corresponding tables where the queue size changes. On the other shown replicates there is no current table activity and in my opinion the queue size should be "0". All replicates are ACTIVE on both database servers. With OAT I can monitoring the following (hope a picture can be send and seen): In the header of the "Send Queue Statistics of hzv_tli (Active)" there is shown a "Send Queue (Data in Queue (bytes)" of 5,415. This value changes permanently (up and down). In the Target Nodes Table in line grp_dbinthzv (Target Node) in the column "Data in Queue (bytes)" we find 35,078,985. This value will never be less than about 35,000,000. Is there something going wrong? How can I clear those replicate queues. TIA Reinhard

Hi, sorry, the picture from OAT is not visible in the posts. Perhaps the output of onstat -g rqm may help: IBM Informix Dynamic Server Version 11.70.FC5 -- On-Line -- Up 160 days 06:40:12 -- 1392640 Kbytes CDR Reliable Queue Manager (RQM) Statistics: RQM Statistics for Queue (0x13d15b028) trg_send Transaction Spool Name: trg_send_stxn Insert Stamp: 4542401 Flags: SEND_Q, SPOOLED, PROGRESS_TABLE, NEED_ACK Txns in queue: 3 Log Events in queue: 0 Txns in memory: 3 Txns in spool only: 0 Txns spooled: 0 Unspooled bytes: 3249 Size of Data in queue: 3249 Bytes Real memory in use: 3249 Bytes Pending Txn Buffers: 0 Pending Txn Data: 0 Bytes Max Real memory data used: 16543776 (104857600) Bytes Max Real memory hdrs used 7939920 (2276352000) Bytes Total data queued: 6267081049 Bytes Total Txns queued: 4542401 Total Txns spooled: 149 Total Txns restored: 0 Total Txns recovered: 0 Spool Rows read: 0 Total Txns deleted: 4542398 Total Txns duplicated: 0 Total Txn Lookups: 13576147 Progress Table: Progress Table is Stable On-disk table name............: spttrg_send Flush interval (time).........: 30 Time of last flush............: 1357234083 Flush interval (serial number): 1000 Serial number of last flush...: 20932 Current serial number.........: 20934 Server Group Bytes Queued Acked Sent ------------------------------------------------------------------------ ------ 91 0x5a002f 0 90/2565/0x2ac7558/0x0 - 90/2565/0x2ac7558/0x0 91 0x5a0030 0 90/2507/0x175330/0x0 - 90/2507/0x175330/0x0 91 0x5a000e 0 90/2477/0x98a390/0x0 - 90/2477/0x98a390/0x0 91 0x5a0017 0 90/2434/0x45b7198/0x0 - 90/2434/0x45b7198/0x0 91 0x5a0016 0 90/2558/0x46e128/0x0 - 90/2558/0x46e128/0x0 91 0x5a0011 0 90/2565/0x294b110/0x0 - 90/2565/0x294b110/0x0 91 0x5a0012 0 90/2472/0x1b55474/0x0 - 90/2472/0x1b55474/0x0 91 0x5a0015 0 90/2472/0x1ce97b0/0x0 - 90/2472/0x1ce97b0/0x0 91 0x5a0002 0 90/2478/0xad480/0x0 - 90/2478/0xad480/0x0 91 0x5a0010 0 90/2572/0x20ca794/0x0 - 90/2572/0x20ca794/0x0 91 0x5a0008 122494 90/2565/0x2400680/0x0 - 90/2565/0x2400680/0x0 91 0x5a0009 781954 90/2565/0x25697a8/0x0 - 90/2565/0x25697a8/0x0 91 0x5a000b 628625 90/2565/0x2802700/0x0 - 90/2565/0x2802700/0x0 91 0x5a000a 209272 90/2565/0x2681100/0x0 - 90/2565/0x2681100/0x0 91 0x5a0003 0 90/2565/0x2850268/0x0 - 90/2565/0x2850268/0x0 91 0x5a0007 0 90/2565/0x234e698/0x0 - 90/2565/0x234e698/0x0 91 0x5a0006 0 90/2565/0x22bb2ec/0x0 - 90/2565/0x22bb2ec/0x0 91 0x5a0005 33321020 90/2565/0x222c5c4/0x0 - 90/2565/0x222c5c4/0x0 91 0x5a001c 0 90/1119/0x720f0/0x0 - No Content Yet 91 0x5a000c 0 90/2565/0x29cc43c/0x0 - 90/2565/0x29cc43c/0x0 91 0x5a001a 0 90/2565/0x295a170/0x0 - 90/2565/0x295a170/0x0 91 0x5a0014 0 90/2565/0x2a36248/0x0 - 90/2565/0x2a36248/0x0 91 0x5a000f 3057 90/2572/0x20df0ec/0x0 - 90/2572/0x20ec0ec/0x0 91 0x8018 0 90/678/0x3963e4/0x0 - No Content Yet First Txn (0x14021b028) Key: 90/2572/0x020e60ec/0x00000000 Txn Stamp: 4542399, Reference Count: 0. Txn Flags: Notify Txn Commit Time: (1357234101) 2013/01/03 18:28:21 Txn Size in Queue: 1083 First Buf's (0x13d0f6178) Queue Flags: Resident First Buf's Buffer Flags: TRG, Stream, More NeedAck: Waiting for Acks from <[0004]> No open handles on txn. Last Txn (0x13f7767d8) Key: 90/2572/0x020ec0ec/0x00000000 Txn Stamp: 4542401, Reference Count: 0. Txn Flags: Notify Txn Commit Time: (1357234102) 2013/01/03 18:28:22 Txn Size in Queue: 1083 First Buf's (0x13f776a80) Queue Flags: Resident First Buf's Buffer Flags: TRG, Stream, More NeedAck: Waiting for Acks from <[0004]> Traverse handle (0x13d738028) for thread CDRNsA91 at txn (0x13f7767d8) End_of_Q, Flags: None Traverse handle (0x13d738028) for thread CDRNsA91 at txn (0x13f7767d8) End_of_Q, Flags: None Traverse handle (0x13f5f2028) for thread CDRNrA91 at Head_of_Q, Flags: None Traverse handle (0x13e1ee028) for thread CDRGeval3 at Head_of_Q, Flags: None Traverse handle (0x14021b1f0) for thread CDRGeval2 at Head_of_Q, Flags: None Traverse handle (0x14021c298) for thread CDRGeval0 at Head_of_Q, Flags: None Traverse handle (0x13d0f61f0) for thread CDRGeval1 at Head_of_Q, Flags: None Traverse handle (0x13f776ca8) for thread CDRACK_1 at Head_of_Q, Flags: None Traverse handle (0x13f777d50) for thread CDRACK_0 at Head_of_Q, Flags: None CDR Reliable Queue Manager (RQM) Statistics: RQM Statistics for Queue (0x13cc09028) control_send Transaction Spool Name: control_send_stxn Insert Stamp: 2851858284585 Flags: CTRL_SEND_Q, STABLE, USERTXN, PROGRESS_TABLE, NEED_ACK Txns in queue: 0 Txns in memory: 0 Txns in spool only: 0 Txns spooled: 0 Unspooled bytes: 0 Size of Data in queue: 0 Bytes Real memory in use: 0 Bytes Pending Txn Buffers: 0 Pending Txn Data: 0 Bytes Max Real memory data used: 5697 (0) Bytes Max Real memory hdrs used 768 (0) Bytes Total data queued: 65161 Bytes Total Txns queued: 42 Total Txns spooled: 42 Total Txns restored: 0 Total Txns recovered: 0 Spool Rows read: 0 Total Txns deleted: 42 Total Txns duplicated: 0 Total Txn Lookups: 131 Progress Table: Progress Table is Stable On-disk table name............: sptcontrol_send Flush interval (time).........: 30 Time of last flush............: 1356080077 Flush interval (serial number): 1000 Serial number of last flush...: 19 Current serial number.........: 20 Server Group Bytes Queued Acked Sent ------------------------------------------------------------------------ ------ 91 0x5b0000 0 90/0/0x14d/0x0 - 90/0/0x14d/0x0 Traverse handle (0x13ea4d028) for thread CDRNsA91 at Head_of_Q, Flags: None Traverse handle (0x13f77b028) for thread CDRACK_0 at Head_of_Q, Flags: None Traverse handle (0x13f775028) for thread CDRACK_1 at Head_of_Q, Flags: None CDR Reliable Queue Manager (RQM) Statistics: RQM Statistics for Queue (0x13cbdf028) ack_send Insert Stamp: 66 Flags: ACKSEND_Q Txns in queue: 0 Txns in memory: 0 Txns in spool only: 0 Txns spooled: 0 Unspooled bytes: 0 Size of Data in queue: 0 Bytes Real memory in use: 0 Bytes Pending Txn Buffers: 0 Pending Txn Data: 0 Bytes Max Real memory data used: 36 (0) Bytes Max Real memory hdrs used 384 (0) Bytes Total data queued: 2304 Bytes Total Txns queued: 66 Total Txns spooled: 0 Total Txns restored: 0 Total Txns recovered: 0 Spool Rows read: 0 Total Txns deleted: 66 Total Txns duplicated: 0 Total Txn Lookups: 133 Progress Table: Not configured. Traverse handle (0x13d736028) for thread CDRNsA91 at Head_of_Q, Flags: None CDR Reliable Queue Manager (RQM) Statistics: RQM Statistics for Queue (0x13ea2f028) sync_send Insert Stamp: 0 Flags: SYNC_Q, NEED_ACK Txns in queue: 0 Txns in memory: 0 Txns in spool only: 0 Txns spooled: 0@@NL@

Solved: The queues of the affected replicates could be cleared by "cdr stop replicate replicatename" and " cdr start replicate replicatename" while there was no activity on the corresponding tables. THX. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Habichtsberg, Reinhard > Sent: Thursday, January 03, 2013 6:35 PM > To: ids@iiug.org > Subject: RE: ER queue question [29174] > > Hi, > > sorry, the picture from OAT is not visible in the posts. Perhaps the output of > onstat -g rqm may help:> > IBM Informix Dynamic Server Version 11.70.FC5 -- On-Line -- Up 160days > 06:40:12 -- 1392640 Kbytes > > CDR Reliable Queue Manager (RQM) Statistics: > > RQM Statistics for Queue (0x13d15b028) trg_send Transaction Spool Name: > trg_send_stxn Insert Stamp: 4542401 > Flags: SEND_Q, SPOOLED, PROGRESS_TABLE, NEED_ACK Txns in queue: 3 > Log Events in queue: 0 Txns in memory: 3 Txns in spool only: 0 Txns spooled: 0 > Unspooled bytes: 3249 Size of Data in queue: 3249 Bytes Real memory in use: > 3249 Bytes Pending Txn Buffers: 0 Pending Txn Data: 0 Bytes Max Real memory > data used: 16543776 (104857600) Bytes Max Real memory hdrs used 7939920 > (2276352000) Bytes Total data queued: 6267081049 Bytes Total Txns queued: > 4542401 Total Txns spooled: 149 Total Txns restored: 0 Total Txns recovered: 0 > Spool Rows read: 0 Total Txns deleted: 4542398 Total Txns duplicated: 0 Total > Txn Lookups: 13576147 > > Progress Table: > > Progress Table is Stable > > On-disk table name............: spttrg_send > > Flush interval (time).........: 30 > > Time of last flush............: 1357234083 > > Flush interval (serial number): 1000 > > Serial number of last flush...: 20932 > > Current serial number.........: 20934 > > Server Group Bytes Queued Acked Sent > ------------------------------------------------------------------------ > ------ > > 91 0x5a002f 0 90/2565/0x2ac7558/0x0 - > 90/2565/0x2ac7558/0x0 > > 91 0x5a0030 0 90/2507/0x175330/0x0 - > 90/2507/0x175330/0x0 > > 91 0x5a000e 0 90/2477/0x98a390/0x0 - > 90/2477/0x98a390/0x0 > > 91 0x5a0017 0 90/2434/0x45b7198/0x0 - > 90/2434/0x45b7198/0x0 > > 91 0x5a0016 0 90/2558/0x46e128/0x0 - > 90/2558/0x46e128/0x0 > > 91 0x5a0011 0 90/2565/0x294b110/0x0 - > 90/2565/0x294b110/0x0 > > 91 0x5a0012 0 90/2472/0x1b55474/0x0 - > 90/2472/0x1b55474/0x0 > > 91 0x5a0015 0 90/2472/0x1ce97b0/0x0 - > 90/2472/0x1ce97b0/0x0 > > 91 0x5a0002 0 90/2478/0xad480/0x0 - > 90/2478/0xad480/0x0 > > 91 0x5a0010 0 90/2572/0x20ca794/0x0 - > 90/2572/0x20ca794/0x0 > > 91 0x5a0008 122494 90/2565/0x2400680/0x0 - 90/2565/0x2400680/0x0 > > 91 0x5a0009 781954 90/2565/0x25697a8/0x0 - 90/2565/0x25697a8/0x0 > > 91 0x5a000b 628625 90/2565/0x2802700/0x0 - 90/2565/0x2802700/0x0 > > 91 0x5a000a 209272 90/2565/0x2681100/0x0 - 90/2565/0x2681100/0x0 > > 91 0x5a0003 0 90/2565/0x2850268/0x0 - > 90/2565/0x2850268/0x0 > > 91 0x5a0007 0 90/2565/0x234e698/0x0 - > 90/2565/0x234e698/0x0 > > 91 0x5a0006 0 90/2565/0x22bb2ec/0x0 - > 90/2565/0x22bb2ec/0x0 > > 91 0x5a0005 33321020 90/2565/0x222c5c4/0x0 - 90/2565/0x222c5c4/0x0 > > 91 0x5a001c 0 90/1119/0x720f0/0x0 - > No Content Yet > > 91 0x5a000c 0 90/2565/0x29cc43c/0x0 - > 90/2565/0x29cc43c/0x0 > > 91 0x5a001a 0 90/2565/0x295a170/0x0 - > 90/2565/0x295a170/0x0 > > 91 0x5a0014 0 90/2565/0x2a36248/0x0 - > 90/2565/0x2a36248/0x0 > > 91 0x5a000f 3057 90/2572/0x20df0ec/0x0 - 90/2572/0x20ec0ec/0x0 > > 91 0x8018 0 90/678/0x3963e4/0x0 - > No Content Yet > > First Txn (0x14021b028) Key: 90/2572/0x020e60ec/0x00000000 Txn Stamp: > 4542399, Reference Count: 0. > Txn Flags: Notify > Txn Commit Time: (1357234101) 2013/01/03 18:28:21 Txn Size in Queue: 1083 > First Buf's (0x13d0f6178) Queue Flags: Resident First Buf's Buffer Flags: TRG, > Stream, More > NeedAck: Waiting for Acks from <[0004]> No open handles on txn. > > Last Txn (0x13f7767d8) Key: 90/2572/0x020ec0ec/0x00000000 Txn Stamp: > 4542401, Reference Count: 0. > Txn Flags: Notify > Txn Commit Time: (1357234102) 2013/01/03 18:28:22 Txn Size in Queue: 1083 > First Buf's (0x13f776a80) Queue Flags: Resident First Buf's Buffer Flags: TRG, > Stream, More > NeedAck: Waiting for Acks from <[0004]> Traverse handle (0x13d738028) for > thread CDRNsA91 at txn (0x13f7767d8) End_of_Q, Flags: None > > Traverse handle (0x13d738028) for thread CDRNsA91 at txn (0x13f7767d8) > End_of_Q, Flags: None Traverse handle (0x13f5f2028) for thread CDRNrA91 at > Head_of_Q, Flags: > None > Traverse handle (0x13e1ee028) for thread CDRGeval3 at Head_of_Q, > Flags: None > Traverse handle (0x14021b1f0) for thread CDRGeval2 at Head_of_Q, > Flags: None > Traverse handle (0x14021c298) for thread CDRGeval0 at Head_of_Q, > Flags: None > Traverse handle (0x13d0f61f0) for thread CDRGeval1 at Head_of_Q, > Flags: None > Traverse handle (0x13f776ca8) for thread CDRACK_1 at Head_of_Q, Flags: > None > Traverse handle (0x13f777d50) for thread CDRACK_0 at Head_of_Q, Flags: > None > > CDR Reliable Queue Manager (RQM) Statistics: > > RQM Statistics for Queue (0x13cc09028) control_send Transaction Spool Name: > control_send_stxn Insert Stamp: 2851858284585 > Flags: CTRL_SEND_Q, STABLE, USERTXN, PROGRESS_TABLE, NEED_ACK > Txns in queue: 0 Txns in memory: 0 Txns in spool only: 0 Txns spooled: 0 > Unspooled bytes: 0 Size of Data in queue: 0 Bytes Real memory in use: 0 Bytes > Pending Txn Buffers: 0 Pending Txn Data: 0 Bytes Max Real memory data used: > 5697 (0) Bytes Max Real memory hdrs used 768 (0) Bytes Total data queued: > 65161 Bytes Total Txns queued: 42 Total Txns spooled: 42 Total Txns restored: 0 > Total Txns recovered: 0 Spool Rows read: 0 Total Txns deleted: 42 Total Txns > duplicated: 0 Total Txn Lookups: 131 > > Progress Table: > > Progress Table is Stable > > On-disk table name............: sptcontrol_send > > Flush interval (time).........: 30 > > Time of last flush............: 1356080077 > > Flush interval (serial number): 1000 > > Serial number of last flush...: 19 > > Current serial number.........: 20 > > Server Group Bytes Queued Acked Sent > ------------------------------------------------------------------------ > ------ > > 91 0x5b0000 0 90/0/0x14d/0x0 - > 90/0/0x14d/0x0 > > Traverse handle (0x13ea4d028) for thread CDRNsA91 at Head_of_Q, Flags: > None > Traverse handle (0x13f77b028) for thread CDRACK_0 at Head_of_Q, Flags: > None > Traverse handle (0x13f775028) for thread CDRACK_1 at Head_of_Q, Flags: > None > > CDR Reliable Queue Manager (RQM) Statistics: > > RQM Statistics for Queue (0x13cbdf028) ack_send Insert Stamp: 66 > Flags: ACKSEND_Q > Txns in queue: 0 > Txns in memory: 0 > Txns in spool only: 0 > Txns spooled: 0 > Unspooled bytes: 0 > Size of Data in queue: 0 Bytes > Real memory in use: 0 Bytes > Pending