DB2_ 如何从系统编目中获得有用的信息

最新推荐文章于 2022-05-27 12:55:33 发布

Coffeemy

最新推荐文章于 2022-05-27 12:55:33 发布

阅读量1.5k

点赞数

简介

我们所知道的所有关于数据库的信息几乎都可以在称作 系统编目的元数据存储库中找到。元数据是关于数据库中数据的信息。元数据与数据本身是分开来维护的。系统编目描述数据的逻辑和物理结构。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 编目表，您应该记得，这个表是用来存储与主键、惟一性或外键约束涉及的列有关的信息。下面是这个视图的定义：

清单 1. SYSCAT.KEYCOLUSE 编目视图的定义

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 这两个编目表。下面是这个视图的定义：

清单 2. SYSCAT.STATEMENTS 编目视图的定义

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 模式中所有编目视图的一个列表：

清单 3. 识别 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）。

图 1. 在对象树中选中 Views 时，与 DB2 Control Center 的内容面板中列出特定数据库相关联的视图。这个列表已经在模式 SYSCAT 上进行了过滤

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

图 2. Open View 窗口为查看视图的数据提供了一种方便方法

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

图 3. 通过 Alter View 窗口可以查看视图定义

检索授权数据

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

清单 4. 识别拥有特权的授权 ID

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.

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

清单 5. 检索授权数据

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

检索数据类型和例程数据

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

清单 6. 检索数据类型和例程数据

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、惟一、主键、外键和表检查约束的信息。例如：

清单 7. 检索约束数据

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

检索依赖数据

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

清单 8. 检索依赖数据

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）是一样的。

图 4. 通过 Show Related 窗口可以查看视图与其他对象之间的初级依赖关系。在这种情况下，我们看到 SYSCAT.COLUMNS 视图所依赖的一组编目表

图 5. Show SQL 窗口提供了对图 4 中显示的一组依赖关系的底层查询

检索存储管理数据

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

清单 9. 检索存储管理数据

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.

检索数据库分区数据

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

清单 10. 检索数据库分区数据

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 权限。

图 6. 通过 View Privileges 窗口可以查看或更改所拥有的视图上的权限

考虑一个包含每个表的名称的视图，并且用户的授权 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 权限。

清单 11. 创建和使用基于 SYSCAT.TABAUTH 系统编目视图的视图

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 很方便地访问这些视图。本文提供了各种有用的查询示例，但我们只是略微涉及一点皮毛而已。我鼓励您进一步探索编目，以便加深对什么是可用信息以及如何检索所需信息的理解。