DB2 SQL code to extract stored procedures

Question

My colleagues and I have several hundred SQL stored procedures sitting on a hosted DB2/z database (version 8.1). We have no administrator rights and our access to the database is via QMF screens. Downloads are done through the 3270 terminal session with the TSO FT command.

Is there a simple/efficient way to extract the definitions/text of all of our stored procedures?

I'll like to do a weekly dump that we keep on-site in SVN or some other revision control system.

Any suggestions would be greatly appreciated.

Thanks
Stephen

---

Update -- 9 July 2009

Many thanks for the suggestions, but they don't seem to help in our particular config. I'll go back to our vendor and ask they for more info. Will update when I learn anything further.

Stephen

Answer 1

I think db2look should be able to get the DDL for you.

According to the docs the only thing you need is SELECT privilege on the system catalog tables.

I'm not too familiar with OS/390 anymore so not sure how you run db2look on that platform. Hopefully your DBA can help you out.

Answer 2

On DB2 z/OS, you will want to look in the system catalog tables, primarily SYSIBM.SYSROUTINES, SYSIBM.SYSROUTINES_OPTS, and SYSIBM.SYSROUTINES_SRC

Answer 3

If they have only end-user right on that db, does it matter?

from info center for v9.5 the following privilege is required:

SELECT privilege on the system catalog tables.

In some cases, such as generating table space container DDL (which calls the APIs sqlbotcq, sqlbftcq, and sqlbctcq), you will require one of the following:

* sysadm
* sysctrl
* sysmaint
* dbadm

Answer 4

You can get the stored procedure text by doing a

SELECT ROUTINE_DEFINITION FROM SYSIBM.ROUTINES;

Alternately, you can choose to retrieve only the SPs in your schema by doing a:

SELECT ROUTINE_DEFINITION FROM SYSIBM.ROUTINES WHERE SPECIFIC_SCHEMA = 'MYSCHEMA';

If you decide to limit the results by having a where clause, please note that it is case-sensitive and you need to specify the criteria in CAPS only.