DB2 Stored Procedures

Robert J. Milonas

DB2 Stored Procedures
We are currently using OS/390 DB2 UDB V6. We want to write cobol stored procedures that will call other cobol stored procedures. DB2 calls this nested stored procedures. Has anyone written a cobol stored procedure that calls another cobol stored procedure? or a plain cobol subroutine (without any SQL)?

DB2 UDB for OS/390 V6 Application Programming and SQL Guide states:
7.2.4.2 Calling other programs
A stored procedure can consist of more than one program, each with its own
package. Your stored procedure can call other programs, stored procedures,
or user-defined functions. Use the facilities of your programming language
to call other programs

At another IBM site describing the enhancements for DB2 OS/390 V7, it says:
DB2 now delivers nested stored procedure support that not only simplifies the migration of applications from other database vendors, but also allows application developers to use and exploit stored procedures in a more effective way. See the Application Development Guide for more information.

In an IBM software announcement 200-187 dated Apr 18,200 it says:
Now, you can take advantage of Precompiler Services to perform the tasks currently executed by the DB2 precompiler. This API can be called by the COBOL compiler. By using this option, you can eliminate the DB2 precompile step in program preparation. You can take advantage of language capabilities that had been restricted by the precompiler, such as nested COBOL programs. Use of the host language compiler enhances DB2 Family compatibility, making it easier to import applications from other database management systems and from other operating environments.

And at another IBM site http://www-4.ibm.com/software/data/db2/udb/v7/beta/ebus.html It states:
4) Nested Stored Procedures:
DB2 now delivers nested stored procedure support that not only simplifies the migration of applications from other database vendors, but also allows application developers to use and exploit stored procedures in a more effective way. Nested stored procedure calls will be permitted for stored procedures catalogued as LANGUAGE SQL, LANGUAGE C, and LANGUAGE JAVA provided that both the caller and the callee are fenced. A stored procedure can only call another stored procedure if it is written in the same language. COBOL stored procedures may not be nested.
--




__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with [login to unmask email]! http://shopnow.netscape.com/

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/



Craig Theisen

Re: DB2 Stored Procedures
(in response to Robert J. Milonas)
Yes Robert, we are also running version 6 of DB2 on OS/390 and are doing
both of your scenarios. We have a COBOL stored procedure calling another
COBOL program which in turn reads VSAM files. Also we have stored
procedures calling a common error logging stored procedure. Everything
is working fine, I do not remember any unexpected problems while
developing these either!


Craig Theisen
Database Support
GuideOne Insurance Company



-----Original Message-----
From: Robert J. Milonas [mailto:[login to unmask email]
Sent: Wednesday, January 02, 2002 10:42 AM
To: [login to unmask email]
Subject: DB2 Stored Procedures


We are currently using OS/390 DB2 UDB V6. We want to write cobol stored
procedures that will call other cobol stored procedures. DB2 calls this
nested stored procedures. Has anyone written a cobol stored procedure that
calls another cobol stored procedure? or a plain cobol subroutine (without
any SQL)?

DB2 UDB for OS/390 V6 Application Programming and SQL Guide states:
7.2.4.2 Calling other programs
A stored procedure can consist of more than one program, each with its own
package. Your stored procedure can call other programs, stored procedures,
or user-defined functions. Use the facilities of your programming language
to call other programs

At another IBM site describing the enhancements for DB2 OS/390 V7, it says:
DB2 now delivers nested stored procedure support that not only simplifies
the migration of applications from other database vendors, but also allows
application developers to use and exploit stored procedures in a more
effective way. See the Application Development Guide for more information.

In an IBM software announcement 200-187 dated Apr 18,200 it says:
Now, you can take advantage of Precompiler Services to perform the tasks
currently executed by the DB2 precompiler. This API can be called by the
COBOL compiler. By using this option, you can eliminate the DB2 precompile
step in program preparation. You can take advantage of language capabilities
that had been restricted by the precompiler, such as nested COBOL programs.
Use of the host language compiler enhances DB2 Family compatibility, making
it easier to import applications from other database management systems and
from other operating environments.

And at another IBM site
http://www-4.ibm.com/software/data/db2/udb/v7/beta/ebus.html It states:
4) Nested Stored Procedures:
DB2 now delivers nested stored procedure support that not only simplifies
the migration of applications from other database vendors, but also allows
application developers to use and exploit stored procedures in a more
effective way. Nested stored procedure calls will be permitted for stored
procedures catalogued as LANGUAGE SQL, LANGUAGE C, and LANGUAGE JAVA
provided that both the caller and the callee are fenced. A stored procedure
can only call another stored procedure if it is written in the same
language. COBOL stored procedures may not be nested.
--




__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas.
Experience the convenience of buying online with [login to unmask email]!
http://shopnow.netscape.com/

Get your own FREE, personal Netscape Mail account today at
http://webmail.netscape.com/








Steve Grimes

Re: DB2 Stored Procedures
(in response to Craig Theisen)
Hello,

We do this, OS/390, V6.1. We have SP's that call SP's, which in turn do
updates that fire triggers that call SP's which call other SP's. There is a
limit of 16 that I'm aware of on the triggers, but I'm not sure what if any the
limits are on the SP's.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Steve Grimes
Washington University Information Systems
OS/390 V2R10 DB2 UDB 6.1
W: 314-935-4376 H: 636-928-5005 Isa.64:6
[login to unmask email]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Robert J. Milonas

Re: DB2 Stored Procedures
(in response to Steve Grimes)
Thanks for the information. We want to take some of our CICS cobol subroutines and convert them to stored procedures so that we can call
them from HTML/Visual Basic scripts. And in cobol we call down two or
three module levels.



"Theisen, Craig" <[login to unmask email]> wrote:

>Yes Robert, we are also running version 6 of DB2 on OS/390 and are doing
>both of your scenarios. We have a COBOL stored procedure calling another
>COBOL program which in turn reads VSAM files. Also we have stored
>procedures calling a common error logging stored procedure. Everything
>is working fine, I do not remember any unexpected problems while
>developing these either!
>
>
>Craig Theisen
>Database Support
>GuideOne Insurance Company
>
>
>
>-----Original Message-----
>From: Robert J. Milonas [mailto:[login to unmask email]
>Sent: Wednesday, January 02, 2002 10:42 AM
>To: [login to unmask email]
>Subject: DB2 Stored Procedures
>
>
>We are currently using OS/390 DB2 UDB V6. We want to write cobol stored
>procedures that will call other cobol stored procedures. DB2 calls this
>nested stored procedures. Has anyone written a cobol stored procedure that
>calls another cobol stored procedure? or a plain cobol subroutine (without
>any SQL)?
>
>DB2 UDB for OS/390 V6 Application Programming and SQL Guide states:
> 7.2.4.2 Calling other programs
>A stored procedure can consist of more than one program, each with its own
>package. Your stored procedure can call other programs, stored procedures,
>or user-defined functions. Use the facilities of your programming language
>to call other programs
>
>At another IBM site describing the enhancements for DB2 OS/390 V7, it says:
>DB2 now delivers nested stored procedure support that not only simplifies
>the migration of applications from other database vendors, but also allows
>application developers to use and exploit stored procedures in a more
>effective way. See the Application Development Guide for more information.
>
>In an IBM software announcement 200-187 dated Apr 18,200 it says:
>Now, you can take advantage of Precompiler Services to perform the tasks
>currently executed by the DB2 precompiler. This API can be called by the
>COBOL compiler. By using this option, you can eliminate the DB2 precompile
>step in program preparation. You can take advantage of language capabilities
>that had been restricted by the precompiler, such as nested COBOL programs.
>Use of the host language compiler enhances DB2 Family compatibility, making
>it easier to import applications from other database management systems and
>from other operating environments.
>
>And at another IBM site
>http://www-4.ibm.com/software/data/db2/udb/v7/beta/ebus.html It states:
>4) Nested Stored Procedures:
>DB2 now delivers nested stored procedure support that not only simplifies
>the migration of applications from other database vendors, but also allows
>application developers to use and exploit stored procedures in a more
>effective way. Nested stored procedure calls will be permitted for stored
>procedures catalogued as LANGUAGE SQL, LANGUAGE C, and LANGUAGE JAVA
>provided that both the caller and the callee are fenced. A stored procedure
>can only call another stored procedure if it is written in the same
>language. COBOL stored procedures may not be nested.
>--
>
>
>
>
>__________________________________________________________________
>Your favorite stores, helpful shopping tools and great gift ideas.
>Experience the convenience of buying online with [login to unmask email]!
>http://shopnow.netscape.com/
>
>Get your own FREE, personal Netscape Mail account today at
>http://webmail.netscape.com/
>
>
>
>
>
>
>
>
>
--




__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with [login to unmask email]! http://shopnow.netscape.com/

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/