| Author |
Message
|
| EvolutionQuest |
 Posted: Fri Aug 22, 2003 8:18 am Post subject: Compute node calling SQL Procedural Routine |
 |
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
I have been having problems accessing a stored procedural routine that was created in DB2 7.1. I am using the CALL {procedure name} ( parameters) and also the CREATE PROCEDURE {procedure name} ( parameters) EXTERNAL NAME "schema.procedure name". In the Compute node I am also setting my data source to the database name for the ODBC connection, while putting the stored procedure name in the table field. Is this right? I am using WMQI 2.1 CSD 5 and WMQ 5.3 CSD 2. The broker is on AIX, while DB2 is on OS/390. I haven't used stored procedure routines before so don't abuse me to much  |
|
| Back to top |
|
 |
| inder |
| Posted: Fri Aug 22, 2003 11:03 am Post subject: |
|
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
HI,
You can declare external procedure and call it as below. The parameters ST_NUM and others are corresponding decimal, char and int variables declared and initialized in the compute node
| Quote: |
CALL MIKE( ST_NUM, ORDNUM, TYP, STATUS, ERR_TEXT, FIELD_IR,FIELD_DB, ARET_CODE);
CREATE PROCEDURE MIKE(INOUT COMPCODE DECIMAL, INOUT ORD DECIMAL, INOUT TYPE CHAR, INOUT A INT, INOUT B CHAR, INOUT C1 CHAR, INOUT C2 CHAR, INOUT D INT)
EXTERNAL NAME "SKUDB.GET_INFO_RTL";
|
The Database and table names are for referring the Database. So give the Database name and a valid table name in it.
What do you mean by you are having some problems? DO you have deploy problems or exceptions during your execution?
regards
Inder |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Aug 22, 2003 11:16 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
Sorry about the vagueness (sp?) The compute node throws an exception which I catch in the failed terminal. I have a trace node that spits the ExceptionList along with the Root, and what I see in the trace is the following:
(
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbDataFlow
Node.cpp'
(0x3000000)Line = 536
(0x3000000)Function = 'ImbDataFlowNode::createExceptionList'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = 'db297b02-f700-0000-0080-ef53b57b62af.b04bd9f
6-f600-0000-0080-ef53b57b62af'
(0x3000000)Label = 'FOA.STD.ERROR.RTN.V001.FOA.STD.ERROR.RTN .V0
01.Subflow1.Process message'
(0x3000000)Text = 'Node throwing exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.c
pp'
(0x3000000)Line = 152
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Root SQL exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '100'
)
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 252
(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 = '02000'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '100'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '[IBM][CLI Driver][DB2] SQL0100W No row was found f
or FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTAT
E=02000
'
)
)
)
)
)
The ESQL looks like:
CALL STDERR01 (oi_temp, Out_Error_Code, Out_Error_Msg);
SET Environment.Variables.ReturnCode = Out_Error_Code;
SET Environment.Variables.ReturnMsg = Out_Error_Msg;
CREATE PROCEDURE STDERR01(
OUT oi_temp INTEGER,
OUT SQLSTATE_OUT CHARACTER,
OUT SQLCODE_OUT INTEGER
) EXTERNAL NAME "XXXXXX.STDERR01";
I have my datasource set to the defined ODBC provided by the DBA and the table is "STDERR01".
I have also tried other stored procedure routines and get the same error, so I suspect it is permissions, but I am not absolutely sure. |
|
| Back to top |
|
|
| inder |
| Posted: Fri Aug 22, 2003 11:39 am Post subject: |
|
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
has any of your normal SQL operations like Select worked for you in that compute node?, If not try running a simple select query like this on a valid small table.
| Quote: |
declare CNT int;
set CNT = the (select count(*) from Database.Table_name);
|
See whether or not u got any errors. If this runs through fine then your database connectivity is fine. We can look in other things.
And also the Error looks like a warning. Did you select in the advance tab of compute node "treat warnings as errors" |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Fri Aug 22, 2003 12:15 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
Found the little bugger. I had earlier turned on the switch to make all warnings into an error. The warning message made no sense at all, so it didn't hit me that I left that switch on under the Advance Tab for the Compute node.
Thanks for the help  |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Mon Aug 25, 2003 7:34 am Post subject: Another issue related to stored procedure routines |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
This is frustrating, especially when the errors are not descriptive enough.
I made sure the necessary permissions have been granted to me on the stored procedure routine, and I have ran it externally to verify it works fine. Now I get this hoky message saying an execption is being thrown. Why?
(
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbDataF
lowNode.cpp'
(0x3000000)Line = 536
(0x3000000)Function = 'ImbDataFlowNode::createExceptionList'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = 'db297b02-f700-0000-0080-ef53b57b62af.b04b
d9f6-f600-0000-0080-ef53b57b62af'
(0x3000000)Label = 'FOA.STD.ERROR.RTN.V001.FOA.STD.ERROR.RTN
.V001.Subflow1.Process message'
(0x3000000)Text = 'Node throwing exception' (0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlRouti
ne.cpp'
(0x3000000)Line = 836
(0x3000000)Function = 'SqlRoutine::clearDownChildEnv'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = 'db297b02-f700-0000-0080-ef53b57b62af.b04bd9f6-f600-
0000-0080-ef53b57b62af'
(0x3000000)Label = 'FOA.STD.ERROR.RTN.V001.FOA.STD.ERROR.RTN .V001.Subf
low1.Process message'
(0x3000000)Text = ''
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2930
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'TEMP'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'INTEGER'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'CHARACTER'
)
)
)
) |
|
| Back to top |
|
|
| inder |
| Posted: Mon Aug 25, 2003 7:46 am Post subject: |
|
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
The exception looks like you are sending incorrect parameters. sending in a character string in place of an integer. Check that out. You can look at the messages PDF to find out the meaning on the error messages
| Quote: |
BIP2930E A function or procedure was called but the value supplied for the ’&1’parameter was of type ’&2’ but the function/procedure expects value of type ’&3’ Explanation: Functions and procedures require an exact matching between the data type of values supplied as parameters and the data type in its definition.
User Response: Examine and correct the SQL program
|
regards
Inder |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Mon Aug 25, 2003 9:48 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
hehe, What I need to do is act like I am sending a message to the board and then in a short period of time I'll figure it out. Sorry to waste your time as I did find my error with the type declaration being incorrect. It must be Monday!!!
 |
|
| Back to top |
|
|
|
|
