使用 Informix 系统目录

最新推荐文章于 2023-07-25 09:17:47 发布

weixin_30824277

最新推荐文章于 2023-07-25 09:17:47 发布

阅读量194

点赞数

文章标签：数据库 awk 前端 ViewUI

原文链接：http://www.cnblogs.com/sandwater/archive/2007/01/14/620032.html

版权

Jack Parker
系统架构设计师, Arten Technology Group
2003 年 5 月

本文描述了如何使用 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;

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


            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 表，会产生如下结果：

tabname	colno	colname	coltype	collength
items	1	item_num	1	2
items	2	order_num	2	4
items	3	stock_num	257	2
items	4	manu_code	256	3
items	5	quantity	1	2
items	6	total_price	8	2050

啊。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 <permission> [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

tabname	grantor	grantee	tabauth
foo	informix	joe	Su*idxar
foo	informix	john	Su------
foo	john	joe	Su------

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 <privilege> ON <object> FROM <user> AS <grantor>

要查看列许可权，我们可以：


            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

针对 stores_demo 数据库中的 sysdistrib 表运行该查询，我们将看到：

tabname	colname	grantor	grantee	colauth
Sysdistrib	tabid	informix	public	s--
Sysdistrib	colno	informix	public	s--
Sysdistrib	seqno	informix	public	s--
Sysdistrib	constructed	informix	public	s--
Sysdistrib	mode	informix	public	s--
Sysdistrib	resolution	informix	public	s--
Sysdistrib	confidence	informix	public	s--
Sysdistrib	encdat
Sysdistrib	type	informix	public	s--

public 仅被授予了选择任何列的权限。没有为 encdat 列指定任何许可权。colauth 可能会在第二个位置包含“u”（表示更新特权），并且／或者在第三个位置包含“r”（表示引用特权）。

存储过程
最好使用 dbschema 抽取存储过程。但是，在用来定义存储过程的结构中，还有一些重要的信息。

过程本身是在 sysprocedures 表中描述的。过程的主体（文本形式和已编译形式）存储在 sysprocbody表中。

因此，可以用如下语句访问存储过程的文本：


            SELECT data, seqno
            FROM sysprocbody a, sysprocedures b
            WHERE a.procid = b.procid
            AND b.procname = ?
            AND datakey = 'T'
            ORDER BY seqno

该过程的优化方案存储在 sysprocplan 中。在创建过程时创建该方案，而且每当执行 update statistics for procedure <procedure> 时则会更新该方案。因为存储过程所用的表的内容可能会随时间变化而更改，所以定期地更新这个优化方案是明智的。sysprocplan 表中有一个名为 created的列，它表明方案的最近一次更新时间。用于过程的更新统计信息例程可能会从选择过程的名称开始，这些过程的统计信息应当进行更新：


            SELECT procname
            FROM sysprocedures a, sysprocplan b
            WHERE a.procid = b.procid
            AND created < today - 7;

sysprocauth 维护着针对过程的许可权。对于确定谁有权访问某个过程，这可能很有用。请记住，过程是用创建该过程的用户的许可权运行的。

引用完整性和其它链接
引用完整性的问题之一是：在重新组织关系中的某个表之前，我们可能需要删除或禁用某个外键约束。附录中的 depend.sh 脚本将有助于我们对这方面的理解。

sysconstraints 表拥有数据库中每个约束的纪录。每种约束都有不同的类型（constrtype）：

检查（Check）

主键（Primary key）

引用（外键）（Reference，Foreign Key）

唯一性（Unique）

非空（Not Null）

通过 sysreferences 表，这些约束又将源表和引用表联系到一起（用于引用键）。


            SELECT a.tabname, constrname, d.tabname
            FROM systables a, sysconstraints b, sysreferences c,
            systables d
            WHERE b.constrtype = 'R'
            AND a.tabid = b.tabid
            AND b.constrid = c.constrid
            AND c.ptabid = d.tabid
            AND a.tabname = ?;

这会显示第一个表，以及引用约束和被引用的表。

我们可能还希望将对象状态附加到上述查询，这样就可以确定是否禁用了某个约束。此处， sysobjstate 表会帮助我们实现这一点。它按名称跟踪约束、触发器和索引。要抽取该信息，我们可以将查询更改为：


            SELECT a.tabname, constrname, d.tabname, e.state
            FROM systables a, sysconstraints b, sysreferences c,
            systables d, sysobjstate e
            WHERE b.constrtype = 'R'
            AND a.tabid = b.tabid
            AND b.constrid = c.constrid
            AND b.constrname = e.name
            AND c.ptabid = d.tabid
            AND a.tabname = ?;

syschecks表也很重要，它标识了检查约束。它有一个匹配的表 syscoldepend，该表标识了从属表和列。这里有一个有用的查询：


            SELECT a.constrname, a.constrtype, b.tabname, c.colname,
            d.checktext
            FROM sysconstraints a, systables b, syscolumns c,
            syschecks d, syscoldepend e
            WHERE a.constrid = d.constrid
            AND a.constrid = e.constrid
            AND e.tabid = b.tabid
            AND e.colno = c.colno
            AND b.tabid = c.tabid
            AND d.type = 'C'

对我们友好的 stores 数据库进行查询，产生了如下结果：

Constrname	c104_15
Constrtype	C
Tabname	items
Colname	quantity
Checktext	(quantity >= 1)

这表明对于 items 表中的 quantity 列有检查约束（quantity >=1）。约束名可以任取。

结束语
系统目录不仅对于数据仓库的元数据需求或逆向工程需求而言是有用的帮手，而且对于数据库的日常维护也是如此。还有其它几个系统目录以及 sysutil 和 sysmaster 数据库，它们恰好是另一篇文章所讨论的主题。这些目录和数据库的数量和内容都随着产品的发展而发展，尽管原始目录仍然存在着。当我们确定数据库中的元数据需求时，应该检查这些目录 — 也许我们所需要的东西已经在那里了。

附录
也可以从下列地址下载本文中所讨论代码的电子版本：

4gl 函数（dbdiff2）	http://www.iiug.org/software/index_DBA.html
analyse_idx	http://www.iiug.org/software/index_DBA.html
findcol 和 depend.sh	http://www.artentech.com/downloads.htm

这个附录中复制了这些代码，以提供进一步的示例。

清单 1. 4gl 函数


            #############################################################
            # Convert coltype/length into an SQL descriptor string
            #############################################################
            FUNCTION col_cnvrt(coltype, collength)
            DEFINE coltype, collength, NONULL SMALLINT,
            SQL_strg CHAR(40),
            tmp_strg CHAR(4)
            LET coltype = coltype + 1	  # datatype[] is offset by one
            LET NONULL  = coltype/256	  # if > 256 then is NO NULLS
            LET coltype = coltype MOD 256  # lose the NO NULLS determinator
            LET SQL_strg = datatype[coltype] # basic datatype
            CASE coltype
            WHEN 1				# char
            LET tmp_strg = collength using "<<<<"
            LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")"
            # SQL syntax supports float(n) - Informix ignores this
            # WHEN 4    			# float
            #  LET SQL_strg = SQL_strg clipped, " (", ")"
            WHEN 6    			# decimal
            LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"
            # Syntax supports serial(starting_no) - starting_no is unavaliable
            #  WHEN 7    			# serial
            #  LET SQL_strg = SQL_strg clipped, " (", ")"
            WHEN 9    			# money
            LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"
            WHEN 11 				# datetime
            LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped
            WHEN 14 				# varchar
            LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")"
            WHEN 15 				# interval
            LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped
            END CASE
            IF NONULL THEN
            LET SQL_strg = SQL_strg clipped, " NOT NULL"
            END IF
            RETURN SQL_strg
            END FUNCTION
            ##############################################################
            # Turn collength into two numbers - return as string
            ##############################################################
            FUNCTION fix_nm(num,tp)
            DEFINE num integer,
            tp smallint,
            strg CHAR(8),
            i, j   SMALLINT,
            strg1, strg2 char(3)
            LET i = num / 256
            LET j = num MOD 256
            LET strg1 = i using "<<&"
            LET strg2 = j using "<<&"
            IF tp = 0 THEN
            IF j > i THEN
            LET strg = strg1 clipped
            ELSE
            LET strg = strg1 clipped, ", ", strg2 clipped
            END IF
            ELSE		# varchar is just the opposite
            IF i = 0 THEN
            LET strg = strg2 clipped
            ELSE
            LET strg = strg2 clipped, ", ", strg1 clipped
            END IF
            END IF
            RETURN strg
            END FUNCTION
            ##############################################################
            # Turn collength into meaningful date info - return as string
            ##############################################################
            FUNCTION fix_dt(num)
            DEFINE num integer,
            i, j, k, len   SMALLINT,
            strg CHAR(30)
            LET i   = (num mod 16) + 1		# offset again
            LET j   = ((num mod 256) / 16) + 1	# offset again
            LET k   = num / 256			# length of value
            # If this is an interval then life gets interesting, 'k' is
            # the length of the entire string.  So a YEAR TO DAY is
            # YYYYMMDD or 8.  A DAY(3) TO MINUTE is DDDHHMM or 7.
            # We don't know how long the first one is, but
            # we can work it out by computing the 'should
            # be length' of the string and then adding/subtracting
            # the result from the 'should be length' of
            # the major element.
            #
            # Keep in mind --->    YYYYMMDDHHMMSSFFFFF
            #     vs.         j =    1  2 3 4 5 678901
            #
            # I was just working an algorithm to do this, 4
            # notepads, 90 minutes, and 50 lines into it I realized
            # that I was creating something impossible to test or
            # maintain.  Therefore I am opting for something a lot simpler.
            #
            # In the globals I have created an ARRAY of RECORD with
            # start and end points for the major and minor pieces.
            # By subtracting the START point of the
            # major element from the END point of the minor element
            #  I get the 'should be length'
            #
            LET len = intvray[i].end_point - intvray[j].start_point
            # len should match k.	e.g.:
            #				DAY(5) TO MINUTE  ==> k = 9, len = 6
            #				YEAR(6) TO HOUR   ==> k = 12, len = 14
            LET len = k - len 		   # add len to the major
            IF len = 0 OR j > 11 THEN   # is the default
            # 12 on have the precision already coded
            LET strg = datetype[j] clipped, " TO ", datetype[i] clipped
            ELSE				# isn't the default
            # uh-oh, how long IS the default major?
            LET k = intvray[j].end_point - intvray[j].start_point
            # add in the extra
            LET k = k + len
            LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ",
            datetype[i] clipped
            END IF
            RETURN strg
            END FUNCTION
            #############################################
            Required global definitions:
            DEFINE  datatype ARRAY[40] OF CHAR(20), # for coltype conversions
            datetype ARRAY[16] OF CHAR(11),
            intvray  ARRAY[16] OF RECORD
            start_point SMALLINT,
            end_point SMALLINT
            END RECORD
            LET datatype[1]  = "CHAR"
            LET datatype[2]  = "SMALLINT"
            LET datatype[3]  = "INTEGER"
            LET datatype[4]  = "FLOAT"
            LET datatype[5]  = "SMALLFLOAT"
            LET datatype[6]  = "DECIMAL"
            LET datatype[7]  = "SERIAL"
            LET datatype[8]  = "DATE"
            LET datatype[9]  = "MONEY"
            LET datatype[10] = "UNKNOWN"
            LET datatype[11] = "DATETIME"
            LET datatype[12] = "BYTE"
            LET datatype[13] = "TEXT"
            LET datatype[14] = "VARCHAR"
            LET datatype[15] = "INTERVAL"
            LET datatype[16] = "NCHAR"
            LET datatype[17] = "NVARCHAR"
            LET datatype[18] = "UNKNOWN"		# little room for growth
            LET datatype[19] = "UNKNOWN"
            LET datatype[20] = "UNKNOWN"
            LET datetype[1] = "YEAR"
            LET intvray[1].start_point = 1
            LET intvray[1].end_point = 5		# offset by one for easy math
            LET datetype[3] = "MONTH"
            LET intvray[3].start_point = 5
            LET intvray[3].end_point = 7
            LET datetype[5] = "DAY"
            LET intvray[5].start_point = 7
            LET intvray[5].end_point = 9
            LET datetype[7] = "HOUR"
            LET intvray[7].start_point = 9
            LET intvray[7].end_point = 11
            LET datetype[9] = "MINUTE"
            LET intvray[9].start_point = 11
            LET intvray[9].end_point = 13
            LET datetype[11] = "SECOND"
            LET intvray[11].start_point = 13
            LET intvray[11].end_point = 15
            LET datetype[12] = "FRACTION(1)"
            LET intvray[12].start_point = 15
            LET intvray[12].end_point = 16
            LET datetype[13] = "FRACTION(2)"
            LET intvray[13].start_point = 16
            LET intvray[13].end_point = 17
            LET datetype[14] = "FRACTION(3)"
            LET intvray[14].start_point = 17
            LET intvray[14].end_point = 18
            LET datetype[15] = "FRACTION(4)"
            LET intvray[15].start_point = 18
            LET intvray[15].end_point = 19
            LET datetype[16] = "FRACTION(5)"
            LET intvray[16].start_point = 19
            LET intvray[16].end_point = 20

清单 2. 功能相当的 awk 脚本


            #!/bin/ksh
            ###########################################################
            #
            # findcol database column
            #
            # returns any table or column within 'database' that has some
            # portion of 'column' within it's name
            #
            # Jack Parker 2002
            #
            ###########################################################
            echo "
            select tabname[1,25], colname[1,25], coltype, collength
            from systables a, syscolumns b
            where a.tabid = b.tabid
            and (colname matches \\"*$2*\\" or tabname matches \\"*$2*\\");
            " | dbaccess $1 - 2>/dev/null | tail +4l | grep -v ^$ | awk '
            BEGIN {
            dtp[0]="char"
            dtp[1]="smallint"
            dtp[2]="integer"
            dtp[3]="float"
            dtp[4]="smallfloat"
            dtp[5]="decimal"
            dtp[6]="serial"
            dtp[7]="date"
            dtp[8]="money"
            dtp[9]="unknown"
            dtp[10]="datetime"
            dtp[11]="byte"
            dtp[12]="text"
            dtp[13]="varchar"
            dtp[14]="interval"
            dtp[15]="nchar"
            dtp[16]="nvarchar"
            dtp[17]="unk"
            dtp[18]="unk"
            dtp[19]="unk"
            dtp[20]="unk"
            datp[1] = "year"
            int_start[1]=1
            int_end[1]=5
            datp[3] = "month"
            int_start[3]=5
            int_end[3]=7
            datp[5] = "day"
            int_start[5]=7
            int_end[5]=9
            datp[7] = "hour"
            int_start[7]=9
            int_end[7]=11
            datp[9] = "minute"
            int_start[9]=11
            int_end[9]=13
            datp[11] = "second"
            int_start[11]=13
            int_end[11]=15
            datp[12] = "fraction(1)"
            int_start[12]=15
            int_end[12]=16
            datp[13] = "fraction(2)"
            int_start[13]=16
            int_end[13]=17
            datp[14] = "fraction(3)"
            int_start[14]=17
            int_end[14]=18
            datp[15] = "fraction(4)"
            int_start[15]=18
            int_end[15]=19
            datp[16] = "fraction(5)"
            int_start[16]=19
            int_end[16]=20
            }
            function fixnm(coll,tp)
            {
            i = int(coll / 256)
            j = coll % 256
            if (tp == 0) {
            if (j > i ) strg=i
            else strg = sprintf("%s,%s",i,j)
            }
            else {
            if (i == 0) strg=j
            else strg = sprintf("%s,%s",j,i)
            }
            return strg
            }
            function fixdt(coll)
            {
            i = coll % 16 + 1
            j = int((coll % 256) / 16 ) + 1
            k = int(coll / 256)
            ln = int_end[i] - int_start[j]
            ln = k - ln
            if (ln == 0 || j > 11) { strg = sprintf("%s to %s", datp[j], datp[i]) }
            else {
            k int_end[j] - int_start[j]
            k = k + ln
            strg = sprintf("%s (%d) to %s", datp[j], k, datp[i])
            }
            return strg
            }
            {
            # Tab, colname, type, length
            outstrg=""
            tabname=$1
            colname=$2
            nonull=$3/256
            coltype=$3%256
            collength=$4
            outstrg=dtp[coltype]
            if (coltype == 0) {outstrg=sprintf("%s(%s)",outstrg, collength)}
            if (coltype == 5 || coltype == 8 ) {
            outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,0)) }
            if (coltype == 13 ) {outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,1)) }
            if (coltype == 10 || coltype == 14 ) {
            outstrg=sprintf("%s(%s)",outstrg,fixdt(collength)) }
            printf("%-20s %-20s %-25s\\n", tabname, colname, outstrg)
            }'

清单 3. depend.sh


            #!/bin/ksh
            ###############################################################
            #
            # Quick script thrown together to find dependencies on
            # a table.
            #
            # Usage:
            #  depend.sh database table
            #
            # This will list all tables which refer to this table either
            # by view or by foreign key.  It will also list any tables
            # that this table depends on.  It will recurse through a
            # dependency chain.
            #
            # Jack Parker 2003
            #
            ###############################################################
            find_par_view()
            {
            dbaccess $1 - 2>/dev/null <<EOF
            output to pipe "cat" without headings
            select trim(b.tabname)
            from sysdepend c, systables a, systables b
            where btabid=a.tabid
            and dtabid=b.tabid
            and b.tabname = "$2";
            EOF
            }
            find_child_view()
            {
            dbaccess $1 - 2>/dev/null <<EOF
            output to pipe "cat" without headings
            select trim(b.tabname)
            from sysdepend c, systables a, systables b
            where btabid=a.tabid
            and dtabid=b.tabid
            and a.tabname = "$2";
            EOF
            }
            find_par_fk()
            {
            dbaccess $1 - 2>/dev/null <<EOF
            output to pipe "cat" without headings
            select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state
            from systables a, sysconstraints b, sysreferences c, systables d,
            sysobjstate e
            where constrtype='R'
            and a.tabid=b.tabid
            and b.constrid=c.constrid
            and b.constrname=e.name
            and c.ptabid=d.tabid
            and a.tabname = "$2";
            EOF
            }
            find_child_fk()
            {
            dbaccess $1 - 2>/dev/null <<EOF
            output to pipe "cat" without headings
            select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state
            from systables a, sysconstraints b, sysreferences c, systables d,
            sysobjstate e
            where constrtype='R'
            and a.tabid=b.tabid
            and b.constrid=c.constrid
            and b.constrname=e.name
            and c.ptabid=d.tabid
            and d.tabname = "$2";
            EOF
            }
            rec_pfk()
            {
            idnt="$idnt-"
            find_par_fk $1 $2 | grep -v "^$" |
            while read junk
            do
            echo $idnt "Parent of FK: $junk"
            tab=`echo $junk | cut -d" " -f1 `
            rec_pfk $1 $tab
            done
            idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
            }
            rec_cfk()
            {
            idnt="$idnt-"
            find_child_fk $1 $2 | grep -v "^$" |
            while read junk
            do
            echo $idnt "Child of FK: $junk"
            tab=`echo $junk | cut -d" " -f1 `
            rec_cfk $1 $tab
            done
            idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
            }
            rec_pvw()
            {
            idnt="$idnt-"
            find_par_view $1 $2 | grep -v "^$" |
            while read tab
            do
            echo $idnt "Parent View : $tab"
            rec_cfk $1 $tab
            done
            idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
            }
            rec_cvw()
            {
            idnt="$idnt-"
            find_child_view $1 $2 | grep -v "^$" |
            while read tab
            do
            echo $idnt "Child of View : $tab"
            rec_cfk $1 $tab
            done
            idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
            }
            echo "
            Database: $1
            "
            idnt=""
            echo "Searching for tables that ($2) has an FK to:"
            rec_pfk $1 $2
            idnt=""
            echo "Searching for tables that refer to ($2) with an FK:"
            rec_cfk $1 $2
            idnt=""
            echo "Searching for Parent tables for potential view ($2):"
            rec_pvw $1 $2
            idnt=""
            echo "Searching for Child tables for table ($2):"
            rec_cvw $1 $2

转载于:https://www.cnblogs.com/sandwater/archive/2007/01/14/620032.html

weixin_30824277

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用 Informix 系统目录

Jack Parker系统架构设计师, Arten Technology Group2003 年 5 月本文描述了如何使用 Informix 系统目录表来收集关于表、视图、列、索引、权限、约束和 DBA 感兴趣的任何其它细节的信息。本文包含样本代码。重要：在阅读本文之前，请先阅读免责声明。简介随着数据仓库的出现及其对有关数据信息的需求，人们对元数据投入了极大的关注。其中大部分对元数...
复制链接

扫一扫