The number of messages in Export queue are huge and not going down though backlog is 0 (4302047)

SharePlex is replicating to one or more target. The replication to one or more of these is working fine but the number of messages in Export queue are huge and keep growing though backlog may be a non-zero number but comes down to 0 when there is no activity on the source. This signifies that the Export queue is able to send the messages to at least some of the targets and thereby it does make the backlog to 0 from time to time whenever there are no more messages to be sent to those good targets. 

Note:  Though the illustration of the problem and its resolution is fairly straightforward, please feel free to email/call Support if you want to be walked thru the process.

The most common reason for seeing this error is, one or more of the target is decommissioned or is non-functional for some reasons. In our example, there are two targets involved and one of them is decommissioned even though Export process on source is still referencing the defunct target while also referencing the good target. The qstatus shows that the # of messages in Export queue keep growing and can reach an astronomical figure over a period of time:

Name:  source_server_name (Export queue)

    Number of messages:  704196929 (Age     19541 min; Size     414551 mb)

    Backlog (messages):          0 (Age         0 min)

The output of “show” issued from source sp_ctrl lists Export process to be running for both the targets even though the target named splex-target is defunct and is not being replicated to:

sp_ctrl (source_server_name:2100)> show

Process    Source                               Target                 State                   PID

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

Capture    o.motion                                                    Running                1377

Read       o.motion                                                    Running               12448

Export     source_server_name                             splex-target           Running               17843

Export     source_server_name                             target_server_name             Running               17842

To resolve the problem, you will need to remove the extra Export process pointing to the target named splex-target by running deluser command in qview utility after shutting down SharePlex on source.

1.      Shutdown SharePlex

2.      While in /proddir/bin directory (where proddir denotes SharePlex’s Product directory) invoke qview and initialize the qview with qsetup:

./qview –i

qview> qsetup

3.      Then issue list to see the details of the Export process involved and the IP addresses associated with those processes to be able to decide which ones of the two are pointing to defunct target:

qview> list

The following queues exist:

  o.motion+C

        WRITER  +PA+o.motion+sp_ocap+o.motion

        READER  +PR+o.motion+sp_ordr+o.motion

  source_server_name+X

        WRITER  +PR+o.motion+sp_ordr+o.motion

        READER  +PX+source_server_name+sp_xport+nnnn7a301e         (xx.xxx.48.30)

        READER  +PX+source_server_name+sp_xport+nnnn7a143e         (xx.xxx.20.62)

4.      In our case the target with IP address xx.xxx.48.30 is not participating in replication anymore. So we will remove that process with deluser command of qview. The syntax is “deluser <export queue name> <export process name>” where there is a space between all the three parameters of the command:

qview> deluser source_server_name+X +PX+source_server_name+sp_xport+nnnn7a301e

5.      Issue another “list” command within qview to verify that the unwanted Export process is gone:

qview> list

The following queues exist:

  o.motion+C

        WRITER  +PA+o.motion+sp_ocap+o.motion

        READER  +PR+o.motion+sp_ordr+o.motion

  source_server_name+X

        WRITER  +PR+o.motion+sp_ordr+o.motion

        READER  +PX+source_server_name+sp_xport+nnnn7a143e           (xx.xxx.20.62)

6.      Then exit qview:

qview>exit

7.      Restart SharePlex.

You should now see the # of messages reduced considerably and they would go down to 0 from time to time as will the backlog. This indicates that the Export is running normally.

The above problem will never arise if we use separate entries for both targets in the config file when the same table replicates to multiple targets. The best practice for creating config file entry is:

owner.source_table     owner.target_table      server1@o.SID1

owner.source_table     owner.target_table      server2@o.SID1

The following way of combined routing to achieve the same is prone to error (when one of the target is decommissioned) even though it saves some resources because only one Export queue is used:

owner.source_table     owner.target_table      server1@o.SID1 + server2@o.SID1