| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| 2.1 -> 6 Migration: ESQL db select issue? |
« View previous topic :: View next topic » |
| Author |
Message
|
| catwood2 |
 Posted: Fri Feb 23, 2007 7:21 am Post subject: 2.1 -> 6 Migration: ESQL db select issue? |
 |
|
Centurion
Joined: 17 May 2002
Posts: 108
|
TIA for any pointers.
We are experiencing an issue when testing this code after migration:
P_Convert_to_MRM_Convert_DEV_MRM.main', '1259.1'
| Code: |
SET CAT = UPPER(OutputRoot.MRM.Category);
SET SENDER_SYSTEM = OutputRoot.MRM.Sender;
SET TARGET_SYSTEM = OutputRoot.MRM.Target;
SET ACCT = OutputRoot.MRM.Account;
SET LOCATION = OutputRoot.MRM.Site;
SET SUBCAT = NEWFIELD;
SET SUPPRESS_CHECK = THE (SELECT ITEM T.target
from Database.g2mq_filter as T
WHERE
(T.sender = SENDER_SYSTEM or T.sender ='ALL')
AND (T.target = TARGET_SYSTEM or T.target ='ALL')
AND (T.account = ACCT or T.account ='ALL')
AND (T.site = LOCATION or T.site = 'ALL')
AND (T.category = CAT or T.category = 'ALL')
AND (T.subcategory = SUBCAT or T.subcategory = 'ALL')); |
The trace output is showing this error:
| Quote: |
2007-02-23 15:04:46.316932 5143 UserTrace BIP2544I: Node 'VMSEvent_G2.P_
Convert_to_MRM.Convert_DEV_MRM': Executing database SQL statement ''SELECT T.target FROM g2mq_filter T WHERE (((((((T.sender)=(?))OR((T.sender)=('ALL')))AND(((T
.target)=(?))OR((T.target)=('ALL'))))AND(((T.account)=(?))OR((T.account)=('ALL')
)))AND(((T.site)=(?))OR((T.site)=('ALL'))))AND(((T.category)=(?))OR((T.category)
=('ALL'))))AND(((T.subcategory)=(?))OR((T.subcategory)=('ALL')))'' derived from
('', '1.1'); expressions ''SENDER_SYSTEM, TARGET_SYSTEM, ACCT, LOCATION, CAT
, SUBCAT''; resulting parameter values '''mcnxd001', 'IEMC.MQSI.VMSEVENT', 'E
CS', 'NDC', 'TESTING', 'LOS-FLOW-COMPLETED'''.
2007-02-23 15:04:46.364921 5143 UserTrace BIP2231E: Error detected whils
t processing a message in node 'VMSEvent_G2.P_Convert_to_MRM.Convert_DEV_MRM'.
The message broker detected an error whil
st processing a message in node 'VMSEvent_G2.P_Convert_to_MRM.Convert_DEV_MRM'.
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.
2007-02-23 15:04:46.364967 5143 RecoverableException BIP2488E: ('.P_Conv
ert_to_MRM_Convert_DEV_MRM.main', '1259.1') Error detected whilst executing the
SQL statement ''SET SUPPRESS_CHECK = THE (SELECT ITEM COLUMN(0) FROM DATABASE(,
SENDER_SYSTEM, TARGET_SYSTEM, ACCT, LOCATION, CAT, SUBCAT));''.
|
Using sqlplus and the brokers credentials to the db - the query runs fine. The final snippet regarding the sql statement appears off (leading comma in values list). I'm heading back to the manuals to see if I missed some relevant ESQL changes between the versions that might relate to the code.
thanks again |
|
| Back to top |
|
 |
| catwood2 |
| Posted: Fri Feb 23, 2007 1:50 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
I traced the working legacy code and the migrated code. Looks like there is an extra ' on the sql statement on the migrated.
migrated:
| Quote: |
'VMSEvent_G2.P_
Convert_to_MRM.Convert_DEV_MRM': Executing database SQL statement ''SELECT T.tar
get FROM g2mq_filter T WHERE (((((((T.sender)=(?))OR((T.sender)=('ALL')))AND(((T
.target)=(?))OR((T.target)=('ALL'))))AND(((T.account)=(?))OR((T.account)=('ALL')
)))AND(((T.site)=(?))OR((T.site)=('ALL'))))AND(((T.category)=(?))OR((T.category)
=('ALL'))))AND(((T.subcategory)=(?))OR((T.subcategory)=('ALL')))'' derived from
('', '1.1'); expressions ''SENDER_SYSTEM, TARGET_SYSTEM, ACCT, LOCATION, CAT
, SUBCAT''; resulting parameter values '''mcnxd001', 'IEMC.MQSI.VMSEVENT', 'E
CS', 'NDC', 'TESTING', 'LOS-FLOW-COMPLETED'''. |
legacy:
| Quote: |
'VMSEvent_G2.P_
Convert_to_MRM.Convert_TIF_MRM': Executing database SQL statement 'SELECT T.targ
et FROM g2mq_filter T WHERE (((((((T.sender)=(?))OR((T.sender)=('ALL')))AND(((T.
target)=(?))OR((T.target)=('ALL'))))AND(((T.account)=(?))OR((T.account)=('ALL'))
))AND(((T.site)=(?))OR((T.site)=('ALL'))))AND(((T.category)=(?))OR((T.category)=
('ALL'))))AND(((T.subcategory)=(?))OR((T.subcategory)=('ALL')))' derived from (1
, 1); expressions 'SENDER_SYSTEM, TARGET_SYSTEM, ACCT, LOCATION, CAT, SUBCA
T'; resulting parameter values ''mcnxt001', 'IEMC.MQSI.VMSEVENT', 'ECS', 'NDC
', 'TESTING', 'LOS-FLOW-COMPLETED''. |
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 23, 2007 6:28 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You should make sure you are using 6.0.0.3 runtime, and then open a PMR.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Feb 23, 2007 10:16 pm Post subject: Re: 2.1 -> 6 Migration: ESQL db select issue? |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi catwood2,
Not sure why the migrated code did not work, maybe as jeff said, raising a PMR might be a good idea.
For now I think this should work...
| Code: |
SET SUPPRESS_CHECK = THE (SELECT ITEM T.target
from Database.g2mq_filter as T
WHERE
(T.sender = SENDER_SYSTEM or T.sender =''ALL'')
AND (T.target = TARGET_SYSTEM or T.target =''ALL'')
AND (T.account = ACCT or T.account =''ALL'')
AND (T.site = LOCATION or T.site = ''ALL'')
AND (T.category = CAT or T.category = ''ALL'')
AND (T.subcategory = SUBCAT or T.subcategory = ''ALL'')); |
Regards. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Sat Feb 24, 2007 6:22 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You may need three ' there.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| catwood2 |
| Posted: Mon Feb 26, 2007 6:35 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
Thanks for the input. Opening PMR.
I altered the ESQL as suggested. 3 ' would be accepted - but, identical error occurred. Could not get 2 ' accepted without syntax error -
| Quote: |
Severity Description Resource In Folder Location Creation Time
2 Unable to find function or procedure named "Main" in module "P_Convert_to_MRM_Convert_DEV_MRM" in default broker schema. P_Convert_to_MRM.msgflow TEST6.0 February 26, 2007 9:31:02 AM
|
Jeff - did you suspect syntactical error with just 2 ' ?
thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Feb 26, 2007 6:48 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I did. Two ' means the same as "'". So to produce a string value of 'ALL' would mean that the CONTENTS of the quoted string would have to be ''ALL''.
And the string has to be quoted with a '. So it would have to be '''ALL'''.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| catwood2 |
| Posted: Tue Feb 27, 2007 6:38 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
This issue has been resolved. The ODBC stanza was specifying the broker database instead of the target external db. So, the error that the table or view did not exist was correct. The issue around how the trace reflected the query construction, query syntax, etc were not factors.  |
|
| 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
|
|
|
|
