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

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.