| Author |
Message
|
| reldb |
 Posted: Fri Aug 24, 2007 3:57 pm Post subject: (0x3000000)Text = '[IBM][CLI Driver][DB2/HPUX] SQL0007N The |
 |
|
Apprentice
Joined: 06 Sep 2006
Posts: 34
|
Database server = DB2/HPUX 7.2.0
SQL authorization ID = MQSI
WMQI B.11.210.04.0.0 WebSphere MQ Integrator
WMQI B.11.210.07.0.0 WebSphere MQ Integrator Update (U495344) for HP-UX
WSMQWorkflow 3.4.0.0 WebSphere MQ Workflow for HP-UX
HP
Whenever i initiate a new transaction i get the below error in logs
WBGet_Trace.log
(0x3000000)Label = 'WFtoBeanGet.WFtoB.OrderTBRecord.Filter Wait'
(0x3000000)Text = 'The following error occurred during execution of an SQL statement'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2519
(0x1000000)Insert = (
SELECT COUNT(*) FROM ORDER_TB T WHERE ((((T.ORDER_ID)=(ABCHDH))AND((T.ORD_INIT)=(XYZ))) AND((T.REPLY_Q)=(ORD2TP)))AND(({fn LENGTH({fn RTRIM({fn CONVERT(T.WF_COR_ID, SQL_CHAR)})})})<(80))
(0x3000000)Text = 'Root SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '-1'
)
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 258
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Child SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2322
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '42601'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '-7'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '[IBM][CLI Driver][DB2/HPUX] SQL0007N The character "{" following ".REPLY_Q)=(?)))AND((" is not valid. SQLSTATE=42601'
)
I searched on this forum and google could not find anything.
Initially i thought it is permission issue, recreated the DB2 db with proper permission but still the same issue.
On another machine with DB2 DB as Database server = DB2/HPUX 7.2.3, same thing is working fine.
Do you guys knows why i am getting this error ? is this because of old version. but isnt these functional very basic function and should not matter to db2 patch.?
Has anyone of you come across any issue like this? |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sat Aug 25, 2007 9:35 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks like your beef is with the correlation id. I sure hope you are looking at a hex representation of the byte array.
Anything else will give you grief as the byte array is not a representation of a String...
In consequence I see no interest in the functions for length or rtrim.
Maybe convert as we are talking about a text representation of a byte array (48 chars from 0 to F). (or 51 in JMS = 48 prepended by "ID:")
Maybe a case conversion so that you always store as upper or lower case...
Review your design as it is essentially flawed by the assumption that the correlation id is a String.... 
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| reldb |
| Posted: Sat Aug 25, 2007 7:06 pm Post subject: |
|
|
Apprentice
Joined: 06 Sep 2006
Posts: 34
|
Thanks fjb_saper
The same code is working fine on another machine.
I read some where it is related to DB2 CLI, seems CLI should convert these functions (TRIM LENGHT ) to DB2 sql which is not happening.
I am not sure why it is not happening.
any idea? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Aug 25, 2007 7:53 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| reldb wrote: |
Thanks fjb_saper
The same code is working fine on another machine.
I read some where it is related to DB2 CLI, seems CLI should convert these functions (TRIM LENGHT ) to DB2 sql which is not happening.
I am not sure why it is not happening.
any idea? |
Because as I said your design is essentially flawed.
CorrelationId is a byte[] and not a String. However you are treating it as String hence the function calls. Remember that a String restricts the values admissible for each byte in the array. [00] is not a valid String value. It is however the filler for the correlation Id if the supplied value is less than 24 bytes.
Enjoy... 
Doesn't matter if the code works on other machines. It might just be happenstance related to the data.
This is a design issue. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun Aug 26, 2007 1:45 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
There's been a number of discussions in the forum on why treating either message id or correlation id as a string is a really bad idea.
The search button is your friend.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| reldb |
| Posted: Mon Aug 27, 2007 8:55 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2006
Posts: 34
|
i think issue is not related to correlation id
as logs says
The character "{" following ".REPLY_Q)=(?)))AND((" is not valid. SQLSTATE=42601
which is {fn LENGTH(
i think it is not able to understand this {fn LENGTH functions which is CLI function and it should be translated to DB2 sql. seems something wrong with CLI
Same thing same code is working on other machine. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 27, 2007 9:36 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| reldb wrote: |
i think issue is not related to correlation id
as logs says
The character "{" following ".REPLY_Q)=(?)))AND((" is not valid. SQLSTATE=42601
|
It's exactly releated. Correlid is NOT character and hence contains invalid characters like the message says.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Aug 27, 2007 10:11 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I don't think it is related.
I think reldb is trying to use DB2 SQL syntax on an ESQL Select statement, which will never work.
Unless reldb is actually using PASSTHRU and has neglected to tell us that.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| reldb |
| Posted: Thu Sep 06, 2007 11:39 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2006
Posts: 34
|
Thanks Everyone.
Issue is fixed now.
1) I had to install the missing db2 patch 5
2) db2updv7 was ran
Basically readme for patch 5 was followed
it worked fine after that.
There was no code change or etc |
|
| Back to top |
|
|
|
|
