工作需要——研究DB2数据库系统表（收集备用）

杨叔叔

db2 系统表信息  

2011-05-25 10:00:18|  分类： DB2 |字号 订阅

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0706changw/index.html?S_TACT=105AGX52&S_CMP=techcto


DB2 CLP 简介

DB2 Command Line Processor（DB2 CLP）是所有 DB2 产品中都有的，可以使用这个应用程序运行 DB2 命令、操作系统命令或 SQL 语句。DB2 CLP 可以成为强大的工具，因为它能够将经常使用的命令或语句序列存储在批处理文件中，可以在必要的时候运行这些批处理文件。在 Windows 系统中，必须先（从普通命令窗口）运行 db2cmd 命令来启动 DB2 命令行环境。

在什么时候使用本文介绍的脚本？

脚本的优势在于可以重复执行。如果一个任务会被执行多次，或者被多人执行。那么将该任务编写成脚本会提高任务的执行效率和准确性。本文介绍的脚本都是一些在我们进行存储过程开发和调试中经常用到的。读者在后面会渐渐体会到脚本给我们带来的便利。

DB2 系统表的功能

编写针对 DB2 的脚本，我们需要首先了解 DB2 为我们提供了哪些命令和信息。DB2 为我们提供了大量的命令例如连接数据库，执行一个 SQL 文件，获得表结构的信息等等。我们会在下面解释具体的脚本的同时对一些简单的 DB2 命令进行解释说明。同时，DB2 把数据库对象的很多信息都存储到了系统表中。熟悉这些系统表就能够通过 SQL 语句获得我们需要的信息。下面我们先来学习一下 DB2 系统表。

在 DB2 数据库被创建的时候，DB2 会创建一些系统表。这些系统表中记录了所有数据库对象的信息，表或视图的列的数据类型，约束的定义，对象的权限和对象之间的依赖关系等。这些系统表的模式为 SYSIBM，其表名以 SYS 作为前缀。例如: SYSTABLES、SYSVIEWS 等等。DB2 为这些系统表建立了相对应的只读视图。这些视图的模式是 SYSCAT，它们的内容是其相对应的系统表的全部或者部分内容。这些视图的名字没有 SYS 的前缀。例如：SYSCAT.TABLES 是 SYSIBM.SYSTABLES 的视图。

我们可以通过 LIST TABLES FOR SYSTEM 或 LIST TABLES FOR SCHEMA schemaname 命令查看所有的系统表和相关的视图信息。下面我们会介绍一些本文用到的系统表和视图。

SYSCAT.TABLES：数据库中对象的信息，包括 table，view，nickname 和 alias 的一些定义。详细说明见表 1。

表 1. SYSCAT.TABLES 视图的说明
列名数据类型描述
TABSCHEMAVARCHAR(128)记录 schema 的名字
TABNAMEVARCHAR(128)记录数据库对象的名称。包括表、视图、别名等
TYPECHAR(1)表示该数据库对象是表，视图还是别名 ('T'表示table； 'V'表示 view； 'N' 表示nickname； 'A' 表示 alias。)
COLCOUNTSMALLINT表或视图中列的个数
……

SYSCAT.VIEWS：视图的定义信息。详细说明见表 2。

表 2. SYSCAT.VIEWS 视图的说明
列名数据类型描述
VIEWSCHEMAVARCHAR(128)视图的 Schema
VIEWNAMEVARCHAR(128)视图名称
READONLYCHAR(1)视图是否只读:

    Y = 视图是只读
    N = 视图不是只读

VALIDCHAR(1)视图状态是否合法:

    Y = 视图状态合法（valid）
    X = 视图状态不合法（invalid）

TEXTCLOB (64K)视图的源程序（DDL）
……

SYSCAT.ROUTINES：DB2 UDF，系统方法（system-generated method），用户定义方法（user-defined method）和存储过程（SP）的定义。我们可以认为该视图包含了数据库中程序的定义。见表 3。

表 3. SYSCAT.ROUTINES 视图的说明
列名数据类型描述
ROUTINESCHEMAVARCHAR(128)记录程序的 schema
ROUTINENAMEVARCHAR(128)记录程序名称
ROUTINETYPECHAR(1)记录程序类型：

    F = Function
    M = Method
    P = Procedure

SPECIFICNAMEVARCHAR(128)程序实例的名称（可以指定，也可以由系统自动生成）
VALIDCHAR(1)如果存储过程依赖的一些对象被删除或修改了，该存储过程必须要被重建

    Y = SQL 存储过程是合法的
    N = SQL 存储过程是非法的
    X = SQL 存储过程是不可操作的

TEXTCLOB(1M)如果是用 SQL 编写的程序，该字段记录了其创建的 DDL
…

表 4 所示的 SYSCAT.ROUTINEDEP 说明了 DB2 UDF与其他对象的依赖关系。

表 4. SYSCAT.ROUTINEDEP 视图的说明
列名数据类型描述
ROUTINESCHEMAVARCHAR(128)依赖于其他对象的 DB2 程序的 schema
ROUTINENAMEVARCHAR(128)依赖于其他对象的 DB2 程序的名称
BTYPECHAR(1)依赖对象的类型：

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMAVARCHAR(128)被依赖的对象的 schema
BNAMEVARCHAR(128)被依赖的对象的名称

表 5 所示的 SYSCAT.COLUMNS 说明了表或视图的每一个列的信息。

表 5. SYSCAT.COLUMNS 视图的说明
列名数据类型描述
TABSCHEMAVARCHAR(128)表或视图的 Schema
TABNAMEVARCHAR(128)表或视图名称
COLNAMEVARCHAR(128)列名称
KEYSEQSMALLINT记录列在其表的主键的位置

表 6 所示的 SYSCAT.PACKAGEDEP 说明了 Pachage 与其他数据库对象的依赖关系。

表 6. SYSCAT.PACKAGEDEP 视图的说明
列名数据类型描述
PKGSCHEMAVARCHAR(128)Package 的 schema
PKGNAMEVARCHAR(18)Package 的名称
BTYPECHAR(1)依赖对象的类型：

    A = Alias
    B = Trigger
    I = Index
    S = Materialized query table
    T = Table
    V = View

BSCHEMAVARCHAR(128)被依赖的对象的 schema
BNAMEVARCHAR(128)被依赖的对象的名称

表 7 所示的 SYSCAT.TABDEP 说明了视图或者固化视图和 DB2 对象之间的依赖关系。

表 7. SYSCAT.TABDEP 视图的说明
列名数据类型描述
TABSCHEMAVARCHAR(128)视图或者固化视图的 schema
TABNAMEVARCHAR(128)视图或者固化视图的名称
DTYPECHAR(1)视图的类型：

    S = Materialized query table
    V = View (untyped)
    W = Typed view

BTYPECHAR(1)依赖对象的类型：

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMAVARCHAR(128)被依赖的对象的 schema
BNAMEVARCHAR(128)被依赖的对象的名称

回页首

使用脚本进行存储过程开发

在 DB2 开发过程中我们经常使用的操作是：

    连接数据库；
    在不同的数据库中执行编写好的 SQL 文件，对创建的数据库对象进行简单测试；
    当我们编写存储过程或者 UDF 的时候，我们需要查看其依赖的数据库表或视图的定义；
    修改表结构的时候，我们需要获得所有依赖这个表的存储过程和 UDF，防止因为表结构变化，使得相关的存储过程和 UDF 不可用。

以上操作我们可以在 CLP 下通过调用 DB2 提供的命令或者通过 SELECT 语句从 DB2 系统表中查出相关信息来完成。我们把这些操作编写为如清单 1 所示的脚本。

清单 1. conn.bat ――使用脚本连接数据库

                1. @echo off 2. if {%1}=={} goto noparms  3. if {%1}=={dev} goto dev 4. if {%1}=={test} goto test 5. if {%1}=={prod} goto prod 6. if {%1}=={off} goto terminate 7. goto dev  8. :noparms  9. echo Usage: conn db2type  10. echo example: conn dev  11. echo db2type -- dev, test, prod, off 12. goto end   13. :dev 14. db2 connect to db2dev user username using password 15. PROMPT $P(%1)$G 16. goto end  17. :test 18. db2 connect to db2test user username using password 19. PROMPT $P(%1)$G 20. goto end  21. :prod 22. db2 connect to db2prod user username using password 23. PROMPT $P(%1)$G 24. goto end  25. :terminate 26. db2 terminate 27. PROMPT $P$G 28. goto end   29. :end


每次连接数据库我们只需要键入 conn dev/test 就可以完成数据库的连接，简化了命令输入，避免输入错误的用户和密码。同时这个脚本也可被其他的脚本调用。

第 2 个批处理脚本是如清单 2 所示的执行 SQL 文件的脚本。使用方法是 esql test.sql [logs\out.log]。

清单 2. esql.bat――执行SQL文件

                1. @echo off 2. if {%2} =={} goto setlog 3. set LogFile=%2 4. goto exe 5. rem 如果没有指定输出文件，设置默认输出文件 6. :setlog 7. set LogFile=logs\run.log 8.  9. :exe 10. echo %LogFile% 11. echo --------------------- execute file:%1-------------------- >> %LogFile% 12. db2 values(current time) >> %LogFile% 13. db2 -td@ -f %1 -z %LogFile% 14. db2 values(current time) >> %LogFile% 15. pause


其中 –td@ 选项表示 SQL 文件中各 SQL 语句间的分割符为 @。–z 选项可以使 sql 的执行结果输出到指定的 Log 文件中。这里我们使用 -z 而不使用脚本中的管道符号（>）是因为 -z 不仅可以把执行结果记录到指定的文件中，同时还会在控制台上显示，这样即方便我们在执行 sql 的时候实时掌握 sql 运行情况，又可以在 log 中查找分析 sql 执行情况。

该脚本需要两个参数，第一个参数指明要执行的 sql 文件，第二个参数指明 log 文件名称,该参数可选。在脚本的第 12 行，第 14 行，我们使用 values(current time) 来记录脚本执行的开始时间和结束时间。这样可以帮助我们计算出脚本执行完毕需要多长时间。

第 3 个批处理脚本的作用是获得表定义,包括字段属性、主键、前 20 条记录，如果是视图，显示其创建语句，并显示视图中包含的表。如清单 3 所示。它的使用方法是 viewtable schema tablename。

清单 3. viewtable.bat ――获得表定义

                1. @echo off 2. mkdir logs 3. set logfile=logs\view_%2.txt 4. db2 describe table %1.%2 show detail >%logfile% 5. echo  -------below is (are) pk-----------   >>%logfile% 6. db2 "select substr(colname,1,30) as pk from syscat.columns  where keyseq is not null and tabschema=upper('%1') and tabname = upper('%2')" >>%logfile% 7. echo  -----below are the fisrt 20 rows in the %1.%2 table----   >>%logfile% 8. db2 select * from %1.%2 fetch first 20 rows  only >>%logfile% 9. echo  ---------below is the sql of the %1.%2 view ----   >>%logfile% 10. db2 "select varchar(text,32670) from syscat.views  where viewschema=upper('%1')and  viewname = upper('%2')" >>%logfile% 11. echo  -----below are tables  of the %1.%2 view ----   >>%logfile% 12. db2 "select distinct bname from syscat.viewdep  where viewname= upper('%1')  and btype='T'" >>%logfile% 13. start %logfile%


脚本中第 4 行的 describe 命令用于显示数据库表或视图的结构。包括字段名称，数据类型等。第 6 行用于查出表中的主键。视图 syscat.columns 中的 keyseq 字段表明了该字段在表的主键的顺序，为空表示该字段不是表的主键。第 8 行是获得表或视图中的前 20 条记录，便于我们对其分析。第 10 行是获得视图的 SQL 定义。第 12 行获得视图中依赖的表。

第 4 个批处理脚本的作用是查看存储过程或 UDF 的定义。如清单 4 所示。它的使用方法是 view spname。

清单 4. view.bat ――查看存储过程或 UDF 的定义

                1. @echo off 2. set logfile=logs\view_%1.sql 3. db2 select varchar(text,32670)  from syscat.routines  where routinename=upper('%1') > %logfile% 4. echo  -----------------------below are tables used by %1 ---------  >>%logfile% 5. db2 select bname from syscat.packagedep  where (btype='T' or btype='V') and pkgname in (select bname from sysibm.sysdependencies where dname =upper('%1')) >> %logfile% 6.  7. start %logfile%


在脚本中我们之所以使用 VARCHAR 函数来格式 TEXT 字段中的内容，是因为 TEXT 字段是 CLOB 类型，其存储的存储过程或 UDF 的创建语句的字符比较多。如果我们不使用 VARCHAR 函数进行格式化，DB2 就会使用默认的 CHAR 函数（CHAR 的最大长度是 254）对其格式化，这样返回的 SQL 创建语句就可能被截去后面的部分。

第 5 行中脚本首先获得依赖某存储过程的所有的包（package）名，然后查找依赖这些包的所有的表和视图。这样就可以获得存储过程中使用的表和视图。

第 4 个批处理脚本的作用是使用脚本获得依赖某个表的所有存储过程，UDF 和视图。如清单 5 所示。它的使用方法是 dep schema spname。

清单 5. dep.bat ――使用脚本获得依赖某个表的所有存储过程，UDF 和视图

                1. @echo off 2. mkdir logs 3. set logfile=logs\%2.dep.txt 4. echo --- dependent SPs --- >> %logfile% 5. db2 "select r.routineschema, r.routinename  from syscat.routines r,  syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1')  and pdep.pkgname = 'p'||substr(char(r.lib_id+10000000),2) " >> %logfile% 6. echo --- dependent UDF --- >> %logfile% 7. db2 select routineschema, routinename from syscat.routinedep  where bschema = upper('%1')  and bname = upper('%2') and btype ='T'  order by bname >> %logfile% 8. echo --- dependent view --- >> %logfile% 9. db2 select viewschema, viewname from syscat.viewdep  where bschema = upper('%1')  and bname = upper('%2') and btype ='T'  order by bname >> %logfile% 10. start %logfile%


脚本第 5 行是获得依赖某表的存储过程。因为存储过程会被编译成包（package）。所以，存储过程和表的依赖关系实际上是存储过程对应的包（package）和表之间的依赖关系。这种关系被记录在 syscat.packagedep 视图中。同样的，我们可以通过查询 syscat.routinedep 和 syscat.viewdep 视图，获得 UDF 和表，视图和表之间的依赖关系。

回页首

开发实例

使用上面的脚本，进行数据库开发就会很方便。下面我们看一个开发的例子。

需求描述

由于业务变更，我们的《订单管理系统》中的表 MIS.ORDER 需要加一个字段 GOV_FLAG SMALLINT 来标识是否是政府订单，如果是则需要执行特殊的折扣政策。同时需要去除冗余字段 ORDER_PRICE_TOT DECIMAL(19,4)。

需求描述

开发过程

我们需要修改相应的表，存储过程和 UDF 的 SQL 文件，并把它们重新装载到开发数据库和集成测试数据库中。并需要保留表中原有数据。

假设我们有两个数据库，一个是名为 dev 的开发数据库，一个是名为 test 的集成测试数据库。只有项目组长拥有在 test 数据库上执行的权限，开发人员只可以在 dev 数据库上执行操作。

首先项目组长会分析需求，制定任务分配；然后开发人员根据任务分配编写相应的 SQL 和脚本文件，并且在 dev 数据库上进行装载和测试；最后项目组长把开发人员提交的脚本文件汇总起来，在 test 数据库上进行装载和测试。

1. 项目组长

项目组长需要把这个任务分配给组员去完成。首先组长使用脚本 viewtable MIS ORDER 查看一下当前在数据库中表的结构，检查添加的字段是否会和其他字段有冲突。然后项目组长使用脚本 dep MIS ORDER 获得依赖表 MIS.ORDER 的所有 DB2 对象。经过分析这些依赖对象，项目组长发现，表结构的修改会导致存储过程 MIS.SP_ADD_ORDER、UDF MIS.F_CHECK_ORDER 和视图 MIS.V_ORDER 的修改，并会导致两个存储过程需要 rebind。分析结果汇总如下表 8 所示：

表 8. 项目组长的分析结果
名称执行操作备注
MIS.SP_ADD_ORDER修改
MIS.SP_UPDATE_ORDERrebind没有使用要删除的字段，不必修改
MIS.SP_ADD_ORDERrebind没有使用要删除的字段，不必修改
MIS.F_CHECK_ORDER修改
MIS.V_ORDER修改

于是项目组长进行如表 9 的任务分配。

表 9. 项目组长制定的任务分配
编号任务执行人前提条件完成标志
10修改 ORDER.SQL，在 MIS.ORDER 中：

    添加一个字段 GOV_FLAG SMALLINT；
    去除冗余字段 ORDER_PRICE_TOT DECIMAL(19,4)；
    备注：在删除原表前，需要首先删除依赖该表的 UDF 和视图。创建新表后，需要把除了 MIS.F_CHECK_ORDER 和 MIS.V_ORDER 以外的其他的 UDF 等重新装载到数据库中，并且 rebind 存储过程 MIS.SP_UPDATE_ORDER 和 MIS.SP_SELECT_ORDER。

A无

    项目组长检查完毕 SQL 脚本；
    在开发数据库实例 db2dev 上创建和测试成功。

20根据新的 MIS.ORDER 表结构修改储存过程: SP_ADD_ORDER.SQLB任务 10 完成

    项目组长检查完毕 SQL 脚本；
    在开发数据库实例 db2dev 上创建和测试成功。

30

    根据 MIS.ORDER 表结构修改 UDF F_CHECK_ORDER.SQL 和视图 V_ORDER.SQL
    备注：请先删除依赖它们的 DB2 对象，并重新装载它们，使它们的状态合法。

C任务 10 完成

    项目组长检查完毕 SQL 脚本；
    在开发数据库实例 db2dev 上创建和测试成功。


2. 开发人员 A

A 接到任务后，从 CVS 中获得表创建 SQL 文件 ORDER.SQL，根据任务要求修改了字段 GOV_FLAG 和 ORDER_PRICE_TOT。下面 A 需要把它装载到数据库中。在重新创建表 MIS.ORDER 前，我们需要先做一些准备工作：

    备份表 MIS.ORDER 中的数据；
    获得依赖表 MIS.ORDER 所有 UDF，备份这些UDF；
    获得依赖表 MIS.ORDER 的所有存储过程，以便在重新创建表后，对它们进行 rebind。

A 首先编写了备份表的 SQL 文件 BACKUP_MIS_ORDER.SQL；然后编写恢复数据的 SQL 文件 RESTORE_MIS_ORDER.SQL；执行 dep MIS ORDER 获得依赖该表的 UDF 和存储过程。编写 DROP_UDF_4_ORDER.SQL，RELOAD_UDF_4_ORDER.SQL 和 REBIND_SP_4_ORDER.SQL。如果读者不熟悉如何获得和 rebind 非法的存储过程，可以参考 DB2 存储过程开发最佳实践。

当所有的 SQL 文件编写完后，A 开始编写执行脚本，如清单 6 所示：

清单 6. 开发人员 A 的执行脚本

                rem 连接开发数据库 dev conn dev rem 备份表 esql BACKUP_MIS_ORDER.SQL rem 删除依赖表的 UDF esql DROP_UDF_4_ORDER.SQL rem 重建表 esql ORDER.SQL rem 恢复表数据 esql RESTORE_MIS_ORDER.SQL rem 恢复 UDF esql RELOAD_UDF_4_ORDER.SQL rem rebind 相关存储过程 esql REBIND_SP_4_ORDER.SQL rem 断开数据库连接 conn off


通过上面的脚本，A 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

3. 开发人员 B

B 接到任务后，从 CVS 中得到 SP_ADD_ORDER.SQL。 为了避免其他人也在修改这个存储过程，B 使用脚本 view SP_ADD_ORDER 命令获得了当前数据库中的存储过程脚本，然后和 CVS 上的进行比较。当确认获得了最新的 SP_ADD_ORDER.SQL 后，B 开始修改 SP_ADD_ORDER.SQL。修改完毕以后，B 又修改了针对该存储过程的测试脚本 TEST_SP_ADD_ORDER.SQL。所有的 SQL 修改完，并且被检查通过后，B 开始编写脚本把存储过程装载到数据库中。脚本内容大致如清单 7 所示。

清单 7. 开发人员 B 的执行脚本

                rem 连接开发数据库 conn dev rem 重建修改过的存储过程 esql SP_ADD_ORDER.SQL rem 对重建的存储过程进行测试 esql TEST_SP_ADD_ORDER.SQL conn off


通过上面的脚本，B 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

4. 开发人员 C

C 同样使用 dep MIS F_CHECK_ORDER 和 dep MIS V_ORDER 获得依赖它们的对象，发现只是 F_CHECK_ORDER 有一些依赖对象需要重建。所以 C 编写了下面的 SQL 文件：DROP_UDF_4_MIS_F_CHECK_ORDER.SQL 和 RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL。下面是 Windows 脚本，如清单 8 所示：

清单 8. 开发人员 C 的执行脚本

                rem 连接开发数据库 conn dev rem 删除 F_CHECK_ORDER 的依赖对象 esql DROP_UDF_4_MIS_F_CHECK_ORDER.SQL rem 重建修改的 UDF 和视图 esql V_ORDER.SQL esql F_CHECK_ORDER.SQL rem 恢复依赖对象 esql RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL rem 测试重建的 UDF 和视图 esql TEST_V_ORDER.SQL esql TEST_F_CHECK_ORDER.SQL conn off


通过上面的脚本，C 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

5. 项目组长

项目组长把 A、B、C 提交上的 Windows 脚本合并成一个脚本，并改为连接集成测试数据库，把这些存储过程，UDF 等的改变装载到了集成开发测试数据库中。脚本如清单 9 所示：

清单 9. 合并执行脚本

                conn test esql BACKUP_MIS_ORDER.SQL … esql TEST_V_ORDER.SQL … conn off


这样，一个数据库对象的修改就完成了。在实际开发中的情况可能要更复杂一些。但是通过这个例子我们可以看到，通过使用 Windows 脚本，我们可以节省一些重复性的工作并且提高了工作的准确性。当整个团队熟悉了这些脚本，工作效率的提高是相当可观的。

回页首

总结

对脚本的灵活快捷特性以及 DB2 系统表含义的理解，是编写出文中脚本的根本。利用脚本和 DB2 系统表的方式和方法远比本文描述的要丰富得多。有兴趣的读者可以加深对它们的研究。相信会对大家的日常工作有所帮助。