db2 系统表信息.docx
- 文档编号:14882753
- 上传时间:2023-06-28
- 格式:DOCX
- 页数:17
- 大小:23.38KB
db2 系统表信息.docx
《db2 系统表信息.docx》由会员分享,可在线阅读,更多相关《db2 系统表信息.docx(17页珍藏版)》请在冰点文库上搜索。
db2系统表信息
DB2CLP简介
DB2CommandLineProcessor(DB2CLP)是所有DB2产品中都有的,可以使用这个应用程序运行DB2命令、操作系统命令或SQL语句。
DB2CLP可以成为强大的工具,因为它能够将经常使用的命令或语句序列存储在批处理文件中,可以在必要的时候运行这些批处理文件。
在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的视图。
我们可以通过 LISTTABLESFORSYSTEM 或 LISTTABLESFORSCHEMAschemaname 命令查看所有的系统表和相关的视图信息。
下面我们会介绍一些本文用到的系统表和视图。
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。
)
∙A=Alias
∙G=Globaltemporarytable
∙H=Hierarchytable
∙L=Detachedtable
∙N=Nickname
∙S=Materializedquerytable
∙T=Table(untyped)
∙U=Typedtable
∙V=View(untyped)
∙W=Typedview
COLCOUNT
SMALLINT
表或视图中列的个数
OWNER
VARCHAR(128)
AuthorizationIDunderwhichthetable,view,alias,ornicknamewascreated.
……
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:
DB2UDF,系统方法(system-generatedmethod),用户定义方法(user-definedmethod)和存储过程(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说明了DB2UDF与其他对象的依赖关系。
表4.SYSCAT.ROUTINEDEP视图的说明
列名
数据类型
描述
ROUTINESCHEMA
VARCHAR(128)
依赖于其他对象的DB2程序的schema
ROUTINENAME
VARCHAR(128)
依赖于其他对象的DB2程序的名称
BTYPE
CHAR
(1)
依赖对象的类型:
∙A=Alias
∙S=Materializedquerytable
∙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=Materializedquerytable
∙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=Materializedquerytable
∙V=View(untyped)
∙W=Typedview
BTYPE
CHAR
(1)
依赖对象的类型:
∙A=Alias
∙S=Materializedquerytable
∙T=Table
∙V=View
BSCHEMA
VARCHAR(128)
被依赖的对象的schema
BNAME
VARCHAR(128)
被依赖的对象的名称
回页首
使用脚本进行存储过程开发
在DB2开发过程中我们经常使用的操作是:
1.连接数据库;
2.在不同的数据库中执行编写好的SQL文件,对创建的数据库对象进行简单测试;
3.当我们编写存储过程或者UDF的时候,我们需要查看其依赖的数据库表或视图的定义;
4.修改表结构的时候,我们需要获得所有依赖这个表的存储过程和UDF,防止因为表结构变化,使得相关的存储过程和UDF不可用。
以上操作我们可以在CLP下通过调用DB2提供的命令或者通过SELECT语句从DB2系统表中查出相关信息来完成。
我们把这些操作编写为如清单1所示的脚本。
清单1.conn.bat――使用脚本连接数据库
1.@echooff2.if{%1}=={}gotonoparms3.if{%1}=={dev}gotodev4.if{%1}=={test}gototest5.if{%1}=={prod}gotoprod6.if{%1}=={off}gototerminate7.gotodev8.:
noparms9.echoUsage:
conndb2type10.echoexample:
conndev11.echodb2type--dev,test,prod,off12.gotoend13.:
dev14.db2connecttodb2devuserusernameusingpassword15.PROMPT$P(%1)$G16.gotoend17.:
test18.db2connecttodb2testuserusernameusingpassword19.PROMPT$P(%1)$G20.gotoend21.:
prod22.db2connecttodb2produserusernameusingpassword23.PROMPT$P(%1)$G24.gotoend25.:
terminate26.db2terminate27.PROMPT$P$G28.gotoend29.:
end
每次连接数据库我们只需要键入 conn dev/test 就可以完成数据库的连接,简化了命令输入,避免输入错误的用户和密码。
同时这个脚本也可被其他的脚本调用。
第2个批处理脚本是如清单2所示的执行SQL文件的脚本。
使用方法是 esql test.sql[logs\out.log]。
清单2.esql.bat――执行SQL文件
1.@echooff2.if{%2}=={}gotosetlog3.setLogFile=%24.gotoexe5.rem如果没有指定输出文件,设置默认输出文件6.:
setlog7.setLogFile=logs\run.log8.9.:
exe10.echo%LogFile%11.echo---------------------executefile:
%1-------------------->>%LogFile%12.db2values(currenttime)>>%LogFile%13.db2-td@-f%1-z%LogFile%14.db2values(currenttime)>>%LogFile%15.pause
其中–td@选项表示SQL文件中各SQL语句间的分割符为@。
–z选项可以使sql的执行结果输出到指定的Log文件中。
这里我们使用-z而不使用脚本中的管道符号(>)是因为-z不仅可以把执行结果记录到指定的文件中,同时还会在控制台上显示,这样即方便我们在执行sql的时候实时掌握sql运行情况,又可以在log中查找分析sql执行情况。
该脚本需要两个参数,第一个参数指明要执行的sql文件,第二个参数指明log文件名称,该参数可选。
在脚本的第12行,第14行,我们使用values(currenttime)来记录脚本执行的开始时间和结束时间。
这样可以帮助我们计算出脚本执行完毕需要多长时间。
第3个批处理脚本的作用是获得表定义,包括字段属性、主键、前20条记录,如果是视图,显示其创建语句,并显示视图中包含的表。
如清单3所示。
它的使用方法是 viewtable schematablename。
清单3.viewtable.bat――获得表定义
1.@echooff2.mkdirlogs3.setlogfile=logs\view_%2.txt4.db2describetable%1.%2showdetail>%logfile%5.echo-------belowis(are)pk----------->>%logfile%6.db2"selectsubstr(colname,1,30)aspkfromsyscat.columnswherekeyseqisnotnullandtabschema=upper('%1')andtabname=upper('%2')">>%logfile%7.echo-----belowarethefisrt20rowsinthe%1.%2table---->>%logfile%8.db2select*from%1.%2fetchfirst20rowsonly>>%logfile%9.echo---------belowisthesqlofthe%1.%2view---->>%logfile%10.db2"selectvarchar(text,32670)fromsyscat.viewswhereviewschema=upper('%1')andviewname=upper('%2')">>%logfile%11.echo-----belowaretablesofthe%1.%2view---->>%logfile%12.db2"selectdistinctbnamefromsyscat.viewdepwhereviewname=upper('%1')andbtype='T'">>%logfile%13.start%logfile%
脚本中第4行的describe命令用于显示数据库表或视图的结构。
包括字段名称,数据类型等。
第6行用于查出表中的主键。
视图syscat.columns中的keyseq字段表明了该字段在表的主键的顺序,为空表示该字段不是表的主键。
第8行是获得表或视图中的前20条记录,便于我们对其分析。
第10行是获得视图的SQL定义。
第12行获得视图中依赖的表。
第4个批处理脚本的作用是查看存储过程或UDF的定义。
如清单4所示。
它的使用方法是 view spname。
清单4.view.bat――查看存储过程或UDF的定义
1.@echooff2.setlogfile=logs\view_%1.sql3.db2selectvarchar(text,32670)fromsyscat.routineswhereroutinename=upper('%1')>%logfile%4.echo-----------------------belowaretablesusedby%1--------->>%logfile%5.db2selectbnamefromsyscat.packagedepwhere(btype='T'orbtype='V')andpkgnamein(selectbnamefromsysibm.sysdependencieswheredname=upper('%1'))>>%logfile%6.7.start%logfile%
在脚本中我们之所以使用VARCHAR函数来格式TEXT字段中的内容,是因为TEXT字段是CLOB类型,其存储的存储过程或UDF的创建语句的字符比较多。
如果我们不使用VARCHAR函数进行格式化,DB2就会使用默认的CHAR函数(CHAR的最大长度是254)对其格式化,这样返回的SQL创建语句就可能被截去后面的部分。
第5行中脚本首先获得依赖某存储过程的所有的包(package)名,然后查找依赖这些包的所有的表和视图。
这样就可以获得存储过程中使用的表和视图。
第4个批处理脚本的作用是使用脚本获得依赖某个表的所有存储过程,UDF和视图。
如清单5所示。
它的使用方法是 dep schemaspname。
清单5.dep.bat――使用脚本获得依赖某个表的所有存储过程,UDF和视图
1.@echooff2.mkdirlogs3.setlogfile=logs\%2.dep.txt4.echo---dependentSPs--->>%logfile%5.db2"selectr.routineschema,r.routinenamefromsyscat.routinesr,syscat.packagedeppdepwherepdep.bname=upper('%2')andpdep.bschema=upper('%1')andpdep.pkgname='p'||substr(char(r.lib_id+10000000),2)">>%logfile%6.echo---dependentUDF--->>%logfile%7.db2selectroutineschema,routinenamefromsyscat.routinedepwherebschema=upper('%1')andbname=upper('%2')andbtype='T'orderbybname>>%logfile%8.echo---dependentview--->>%logfile%9.db2selectviewschema,viewnamefromsyscat.viewdepwherebschema=upper('%1')andbname=upper('%2')andbtype='T'orderbybname>>%logfile%10.start%logfile%
脚本第5行是获得依赖某表的存储过程。
因为存储过程会被编译成包(package)。
所以,存储过程和表的依赖关系实际上是存储过程对应的包(package)和表之间的依赖关系。
这种关系被记录在syscat.packagedep视图中。
同样的,我们可以通过查询syscat.routinedep和syscat.viewdep视图,获得UDF和表,视图和表之间的依赖关系。
回页首
开发实例
使用上面的脚本,进行数据库开发就会很方便。
下面我们看一个开发的例子。
需求描述
由于业务变更,我们的《订单管理系统》中的表MIS.ORDER需要加一个字段GOV_FLAGSMALLINT来标识是否是政府订单,如果是则需要执行特殊的折扣政策。
同时需要去除冗余字段ORDER_PRICE_TOTDECIMAL(19,4)。
需求描述
开发过程
我们需要修改相应的表,存储过程和UDF的SQL文件,并把它们重新装载到开发数据库和集成测试数据库中。
并需要保留表中原有数据。
假设我们有两个数据库,一个是名为dev的开发数据库,一个是名为test的集成测试数据库。
只有项目组长拥有在test数据库上执行的权限,开发人员只可以在dev数据库上执行操作。
首先项目组长会分析需求,制定任务分配;然后开发人员根据任务分配编写相应的SQL和脚本文件,并且在dev数据库上进行装载和测试;最后项目组长把开发人员提交的脚本文件汇总起来,在test数据库上进行装载和测试。
1.项目组长
项目组长需要把这个任务分配给组员去完成。
首先组长使用脚本viewtableMISORDER查看一下当前在数据库中表的结构,检查添加的字段是否会和其他字段有冲突。
然后项目组长使用脚本depMISORDER获得依赖表MIS.ORDER的所有DB2对象。
经过分析这些依赖对象,项目组长发现,表结构的修改会导致存储过程MIS.SP_ADD_ORDER、UDFMIS.F_CHECK_ORDER和视图MIS.V_ORDER的修改,并会导致两个存储过程需要rebind。
分析结果汇总如下表8所示:
表8.项目组长的分析结果
名称
执行操作
备注
MIS.SP_ADD_ORDER
修改
MIS.SP_UPDATE_ORDER
rebind
没有使用要删除的字段,不必修改
MIS.SP_ADD_ORDER
rebind
没有使用要删除的字段,不必修改
MIS.F_CHECK_ORDER
修改
MIS.V_ORDER
修改
于是项目组长进行如表9的任务分配。
表9.项目组长制定的任务分配
编号
任务
执行人
前提条件
完成标志
10
修改ORDER.SQL,在MIS.ORDER中:
∙添加一个字段GOV_FLAGSMALLINT;
∙去除冗余字段ORDER_PRICE_TOTDECIMAL(19,4);
∙备注:
在删除原表前,需要首先删除依赖该表的UDF和
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- db2 系统表信息 系统 信息