Not able to write Non-XML message arriving at MQ Queue into database

Question

I'm using following ESQL code to put the non-xml message (arriving at input queue) into an audit database (Oracle 10g) as BLOB Object. The code is working when I'm using it in IBM WebSphere Message Broker 6.0. But recently I migrated to IBM WebSphere Message Broker 7.0 and now the non-xml message goes to Backout queue instead of being audited into database. The ESQL code for the subflow (that writes the non xml messages into db) I'm using is:

CREATE COMPUTE MODULE "WriteNonXMLToAudit_1_0_WriteNonXMLToAudit"
CREATE FUNCTION main() RETURNS BOOLEAN BEGIN
    SET OutputRoot = InputRoot;

    DECLARE ver CHAR;
    DECLARE codeName CHAR;

    SET ver = '1_0_2';
    SET codeName = 'WriteNonXMLToAudit.' || 'Version:' || ver;

    DECLARE aonMSG REFERENCE To "OutputRoot"."BLOB";
    DECLARE appError REFERENCE To InputExceptionList;

    DECLARE msid INTEGER;
    DECLARE messageType CHAR;

    DECLARE sourceApplication CHAR;

    DECLARE mqHdrBlob BLOB;
    DECLARE mqHdrChar CHAR;

    DECLARE msgBlob BLOB;
    DECLARE msgChar CHAR;
    DECLARE msgUUID CHAR;

    SET OutputRoot.MQMD.Format = 'MQSTR   ';
    SET OutputRoot.MQMD.CodedCharSetId = 819;
    SET OutputRoot.MQMD.Encoding = 273;
    SET OutputRoot."MQRFH2" = NULL;

    MOVE aonMSG TO "OutputRoot"."BLOB";

    SET msgChar = aonMSG;

    SET messageType = 'NON_STANDARD_MESSAGE';
    SET msid = 9998;

    SET msgUUID = UUIDASCHAR;
    SET sourceApplication = RTRIM(OutputRoot.MQMD.PutApplName);

    SET msgChar = CAST(OutputRoot."BLOB"."BLOB" As CHAR CCSID 819 ENCODING   OutputRoot.MQMD.Encoding );

    Insert Into Database.Message_Audit (UUID, MSID, Message_Type, Source_Application, Message_Body)
    Values ( SUBSTRING(msgUUID FROM 1 FOR 35),
    msid,
    SUBSTRING(messageType FROM 1 FOR 29),
    SUBSTRING(sourceApplication FROM (LENGTH(sourceApplication) - 9) FOR 10),
    msgChar);

    IF SQLCODE <> 0 THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;

END;

END MODULE;

The Main flow is as follows:

CREATE COMPUTE MODULE "ACIF_AUDIT_1_5_Insert exception into msg"
CREATE FUNCTION main() RETURNS BOOLEAN BEGIN
SET OutputRoot = InputRoot;


CREATE FIRSTCHILD OF OutputRoot.XML.MSG.APP_DATA Name 'APP_ERROR';
CREATE LASTCHILD OF OutputRoot.XML.MSG.APP_DATA.APP_ERROR Name 'ERROR_SOURCE' VALUE 

'ACIFBROKER';
CREATE LASTCHILD OF OutputRoot.XML.MSG.APP_DATA.APP_ERROR Name 'ERROR_OPERATION' VALUE 'ACIF_AUDIT_Exception';
CREATE LASTCHILD OF OutputRoot.XML.MSG.APP_DATA.APP_ERROR Name 'ERROR_CODE' VALUE '';
CREATE LASTCHILD OF OutputRoot.XML.MSG.APP_DATA.APP_ERROR Name 'ERROR_STRING';
Set OutputRoot.XML.MSG.APP_DATA.APP_ERROR.ERROR_STRING = InputExceptionList;
CREATE LASTCHILD OF OutputRoot.XML.MSG.APP_DATA.APP_ERROR Name 'ERROR_DETAIL' VALUE 'Exception List';

SET OutputRoot."MQRFH2" = NULL;
SET OutputRoot.MQMD.CodedCharSetId = MQCCSI_INHERIT;  /*819*/
SET OutputRoot.MQMD.Encoding = MQENC_NATIVE;          /*273*/
Set OutputRoot.MQMD.Format = 'MQSTR';


RETURN true;
END;

END MODULE;
CREATE COMPUTE MODULE "ACIF_AUDIT_1_5_Insert audit record"
CREATE FUNCTION main() RETURNS BOOLEAN BEGIN
SET OutputRoot = InputRoot;


Declare refAONDataRoot      REFERENCE To InputBody.MSG;
Declare refSOAPDataRoot     REFERENCE TO InputBody."SOAP-ENV:Envelope";
Declare MSGChar         CHAR;
Declare MSGBlob         BLOB;
DECLARE intSQLCode      INTEGER;
DECLARE chrSQLErrorText     CHAR;

/* Added by Shailen **/
DECLARE uuid        CHAR;
DECLARE msid        INT;
DECLARE mType       CHAR;
DECLARE srcApp      CHAR;
Declare msgRef      REFERENCE TO InputBody;

If FIELDNAME(refAONDataRoot.AONHEADER) = 'AONHEADER' Then

    Set MSGBlob = BITSTREAM(refAONDataRoot);
    Set MSGChar  = CAST(MSGBlob As CHAR CCSID InputRoot.MQMD.CodedCharSetId);
    Set msid = refAONDataRoot.AONHEADER.MSID;

    IF  refAONDataRoot.APP_DATA.APP_HEADER.UUID IS NOT NULL THEN
        SET uuid = refAONDataRoot.APP_DATA.APP_HEADER.UUID;
    ELSE
        /* Let check for MESSAGE_ID - BRIDGE Messages */
        IF  FIELDNAME(refAONDataRoot.APP_DATA.AON_APPLICATION_MESSAGE.HEADER.MESSAGE_ID ) IS NOT NULL THEN
            SET uuid = refAONDataRoot.APP_DATA.AON_APPLICATION_MESSAGE.HEADER.MESSAGE_ID;
            SET msid = 9999;
        END IF;
    END IF;

IF  FIELDNAME(refAONDataRoot.APP_DATA.APP_HEADER.MESSAGE_TYPE) IS NOT NULL THEN
    SET mType = refAONDataRoot.APP_DATA.APP_HEADER.MESSAGE_TYPE;
ELSE
    /* Let check another location - historical message type location */
    IF  

FIELDNAME(refAONDataRoot.APP_DATA.AON_APPLICATION_MESSAGE.HEADER.MESSAGE_TYPE ) IS NOT NULL THEN
            SET mType = refAONDataRoot.APP_DATA.AON_APPLICATION_MESSAGE.HEADER.MESSAGE_TYPE;
        END IF;
    END IF;

    Insert Into Database.MESSAGE_AUDIT (UUID,MSID,MESSAGE_TYPE,SOURCE_APPLICATION,MQ_HEADER,MESSAGE_BODY) 
    Values (uuid,msid,mType,refAONDataRoot.AONHEADER.APPLICATION_NAME,'',MSGChar);

/* 08-11-2004:The following assumes that the flow will throw an error on an database error.
 * As part of this change the flow is being changed to do this, otherwise the message is just thrown away.

RETURN FALSE;   
 */
ElseIf FIELDNAME(refSOAPDataRoot."SOAP-ENV:Header") = 'SOAP-ENV:Header' Then

    Set MSGBlob = BITSTREAM(refSOAPDataRoot);
    Set MSGChar  = CAST(MSGBlob As CHAR CCSID InputRoot.MQMD.CodedCharSetId);

    Insert Into Database.MQUSER.MESSAGE_AUDIT (UUID,MSID,MESSAGE_TYPE,SOURCE_APPLICATION,MQ_HEADER,MESSAGE_BODY) 
    Values (refSOAPDataRoot."SOAP-ENV:Header".MessageHeader.uuid,'',refSOAPDataRoot."SOAP-ENV:Header".MessageHeader.messageType,'','',MSGChar);
    /* 08-11-2004:The following assumes that the flow will throw an error on an database error.
 * As part of this change the flow is being changed to do this, otherwise the message is just thrown away.
RETURN FALSE;
 */ 
ElseIf FIELDNAME(InputBody.AON_APPLICATION_MESSAGE) IS NOT NULL Then
    /* This is to audit the message from AME to bridge.
     * The MESSAGE ID is effectively the UUID for this message.
     */
    MOVE msgRef TO InputBody.AON_APPLICATION_MESSAGE;
    Set MSGBlob = BITSTREAM(msgRef);
    Set MSGChar = CAST(MSGBlob As CHAR CCSID InputRoot.MQMD.CodedCharSetId);

    Set msid = '9999';
    Set mType = 'Unset';
    Set srcApp = 'Unset';

    IF FIELDNAME(msgRef.HEADER.MESSAGE_ID) IS NOT NULL THEN
        SET uuid = msgRef.HEADER.MESSAGE_ID;
    END IF;

    IF FIELDNAME(msgRef.HEADER.MSID) IS NOT NULL THEN
        SET msid = CAST(msgRef.HEADER.MSID AS INT);
    END IF;

    IF FIELDNAME(msgRef.HEADER.MESSAGE_TYPE) IS NOT NULL THEN
        SET mType = msgRef.HEADER.MESSAGE_TYPE;
    END IF;

    IF FIELDNAME(msgRef.HEADER.SENDER) IS NOT NULL THEN
        SET srcApp = msgRef.HEADER.SENDER;
    END IF;

    Insert Into Database.MESSAGE_AUDIT (UUID,MSID,MESSAGE_TYPE,SOURCE_APPLICATION,MQ_HEADER,MESSAGE_BODY) 
    Values (uuid,msid,mType,srcApp,'',MSGChar);

    /* 08-11-2004:The following assumes that the flow will throw an error on an database error.
     * As part of this change the flow is being changed to do this, otherwise the message is just thrown away.
    RETURN FALSE;   
     */ 
ElseIf FIELDNAME(InputBody.msg.header.application_name) IS NOT NULL Then
    /* Then assume this is an ATLAS message.
     * The MESSAGE ID is effectively the UUID for this message.
     */
    MOVE msgRef TO InputBody.msg;
    Set MSGBlob = BITSTREAM(msgRef);
    Set MSGChar = CAST(MSGBlob As CHAR CCSID InputRoot.MQMD.CodedCharSetId);

    Set srcApp = msgRef.header.application_name;

    IF FIELDNAME(msgRef.header.msid) IS NOT NULL Then
        Set msid = msgRef.header.msid;
    ELSE
        Set msid = '9997';
    END IF;

    Set mType = 'Unset:ATLAS_MESSAGE';
    Set uuid = UUIDASCHAR;

    Insert Into Database.MESSAGE_AUDIT (UUID,MSID,MESSAGE_TYPE,SOURCE_APPLICATION,MQ_HEADER,MESSAGE_BODY) 
    Values (uuid,msid,mType,srcApp,'',MSGChar);

    /* 08-11-2004:The following assumes that the flow will throw an error on an database error.
     * As part of this change the flow is being changed to do this, otherwise the message is just thrown away.
    RETURN FALSE;   
     */     
Else
    /* We have an XML message, but we do not know what it is. Lets treat is a a NONXML Message.
     */
    RETURN TRUE;
End If;

/* 
 * Return FALSE - this means that everything was okay in this case. We have wired up the TRUE terminal to handle the case when we have 
 * an XML message, but one that is not recognised. NB Non XML messages will cause an error because the MQINPUT node is expecting an XML 
 * message.
 */
RETURN FALSE;

END;

END MODULE;

Broker logs:

Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2232E: Error detected whilst handling a previous error in node 'ACIF_AUDIT_1_5.ACIF_ERROR_01'. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp: 893: ImbDataFlowNode::logExceptionList: ComIbmMQOutputNode: ACIF_AUDIT_1_5#FCMComposite_1_2
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2230E: Error detected whilst processing a message in node 'ACIF_AUDIT_1_5.Insert audit record'. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbComputeNode.cpp: 489: ImbComputeNode::evaluate: ComIbmComputeNode: ACIF_AUDIT_1_5#FCMComposite_1_4
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2488E:  (.ACIF_AUDIT_1_5_Insert audit record.main, 16.1) Error detected whilst executing the SQL statement 'DECLARE refAONDataRoot REFERENCE TO InputBody.MSG;'. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp: 643: SqlStatementGroup::execute: ComIbmComputeNode: ACIF_AUDIT_1_5#FCMComposite_1_4
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2498E: (.ACIF_AUDIT_1_5_Insert audit record.main, 16.39) : An error occurred when navigating to path element '2' of the field reference at the given location. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlFieldRef.cpp: 1916: SqlFieldReference::navigateAbsoluteToFirst: ComIbmComputeNode: ACIF_AUDIT_1_5#FCMComposite_1_4
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP5009E: XML Parsing Errors have occurred. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/MTI/MTIforBroker/GenXmlParser2/XmlImbParser.cpp: 720: XmlImbParser::parseRightSibling: : 
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP5004E: An XML parsing error 'Invalid character (Unicode: 0x2) ' occurred on line 1 column 441 when parsing element 'app_data'.  Internal error codes are '196' and ''. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/MTI/MTIforBroker/GenXmlParser2/XmlBrokerAsgardParser.cpp: 730: XmlBrokerAsgardParser::error: : 
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2628E: Exception condition detected on input node 'ACIF_AUDIT_1_5.ACIF_AUDIT_01'. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbCommonInputNode.cpp: 1912: ImbCommonInputNode::run: ComIbmMQInputNode: ACIF_AUDIT_1_5#FCMComposite_1_5
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP2230E: Error detected whilst processing a message in node 'ACIF_AUDIT_1_5.ACIF_ERROR_01'. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/DataFlowEngine/ImbMqOutputNode.cpp: 864: ImbMqOutputNode::evaluate: ComIbmMQOutputNode: ACIF_AUDIT_1_5#FCMComposite_1_2
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP5010E: XML Writing Errors have occurred. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/MTI/MTIforBroker/GenXmlParser2/XmlImbParser.cpp: 443: XmlImbParser::refreshBitStreamFromElementsInner: ComIbmMQInputNode: ACIF_AUDIT_1_5#FCMComposite_1_5
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15201]: (UKS06.ACIF_001)[35]BIP5005E: There are 2 top level elements 'msg,MSG' in the document. : UKS06.7aaf0524-3801-0000-0080-9e3a13c0b2a4: /build/S700_P/src/MTI/MTIforBroker/GenXmlParser2/XmlImbParser.cpp: 954: XmlImbParser::checkForBodyElement: : 
Mar 25 14:50:31 ukvmgstg008 WebSphere Broker v7005[15195]: (UKS06.CONCERT_001)[7]BIP2648E: Message backed out to a queue; node 'ACIF_AUDIT_1_5.ACIF_AUDIT_01'. : UKS06.d0420624-3801-0000-0080-83fda0200698: /build/S700_P/src/DataFlowEngine/ImbMqInputNode.cpp: 2122: ImbCommonInputNode::eligibleForBackout: ComIbmMQInputNode: ACIF_AUDIT_1_5#FCMComposite_1_5

Answer 1

You need to get hold of the exception list and find out what the root cause of the failure is. If you don't have Catch of Fail terminals the error messages will be in the syslog / event viewer.

If you have error handling however you need to ensure that you process the exception list in a suitable way to report the root cause of the exception.

If you are really stuck you could try taking a user trace and following through execution of the affected compute node to try and identify the point of failure.

If the issue is a db or db driver problem then the BIP message that gets thrown should have an SQLState and native error code which you can reference in the db vendors documentation.

Answer 2

From your log, we see: BIP5005E: There are 2 top level elements 'msg,MSG' in the document.

A valid XML message can only have one top level element. I'm assuming one is a typo. Correct and try again.

The log provided shows no issues with your ESQL, only XML parsing. This may prevent the rest of your flow from running, or running as expected. A user trace would be more valuable than your broker log.