DB2 基础: 如何从 DB2 UDB 系统编目中获得有用的信息

简介： IBM? DB2? Universal Database? (UDB) 维护着一组特殊的称作系统编目的表。这些表包含描述数据库对象的元数据，并且在这些表上定义有两组视图：SYSCAT 模式下的只读视图和 SYSSTAT 模式下的可更新视图。本文将介绍 DB2 UDB 系统编目，展示如何挖掘这个丰富的数据库信息仓库，获得有用的信息。文中还将提供关于如何查询编目（使用命令行或 DB2 Control Center）的例子。

发布日期： 2004 年 12 月 01 日
级别： 初级
访问情况 138 次浏览
建议: 0 (添加评论)

简介

我们所知道的所有关于数据库的信息几乎都可以在称作 系统编目的元数据存储库中找到。元数据是 关于数据库中数据的信息。元数据与数据本身是分开来维护的。系统编目描述数据的逻辑和物理结构。DB2 UDB 系统编目（或简称为“编目”）由很多表和视图组成，这些表和视图由数据库管理器来维护。在创建一个数据库时，也会顺带创建一组编目表和视图。这些编目描述了数据库对象，例如表、列和索引，并包含关于用户所拥有的访问这些对象的访问类型的信息。将数据库对象和特权添加到数据库中时，编目表也会随之增长。在创建、修改或删除一个对象时，数据库管理器会插入、更新或删除编目表中描述该对象以及描述该对象与其他对象之间的关系的行。

当然，不能显式地创建或删除这些系统编目表，但是可以查询和显示这些表的内容。这些视图中收藏了丰富的有用信息，其中包括可以帮助您理解数据库如何运行的信息。在本文中，我将通过实际的例子展示如何从 DB2 UDB 系统编目检索有用的信息。

回页首

系统编目基表（SYSIBM 模式）

编目表是在 SYSIBM 模式下创建的，存储在 SYSCATSPACE 表空间中。分区数据库的编目表只存储在发出 CREATE DATABASE 命令时所在的那个分区上。有些编目表还存在父子关系。例如，SYSIBM.SYSCOLAUTH 包含列级权限的详细信息，它是 SYSIBM.SYSTABAUTH 的子表，后者记录了表级权限。

很多组成系统编目的表存储了关于数据库对象的信息，这些表的名称标识了它们所描述的对象。例如 SYSINDEXES、SYSTRIGGERS 和 SYSVIEWS。根据所存储数据的类型，可以将编目表分成很多类。例如：

• 授权（Authorization） 数据存储在诸如 SYSDBAUTH（用于数据库级的权限）、SYSTBSPACEAUTH（用于表空间上的权限）、SYSTABAUTH（用于表和视图上的权限）以及 SYSCOLAUTH（用于列级权限）之类的表中。
• 数据类型和例程 数据存储在诸如 SYSDATATYPES（用于内置数据类型和用户定义数据类型）、SYSROUTINES（用于函数或过程） 和 SYSROUTINEPARMS（用于在 SYSROUTINES 中列出的例程的参数）之类的表中。
• 约束（Constraint） 数据存储在诸如 SYSCHECKS（用于检查约束）、SYSRELS（用于外键约束）和 SYSKEYCOLUSE（用于参与主键、惟一性或外键约束的列）之类的表中。关于约束的更多信息，请参阅 DB2 基础：约束。
• 依赖性（Dependency） 数据存储在诸如 SYSCONSTDEP（对其他某种对象上的约束的依赖性）、SYSDEPENDENCIES（对其他某种对象上的触发器、函数、索引或索引扩展的依赖性）和 SYSVIEWDEP（对其他某种对象上的视图的依赖性）之类的表中。
• 存储管理（Storage management） 数据存储在诸如 SYSTABLESPACES（用于表空间）和 SYSTABLES（用于与某个特定表关联的表空间）之类的表中。关于表空间 的更多信息，请参阅 DB2 基础: 阐明表和表空间的状态。
• 数据库分区（Database partition） 数据存储在诸如 SYSNODEGROUPS（用于数据库分区组）和 SYSPARTITIONMAPS（用于将散列键值与数据库分区关联的分区映射）之类的表中。

数据库管理器创建和维护两组系统编目视图，这些视图是在基本系统编目表之上定义的。其中一组只读视图在 SYSCAT 模式下创建，另一组更小的可更新视图则是在 SYSSTAT 模式下创建的。一个编目视图可以基于一个或多个编目表，并且视图中的列名常常与它们在编目表中对应的列不同。为了更好地理解这一点，让我们看两个例子。其中一个例子是一个简单的只基于一个表的视图，即 SYSCAT.KEYCOLUSE。该视图基于 SYSIBM.SYSKEYCOLUSE 编目表，您应该记得，这个表是用来存储与主键、惟一性或外键约束涉及的列有关的信息。下面是这个视图的定义：

create view syscat.keycoluse (constname, tabschema, tabname, colname, colseq)                as select constname, tbcreator, tbname, colname, colseq                from                sysibm.syskeycoluse                

在这种情况下，所有 5 个表列都出现在视图中，但是其中有些列名有所不同。这样做只是为了一致性和清晰。

另一个例子是一个更复杂的基于多个表的视图，即 SYSCAT.STATEMENTS。该视图包含与数据库中每个包中的每条 SQL 语句有关的信息，它基于 SYSIBM.SYSPLAN 和 SYSIBM.SYSSTMT 这两个编目表。下面是这个视图的定义：

create view syscat.statements (pkgschema, pkgname, unique_id, version,                stmtno, sectno, seqno, text)                as select s.plcreator, s.plname, s.unique_id,                (select p.pkgversion from                sysibm.sysplan p                where s.plcreator = p.creator                and s.plname = p.name                and s.unique_id = p.unique_id),                s.stmtno, s.sectno, 1, s.text                from                sysibm.sysstmt s                

回页首

面向用户的编目视图（SYSCAT 模式）

SYSCAT 模式包含编目表的有用只读视图。SYSCAT 中所有视图上的 SELECT 特权都是 PUBLIC，可以只通过这些视图以及 SYSSTAT 中的视图（后面会更详细地谈到）与系统编目进行交互。

下面的代码将建立到 SAMPLE 数据库的连接，并返回 SYSCAT 模式中所有编目视图的一个列表：

connect to sample                list tables for schema syscat                or, alternatively:                select tabname from syscat.tables where tabschema = 'SYSCAT'                connect reset                

对于需要知道可用视图中的列，以及这些视图本身的名称和用途，以构造对编目视图更有意义的查询的用户， IBM DB2 Universal Database SQL Reference, Volume 1 中描述了 DB2 UDB 系统编目视图，或者，如果您更喜欢在线信息，那么还可以在 DB2 Information Center（依次访问 Reference → SQL → catalog views → DB2 Universal Database)找到相同的内容。您也可以在 DB2 Control Center 中研究编目视图（参见图 1）。

通过 Open View 窗口可以查看视图的详细内容，通过它可以很方便地浏览视图列（图 2）。

如果您感兴趣的话，甚至还可以访问视图定义本身，方法是从弹出式菜单（图 3）选中 Alter。

检索授权数据

如前所述，没有哪个单独的系统编目视图可以包含所有可用的授权数据。虽然关于授权和权限的元数据包含在多个编目视图中，但是可以构造一条查询，检索所有拥有特权的授权 ID。我们将利用 UNION 操作来完成查询任务，并（在这里和在其他例子中）使用 SUBSTR 内置函数来帮助格式化结果集：

select distinct substr(grantee,1,16) as grantee, granteetype, 'Database'                from syscat.dbauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, '表空间'                from syscat.tbspaceauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Schema'                from syscat.schemaauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Table'                from syscat.tabauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Index'                from syscat.indexauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Column'                from syscat.colauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Package'                from syscat.packageauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Routine'                from syscat.routineauth                union                select distinct substr(grantee,1,16) as grantee, granteetype, 'Server'                from syscat.passthruauth                order by grantee, granteetype                GRANTEE          GRANTEETYPE 3                ---------------- ----------- -----------                MELNYK           U           Database                MELNYK           U           Index                MELNYK           U           Package                MELNYK           U           Table                MELNYK           U           表空间                PUBLIC           G           Database                PUBLIC           G           Package                PUBLIC           G           Routine                PUBLIC           G           Schema                PUBLIC           G           Table                PUBLIC           G           表空间                11 record(s) selected.                

可以构造很多其他的查询来检索授权数据。下面是另外两个例子：

connect to sample                检索所有被显式地授予 DBADM 权限的授权名称：                 select distinct grantee from syscat.dbauth                where dbadmauth = 'Y'                检索授给其他用户的一组表权限：                select * from syscat.tabauth where grantor = user                connect reset                

检索数据类型和例程数据

可以使用系统编目视图来轻松地检索关于用户定义数据类型或例程的信息。例如：

connect to sample                检索关于所有用户定义类型（即不属于 SYSIBM 模式的类型）的信息：                select typeschema, typename, sourcename, metatype from syscat.datatypes                where typeschema != 'SYSIBM'                检索关于所有用户定义例程（即所属模式的名称不是以 SQL 或 SYS 开头的例程）的信息：                select routineschema, routinename, routinetype, origin, language, text                from syscat.routines                where substr(routineschema,1,3) != 'SQL' and substr(routineschema,1,3) != 'SYS'                connect reset                

检索约束数据

也可以使用系统编目视图来检索关于 NOT NULL、惟一、主键、外键和表检查约束的信息。例如：

connect to sample                检索由用户 MELNYK 创建的表中所有不能为空的一组列：                select substr(tabname,1,16) as tabname, substr(colname,1,16) as colname, nulls                from syscat.columns                where tabschema = 'MELNYK' and nulls = 'N'                检索其上定义了惟一性约束（不是主键约束）的一组表：                "select substr(tabschema,1,16) as tabschema, substr(tabname,1,16) as tabname, keyunique                from syscat.tables                where keyunique > 0"                检索所有为 SAMPLE 数据库定义的主键。如果一个列是它所属表的主键的一部分，                那么 KEYSEQ 包含一个非空的值。该值表示这个列在主键中所处的以数字表示的位置：                select substr(tabschema, 1, 16) as tabschema, substr(tabname, 1, 16) as tabname,                substr(colname, 1, 16) as colname, keyseq                from syscat.columns                where keyseq is not null                检索参与由惟一性、主键或外键约束定义的一个键的所有列。识别约束名和类型，                以及该列在键中所处的以数字表示的位置（起始位置是 1）：                select substr(k.constname,1,20) as constname, t.type, substr(k.tabname,1,20) as tabname,                substr(k.colname,1,16) as colname, k.colseq                from syscat.keycoluse k, syscat.tabconst t                where k.constname = t.constname                检索每个表检查约束：                select substr(constname,1,20) as constname, substr(tabname,1,20) as tabname,                substr(text,1,32) as text                from syscat.checks                connect reset                

检索依赖数据

可以使用系统编目视图来检索关于对象相互之间依赖关系的信息。

connect to sample                检索 SYSCAT.COLUMNS 视图所依赖的所有表：                select distinct substr(a.tabschema,1,16) as tabschema,                substr(a.tabname,1,16) as tabname, a.type, substr(a.tbspace,1,16) as tbspace                from syscat.tables a, syscat.viewdep b                where a.type = 'T'                and a.tabname = b.bname                and a.tabschema = b.bschema                and b.btype = 'T'                and b.viewname = 'COLUMNS'                and b.viewschema = 'SYSCAT'                connect reset                TABSCHEMA        TABNAME          TYPE TBSPACE                ---------------- ---------------- ---- ----------------                SYSIBM           SYSCHECKS        T    SYSCATSPACE                SYSIBM           SYSCOLCHECKS     T    SYSCATSPACE                SYSIBM           SYSCOLPROPERTIES T    SYSCATSPACE                SYSIBM           SYSCOLUMNS       T    SYSCATSPACE                4 record(s) selected.                

该查询实际上与您选择 SYSCAT.COLUMNS 视图的 Show Related 动作时由 DB2 Control Center 生成的底层查询（图 4 和图 5）是一样的。

检索存储管理数据

可以使用系统编目视图来检索关于表空间之类的存储管理对象的信息。

connect to sample                检索关于与用户 MELNYK 创建的表相关联的表空间的信息：                select substr(t.tabname, 1, 12) as tabname, t.tbspaceid as tsp_id,                s.tbspacetype as tsp_type, s.datatype, s.extentsize, s.pagesize, s.dbpgname                from syscat.tables t, syscat.tablespaces s                where tabschema = 'MELNYK' and type = 'T' and t.tbspaceid = s.tbspaceid                connect reset                TABNAME      TSP_ID TSP_TYPE DATATYPE EXTENTSIZE  PAGESIZE    DBPGNAME                ------------ ------ -------- -------- ----------- ----------- ------------------                CL_SCHED          2 S        A                 32        4096 IBMDEFAULTGROUP                DEPARTMENT        2 S        A                 32        4096 IBMDEFAULTGROUP                EMP_ACT           2 S        A                 32        4096 IBMDEFAULTGROUP                EMP_PHOTO         2 S        A                 32        4096 IBMDEFAULTGROUP                EMP_RESUME        2 S        A                 32        4096 IBMDEFAULTGROUP                EMPLOYEE          2 S        A                 32        4096 IBMDEFAULTGROUP                IN_TRAY           2 S        A                 32        4096 IBMDEFAULTGROUP                ORG               2 S        A                 32        4096 IBMDEFAULTGROUP                PROJECT           2 S        A                 32        4096 IBMDEFAULTGROUP                SALES             2 S        A                 32        4096 IBMDEFAULTGROUP                STAFF             2 S        A                 32        4096 IBMDEFAULTGROUP                11 record(s) selected.                

检索数据库分区数据

还可以使用系统编目视图来检索关于数据库分区和数据库分区组的信息。

connect to sample                检索关于每个数据库分区和它所属的数据库分区组的信息：                select n.dbpgname, substr(n.definer, 1, 16) as definer, n.pmap_id, n.create_time,                d.dbpartitionnum, d.in_use                from syscat.dbpartitiongroups n, syscat.dbpartitiongroupdef d                where n.dbpgname = d.dbpgname                connect reset                DBPGNAME           DEFINER    PMAP_ID CREATE_TIME                DBPARTITIONNUM IN_USE                ------------------ -------... ------- -------------------------- -------------- ------                IBMCATGROUP        SYSIBM           0 2004-10-18-08.27.54.045000              0 Y                IBMDEFAULTGROUP    SYSIBM           1 2004-10-18-08.27.54.125000              0 Y                2 record(s) selected.                

回页首

可更新编目视图（SYSSTAT 模式）

SYSSTAT 模式包含少量的基于系统编目表的可更新视图。这些视图包括一些包含对查询优化器有用的统计信息的列。优化器使用特定表和索引列（如果这些列也参与了行选择或表连接）中关于数据分布的信息，它使用该信息来比较用于特定查询的不同数据访问计划的开销。

您可能对更改其中某些统计值来影响优化器、或者在开发或测试环境中调查研究数据库性能感兴趣。实际上，您可以使用 SQL UPDATE 语句来更改可更新编目视图中的统计列。如果在某个表上拥有 CONTROL 权限，那么就可以更新属于该表的值，但是如果拥有数据库上显式的 DBADM 授权的话，那么您可以更新任何可更新的列。

可以使用 runstats 实用程序来更新系统编目表中的统计信息，使查询优化过程更容易一些。建议在手动更新任何统计信息之前，先调用 RUNSTATS 命令，以便起始点能够准确地反映当前状态。

关于如何使用可更新编目视图来优化性能的讨论超出了本文的范围。您可以在 DB2 UDB 产品库中发现更多关于这一主题的信息。

回页首

控制对系统编目的访问

在创建一个数据库时，系统编目视图上的 SELECT 权限便被授给 PUBLIC（图 6）。如果数据库包含敏感信息，您可能希望限制对编目视图的访问，因为编目描述了数据库中的每一个对象。在从 PUBLIC 撤消 SELECT 权限之后，可以在必要时将该权限授给特定的用户。必须要有 DBADM 或 SYSADM 权限，来授予或撤消系统编目视图上的 SELECT 权限。

考虑一个包含每个表的名称的视图，并且用户的授权 ID 被显式地授予了这些表上的 SELECT 权限。 清单 11 中的代码就创建了这样一个名为 MYSELECTS 的视图。该视图基于另一个视图 SYSCAT.TABAUTH 编目视图。我们指定一个被授权者类型 U（用于用户；也可以是 G，用于组）和一个被授权者值 USER（引用指定运行时授权 ID 的专用寄存器）。并且还将过滤出那些 SELECT 权限标志被设为 YES 的行。

创建 MYSELECTS 视图之后，我们可以构造一条查询，从该视图和 SYSCAT.TABLES 视图中检索数据。这种同时从两个或更多个表中检索数据的查询称作连接（join）。我们的连接将从 MYSELECTS 视图检索表模式和表名，并从 SYSCAT.TABLES 视图检索相应的表类型（例如 表示视图的 V）。在这个例子中，查询返回了一行结果，这对应于刚才创建的视图。授权 ID MELNYK 只被显式地授予该表或视图上的 SELECT 权限。

最后，可以使 MYSELECTS 视图能够供每一个授权 ID 使用，并从 PUBLIC 调用基本视图（SYSCAT.TABAUTH）上的 SELECT 权限。对基本视图进行一次简单的查询，验证用户 MELNYK 和 PUBLIC 是否都拥有 MYSELECTS 视图上的 SELECT 权限。MELNYK 拥有 SELECT 权限是因为这个 ID 是视图的创建者，而 PUBLIC 拥有该权限是因为它被显式地授予了 SELECT 权限。

create view myselects as                select tabschema, tabname                from syscat.tabauth                where granteetype = 'U'                and grantee = user                and selectauth = 'Y'                select m.tabschema, m.tabname, t.type                from myselects m, syscat.tables t                where m.tabschema = t.tabschema                and m.tabname = t.tabname                TABSCHEMA       TABNAME       TYPE                ------------... ----------... ----                MELNYK          MYSELECTS     V                1 record(s) selected.                grant select on table myselects to public                revoke select on table syscat.tabauth from public                select tabschema, tabname, selectauth, grantee                from syscat.tabauth                where tabname = 'MYSELECTS'                TABSCHEMA       TABNAME       SELECTAUTH GRANTEE                ------------... ----------... ---------- -------...                MELNYK          MYSELECTS     Y          MELNYK                MELNYK          MYSELECTS     Y          PUBLIC                2 record(s) selected.                

回页首

结束语

DB2 UDB 系统编目是描述数据库对象的表的集合。DB2 UDB 提供了大量的视图，可以查询这些视图来获得有助于理解数据库工作原理的信息。您可以建立单独的查询或者脚本来完成这项任务，也可以通过 DB2 Control Center 很方便地访问这些视图。本文提供了各种有用的查询示例，但我们只是略微涉及一点皮毛而已。我鼓励您进一步探索编目，以便加深对什么是可用信息以及如何检索所需信息的理解。

参考资料

• 您可以参阅本文在 developerWorks 全球站点上的 英文原文 。
  
  
• DB2 Technical Support 是查找诸如 Version 8 Information Center 和 PDF 产品手册的理想场所。
  
  

关于作者

Roman B. Melnyk 博士是 DB2 信息开发团队的高级成员，其专长是数据库管理、DB2 实用程序以及 SQL。在 IBM 任职的 9 年多时间里，Roman 撰写了许多关于 DB2 的书籍和其他相关材料。Roman 最近与别人合著了 DB2 Version 8: The Official Guide （Prentice Hall Professional Technical Reference，2003 年）、 DB2: The Complete Reference（Osborne/McGraw-Hill，2001 年）、 DB2 Fundamentals Certification for Dummies （Hungry Minds，2001 年）和 DB2 for Dummies （IDG Books，2000 年）。