Tips & tricks for installing and running ICS products

IBM Connections Communities Replay events DB2 queries

Tom Bosmans  23 January 2018 17:33:25
Working on a recent problem where events are not processed, I was looking at the wsadmin commands to provide information.
The Jython code supplied , for instance for CommunitiesQEventService.viewQueuedEventsByRemoteAppDefId("Blog", None, 100) is pretty useless in situations where you have 100's of 1000's of events in the queue.  The Jython code in the wiki is also plain wrong (but that's a differentl story)

https://www.ibm.com/support/knowledgecenter/en/SSYGQH_6.0.0/admin/admin/r_admin_communities_admin_props.html#r_admin_communities_admin_props__CommunitiesQEventService

So I turned to the DB2 database, to examine the LC_EVENT_REPLAY table.  Unfortunately, the interesting detailed infomration is stored as an XML in a field (CLOB) called EVENT.
It took me quite a bit of time to figure out how to get the information out of that field in an SQL Query.

In fact, the most puzzling fact, was the notation needed for the XML root element and the node elements.  They all need to use the namespace.  Using a wildcard for the namespace , is sufficient in this case.
So this query would give you some detailed information about events in the replay table :

SELECT C1.MANAGEDAPPDEFID,C1.EVENTTYPE,X.*
FROM (SELECT * FROM SNCOMM.LC_EVENT_REPLAY FETCH FIRST 10 ROWS ONLY) as C1,
XMLTABLE( '$tev/*:entry'
passing XMLCAST(XMLPARSE(DOCUMENT C1.EVENT) AS XML) as "tev"
COLUMNS
    "title"          VARCHAR(512) PATH '*:title/text()',
        "author"        VARCHAR(128) PATH '*:author/*:email/text()',
        "communityid"        VARCHAR(128) PATH '*:container/@id',
        "community"        VARCHAR(128) PATH '*:container/@name'
    ) AS X
ORDER BY X."communityid";

Of course, you can show any information from the EVENT XML file you like, but using this query as a start, would help you immensely :-) .