| Author |
Message
|
| jharringa |
 Posted: Fri Sep 07, 2007 1:30 pm Post subject: ESQL PASSTHRU problem when passing to ODBC driver |
 |
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
From what I can tell, the following should work (with the Data source set on the Compute node and a DB2 ODBC Alias created on the broker's server):
| Code: |
DECLARE Database_Schema EXTERNAL CHARACTER '';
SET Temp_Count = PASSTHRU ('SELECT COUNT(CUST.CMN_CUST_KEY) FROM ' || Database_Schema || '.CUST_TB AS CUST WHERE CUST.CMN_CUST_KEY = ?' TO Database.DBALIAS VALUES(CAST(Environment.Variables.Common_Cust_Id AS CHARACTER))); |
(names have been changed to protect the innocent)
However, I am getting the following back from the broker which is getting this from the ODBC driver:
Error Description: 2322 Child SQL exception. HY009. -99999. {IBM}{CLI Driver} CLI0124E Invalid argument value. SQLSTATE=HY009
Any ideas?
I've also tried (along with several other gyrations) and got the same error:
| Code: |
| PASSTHRU ('SELECT COUNT(CUST.CMN_CUST_KEY) FROM ' || Database_Schema || '.UCC_CUST AS CUST WHERE CUST.CMN_CUST_KEY = ?', CAST(Environment.Variables.Common_Cust_Id AS CHARACTER)); |
|
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Sep 07, 2007 2:02 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is CUST.CMN_CUST_KEY a character value in the table?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jharringa |
| Posted: Mon Sep 10, 2007 7:04 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
Well, I was hoping that my silly mistake of forgetting the data type would have fixed the problem but the correct code (I think) still returns the same error. From what I know, I should be able to cast as DECIMAL(15,0) right?
| Code: |
| SET Temp_UCC_Count = PASSTHRU ('SELECT COUNT(CUST.CMN_CUST_KEY) FROM ' || Database_Schema || '.UCC_CUST AS CUST WHERE CUST.CMN_CUST_KEY = ?', CAST(Environment.Variables.Common_Cust_Id AS DECIMAL(15,0))); |
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Sep 10, 2007 10:51 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I guess if you're reasonably sure that the parameter datatype is now correct, I would check the DB2 Info Center to find out more about the error message.
I might also double-check that the SQL statement being fed to passthru is being produced correctly.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jharringa |
| Posted: Tue Sep 11, 2007 9:51 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
This has become pretty confusing. I pulled the node out to another flow, changed the code to be VERY basic (essentially take in an MQ message and output a new message to the reply node without any passthru statements or any database connection references (no Data Source)). It did not work until I actually copied the code from the ESQL, deleted the node and created a new Compute Node using a new ESQL file. I then copied the ESQL into that node's ESQL module (different name) and then it worked.
I tried doing the same thing with the original node in a different project and it would not work if I just created a new Compute Node. I had to create a whole new ESQL file in order to get the simple case to work (all of the same logic that I had before minus the PASSTHRU and Data Source setup). It seems that this ESQL was hosed once it failed on connecting to the data source as I continued to receive these messages in debug mode:
| Quote: |
2007-09-11 10:51:59.725425 6940 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.
2007-09-11 10:51:59.725437 6940 DatabaseException BIP2322E: Database error: SQL State ''HY009''; Native Error Code '-99999'; Error Text ''[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009''.
The error has the following diagnostic information: SQL State ''HY009'' SQL Native Error Code '-99999' SQL Error Text ''[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009''
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.
2007-09-11 10:51:59.725463 6940 UserTrace BIP4163I: Message propagated to the first terminal of the FlowOrder node 'com.....DecisioningFlow.RequestReply_SOAP.FlowOrder'.
The FlowOrder node has received a message and has propagated it to the output terminal called first. Once the processing of the message down the first terminal has finished, the message will be propagated to the second terminal.
No user action required.
|
This seems like very odd behavior since it is my understanding that if I clear out the Data Source in the Compute node and deploy that it should no longer attempt to connect to the Data Source.
I'm going to continue to try and push the limit up to where it would break. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Sep 11, 2007 9:57 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Phantom Execution Group?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jharringa |
| Posted: Tue Sep 11, 2007 10:05 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
It should only be deployed on one execution group per broker in our cluster and I'm directing my MQ message at one particular broker. I've checked the other execution groups on this broker and this flow isn't deployed on any of those groups. Is there something that would cause this execution group to become a "phantom execution group"? Are you thinking that adding the new ESQL brought it out of its phantom state?
Thanks for helping me through this Jeff |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Sep 11, 2007 10:21 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
No, I'm thinking that something caused an additional DataFlowEngine process to start or stay running for some given EG, and this DFE has an older version of your flow deployed to it.
And it's that older version that's being invoked sometimes, and throwing the error messages.
But it's a reach.
The other possibility is that the ESQL file had a mix of Unix and windows line endings somehow.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jharringa |
| Posted: Tue Sep 11, 2007 12:28 pm Post subject: |
|
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
I'm doing my development on a Windows box (in the Broker toolkit) and the Broker is running on AIX. Do I need to be doing a conversion on the files to ensure that there aren't any problems or does the toolkit/deployment take care of that for me? It doesn't sound like it.
I had an admin do an mqsilist and there is only one instance of that execution group running so I think it probably isn't that.
I have another oddity that I'm seeing now. I'm starting to wonder if this execution group is flaky (if that's possible).
I have defined a User Defined Property called "DB2Schema"
I have "DECLARE DB2Schema EXTERNAL CHARACTER;" at the global level within my ESQL
When I try and deploy it says that the property "mynamespace.DB2Schema" must be defined either in the ESQL or in the deployment artifact. I have it defined in my bar file but I noticed that the error message doesn't have my flow name between the namespace and the property. I have backed this off a couple of times successfully by removing the UDP and hard coding a value (still not hitting the database). Now I'm getting this message about one of my subflows:
| Quote: |
The entire internal configuration message failed to be processed successfully.
The internal configuration message failed to be processed, use the messages following this message to determine the reasons for the failure.
BIP4041E: Execution group 'EG1' received an invalid configuration message. See the following messages for details of the error.
The message broker received an invalid configuration message and has not updated its configuration. This can arise as a result of errors in the specification of either message flows or message sets which the configuration manager was unable to detect. It can also result from a message flow requiring a type of node that is not supported by the broker installation, from the broker having become out of step with the configuration database or from other applications sending extraneous messages to the broker's configuration queues (SYSTEM.BROKER.ADMIN.QUEUE & SYSTEM.BROKER.EXECUTIONGROUP.QUEUE).
Check the relevant message flow and message set definitions, check that all necessary user-defined extensions are installed, perform a complete redeploy of the broker's configuration and ensure that no applications are writing to the broker's configuration queues.
BIP4001E: Syntax error in SQL statements in node 'mynamespace.DecisioningFlow.Move_MsgId_To_CorrelId.Move_MsgId_To_CorrelId'.
The configuration failed due to errors in the SQL statement text. See the following messages for details of the error
The following error messages will give specific details of the location and nature of the error. Check and correct the syntax of the SQL statements and redeploy.
Deploy using config /opt/IBM/configfiles/DEV1.configmgr, execution group EG1, and bar file /opt/IBM/deploy/dev/EG1/CustomerDeleteDEV.bar failed |
This is what my code looks like in that subflow:
| Code: |
BROKER SCHEMA myschema
CREATE COMPUTE MODULE Move_MsgId_To_CorrelId_Move_MsgId_To_CorrelId
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
SET Environment.Variables.MQMD.CorrelId = InputLocalEnvironment.WrittenDestination.MQ.DestinationData.msgId;
RETURN TRUE;
END;
END MODULE;
|
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Sep 11, 2007 12:37 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
A phantom EG is one that doesn't show up in mqsilist, but does show up as an additional DFE process.
You need to look at the rest of the deployment messages to find out what's wrong with your ESQL: "The configuration failed due to errors in the SQL statement text. See the following messages for details of the error "
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jharringa |
| Posted: Wed Sep 12, 2007 7:31 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2007
Posts: 70
|
| I changed over to the standard ESQL Database facilities (as opposed to the PASSTHRU), cleaned up the code, and I got it working. I suspect that something may have gotten messed up in the process of trying to debug this. Thanks for your help jeff! |
|
| Back to top |
|
|
|
|
