Need info about Db2 stored procedures

kmdhandapani · New User Joined: 13 Jul 2007 Posts: 10 Location: india

Can someone provide details of store procedure in DB2 ?
And how to invoke Db2 in mainframe..
Is with SPUFI or need to code cobol program etc

Regards,
Dhans.

kussu · New User Joined: 16 May 2007 Posts: 33 Location: India

Hi,

This is the information i know regarding DB2 stored procedures:

1. Load resides in the db2 area.
2. Called using the SQL statement.
3. Is very length sensitive, if mismatched gives -440 sql code.
4. Files cannot be used.
5. Cannot call a batch program but can call a stored procedure in it.
6. Depending on the db2 status the stored procedure can be down.
7. Any open system can call this.

stored procedure definitions can be stored in
SYSIBM TABLES
SYSYROUTINES:-
1. definition of the SP
2. checks in which region the SP Resides.
3. Result set information( data to be returned in the form of temporary
Tables.)
4. Result columns(number of columns For the result table.).
5. Commit on demand ( whether the changes have to be committed after
the execution of the stored Procedure).
SYSPARMS:
In this table the order of the parameters and the lengths of the parameters are declared.

Please open these tables and try to find the fields so that you can understand better.

And You can Invoke DB2 in both the ways
If You eloborate this Question, i can answer you in much better way

hikaps14 · Active User Joined: 02 Sep 2005 Posts: 189 Location: Noida

Hi ,

I am using SPUFI for accessing DB2.
I accessed table SYSROUTINES. I could not find any column having definition of SP.

Can't I view the definition of SP.

Thanks,
-Kapil.

vebs · New User Joined: 27 Oct 2007 Posts: 19 Location: UK

hikaps,

You can check for ROUTINETYPE = 'P' in SYSIBM.SYSROUTINES. The corresponding row will give you all particulars about SP that are used to define, like ASUTIME, LANGUAGE etc. Write all that and you have defination of SP.