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

Solution 1

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.

Solution 2

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