工作需要——研究DB2数据库系统表(收集备用)

时间:2019-05-11 09:39来源:计算机教程
转自: http://www.maomao365.com/?p=5277 摘要: 下文主要讲述,如何对"已上线的系统"中的表,增加新的字段。 db2 系统表信息  2011-05-25 10:00:18|  分类: DB2 |字号 订阅 系统部署脚本,增加列的方

转自: http://www.maomao365.com/?p=5277
摘要:
下文主要讲述,如何对"已上线的系统"中的表,增加新的字段。

db2 系统表信息 


2011-05-25 10:00:18|  分类: DB2 |字号 订阅

系统部署脚本,增加列的方法:
在系统脚本发布中,如何是存储过程 自定义函数 视图的修改和新增,我们通常采用以下步骤来编写此类脚本
1 判断对象(存储过程 自定义函数 视图)是否存在,如何存在我们就删除对象(存储过程 自定义函数 视图)
2 新建对象
<hr />
但是增加系统字段(列)时,我们不能采用删除表,然后重新的方式进行脚本处理,
那么我们通常采用 先判断列是否存在,如果不存在就创建列,如果存在就修改列属性<span style="color:red;font-weight:bold;">(修改列属性--请注意是否会影响历史数据)</span>
例:

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0706changw/index.html?S_TACT=105AGX52&S_CMP=techcto

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS 
WHERE OBJECT_ID = OBJECT_ID(N'表名') 
AND NAME = '列名') ---判断列是否存在
begin
---增加列
alter table [表名] 
add column [列名] [列类型]
end
else
begin
---修改列属性
alter table [表名] 
add column [列名] [列类型]
end

DB2 CLP 简介

采用以上方式编写sql部署脚本的优点为,sql脚本可以执行多次,不会出现报错信息,可以避免已经手动增加列的数据库产生部署错误提示信息

DB2 Command Line Processor(DB2 CLP)是所有 DB2 产品中都有的,可以使用这个应用程序运行 DB2 命令、操作系统命令或 SQL 语句。DB2 CLP 可以成为强大的工具,因为它能够将经常使用的命令或语句序列存储在批处理文件中,可以在必要的时候运行这些批处理文件。在 Windows 系统中,必须先(从普通命令窗口)运行 db2cmd 命令来启动 DB2 命令行环境。

* *

在什么时候使用本文介绍的脚本?

脚本的优势在于可以重复执行。如果一个任务会被执行多次,或者被多人执行。那么将该任务编写成脚本会提高任务的执行效率和准确性。本文介绍的脚本都是一些在我们进行存储过程开发和调试中经常用到的。读者在后面会渐渐体会到脚本给我们带来的便利。

DB2 系统表的功能

编写针对 DB2 的脚本,我们需要首先了解 DB2 为我们提供了哪些命令和信息。DB2 为我们提供了大量的命令例如连接数据库,执行一个 SQL 文件,获得表结构的信息等等。我们会在下面解释具体的脚本的同时对一些简单的 DB2 命令进行解释说明。同时,DB2 把数据库对象的很多信息都存储到了系统表中。熟悉这些系统表就能够通过 SQL 语句获得我们需要的信息。下面我们先来学习一下 DB2 系统表。

在 DB2 数据库被创建的时候,DB2 会创建一些系统表。这些系统表中记录了所有数据库对象的信息,表或视图的列的数据类型,约束的定义,对象的权限和对象之间的依赖关系等。这些系统表的模式为 SYSIBM,其表名以 SYS 作为前缀。例如: SYSTABLES、SYSVIEWS 等等。DB2 为这些系统表建立了相对应的只读视图。这些视图的模式是 SYSCAT,它们的内容是其相对应的系统表的全部或者部分内容。这些视图的名字没有 SYS 的前缀。例如:SYSCAT.TABLES 是 SYSIBM.SYSTABLES 的视图。

我们可以通过 LIST TABLES FOR SYSTEM 或 LIST TABLES FOR SCHEMA schemaname 命令查看所有的系统表和相关的视图信息。下面我们会介绍一些本文用到的系统表和视图。

SYSCAT.TABLES:数据库中对象的信息,包括 table,view,nickname 和 alias 的一些定义。详细说明见表 1。

表 1. SYSCAT.TABLES 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 记录 schema 的名字
TABNAME VARCHAR(128) 记录数据库对象的名称。包括表、视图、别名等
TYPE CHAR(1) 表示该数据库对象是表,视图还是别名 ('T'表示table; 'V'表示 view; 'N' 表示nickname; 'A' 表示 alias。)
COLCOUNT SMALLINT 表或视图中列的个数
……

SYSCAT.VIEWS:视图的定义信息。详细说明见表 2。

表 2. SYSCAT.VIEWS 视图的说明
列名 数据类型 描述
VIEWSCHEMA VARCHAR(128) 视图的 Schema
VIEWNAME VARCHAR(128) 视图名称
READONLY CHAR(1) 视图是否只读:

    Y = 视图是只读
    N = 视图不是只读

VALID CHAR(1) 视图状态是否合法:

    Y = 视图状态合法(valid)
    X = 视图状态不合法(invalid)

TEXT CLOB (64K) 视图的源程序(DDL)
……

SYSCAT.ROUTINES:DB2 UDF,系统方法(system-generated method),用户定义方法(user-defined method)和存储过程(SP)的定义。我们可以认为该视图包含了数据库中程序的定义。见表 3。

表 3. SYSCAT.ROUTINES 视图的说明
列名 数据类型 描述
ROUTINESCHEMA VARCHAR(128) 记录程序的 schema
ROUTINENAME VARCHAR(128) 记录程序名称
ROUTINETYPE CHAR(1) 记录程序类型:

    F = Function
    M = Method
    P = Procedure

SPECIFICNAME VARCHAR(128) 程序实例的名称(可以指定,也可以由系统自动生成)
VALID CHAR(1) 如果存储过程依赖的一些对象被删除或修改了,该存储过程必须要被重建

    Y = SQL 存储过程是合法的
    N = SQL 存储过程是非法的
    X = SQL 存储过程是不可操作的

TEXT CLOB(1M) 如果是用 SQL 编写的程序,该字段记录了其创建的 DDL

表 4 所示的 SYSCAT.ROUTINEDEP 说明了 DB2 UDF与其他对象的依赖关系。

表 4. SYSCAT.ROUTINEDEP 视图的说明
列名 数据类型 描述
ROUTINESCHEMA VARCHAR(128) 依赖于其他对象的 DB2 程序的 schema
ROUTINENAME VARCHAR(128) 依赖于其他对象的 DB2 程序的名称
BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

表 5 所示的 SYSCAT.COLUMNS 说明了表或视图的每一个列的信息。

表 5. SYSCAT.COLUMNS 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 表或视图的 Schema
TABNAME VARCHAR(128) 表或视图名称
COLNAME VARCHAR(128) 列名称
KEYSEQ SMALLINT 记录列在其表的主键的位置

表 6 所示的 SYSCAT.PACKAGEDEP 说明了 Pachage 与其他数据库对象的依赖关系。

表 6. SYSCAT.PACKAGEDEP 视图的说明
列名 数据类型 描述
PKGSCHEMA VARCHAR(128) Package 的 schema
PKGNAME VARCHAR(18) Package 的名称
BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    B = Trigger
    I = Index
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

表 7 所示的 SYSCAT.TABDEP 说明了视图或者固化视图和 DB2 对象之间的依赖关系。

表 7. SYSCAT.TABDEP 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 视图或者固化视图的 schema
TABNAME VARCHAR(128) 视图或者固化视图的名称
DTYPE CHAR(1) 视图的类型:

    S = Materialized query table
    V = View (untyped)
    W = Typed view

BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

回页首

使用脚本进行存储过程开发

在 DB2 开发过程中我们经常使用的操作是:

    连接数据库;
    在不同的数据库中执行编写好的 SQL 文件,对创建的数据库对象进行简单测试;
    当我们编写存储过程或者 UDF 的时候,我们需要查看其依赖的数据库表或视图的定义;
    修改表结构的时候,我们需要获得所有依赖这个表的存储过程和 UDF,防止因为表结构变化,使得相关的存储过程和 UDF 不可用。

以上操作我们可以在 CLP 下通过调用 DB2 提供的命令或者通过 SELECT 语句从 DB2 系统表中查出相关信息来完成。我们把这些操作编写为如清单 1 所示的脚本。

清单 1. conn.bat ――使用脚本连接数据库

                1. @echo off 2. if {%1}=={} goto noparms  3. if {%1}=={dev} goto dev 4. if {%1}=={test} goto test 5. if {%1}=={prod} goto prod 6. if {%1}=={off} goto terminate 7. goto dev  8. :noparms  9. echo Usage: conn db2type  10. echo example: conn dev  11. echo db2type -- dev, test, prod, off 12. goto end   13. :dev 14. db2 connect to db2dev user username using password 15. PROMPT $P(%1)$G 16. goto end 

  1. :test 18. db2 connect to db2test user username using password 19. PROMPT $P(%1)$G 20. goto end  21. :prod 22. db2 connect to db2prod user username using password 23. PROMPT $P(%1)$G 24. goto end  25. :terminate
  2. db2 terminate 27. PROMPT $P$G 28. goto end   29. :end

每次连接数据库我们只需要键入 conn dev/test 就可以完成数据库的连接,简化了命令输入,避免输入错误的用户和密码。同时这个脚本也可被其他的脚本调用。

第 2 个批处理脚本是如清单 2 所示的执行 SQL 文件的脚本。使用方法是 esql test.sql [logsout.log]。

清单 2. esql.bat――执行SQL文件

                1. @echo off 2. if {%2} =={} goto setlog 3. set LogFile=%2 4. goto exe 5. rem 如果没有指定输出文件,设置默认输出文件 6. :setlog 7. set LogFile=logsrun.log 8.  9. :exe 10. echo %LogFile% 11. echo --------------------- execute file:%1-------------------- >> %LogFile% 12. db2 values(current time) >> %LogFile% 13. db2 -td@ -f %1 -z %LogFile% 14. db2 values(current time) >> %LogFile% 15. pause

其中 –td@ 选项表示 SQL 文件中各 SQL 语句间的分割符为 @。–z 选项可以使 sql 的执行结果输出到指定的 Log 文件中。这里我们使用 -z 而不使用脚本中的管道符号(>)是因为 -z 不仅可以把执行结果记录到指定的文件中,同时还会在控制台上显示,这样即方便我们在执行 sql 的时候实时掌握 sql 运行情况,又可以在 log 中查找分析 sql 执行情况。

该脚本需要两个参数,第一个参数指明要执行的 sql 文件,第二个参数指明 log 文件名称,该参数可选。在脚本的第 12 行,第 14 行,我们使用 values(current time) 来记录脚本执行的开始时间和结束时间。这样可以帮助我们计算出脚本执行完毕需要多长时间。

第 3 个批处理脚本的作用是获得表定义,包括字段属性、主键、前 20 条记录,如果是视图,显示其创建语句,并显示视图中包含的表。如清单 3 所示。它的使用方法是 viewtable schema tablename。

清单 3. viewtable.bat ――获得表定义

                1. @echo off 2. mkdir logs 3. set logfile=logsview_%2.txt 4. db2 describe table %1.%2 show detail >%logfile% 5. echo  -------below is (are) pk-----------   >>%logfile% 6. db2 "select substr(colname,1,30) as pk from syscat.columns  where keyseq is not null and tabschema=upper('%1') and tabname = upper('%2')" >>%logfile% 7. echo  -----below are the fisrt 20 rows in the %1.%2 table----   >>%logfile% 8. db2 select * from %1.%2 fetch first 20 rows  only >>%logfile% 9. echo  ---------below is the sql of the %1.%2 view ----   >>%logfile% 10. db2 "select varchar(text,32670) from syscat.views  where viewschema=upper('%1')and  viewname = upper('%2')" >>%logfile% 11. echo  -----below are tables  of the %1.%2 view ----   >>%logfile%

  1. db2 "select distinct bname from syscat.viewdep  where viewname= upper('%1')  and btype='T'" >>%logfile% 13. start %logfile%

脚本中第 4 行的 describe 命令用于显示数据库表或视图的结构。包括字段名称,数据类型等。第 6 行用于查出表中的主键。视图 syscat.columns 中的 keyseq 字段表明了该字段在表的主键的顺序,为空表示该字段不是表的主键。第 8 行是获得表或视图中的前 20 条记录,便于我们对其分析。第 10 行是获得视图的 SQL 定义。第 12 行获得视图中依赖的表。

编辑:计算机教程 本文来源:工作需要——研究DB2数据库系统表(收集备用)

关键词: