Web Development

General discussion


Mainframe DB2 Stored Procedures

By john.mallonee ·
Has anyone ever successfully called a COBOL DB2 Stored Procedure on the mainframe (e.g. OS/390) from a desktop client program? I'm specifically interested in Crystal Reports, but I'm trying to get one to run from PowerBuilder 6.0 as well. I'm alsospecifically interested in returning a results set from the procedure. I have a stored procedure written in COBOL that can be called from another COBOL program successfully, but as yet not from a PC client program. It is recognized in the client (using DB2 CAE through DB2 Connect to the mainframe), apparently through the DB2 catalog, but the COBOL load module does not appear to execute. Thanks in advance...

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Mainframe DB2 Stored Procedures

by Mr.Khan In reply to Mainframe DB2 Stored Proc ...


You can able to call a stored procedure thru any client server application by the use of DB2 connect.

The minimum requirements for a stored procedure that can be executed from remote system are :

(a) Please make sure that if youwant to call stored procedure from any c/s
application, then u need a DB2 of version 4.0. If you want the the SP to
return result set, then u need a version of DB2 version 5.0. Please make sure that
DB2 connect is installed on your machines.(b) For all the stored procedures that are written on the DB2 side, You need
an entry on SYSIBM.SYSPROCEDURES a system defined package which DB2 maintains
like sysibm.syscols, sysibm.syspackage etc.

Please make sure that all the Stored Procedures that u r calling from
application, you should
have atleast a row in SYSIBM.SYSPROCEDURES.

(c) As far the SP side is concerned , there are two things to be done:

(1) Binding of Stored Procedure - Very similar to simple COBOL / DB2

(2) Start and Stop of Stored Procedure - This is achieved thru the commands
START PROCEDURE and STOP PROCEDURE commands. This is required when any of the input or output parm changes i.e when you have updated the row definition in
OR when you want DB2 to invoke the Recent Package.

Both of the steps are very imporant.

I believe the above info will be enough.

Please get back to me if you have any queries or if u need clarification on
any of the
points that i have mentioned above.

Collapse -

Mainframe DB2 Stored Procedures

by john.mallonee In reply to Mainframe DB2 Stored Proc ...

In response to Mr.Khan, thank you for your answer. I believe that I have covered all that you have discussed in your answer.

The DB2 version is 6.
IMS Connect is installed on servers which connect to OS/390, and DB2 Client Application Enabler(CAE) is installed on the desktop and connects to DB2 Connect. Other SQL to DB2 thru CAE and Connect on the desktop has been successful.

A row is in SYSIBM.SYSROUTINES instead of SYSIBM.SYSPROCEDURES (this is the newer catalog table in this version of DB2).

The stored procedure is started as you indicate.

The package and plan are bound (COBOL program and load module).

The desktop clients (PowerBuilder and Crystal Reports) seem to recognize that the stored procedure exists since they both prompt for parameters required. The problem seems to be in the execution of the procedure. The address space (DB2SPAS) job on OS/390 does not indicate that the procedure runs.

If you have any information that would be helpful or would like any further detail, please respond.

Thank you.

Related Discussions

Related Forums