| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| logging errors into a database - urgent please |
« View previous topic :: View next topic » |
| Author |
Message
|
| ydsk |
 Posted: Wed Jul 19, 2006 1:12 am Post subject: logging errors into a database - urgent please |
 |
|
Chevalier
Joined: 23 May 2005
Posts: 410
|
I connected the Catch terminal of MQInput node to a database node and there I am trying to log 5 fields into a database table ( MsgId, Timestamp, Err_Number, Err_Text, Original InputMsg as BLOB).
But I haven't been successful so far.
The flow takes an MRM(cobol) msg and converts it into soap and calls a webservice with HTTPRequestnode and after getting a response back, it sends it to a queue on the broker itself for now.
I am trying to simulate an exception condition with an incorrect cobol message( msg length reduced/increased) and it is throwing an exception as expected. But the database node isn't able to insert the 5 fields into a databse field no matter what i do.
Here is a mqsitrace ( the tail portion that has the error messages):
-------------------------------------------------------------------------
2006-07-19 02:32:57.143821 2120 UserTrace BIP2537I: Node 'ContractReqMsgFlow.Database': Executing statement ''INSERT INTO Database.{Source1}.{Schema1}.{Table1} ( MQMSGID, BKR_TS, ERR_CODE, ERR_DESC, INPUTMSG ) VALUES (MSGID, CURRENT_TIMESTAMP, CAST(ERR_NUM AS INTEGER), ERR_TEXT, INPUTBLOB ) '' at ('.ContractReqMsgFlow_Database.Main', '32.3').
2006-07-19 02:32:57.143859 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''Schema1'' at ('.ContractReqMsgFlow_Database.Main', '32.35'). This resolved to ''Schema1''. The result was '''NULLID'''.
2006-07-19 02:32:57.143898 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''Table1'' at ('.ContractReqMsgFlow_Database.Main', '32.45'). This resolved to ''Table1''. The result was '''BKRERROR4'''.
2006-07-19 02:32:57.143932 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''Source1'' at ('.ContractReqMsgFlow_Database.Main', '32.25'). This resolved to ''Source1''. The result was '''USERDB'''.
2006-07-19 02:32:57.427318 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''MSGID'' at ('.ContractReqMsgFlow_Database.Main', '32.109'). This resolved to ''MSGID''. The result was '''X'414d5120424b52514d312020202020207580bd4420003414''''.
2006-07-19 02:32:57.427398 2120 UserTrace BIP2540I: Node 'ContractReqMsgFlow.Database': Finished evaluating expression ''CURRENT_TIMESTAMP'' at ('.ContractReqMsgFlow_Database.Main', '32.116'). The result was ''TIMESTAMP '2006-07-19 02:32:57.140038'''.
2006-07-19 02:32:57.427448 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''ERR_NUM'' at ('.ContractReqMsgFlow_Database.Main', '32.140'). This resolved to ''ERR_NUM''. The result was ''5288''.
2006-07-19 02:32:57.427661 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''CAST(ERR_NUM AS INTEGER)'' at ('.ContractReqMsgFlow_Database.Main', '32.135'). This resolved to ''CAST(5288 AS INTEGER)''. The result was ''5288''.
2006-07-19 02:32:57.427707 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''ERR_TEXT'' at ('.ContractReqMsgFlow_Database.Main', '32.157'). This resolved to ''ERR_TEXT''. The result was '''MTI. Not all the buffer was used when reading message'''.
2006-07-19 02:32:57.428283 2120 UserTrace BIP2539I: Node 'ContractReqMsgFlow.Database': Evaluating expression ''INPUTBLOB'' at ('.ContractReqMsgFlow_Database.Main', '32.167'). This resolved to ''INPUTBLOB''. The result was ''X'4d442020020000000000000008000000ffffffff0000000022020000b501000020202020202020200000000000000000414d5120424b52514d312020202020207580bd442000341400000000000000000000000000000000000000000000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020424b52514d31202020202020202020202020202020202020202020202020202020202020202020202020202020202020646977616b61722e79616d6d16010515000000b1dc87a2c1b98bdf62f7a80def03000000000000000000000b20202020202020202020202020202020202020202020202020202020202020200b000000735c49483033206578747261637465645c7266687574696c2e6578653230303630373139303733323537323320202020000000000000000000000000000000000000000000000000010000000000000000000000ffffffff4d5347494431494e535431434f4e545243543152454c3031303631393230303630373130323030364d65737361676562726f6b6572436f6e73756c74616e74466f724578656c6f6e4d53504175746f6d6174696f6e465858585858585858585858585858585858584c53544154555331324a4f42434f4445314c4f434154494f4e46585858585858584c3132333435363738393132333132505243434d4e4d315450314249524d494e4748414d4249524d494e4748414d414c33353231363036343630484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b415253484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b415253484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b41525350323030362d30372d31302d48482d4d4d2d53532d4d4d4d4d4d4d2e2e2e4d4f52454c454e475448'''.
2006-07-19 02:32:57.428539 2120 UserTrace BIP2544I: Node 'ContractReqMsgFlow.Database': Executing database SQL statement ''INSERT INTO NULLID.BKRERROR4(MQMSGID, BKR_TS, ERR_CODE, ERR_DESC, INPUTMSG) VALUES (?, ?, ?, ?, ?)'' derived from ('.ContractReqMsgFlow_Database.Main', '32.3'); expressions ''MSGID, CURRENT_TIMESTAMP, CAST(ERR_NUM AS INTEGER), ERR_TEXT, INPUTBLOB''; resulting parameter values '''X'414d5120424b52514d312020202020207580bd4420003414'', TIMESTAMP '2006-07-19 02:32:57.140038', 5288, 'MTI. Not all the buffer was used when reading message', X'4d442020020000000000000008000000ffffffff0000000022020000b501000020202020202020200000000000000000414d5120424b52514d312020202020207580bd442000341400000000000000000000000000000000000000000000000000000000202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020424b52514d31202020202020202020202020202020202020202020202020202020202020202020202020202020202020646977616b61722e79616d6d16010515000000b1dc87a2c1b98bdf62f7a80def03000000000000000000000b20202020202020202020202020202020202020202020202020202020202020200b000000735c49483033206578747261637465645c7266687574696c2e6578653230303630373139303733323537323320202020000000000000000000000000000000000000000000000000010000000000000000000000ffffffff4d5347494431494e535431434f4e545243543152454c3031303631393230303630373130323030364d65737361676562726f6b6572436f6e73756c74616e74466f724578656c6f6e4d53504175746f6d6174696f6e465858585858585858585858585858585858584c53544154555331324a4f42434f4445314c4f434154494f4e46585858585858584c3132333435363738393132333132505243434d4e4d315450314249524d494e4748414d4249524d494e4748414d414c33353231363036343630484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b415253484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b415253484952454d475231484952454d4e4752454d503159414d4d414e5552555359414d4d414e55525553444957414b415253444957414b41525350323030362d30372d31302d48482d4d4d2d53532d4d4d4d4d4d4d2e2e2e4d4f52454c454e475448'''.
2006-07-19 02:32:57.431728 2120 Error BIP2232E: Error detected whilst handling a previous error in node 'ContractReqMsgFlow.Database'.
The message broker has detected an error in node 'ContractReqMsgFlow.Database' whilst handling a previous error.
See the following messages for details of the exception list associated with the original error. Thereafter messages will be associated with the new error.
2006-07-19 02:32:57.431755 2120 RecoverableException BIP2230E: Error detected whilst processing a message in node 'ContractReqMsgFlow.Set Destination'.
The message broker detected an error whilst processing a message in node 'ContractReqMsgFlow.Set Destination'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2006-07-19 02:32:57.435066 2120 RecoverableException BIP2488E: ('.ContractReqMsgFlow_URL.Main', '21.3') Error detected whilst executing the SQL statement ''SET OutputRoot.XMLNS.soap:Envelope.soap:Body.MessageId = InputRoot.MRM.MESSAGEID;''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2006-07-19 02:32:57.435100 2120 RecoverableException BIP2498E: ('.ContractReqMsgFlow_URL.Main', '21.60') : An error occurred whilst navigating to the ''3''th path element of the field reference at the given location.
See further messages for details of the error.
Correct the logic of the program redeploy the message flow.
2006-07-19 02:32:57.435123 2120 ParserException BIP5285E: Message Translation Interface Parsing Errors have occurred:
Message Set Name : ''ContractReq''
Message Set Level : ''1''
Message Format : ''CWF1''
Message Type Path : ''msg_EQCVMSVM001''
Review further error messages for an indication to the cause of the errors.
2006-07-19 02:32:57.435142 2120 ParserException BIP5171E: Custom Wire Format parsing error
An error occurred while parsing a Custom Wire Format message.
Current message : ''msg_EQCVMSVM001''
Current element : ''msg_EQCVMSVM001''
Path from message root : ''''
Offset from start of message : '390'
See following errors for more details.
2006-07-19 02:32:57.435161 2120 ParserException BIP5288E: MRM parsing error. Message bit stream larger than expected.
The bit stream of the message being parsed by the MRM parser is larger than expected for a message of this message type.
The message bit stream has been incorrectly constructed, or the logical model is incorrect.
Ensure that the message is correctly packaged.
Ensure that the message properties are correct.
Ensure that the logical model for this message type is complete (the message set and message type should be quoted in previous messages).
2006-07-19 02:32:57.436588 2120 Error BIP2628E: Exception condition detected on input node 'ContractReqMsgFlow.MQInput'.
The input node 'ContractReqMsgFlow.MQInput' detected an error whilst processing a message. The message flow has been rolled-back and, if the message was being processed in a unit of work, it will remain on the input queue to be processed again. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2006-07-19 02:32:57.436614 2120 RecoverableException BIP2230E: Error detected whilst processing a message in node 'ContractReqMsgFlow.Database'.
The message broker detected an error whilst processing a message in node 'ContractReqMsgFlow.Database'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2006-07-19 02:32:57.436645 2120 RecoverableException BIP2488E: ('.ContractReqMsgFlow_Database.Main', '32.3') Error detected whilst executing the SQL statement ''INSERT INTO Database.{Source1}.{Schema1}.{Table1} ( MQMSGID, BKR_TS, ERR_CODE, ERR_DESC, INPUTMSG ) VALUES (MSGID, CURRENT_TIMESTAMP, CAST(ERR_NUM AS INTEGER), ERR_TEXT, INPUTBLOB ) ''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2006-07-19 02:32:57.436664 2120 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2006-07-19 02:32:57.436683 2120 DatabaseException BIP2322E: Database error: SQL State ''22001''; Native Error Code '-99999'; Error Text ''[IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001''.
The error has the following diagnostic information: SQL State ''22001'' SQL Native Error Code '-99999' SQL Error Text ''[IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
2006-07-19 02:32:58.437255 2120 Error BIP2648E: Message backed out to a queue; node 'ContractReqMsgFlow.MQInput'.
Node 'ContractReqMsgFlow.MQInput' has received a message which has previously been backed out one or more times because of a processing error in the main path of the message flow. The failure terminal is not attached, so the message broker is putting the message directly to the requeue or dead letter backout queue associated with this node. The MQMD 'backoutCount' of the message now equals the 'backoutThreshold' defined for the WebSphere MQ input queue.
Examine previous messages and the message flow to determine why the message is being backed out. Correct this situation if possible. Perform any local error recovery processing required.
2006-07-19 02:32:58.437797 2120 UserTrace BIP2638I: The MQ output node 'ContractReqMsgFlow.MQInput' attempted to write a message to queue ''SYSTEM.DEAD.LETTER.QUEUE'' connected to queue manager ''BKRQM1''. The MQCC was '0' and the MQRC was '0'.
2006-07-19 02:32:58.437828 2120 UserTrace BIP2615I: The WebSphere MQ input node 'ContractReqMsgFlow.MQInput' has backed out the message to the backout requeue or the dead letter queue.
Message backout processing has been invoked, and the message has either been backed out by being written to the backout requeue or dead letter queue, as determined by the WebSphere MQ queue manager and queue configuration.
No user action required.
2006-07-19 02:33:13.617368 4780 UserTrace BIP2632I: Message received and propagated to 'out' terminal of MQ input node 'ConfigurationMessageFlow.InputNode'.
2006-07-19 02:33:13.617429 4780 UserTrace BIP6060I: Parser type ''Properties'' created on behalf of node 'ConfigurationMessageFlow.InputNode' to handle portion of incoming message of length 0 bytes beginning at offset '0'.
2006-07-19 02:33:13.617459 4780 UserTrace BIP6061I: Parser type ''MQMD'' created on behalf of node 'ConfigurationMessageFlow.InputNode' to handle portion of incoming message of length '364' bytes beginning at offset '0'. Parser type selected based on value ''MQHMD'' from previous parser.
2006-07-19 02:33:13.617524 4780 UserTrace BIP6061I: Parser type ''XMLS'' created on behalf of node 'ConfigurationMessageFlow.InputNode' to handle portion of incoming message of length '223' bytes beginning at offset '364'. Parser type selected based on value ''XMLS'' from previous parser.
Threads encountered in this trace:
2120 4780
---------------------------------------------------------------
Somebody pls help. |
|
| Back to top |
|
 |
| ydsk |
| Posted: Wed Jul 19, 2006 1:17 am Post subject: |
|
|
Chevalier
Joined: 23 May 2005
Posts: 410
|
And here is the ESQL from the database node that's connected to the CATCH terminal of the MQInput node:
--------------------------------------------------
CREATE DATABASE MODULE ContractReqMsgFlow_Database
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- Declare variables to hold the data source, schema, and table names -- and set their default values
DECLARE Source1 CHARACTER 'USERDB';
DECLARE Schema1 CHARACTER 'NULLID';
DECLARE Table1 CHARACTER 'BKRERROR3';
DECLARE MSGID CHARACTER Environment.Data.MsgId;
DECLARE ERR_NUM integer 0;
DECLARE ERR_TEXT CHARACTER '';
DECLARE INPUTBLOB BLOB Environment.Data.INPUTBLOB;
-- Code which calculates their actual values comes here
-- Insert the data into the table
-- MQMSGID, BKR_TS, ERR_CODE, ERR_DESC, INPUTMSG
-- Create a reference to the first child of the exception list
DECLARE ptrException reference to ExceptionList.*[1];
-- keep looping while the moves to the child of exception list work
WHILE lastmove(ptrException) DO
-- store the current values for the error number and text
IF ptrException.Number is not null THEN
SET ERR_NUM = ptrException.Number;
SET ERR_TEXT = ptrException.Text;
END IF;
-- now move to the last child which should be the next exceptionlist
MOVE ptrException lastchild;
END WHILE;
INSERT INTO Database.{Source1}.{Schema1}.{Table1} (MQMSGID, BKR_TS, ERR_CODE, ERR_DESC, INPUTMSG) values (MSGID, CURRENT_TIMESTAMP, cast(ERR_NUM as int), ERR_TEXT, INPUTBLOB);
RETURN TRUE; |
|
| Back to top |
|
|
| ydsk |
| Posted: Wed Jul 19, 2006 1:21 am Post subject: |
|
|
Chevalier
Joined: 23 May 2005
Posts: 410
|
Can someone pls tell me how I can log the 5 fields into a database table when an exception occurs in the msgflow ?
Thanks in advance. |
|
| Back to top |
|
|
| fschofer |
| Posted: Wed Jul 19, 2006 2:27 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hi,
| Quote: |
| CLI0109E String data right truncation |
seems to me one of the data fields you try to insert is longer then the actual table column.
Greetings
Frank |
|
| Back to top |
|
|
| shrek |
| Posted: Thu Jul 20, 2006 4:02 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
| You might also want to capture the ExceptionList and EnvironmentVariables along with InputMsg i.e create an alert message with these three in a compute node. ExceptionList is very helpful in locating the Exception in code. Thanks. |
|
| Back to top |
|
|
| ydsk |
| Posted: Thu Jul 20, 2006 12:17 pm Post subject: |
|
|
Chevalier
Joined: 23 May 2005
Posts: 410
|
thanks Frank.
the problem was with the MQMSGID. I defined the table column as 24 chars but MsdId is 24 bytes BLOB, and that was the whole problem.
I redefined the table column as 24 bytes BLOB and it resolved the issue.
Thnx.
ydsk. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
