| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| DB2 Stored procedure on OS/390 called from MQSI 2.1 |
« View previous topic :: View next topic » |
| Author |
Message
|
| mehedi |
 Posted: Wed Nov 20, 2002 12:39 pm Post subject: DB2 Stored procedure on OS/390 called from MQSI 2.1 |
 |
|
Centurion
Joined: 11 Nov 2001
Posts: 102
Location: PSTech
|
Hi All,
MQSI Ver 2.1 ON win nt
MQ series VER 5.2 ON win nt
Db2 ver 6.0 on os/390
Here is the case -
The DB2 stored procedure(return output parameters) on OS/390.
It is called using ESQL passthru in a message flow executing on a broker running on WIN NT.
After succesfully completing on the OS/390 side(confirmed via log view) is failing on return to MQSI with the following message
--------
2002-11-20 15:19:11.206999 147 UserTrace BIP2538I: Node 'testapi01.Compute1': Evaluating expression 'HDRC201O_RESP_VALUE' at (40, 10).
2002-11-20 15:19:11.206999 147 UserTrace BIP2544I: Node 'testapi01.Compute1': Executing database SQL statement 'CALL IBCPLN.HD2001(?,?,?,?,?,?,?,?,?,?,?,?,?,?)' derived from (26, 35);
expressions 'HDRC201I_RQST_ID, HDRC201I_RQST_TYPE, HDRC201I_RQST_SET_SEQ_NO, HDRC201I_RQST_CD_VALUE, HDRC201I_RQST_DATE, HDRC201I_XWALK_CD_SEQ_NO, HDRC201I_COMPLEX_ARGUMENTS, HDRC201O_RESP_STATUS, HDRC201O_RESP_MSG, HDRC201O_RESP_SHORT_DESC,
HDRC201O_RESP_LONG_DESC, HDRC201O_RESP_CD_SEQ_NO, HDRC201O_RESP_VALUE_SEQ, HDRC201O_RESP_VALUE';
resulting parameter values ''ABCD12340', '$', '000000015', '01', '2002-11-11', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL'.
2002-11-20 15:19:11.336999 147 UserTrace BIP2231E: Error detected whilst processing a message 'testapi01.Compute1'.
The message broker detected an error whilst processing a message in node 'testapi01.Compute1'. 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.
2002-11-20 15:19:11.336999 147 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.
2002-11-20 15:19:11.336999 147 DatabaseException BIP2322E: Database error: SQL State '24000'; Native Error Code '-99999'; Error Text '[IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000'.
The error has the following diagnostic information: SQL State '24000' SQL Native Error Code '-99999' SQL Error Text '[IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000'
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.
Regards
Mehedi Hashir/George Dolgov |
|
| Back to top |
|
 |
| kirani |
| Posted: Wed Nov 20, 2002 7:22 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Have you configured "gateway" to access DB2 on OS/390 from your WIN NT (Broker) box? Try executing the stored procedure from db2 command line from your broker box and see if you get the same error message.
_________________
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 |
|
|
| mehedi |
| Posted: Thu Nov 21, 2002 7:00 am Post subject: |
|
|
Centurion
Joined: 11 Nov 2001
Posts: 102
Location: PSTech
|
Hi Kirani,
Here is the "gateway" / odbc configuration information
(1) The db2client is ver 7.1
(2) The odbc driver points to the db2 connect server from where it connects to the db2 database on OS/390
(3) All SQL statements except for the call procedure statement are being
processed successfully from the db2 command line on WIN NT
(4) The same SQL call procedure statement executes successfully from the UNIX box.
Regards
Mehedi |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Nov 21, 2002 9:50 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Hi Mehedi,
This looks like a DB2 Connect setup issue than MQSI, because the call procedure statement is working from UNIX box but not from NT box? Is it possible to compare these 2 setup?
_________________
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 |
|
|
| Ian |
| Posted: Fri Nov 22, 2002 5:28 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Mehedi,
There are a few points :
Point 1:
| Quote: |
Db2 ver 6.0 on os/390
|
Note, MQSI/WMQI only supports accessing user databases on OS/390 at the following DB2 levels :
- DB2 V6.1 (Fix Pack 7 or Fix Pack 8).
- DB2 V7.1 (GA Level, Fix Pack 3, or Fix Pack 4).
- DB2 V7.2 (GA Level or Fix Pack 4).
Refer to the Supported Software pages at :
http://www-3.ibm.com/software/ts/mqseries/platforms/supported/wsmq_integb_for_zos_2_1.html
Point 2:
| Quote: |
The DB2 stored procedure(return output parameters) on OS/390.
It is called using ESQL passthru in a message flow executing on a broker running on WIN NT.
|
Note, in MQSI and WMQI the PASSTHRU function does NOT support OUT or INOUT (returned) parameters.
This means, that in MQSI and WMQI the PASSTHRU function ONLY supports IN parameters.
Refer to the ESQL Reference Manual (PASSTHRU pages 73-75) :
http://www-4.ibm.com/software/ts/mqseries/library/manualsa/manuals/wsmqsiv21.html
Limitations :
There are some limitations when using PASSTHRU to call stored procedures.
To illustrate the limitations, please consider the following example:
| Code: |
PASSTHRU('{call proc_delete_comp(?)}',InputBody.Test.Company);
|
- WebSphere MQ Integrator only supports input parameters.
- SqlMoreResults cannot be used by WebSphere MQ Integrator to retrieve result sets.
Point 3:
The PASSTHRU function remains unchanged, however, WMQIv2.1 CSD02 now includes support for calling stored procedures with IN, OUT and INOUT parameters and is implemented via the CREATE PROCEDURE command :
| Code: |
CREATE PROCEDURE spName (...) EXTERNAL NAME spDBName;
|
Refer to the ESQL Reference Manual (Create Procedure pages 58-62) :
http://www-4.ibm.com/software/ts/mqseries/library/manualsa/manuals/wsmqsiv21.html
_________________
Regards, Ian |
|
| Back to top |
|
|
| mehedi |
| Posted: Wed Nov 27, 2002 7:51 am Post subject: |
|
|
Centurion
Joined: 11 Nov 2001
Posts: 102
Location: PSTech
|
Hi Len,
Thanks for the help. We were able to successfully invoke the stored procedure.
Below is the ESQL from the compute node.
The syntax seems to be to always have the Create Procedure statement as the last or atleast after any statement that references the variable it uses.
SET OutputRoot = InputRoot;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
Declare RQST_ID CHAR;
Declare RQST_TYPE CHAR;
Declare RQST_SET_SEQ_NO CHAR;
Declare RQST_CD_VALUE CHAR;
Declare RQST_DATE CHAR;
Declare XWALK_CD_SEQ_NO CHAR;
Declare COMPLEX_ARGUMENTS CHAR;
Declare RESP_STATUS CHAR;
Declare RESP_MSG CHAR;
Declare RESP_SHORT_DESC CHAR;
Declare RESP_LONG_DESC CHAR;
Declare RESP_CD_SEQ_NO CHAR;
Declare RESP_VALUE_SEQ CHAR;
Declare RESP_VALUE CHAR;
SET RQST_ID = 'TEST SIMPLE CODESET';
SET RQST_TYPE = '$';
SET RQST_SET_SEQ_NO = '000000015';
SET RQST_CD_VALUE = '01';
SET RQST_DATE = '2002-11-25';
SET XWALK_CD_SEQ_NO = ' ';
SET COMPLEX_ARGUMENTS = ' ';
CALL HD2001
(
RQST_ID
,RQST_TYPE
,RQST_SET_SEQ_NO
,RQST_CD_VALUE
,RQST_DATE
,XWALK_CD_SEQ_NO
,COMPLEX_ARGUMENTS
,RESP_STATUS
,RESP_MSG
,RESP_SHORT_DESC
,RESP_LONG_DESC
,RESP_CD_SEQ_NO
,RESP_VALUE_SEQ
,RESP_VALUE
);
Set OutputRoot.XML.a.RESP_STATUS = Cast (RESP_STATUS as CHAR);
Set OutputRoot.XML.a.RESP_MSG = Cast (RESP_MSG as CHAR);
Set OutputRoot.XML.a.RESP_SHORT_DESC = Cast (RESP_SHORT_DESC as CHAR);
Set OutputRoot.XML.a.RESP_LONG_DESC = Cast (RESP_LONG_DESC as CHAR);
Set OutputRoot.XML.a.RESP_STATUS = Cast (RESP_STATUS as CHAR);
Set OutputRoot.XML.a.RESP_CD_SEQ_NO = Cast (RESP_CD_SEQ_NO as CHAR);
Set OutputRoot.XML.a.RESP_VALUE_SEQ = Cast (RESP_VALUE_SEQ as CHAR);
Set OutputRoot.XML.a.RESP_VALUE = Cast (RESP_VALUE as CHAR);
CREATE PROCEDURE HD2001
(
IN RQST_ID CHAR
,IN RQST_TYPE CHAR
,IN RQST_SET_SEQ_NO CHAR
,IN RQST_CD_VALUE CHAR
,IN RQST_DATE CHAR
,IN XWALK_CD_SEQ_NO CHAR
,IN COMPLEX_ARGUMENTS CHAR
, OUT RESP_STATUS CHAR
, OUT RESP_MSG CHAR
, OUT RESP_SHORT_DESC CHAR
, OUT RESP_LONG_DESC CHAR
, OUT RESP_CD_SEQ_NO CHAR
, OUT RESP_VALUE_SEQ CHAR
, OUT RESP_VALUE CHAR
)
EXTERNAL NAME "IBCPLN.HD2001";
Bye
Mehedi |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Nov 28, 2002 3:16 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Mehedi,
Yes, your observation here is correct.
Here is a list of my 'top tips' when calling stored procedures (in particular, note 5) :
Note 1:
MQSIv2.0.* and WMQIv2.1 CSD01 support calling stored procedures with INPUT parameters ONLY and does not support OUT or INOUT parameters.
This is implemented via the PASSTHRU function.
Note 2:
The PASSTHRU functionality remains unchanged between WMQIv2.1 CSD01, CSD02 and CSD03.
Note 3:
WMQIv2.1 CSD02 includes support for calling stored procedures with IN, OUT and INOUT parameters and is implemented via the CREATE
PROCEDURE command :
CREATE PROCEDURE spName (...) EXTERNAL NAME spDBName;
Note 4:
Package name cannot be specified as a part of EXTERNAL name of CREATE PROCEDURE statement.
Refer to the WMQIv2.1 CSD02 README :
| Quote: |
82a. ESQL CREATE PROCEDURE statement
When defining an EXTERNAL procedure using an explicit schema or an Oracle package
name the fully qualified external procedure name must be inside quotation marks,
e.g. "mySchema.myProcedure".
|
Note 5:
Deploy error occurs when SET statements exist after CREATE PROCEDURE statement.
This is not documented in either the README or ESQL Reference manual, but the CREATE PROCEDURE statement(s) must be at the end of ALL the
ESQL within a node otherwise you will get a syntax error at deploy time.
Note 6:
WMQIv2.1 CSD03 has introduced a change to the mechanism for calling stored procedures via the CREATE PROCEDURE command.
This was introduced as part of requirement to enable calling stored procedures on OS390.
In summary, when using a two part naming convention WMQI cannot determine if the first part is the Schema name or Package name. As such, WMQI now requires a three part name where the Procedure is part of a Schema and Package (ie, schema.package.procedure). Where the Procedure is not part of a Package then WMQI requires a two part name comprising Schema and Procedure (ie, schema.procedure).
A BIP2920E error message will be returned in WMQIv2.1 CSD03 if this is not modified appropriately as WMQI will take the CSD02 two part name you are using of "yourPackage.yourProcedure" and interpretting this as "yourSchema.yourProcedure".
Refer to the WMQIv2.1 CSD03 README :
| Quote: |
82a. CREATE PROCEDURE EXTERNAL NAME clause
The EXTERNAL NAME clause of the CREATE PROCEDURE statement can contain either
a qualified or unqualified procedure name. The rules for qualification vary
slightly between DB2 and Oracle databases.
For DB2 and Oracle, if a schema name is not provided, the database connection
username is used as a default schema name. If the required procedure does not
exist in this schema then an explicit schema name must be provided in the form
"mySchema.myProcedure" in the EXTERNAL NAME clause.
For Oracle the rules for a procedure that does not belong to an Oracle Package
are the same as those described above. However, if the procedure does belong to
an Oracle package then the procedure name must be qualified by both a schema
and a package name in the form "mySchema.myPackage.myProcedure" in the
EXTERNAL NAME clause.
|
_________________
Regards, Ian |
|
| 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
|
|
|
|
