| Author |
Message
|
| yaakovd |
 Posted: Mon Mar 03, 2003 12:57 pm Post subject: More about MQSI code transfer from 2.0.2 to 2.1. |
 |
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
I upgrade MQSI from 2.0.2 SP1 to 2.1 SP4.
The following
| Code: |
EXISTS( SELECT * FROM Database.ORDER_TB AS T WHERE
T.ORDER_ID = "Root"."XML"."WfMessage"."ActivityImplInvoke"."ProgramInputData"."FlowController"."orderID"
AND T.ORD_INIT = "Root"."XML"."WfMessage"."ActivityImplInvoke"."ProgramInputData"."FlowController"."orderInitiator"
AND T.REPLY_Q = "Root"."XML"."WfMessage"."ActivityImplInvoke"."ProgramInputData"."FlowController"."replyQueue"
AND LENGTH(RTRIM(CAST (T.WF_COR_ID AS CHARACTER))) <80) |
from old version is not working in MQSI 2.1.
The problem in "AND LENGTH(RTRIM(CAST (T.WF_COR_ID AS CHARACTER))) <80".
It working when I remove it. In debug I see SQL exception - incorrect SQL statement. All field in DB are characters.
What's wrong here?
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
 |
| yaakovd |
| Posted: Tue Mar 04, 2003 12:11 pm Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
The problem that MQSI translate the functions in Filter node as following:
LENGTH(RTRIM(CAST (T.WF_COR_ID AS CHARACTER)))
to
(fn LENGTH{( fn RTRIM ...
and DB is not love '{' caracter.
Itemporary change this to something else, but what is a complete solution here?
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 04, 2003 12:22 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| yaakovd |
| Posted: Tue Mar 04, 2003 12:26 pm Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Thanks, but it is little "hard" operation.
I understand that PASSTHROUGH is generated at runtime not like regular SQL statement.
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Mar 13, 2003 8:44 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Yaakov,
1. Could we see the table definition (DDL) for ORDER_TB ?
2. What database are you using ?
3. What domain are you processing this message in ?
_________________
Regards, Ian |
|
| Back to top |
|
|
| yaakovd |
| Posted: Wed Mar 19, 2003 10:53 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Ian,
1. All fields in ORDER_TB are of type CHAR
2. DB2 7.2 on NT
3. I am using XML domain in output message, but the problem in SQL statement and not in output message. I just try SELECT * or COUNT.
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Mar 26, 2003 5:58 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Yaakov,
Thought this looked similar to something else I was working on and so investigated a little further.
My environment is : Websphere MQ Integrator v2.1 CSD04 on Windows 2000 and DB2v7.1 GA
In my small test this worked successfully and the expected result was returned.
On the face of it I can't see why yours is not working.
The way WMQI works is that the ESQL is evaluated and decisions are made as to whether
something will be resolved at the Broker or the Database. In this case we can see from
the actual SQL statement that is being passed from the Broker to the Database (by looking
at the WMQI User trace) that the Broker has decided that the LENGTH, RTRIM and CAST AS CHAR
are to be performed by the Database as the syntax {fn ... is the ODBC escape sequence
used to pass scalar functions (like LENGTH) to a Database.
My WMQI ESQL :
| Code: |
EXISTS
(
SELECT * FROM Database.MYTABLE AS T
WHERE T.MYCOL2 = Root.XML.Msg.Key
AND LENGTH(RTRIM(CAST(T.MYCOL1 AS CHARACTER))) < 10
)
|
My WMQI User trace :
| Code: |
2003-03-26 10:59:55.263999 2644 UserTrace BIP2537I: Node 'DatabaseSELECTS.Filter1': Executing statement 'RETURN DATABASE(SELECT COUNT(*) FROM MYTABLE T WHERE ((T.MYCOL2)=(?))AND(({fn LENGTH({fn RTRIM({fn CONVERT(T.MYCOL1, SQL_CHAR)})})})<(10)), Root.XML.Msg.Key);' at (1, 1).
2003-03-26 10:59:55.263999 2644 UserTrace BIP2538I: Node 'DatabaseSELECTS.Filter1': Evaluating expression 'DATABASE(SELECT COUNT(*) FROM MYTABLE T WHERE ((T.MYCOL2)=(?))AND(({fn LENGTH({fn RTRIM({fn CONVERT(T.MYCOL1, SQL_CHAR)})})})<(10)), Root.XML.Msg.Key)' at (1, 1).
2003-03-26 10:59:55.263999 2644 UserTrace BIP2538I: Node 'DatabaseSELECTS.Filter1': Evaluating expression 'Root.XML.Msg.Key' at (4, 18).
2003-03-26 10:59:55.273998 2644 UserTrace BIP2544I: Node 'DatabaseSELECTS.Filter1': Executing database SQL statement 'SELECT COUNT(*) FROM MYTABLE T WHERE ((T.MYCOL2)=(?))AND(({fn LENGTH({fn RTRIM({fn CONVERT(T.MYCOL1, SQL_CHAR)})})})<(10))' derived from (1, 1); expressions 'Root.XML.Msg.Key'; resulting parameter values ''1''.
2003-03-26 10:59:55.284000 2644 UserTrace BIP4004I: Message propagated to 'true' terminal of filter node 'DatabaseSELECTS.Filter1'.
|
I would be interested to see your full WMQI User trace of this.
_________________
Regards, Ian |
|
| Back to top |
|
|
| yaakovd |
| Posted: Wed Mar 26, 2003 6:55 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Ian,
It is very strange - same code and same configuration...
This is my trace. I will appretiate if you can find the problem.
| Code: |
2003-03-03 19:32:44.895999 1988 UserTrace BIP2632I: Message received and propagated to 'out' terminal of MQ input node 'WFtoBeanGet.WF2BEAN_GET'.
2003-03-03 19:32:44.925998 1988 UserTrace BIP6060I: Parser type 'Properties' created on behalf of node 'WFtoBeanGet.WF2BEAN_GET' to handle portion of incoming message of length 0 bytes beginning at offset '0'.
2003-03-03 19:32:44.925998 1988 UserTrace BIP6061I: Parser type 'MQMD' created on behalf of node 'WFtoBeanGet.WF2BEAN_GET' to handle portion of incoming message of length '364' bytes beginning at offset '0'. Parser type selected based on value 'MQHMD' from previous parser.
2003-03-03 19:32:44.925998 1988 UserTrace BIP6061I: Parser type 'XML' created on behalf of node 'WFtoBeanGet.WF2BEAN_GET' to handle portion of incoming message of length '1998' bytes beginning at offset '364'. Parser type selected based on value 'XML' from previous parser.
2003-03-03 19:32:44.945999 1988 UserTrace BIP2537I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate': Executing statement 'RETURN Root.XML.WfMessage.TerminateProgram IS NULL;' at (1, 1).
2003-03-03 19:32:44.945999 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate': Evaluating expression 'Root.XML.WfMessage.TerminateProgram IS NULL' at (1, 45).
2003-03-03 19:32:44.945999 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate': Evaluating expression 'Root.XML.WfMessage.TerminateProgram' at (1, 1).
2003-03-03 19:32:44.945999 1988 UserTrace BIP2543E: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate': (1, 26) : Failed to navigate to path element because it does not exist.
2003-03-03 19:32:44.945999 1988 UserTrace BIP2540I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate': Finished evaluating expression 'Root.XML.WfMessage.TerminateProgram IS NULL' at (1, 45). The result was 'TRUE'.
2003-03-03 19:32:44.945999 1988 UserTrace BIP4004I: Message propagated to 'true' terminal of filter node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Terminate'.
2003-03-03 19:32:44.945999 1988 UserTrace BIP2537I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Executing statement 'RETURN DATABASE(SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(?))AND((T.ORD_INIT)=(?)))AND((T.REPLY_Q)=(?)))AND(({fn LENGTH(T.WF_COR_ID)})<(80)), Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderID, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderInitiator, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.replyQueue);' at (1, 1).
2003-03-03 19:32:44.945999 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Evaluating expression 'DATABASE(SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(?))AND((T.ORD_INIT)=(?)))AND((T.REPLY_Q)=(?)))AND(({fn LENGTH(T.WF_COR_ID)})<(80)), Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderID, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderInitiator, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.replyQueue)' at (1, 1).
2003-03-03 19:32:44.945999 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Evaluating expression 'Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderID' at (2, 17).
2003-03-03 19:32:44.956001 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Evaluating expression 'Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderInitiator' at (3, 27).
2003-03-03 19:32:44.956001 1988 UserTrace BIP2538I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Evaluating expression 'Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.replyQueue' at (4, 27).
2003-03-03 19:32:44.956001 1988 UserTrace BIP2544I: Node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait': Executing database SQL statement 'SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(?))AND((T.ORD_INIT)=(?)))AND((T.REPLY_Q)=(?)))AND(({fn LENGTH(T.WF_COR_ID)})<(80))' derived from (1, 1); expressions 'Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderID, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.orderInitiator, Root.XML.WfMessage.ActivityImplInvoke.ProgramInputData.FlowController.replyQueue'; resulting parameter values ''DM14O1770003', 'BSO', 'ORD2VAD''.
2003-03-03 19:32:44.965999 1988 UserTrace BIP2231E: Error detected whilst processing a message 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait'.
The message broker detected an error whilst processing a message in node 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2003-03-03 19:32:44.965999 1988 RecoverableException BIP2519E: (1, 1) : Error executing SQL statement 'SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(?))AND((T.ORD_INIT)=(?)))AND((T.REPLY_Q)=(?)))AND(({fn LENGTH(T.WF_COR_ID)})<(80))' against datasource 'VAD_DB' with parameters ''DM14O1770003', 'BSO', 'ORD2VAD', '.
The following error occurred during execution of a database SQL statement against datasource 'VAD_DB'. The SQL statement was 'SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(?))AND((T.ORD_INIT)=(?)))AND((T.REPLY_Q)=(?)))AND(({fn LENGTH(T.WF_COR_ID)})<(80))'. The parameters passed were ''DM14O1770003', 'BSO', 'ORD2VAD', '.
2003-03-03 19:32:44.965999 1988 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.
2003-03-03 19:32:44.965999 1988 DatabaseException BIP2322E: Database error: SQL State '42601'; Native Error Code '-7'; Error Text '[IBM][CLI Driver][DB2/NT] SQL0007N The character "{" following ".REPLY_Q)=(?)))AND((" is not valid. SQLSTATE=42601'.
The error has the following diagnostic information: SQL State '42601' SQL Native Error Code '-7' SQL Error Text '[IBM][CLI Driver][DB2/NT] SQL0007N The character "{" following ".REPLY_Q)=(?)))AND((" is not valid. SQLSTATE='.
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.
2003-03-03 19:32:44.965999 1988 UserTrace BIP2638I: The MQ output node 'WFtoBeanGet.FAILURE_WBGET' attempted to write a message to the specified queue 'FAILURE_WBGET' connected to queue manager |
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Mar 26, 2003 8:53 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Yaakov,
DB2v7.1 FP3=DB2v7.2
Do you have any further fix packs applied on DB2v7.2 ?
_________________
Regards, Ian |
|
| Back to top |
|
|
| yaakovd |
| Posted: Wed Mar 26, 2003 9:24 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Thanks, Ian.
I have DB2v7.1 FP3=DB2v7.2.
And the same code worked on MQSI 2.0.2 (NT and AIX) !!!
We working on creation AIX environment and I will test it again.
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Mar 27, 2003 3:52 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Yaakov,
Looked through your WMQI user trace and the error message "SQL0007N The character "{" following ... is not valid."
We can see from my WMQI user trace that the ODBC escape sequence for LENGTH {fn LENGTH(...)} is recognised and converted correctly before reaching the database.
If the ODBC escape sequence for whatever scalar function {fn ... was not recognised then I would have
expected to see an error something like : [IBM][CLI Driver] CLI0118E Invalid SQL syntax. SQLSTATE=37000
Since this is not the case I am inclined to think the error may be that the ODBC escape sequence has not been converted before reaching the database and to that extent I was wondering if you have changed one of the "CLI/ODBC Settings" ?
WMQI uses DB2 driver installed as part of the DB2 installation "IBM DB2 ODBC DRIVER".
You also only require the default settings.
To check this you could try created a new ODBC datasource and test your messageflow with this :
"Control Panel" \ "ODBC Data Source Administrator" \ "System DSN" \ "Add"
create a new datasource (and modify your WMQI Filter node accordingly)
select the "IBM DB2 ODBC DRIVER" and do not change any of the default attributes
_________________
Regards, Ian |
|
| Back to top |
|
|
| yaakovd |
| Posted: Wed Apr 02, 2003 10:41 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
The problem is not appears on AIX (DB2 7.1 and MQSI 2.1) - looks like local problem on NT server
1. It is not MQSI bug
2. Possible problem in DB2 7.2 SP3 on NT
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Apr 02, 2003 11:45 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
This might help.
| Quote: |
Note: At DB2 V7.1 Fix Pack 3 or DB2 V7.2 (GA), there is a known problem with ESQL syntax generating ODBC escape sequences. This is resolved in Fix Pack 4.
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
|
|
