| Author |
Message
|
| miron |
 Posted: Mon Jun 30, 2003 11:23 am Post subject: DB2 Stored Procedure on os/390 |
 |
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hello,
I am trying to execute a DB2 stored procedure on os/390.
I run into error:
BIP2920 The procedure is unknown to the database and no definition could be found.
However the procedure is there.
My code looks like this:
| Code: |
Call test_proc
(IN_MACH_TYPE
,IN_SERIAL_NUM
,OP_ID
,IN_CC
,IN_SC
,OUT_STATUS);
set Environment.Variable.PARMS.Out_Status = OUT_STATUS;
Create Procedure test_proc
(in IN_MACH_TYPE char
,in IN_SERIAL_NUM char
,in OP_ID char
,in IN_CC char
,in IN_SC char
,out OUT_STATUS char)
External Name "DSND.GARDDBA.GRTSP01";
|
So i call my stored procedure as "subsystem.schema.procname"
Broker id is different but proc is open to public.
Do I have to create a schema with broker id to make it work?
Is there anything I am missing in configuration?
Thanks for help.
We run:
WMQI 2.1 CSD03 on os/390
MQ 5.21
DB2 7.10 |
|
| Back to top |
|
 |
| shalabh1976 |
| Posted: Mon Jun 30, 2003 9:07 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Does your stored procedure run if you call it through a program on S390 say a COBOL DB2 program.
If you have WLM, you may need to define your stored procedure to it.
IBM has a redbook on it.
I don't remember the name exactly but it was something like :
"Getting started with DB2 stored procedures :
Give them a call over the network " |
|
| Back to top |
|
|
| miron |
| Posted: Tue Jul 01, 2003 3:52 am Post subject: stored procedure |
|
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hi ,
My stored procedure is written in PL/I and it is defined in DB2 catalog.
It works fine when called from another program.
In 'Developing Solutions' book it says:
| Quote: |
External database stored procedures
You can call external database procedures using ESQL (DB2 or Oracle stored
procedure are supported). To call an external database stored procedure you
must define it in the databases as well as the broker.
|
What does 'as well as the broker' means?
Also can I reference this stored procedure with full path "subsystem.schema.procname" or it has to be referenced just by the name like in this book example and the broker assigns broker's id as high level qualifier?
Thanks for your input.
Miron |
|
| Back to top |
|
|
| Craig B |
| Posted: Tue Jul 01, 2003 4:05 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
The following is documented in point 82a of the WMQI V2.1 CSD04 readme.txt :
| 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.
|
This suggests you can specify your schema name in the definitions. From what you have documented you have tried "DSND.GARDDBA.GRTSP01". Do you need the subsystem id? Have you tried just "GARDDBA.GRTSP01"??
_________________
Regards
Craig |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Tue Jul 01, 2003 8:41 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Craig, is CSD04 for NT/win2K equivalent to CSD03 for OS/390 / z/OS ?
For all you know this might not be supported for CSD03(z/OS). |
|
| Back to top |
|
|
| Craig B |
| Posted: Tue Jul 01, 2003 11:35 pm Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
CSD04 for distributed platforms is NOT the same CSD03 on the zOS platform. CSD04 is also available for the zOS platform which is equivalent to its other CSD04 platform counterparts.
The following is documented in the WMQI V2.1 CSD04 readme.txt
| Quote: |
81. Stored Procedure database support
Stored Procedures are supported on DB2 Version 7.1 and Oracle 8.1.6 and 8.1.7.
They are not currently supported on DB2 6.1, Sybase and SQLServer. In addition
overloaded stored procedures are not supported. An overloaded procedure is
one which has the same name as another procedure in the same database schema,
and they only differ on type or number of parameters. If the broker detects
that a given procedure has been overloaded, an exception will be thrown.
|
Points 81 and 82a are documented in both the CSD03 and CSD04 readme.txts so they apply to both these CSD's. However, as pointed out, your IBM support team should be able to give you a formal statement on whether this is supported or not.
_________________
Regards
Craig |
|
| Back to top |
|
|
| miron |
| Posted: Wed Jul 02, 2003 7:26 am Post subject: |
|
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hello,
Thanks for all replies.
The problem was that you can not use subsystem name in your stored procedure name. So the syntax for calling external procedure would be
"schema.procname".
I am however now getting SQLCODE -751 which is related to XA coordination I believe. The stored procedure I call has some SQL Rollback coded in it and this is causing -751.
I do have 'Coordinated Transaction Property' set to yes but still I am getting this error.
Miron |
|
| Back to top |
|
|
|
|
