使用 Informix 系统目录

最新推荐文章于 2021-03-25 08:56:21 发布

xinhao2008

最新推荐文章于 2021-03-25 08:56:21 发布

阅读量393

点赞数

分类专栏： Informix 文章标签： informix 数据库 statistics 引擎架构设计数据仓库

Informix 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

使用 Informix 系统目录

Jack Parker, 系统架构设计师, Arten Technology Group

2003 年 5 月 01 日

本文描述了如何使用 Informix 系统目录表来收集关于表、视图、列、索引、权限、约束和 DBA 感兴趣的任何其它细节的信息。本文包含样本代码。

重要：在阅读本文之前，请先阅读免责声明。

简介
随着数据仓库的出现及其对有关数据信息的需求，人们对元数据投入了极大的关注。其中大部分对元数据的处理都涉及了外部资源库，但许多人忘记了（或并未意识到）任何 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"）是本地表的名称，第二个 tabname（syssyntable 中的 "remote_tab"）是这个同义词所引用的表的名称。如果基本表位于同一实例或数据库上，则 server 和 dbname 将是空白。

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

SELECT tabname, colname

FROM systables a, syscolumns b

WHERE a.tabid = b.tabid;

索引
我们可能还想研究一下索引。 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