DB2 SQL code for retrieving stored procedures

Question

My colleagues and I have several hundred SQL stored procedures that reside in a hosted DB2 / z database (version 8.1). We do not have administrator rights, and our access to the database is through QMF screens. Downloads are made through a 3270 terminal session using the TSO FT command.

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

I would like to make a weekly dump that we save in place in SVN or some other version control system.

Any suggestions are welcome.

thanks
Stephen

---

Update - July 9, 2009

Thanks a lot for the suggestions, but they don't seem to help in our specific configuration. I will return to our supplier and ask them for more information. Will be updated when I find out anything else.

Stephen

Answer 1

You can get the text of the stored procedure by doing

 SELECT ROUTINE_DEFINITION FROM SYSIBM.ROUTINES; 

Alternatively, you can choose to receive only SP in your circuit by doing:

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

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

Answer 2

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

Answer 3

If they have only the end user on this db, does it matter?

The following privilege is required from the information center for version 9.5:

SELECT privilege in system catalog tables.

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

 * sysadm * sysctrl * sysmaint * dbadm 

Answer 4

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

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

I'm not too familiar with OS / 390, so I'm not sure how you run db2look on this platform. I hope your database administrator can help you.