使用 Informix 系统目录(1)

使用 Informix 系统目录(1)

随着数据仓库的出现及其对有关数据信息的需求，人们对元数据投入了极大的关注。其中大部分对元数据的处理都涉及了外部资源库，但许多人忘记了或并未意识到）任何 Informix® SQL 数据库的系统目录中都已经存在着实实在在的元数据。此外，系统目录知识也是数据库日常维护的非常有用的工具。

最近，在考虑这一问题时，我意识到我还没有阅读过能告诉我利用这些目录做什么的文章。因此，我决定写一篇这样的文章。尽管我不能说本文胜过了 SQL 参考手册第 2 章）中的优秀文档，但我也许可以提供一些关于如何以有用的方式从这些目录中汲取信息的深入见解。阅读本文的正确方法是，翻开 SQL 参考手册并把一只手放上键盘，准备尝试示例。

本文不是对该主题的详尽讨论。有些目录表不太重要，而另一些表则是描述以某种形式过程、触发器和视图）放置到系统中的代码；dbschema 是用于抽取这些信息的较好的工具。

决不要手工地将数据更新或插入到系统目录中。应该总是使用适当的 SQL/DDL 语句执行对目录的更新。

表
让我们从简单地研究 systables表入手。正如您从文档中看到的，这个表包含了一些相当基本的信息：表名tabname）、表所有者owner）、行数nrows）、列数ncols）和索引数nindexes）等。一个合适的“hello world”类型的查询可能如下所示：

SELECT tabname FROM systables;

这个语句将向我们提供数据库中所有表名称的列表。我们将在该列表中发现 systables 本身！实际上我们并不关心目录本身，所以让我们从查询中排除它们。恰好，在创建数据库后，会重新设置用于对表标号的 tabid，因此第一个新表的 tabid 将是 100：

SELECT tabname FROM systables WHERE tabid > 99;

现在我们只看到自己创建的表的列表…… 嗯，不完全这样：我们还看到了诸如 sysmenus、sysmenuitems如果我们定义了 isql 用户菜单）和 syscolatt如果我们用 upscol 定义了数据库中任意列的表单级别属性的话）之类的表。如果我们处于复制环境中，还会看到 syscdr* 表，如果运行了刀片管理器Blade Manager）来列出或注册 DataBlade 模块，则还会看到 bld* 和 sysbld* 表。这些表都不在“小于 100”的表的列表中，因为它们是在创建数据库之后创建的。也许我们本应该这样做：

SELECT tabname 
  FROM systables 
 WHERE tabname NOT MATCHES 'sys*';

这样可以避免大多数系统目录，但会包括几个特殊的“非表”：“GL_COLLATE”、“GL_CTYPE”和“VERSION”；它们描述了我们的 NLS 设置和引擎版本。如果我们使用“sys*”前缀创建了自己的表，则这些表也将被避开，尽管我建议使用单独的数据库存储我们出于自己目的而创建的任何元数据表。为了简洁起见，我打算在所有的后续示例中略去 WHERE 子句限制表的部分。

迄今为止，我们的示例并不是十分有用的；我们所拥有的只不过是一些表名称。也许我们可以为它添加一些更实质的内容：

SELECT tabname, nrows * rowsize, npused 
  FROM systables;

该查询会向我们展示我们的表使用了多少空间，包括逻辑意义上的行数乘以行的大小；对于大小可变的行取近似值）和物理意义上的已用的页数）。在运行该查询之前请确保执行 UPDATE STATISTICS，因为 UPDATE STATISTICS 命令会更新该表中的这些值。已分配空间应该取自 sysmaster 数据库，因为该数据库中拥有已分配给所有表的空间的信息。示例：

SELECT dbsname, tabname, sum(size) total_size
  FROM sysextents
 GROUP by dbsname, tabname
 ORDER BY total_size desc;

在 stores 数据库中，items 表会显示使用了一页；sysmaster 查询会显示分配了 8 页。该表的已分配页数将取决于 Informix 页大小和在 CREATE TABLE 语句中给出的参数 EXTENT SIZE 和 NEXT SIZE。

如果我们将 tabtype 添加到所选择的表的列表，那么可能在那里发现一些反映同义词tabtype='S'）、视图'V'）甚至私有的同义词'P'）的值。通过查询 syssyntable 和 sysviews 表，我们可以找到关于这些表的更多信息如果我们使用的是 4.0 或更旧的引擎，那么我们应该查询 syssynonyms — 现在已经很少使用，但仍然存在）。例如，要列出我们数据库中的所有同义词，可以尝试下列语句：

SELECT a.tabname "local_tab", b.server, b.dbname, b.owner, b.tabname "remote_tab"
  FROM systables a, syssyntable b
 WHERE a.tabid = b.tabid;

这对于表明我们数据库中有哪些表链接到了其它的数据库和实例非常有用。第一个 tabname"local_tab"）是本地表的名称，第二个 tabnamesyssyntable 中的 "remote_tab"）是这个同义词所引用的表的名称。如果基本表位于同一实例或数据库上，则 server 和 dbname 将是空白。

列
当用于数据字典时，列名称还不是非常有用。实际上，我们想知道的是表中列的数据类型。请注意，我们只能使用 dbschema 命令，它将打印 DDL，但有时我们可能希望以不同的方式格式化该信息，或者只是想从目录直接访问该信息）幸好还有另一个表，它标识了属于该表的每个列 — 这个表就是 syscolumns；它通过名为 tabidsystables 的主键）的外键连接到 systables。

SELECT tabname, colname 
  FROM systables a, syscolumns b
 WHERE a.tabid = b.tabid;

现在，我们可以看到每个表以及属于该表的列的细分的情况。虽然很可能按照它们在该表中的出现顺序从系统目录读取，但这是无法保证的 — 因此让我们要确保按照其在该表中实际出现的顺序获取它们。

SELECT tabname, colno, colname 
  FROM systables a, syscolumns b
 WHERE a.tabid = b.tabid
 ORDER BY colno;

但是，如果我们能够列出每一列的数据类型及其长度，那就更好了。

SELECT tabname, colno, colname, coltype, collength
  FROM systables a, syscolumns b
 WHERE a.tabid = b.tabid
 ORDER BY colno;

对于 stores_demo 数据库中的 items 表，会产生如下结果：

啊。 coltype 是个数字，而 collength 有时会大得不可思议。快速地查看一下参考手册，我们将会了解一列此处可能出现的值及其含义。花更多的时间研究一下，我们还可以了解一些根据 collength 值的内容计算实际长度和数据类型精度的公式。不必通读上述所有内容，在此我建议使用一个名为 dbdiff2 的实用程序，它位于 www.iiug.org：它包含三个 4GL 函数col_cnvrt、fix_nm 和 fix_dt），这些函数以这两个值作为输入，并返回定义列的正确的 DDL。这些函数以及与它们功能等价的 awk 函数都包括在本文的附录中。

注：这些实用程序只支持 7.x 和 8.x 引擎现有的列类型。对于 9.x 的数据类型扩展，研究一下 sqltypes.h 以获取最新的数据类型列表。

索引
我们可能还想研究一下索引。 sysindexes表列出了所有这些索引；但是，要弄清楚如何反过来将索引与列联系到一起可能很棘手。该表中的 part1 列是索引中第一列的列号。唉，问题远不止这么简单：一个索引可以涉及 16 列在 SE 中为 8 列），因此我们最终将面对从 part1 到 part16 这么多列。要正确地解释这一点，我们需要一种能够连接到数据库的过程语言。我敢肯定地说，与它值得偶尔使用相比，这样带来的麻烦更多。有关获取索引信息的示例，请研究一下 dbdiff2 或 analyse_idx也位于 www.iiug.org）。

出于这个讨论的目的，我们将只用索引的头第一列）。这里是一个返回关于索引基本信息的查询。

SELECT a.tabname, b.colname, c.idxname
  FROM systables a, syscolumns b, sysindexes c
 WHERE a.tabid = b.tabid
   AND a.tabid = c.tabid
   AND b.colno = c.part1;

sysindexes 还包含一些关于索引本身有价值的信息 — 其深度、叶数、唯一值以及它是否是群集的。这些信息中的前三个信息是通过统计信息收集的，因此要确保在运行下列查询之前执行“UPDATE STATISTICS;”。

SELECT a.tabname, b.colname, c.idxname, c.idxtype,
       c.clustered, c.levels, c.leaves, c.nunique
  FROM systables a, syscolumns b, sysindexes c
 WHERE a.tabid = b.tabid
   AND a.tabid = c.tabid
   AND b.colno = c.part1

• idxtype 表明该索引是“U”唯一的）还是“D”重复的）索引。它可以反映 XPS 下的其它值，以与该引擎中可用索引的更广泛选择相匹配。
• clustered 表明该索引是否是群集索引“C”代表群集索引）
• levels 表明 B 树btree）的深度。2 层索引表明单个根／分支页和 1 个或多个叶子页 — 这些都将在“leaves”列中反映出来。
• nunique 是第一列中唯一值的数量。显然，对于唯一的单个列索引，这个值与该表的 rowcount 是相同的 — 实际上，让我们将 rowcount 添加到查询中，以便确切地了解单个列索引是否具有唯一性：

UPDATE STATISTICS;
SELECT a.tabname, b.colname, c.idxname, c.idxtype,
       c.clustered, c.levels, c.leaves, c.nunique, 
       nrows, nunique/nrows Percent_Unique
  FROM systables a, syscolumns b, sysindexes c
 WHERE a.tabid = b.tabid
   AND a.tabid = c.tabid
   AND b.colno = c.part1
   AND part2 = 0;

我们添加了 part2=0，从而仅限于对构建在单个的列上的索引进行查询）

当然，当我们拥有的是一些不包含行的表时，这个查询会失败，因为由于某个奇怪的原因，我们被禁止以零为除数，但是对于其它情况，该查询会确切地表明我们的索引是否具有唯一性。也许我们应该修改该查询并添加：

AND nrows > 0

对于索引，需要注意的一些事情是那些高度重复的索引和很小的索引。如果整个表确实小并且只占据了一页，那么读取该页并扫描它以获取我们所要的值，其开销比读取索引页然后再读取数据页以获取该值的开销要小。高度重复的索引也意味着我们必须执行许多连续的读取来获取数据页。例如，Percent_Unique 率为 .125 意味着每个索引项平均有 8 个值，这意味着每次使用该索引时可能要执行 9 次读操作！）

缺省值
sysdefaults目录保存了数据库中任何给定列的缺省值如果它们存在的话）：

SELECT a.tabname, b.colname, c.type, c.default
  FROM systables a, syscolumns b, sysdefaults c
 WHERE a.tabid = b.tabid
   AND a.tabid = c.tabid
   AND b.colno = c.colno;

这个查询将向我们提供每个表中每个缺省列的缺省值。有些类型是特殊的：“U”用户）、“C”当前）、“N”空）、“T”今天）和“S”站点名称）。

视图
尽管在数据库中查找视图很容易select tabname from systables where tabtype = 'V'），也许我们还想获取视图定义。重申一次，应该利用 dbschema 完成该工作。但是，可以用下列语句获取该信息：

SELECT viewtext, seqno 
  FROM sysviews 
 WHERE tabid = ? 
 ORDER BY seqno

我们也可能只想看看某个视图是基于哪些表的。可以使用 sysdepend表来确定：

SELECT a.tabname, b.tabname
  FROM sysdepend c, systables a, systables b
 WHERE btabid=a.tabid
   AND dtabid=b.tabid
   AND b.tabname = ?;

这个查询向我们提供了来自 sysviews 表的关键信息，而又不必解析它。

许可权
permissions 表是一组值得研究的有用的表 — 它可以向我们展示谁有权做什么。这方面的常见问题是许可权的撤销 — 如果两个用户都对第三个用户授予了许可权，而其中一人撤销了该许可权，那么第二个用户所授予的许可权仍然有效。

sysusers存储了那些在系统中拥有“public”以外许可权的用户的基本用户标识“public”许可权也是在此列出和控制的）。select * from sysusers 会向我们显示具有“D”数据库管理员）权限、“R”资源）权限和“C”连接）权限的用户。我们用 grant/revoke [to|from] user;语句授予或撤销这些许可权。

sysroleauth，sysroleauth 是这个表的子表，因此任何用户也可以成为某种角色。sysroleauth 描述了“角色”以及谁被授予了对它的访问权然后描述他们是否可以传递该许可权）。因此您可以通过角色为一系列用户设置基本许可权方案，然后将对该角色的访问权授予用户，而不是为每个用户设置对每个表的许可权。遗憾的是这不是 ANSI 标准，并且一些前端工具采用某个角色时会产生混淆。

Systabauth及其“伙伴”表 syscolauth描述了对每个用户授予了每个表以及可能的列）的什么许可权，以及能否传递该许可权。

SELECT tabname, grantor, grantee, tabauth
  FROM systables a, systabauth b
 WHERE a.tabid = b.tabid
 ORDER BY tabname

tabauth 字符串是由 8 个值组成的组合体：

• 选择Select）
• 更新Update）
• 现有的列级别许可权我们研究的案例中是 syscolauth）
• 插入Insert）
• 删除Delete）
• 索引Index）
• 更改Alter）
• 引用Reference）

如果该值是大写的，则正在讨论的用户Joe）有权对第三个用户发出授权语句这是 GRANT ... permission ... WITH GRANT OPTION;的结果）。Joe 不能将 SELECT 许可权授予 John，因为是 John 授予 Joe 该许可权的。在这种情况下，如果用户 Informix 撤销了 Joe 的 SELECT 许可权，Joe 仍然被允许从表foo）进行选择操作，因为 John 也授予了他 SELECT 许可权。在这种情况下，完全撤销 Joe 的 SELECT 许可权的唯一方法是：以 John 的身份登录并撤销该许可权，或者使用 CASCADE撤销 John 的这项许可权。使用更加新的 9.4 发行版，您可以：

 REVOKE  ON 

SELECT a.tabname, b.colname, c.grantor, c.grantee, c.colauth
  FROM systables a, syscolumns b, outer syscolauth c
 WHERE a.tabid = c.tabid
   AND a.tabid = b.tabid
   AND c.colno = b.colno
 ORDER BY tabname