| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Generate XML Schema (Message Model) from an Oracle Table |
« View previous topic :: View next topic » |
| Author |
Message
|
| kimbert |
 Posted: Thu Sep 06, 2012 5:35 am Post subject: |
 |
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Not sure how to troubleshoot that in respect to MB |
Take a user trace. You will need to execute three command-line tools in the following order:
1. mqsichangetrace to switch on user trace
...now invoke your message flow
2. mqsireadlog to read the xml-formatted trace log
3. mqsiformatlog to convert it to human-readable text
You may find it useful to use the -r parameter when you switch on user trace, to avoid getting trace from previous flow invocations. |
|
| Back to top |
|
 |
| neWMBGuy |
| Posted: Thu Sep 06, 2012 6:42 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
ran the user TRACE and pretty much got all event viewer errors in 1 file.
A Question how can i resolve the values being passed to Procedure inside the DB Node see below my code... Environment variables are showing up on File trace perfectly so i know im not sending NULL values.
2012-09-06 10:03:34.447906 9496 RecoverableException BIP2488E: ('.GeneralLedger_XML_RequestProcessor_DatabaseInsertRecords.Main', '33.3') Error detected whilst executing the SQL statement ''INSERT_HEADER_RECORD(Environment.Variables.sentCode, Environment.Variables.groupIdOf, CURRENT_TIMESTAMP, Environment.Variables.fileCount, Environment.Variables.groupIdOf, '1250', Environment.Variables.groupIdOf, Environment.Variables.sessionID, 'TGTB', 'TGTSYS', Environment.Variables.totalDollarValue, 0.00, 0.00, CURRENT_DATE, CURRENT_DATE, 'NA', Environment.Variables.transCount, 0, 'N', 'N', 'N', 'N', 'N', 'N', 0, 0, 0, 'NA');''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
DatabaseException BIP2322E: Database error: SQL State ''23000''; Native Error Code '1400'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1''.
The error has the following diagnostic information: SQL State ''23000'' SQL Native Error Code '1400' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1''
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.
Error BIP2628E: Exception condition detected on input node 'GeneralLedger_XML_RequestProcessor.File Input'.
The input node 'GeneralLedger_XML_RequestProcessor.File Input' 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.
| Code: |
CREATE DATABASE MODULE GeneralLedger_XML_RequestProcessor_DatabaseInsertRecords
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyEntireMessage();
SET Environment.Variables.sentCode = Root.XMLNSC.ns:GeneralLedger01.Message.sending_System_Code;
SET Environment.Variables.groupIdOf = Root.XMLNSC.ns:GeneralLedger01.Message.Message_ID;
SET Environment.Variables.fileCount = CAST(Root.XMLNSC.ns:GeneralLedger01.Message.file_count AS INTEGER);
SET Environment.Variables.sessionID = Root.XMLNSC.ns:GeneralLedger01.Message.SessionID;
SET Environment.Variables.transCount = CAST(Root.XMLNSC.ns:GeneralLedger01.Message.transaction_count AS INTEGER);
DECLARE SIZE INTEGER;
DECLARE I INTEGER 1;
SET SIZE = CARDINALITY(Root.XMLNSC.ns:GeneralLedger01.JournalEntries.JournalEntry[]);
SET Environment.Variables.totalDollarValue = 0.0;
WHILE I <= SIZE DO
SET Environment.Variables.totalDollarValue = Environment.Variables.totalDollarValue + (CAST(Root.XMLNSC.ns:GeneralLedger01.JournalEntries.JournalEntry[I].CreditAmount AS DECIMAL));
SET I = I + 1;
END WHILE;
CALL INSERT_HEADER_RECORD(Environment.Variables.sentCode,Environment.Variables.groupIdOf,CURRENT_TIMESTAMP,Environment.Variables.fileCount,Environment.Variables.groupIdOf,'1250', Environment.Variables.groupIdOf,Environment.Variables.sessionID,'TGTB','TGTSYS',Environment.Variables.totalDollarValue,0.00,0.00,CURRENT_DATE,CURRENT_DATE,'NA',Environment.Variables.transCount,0,'N','N','N','N','N','N',0,0,0,'NA');
RETURN TRUE;
END;
CREATE PROCEDURE INSERT_HEADER_RECORD(
IN SYSTEM_CODE CHAR,
IN GROUP_ID_OF CHAR,
IN TRANSMIT_DATE TIMESTAMP,
IN FILE_COUNT INTEGER,
IN FILE_PATH_NAME CHAR,
IN HOME_BRANCH CHAR,
IN MSG_ID CHAR,
IN SESSION_ID CHAR,
IN TARGET_BRANCH CHAR,
IN TARGET_SYSTEM CHAR,
IN TOTALDOLLARAMOUNT DECIMAL,
IN TOTAL_CR DECIMAL,
IN TOTAL_DR DECIMAL,
IN CREATION_DT DATE,
IN LAST_UPDATE_DATE DATE,
IN ERRORMSG CHAR,
IN TRANSACTION_COUNT INTEGER,
IN GROUP_ID_OUT INTEGER,
IN MATCH_FLAG CHAR,
IN PRE_PROCESS_FLAG CHAR,
IN TOTALS_FLAG CHAR,
IN FLAG1 CHAR,
IN FLAG2 CHAR,
IN FLAG3 CHAR,
IN NBR1 INTEGER,
IN NBR2 INTEGER,
IN NBR3 INTEGER,
IN HEADER_TEXT CHAR
)
LANGUAGE DATABASE
EXTERNAL NAME "Schema.PackageName.SPName";
END MODULE; |
| kimbert wrote: |
| Quote: |
| Not sure how to troubleshoot that in respect to MB |
Take a user trace. You will need to execute three command-line tools in the following order:
1. mqsichangetrace to switch on user trace
...now invoke your message flow
2. mqsireadlog to read the xml-formatted trace log
3. mqsiformatlog to convert it to human-readable text
You may find it useful to use the -r parameter when you switch on user trace, to avoid getting trace from previous flow invocations. |
|
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Sep 06, 2012 6:54 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Look further up the user trace - you should be able to see every field reference being evaluated. You may need to make it a debug-level trace to see that.
Next time you post any trace/console output, please use the [c o d e] button to make it appear in a monospace font. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Sep 06, 2012 7:16 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
nevermind.
| neWMBGuy wrote: |
2. ( MB8BROKER.default ) Database error: SQL State ''23000''; Native Error Code '1400'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1''.
The error has the following diagnostic information: SQL State ''23000'' SQL Native Error Code '1400' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1'' |
"cannot insert NULL into" :
1. Likely the columns are set to (defined by the DDL) as "NOT NULL" (can you check that?)... try to insert something which is not null (e.g. the empty string).
2. It could just be that you are not handing (or not properly handing) over these fields & values. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Thu Sep 06, 2012 7:36 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
From Debug Trace... this shows all values are being resolved.....
although im not sure why there are Question marks in the beginning and why the KEYWORD TIMESTAMP & DATE is also being passed....
2012-09-06 10:58:31.658775 9496 UserTrace BIP2544I: Node 'GeneralLedger_XML_RequestProcessor.DatabaseInsertRecords': Executing database SQL statement ''{ CALL SYSADM.HDS_ETGEL_MBINSERT_PKG.INSERTET_PS_ZY_OF_HDR_TBL ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }'' derived from ('.GeneralLedger_XML_RequestProcessor_DatabaseInsertRecords.INSERT_HEADER_RECORD', '1.2'); expressions '''elecdev', '0000662287_20120226_elecdev_GL_0002', TIMESTAMP '2012-09-06 10:58:31.353775', 54, '0000662287_20120226_elecdev_GL_0002', '1250', '0000662287_20120226_elecdev_GL_0002', '1001662287', 'TGTB', 'TGTSYS', 23452.40, 0.00, 0.00, DATE '2012-09-06', DATE '2012-09-06', 'NA', 7, 0, 'N', 'N', 'N', 'N', 'N', 'N', 0, 0, 0, 'NA'''; resulting parameter values '''elecdev', '0000662287_20120226_elecdev_GL_0002', TIMESTAMP '2012-09-06 10:58:31.353775', 54, '0000662287_20120226_elecdev_GL_0002', '1250', '0000662287_20120226_elecdev_GL_0002', '1001662287', 'TGTB', 'TGTSYS', 23452.40, 0.00, 0.00, DATE '2012-09-06', DATE '2012-09-06', 'NA', 7, 0, 'N', 'N', 'N', 'N', 'N', 'N', 0, 0, 0, 'NA'''. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Sep 08, 2012 7:44 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| neWMBGuy wrote: |
From Debug Trace... this shows all values are being resolved.....
although im not sure why there are Question marks in the beginning and why the KEYWORD TIMESTAMP & DATE is also being passed.... |
The question marks represent placeholders for the parameter values that follow. The usertrace displays the parameter values as ESQL datatypes.
| neWMBGuy wrote: |
| Code: |
| ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 |
|
It looks like the error is occurring inside the stored procedure itself, around line 42. The message flow seems to passing in the values correctly, (including SYSTEM_CODE), but the stored procedure has decided instead to insert a NULL value into the table at this point. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Sun Sep 09, 2012 6:31 pm Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
the problem was with PROPOGATE in the previous Compute node. it was sending wrong message TREE to DB node ...
MB 101 lessons... Thanks to everybody.. this is a great group of eager Helpers.. |
|
| Back to top |
|
|
|
|
|
|
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
|
|
|
|
