The SQLs I am using to detect failed messages are:
or, if want to see more:select * from PSAPMSGPUBCON where STATUSSTRING='ERROR'
select * from PSAPMSGPUBHDR HDR, PSAPMSGPUBCON CON where HDR.IBTRANSACTIONID=CON.IBPUBTRANSACTID and CON.STATUSSTRING not in ('DONE', 'CNCLD')
The STATUSSTRING can be replaced by PUBSTATUS, with its Status Codes:
0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled
I believe it is not hard at all to jump from PUB messages to SUB messages. :)