提取存储过程的 DB2 SQL 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/981295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 SQL code to extract stored procedures
提问by Stephen Simmons
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 FTcommand.
我和我的同事在托管的 DB2/z 数据库（8.1 版）上有数百个 SQL 存储过程。我们没有管理员权限，我们通过 QMF 屏幕访问数据库。下载是通过 3270 终端会话使用 TSOFT命令完成的。
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.
我想做一个每周转储，我们在 SVN 或其他一些版本控制系统中保存在现场。
Any suggestions would be greatly appreciated.
任何建议将不胜感激。
Thanks
Stephen
谢谢
斯蒂芬
Update -- 9 July 2009
更新 -- 2009 年 7 月 9 日
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
斯蒂芬
回答by SO User
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:
或者，您可以通过执行以下操作选择仅检索架构中的 SP：
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-sensitiveand you need to specify the criteria in CAPS only.
如果您决定使用 where 子句来限制结果，请注意它区分大小写并且您只需要在 CAPS 中指定条件。
回答by Stephen Simmons
On DB2 z/OS, you will want to look in the system catalog tables, primarily SYSIBM.SYSROUTINES, SYSIBM.SYSROUTINES_OPTS, and SYSIBM.SYSROUTINES_SRC
在 DB2 z/OS 上，您需要查看系统目录表，主要是 SYSIBM.SYSROUTINES、SYSIBM.SYSROUTINES_OPTS 和 SYSIBM.SYSROUTINES_SRC
回答by Datalogist
If they have only end-user right on that db, does it matter?
如果他们在该数据库上只有最终用户权限，这有关系吗？
from info center for v9.5 the following privilege is required:
来自 v9.5 的信息中心需要以下权限：
SELECT privilege on the system catalog tables.
对系统目录表的 SELECT 特权。
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:
在某些情况下，例如生成表空间容器 DDL（调用 API sqlbotcq、sqlbftcq 和 sqlbctcq），您将需要以下内容之一：
* sysadm
* sysctrl
* sysmaint
* dbadm
回答by Michael Sharek
I think db2lookshould be able to get the DDL for you.
我认为db2look应该能够为您获取 DDL。
According to the docs the only thing you need is SELECT privilege on the system catalog tables.
根据文档，您唯一需要的是对系统目录表的 SELECT 权限。
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.
我对 OS/390 不太熟悉，所以不确定如何在该平台上运行 db2look。希望您的 DBA 可以帮助您。
