DB2常用系统视图小结

1. 查询 tabschema
select * from SYSCAT.SCHEMATA ORDER BY CREATE_TIME DESC
select tabschema from syscat.tables group by tabschema[@more@] 2.查询失效视图
select * from sysibm.sysviews where valid = 'X'
select TABSCHEMA||'.'||TABNAME from SYSCAT.TABLES where TYPE='V' and STATUS='X'
3.查询NICKNAME对应的本地 表 名和远程表名
select tabschema,tabname, remote_schema,remote_table from syscat.nicknames fetch first 10 rows only
select tabschema,tabname, remote_schema,remote_table, server name from syscat.nicknames where tabname='EMPLOYEE'
4.查询字段名为"USER_ID"的表信息
select * from syscat.columns where colname='USER_ID'
5.查询表"EMPLOYEE"的 索引 信息
select indname,tabschema,tabname,colnames,uniquerule from syscat.indexes where tabname='EMPLOYEE'
6.查询基于表TABSCHEMA.EMPLOYEE创建的视图
select tabschema,tabname,dtype,bschema,bname,btype from SYSCAT.TABDEP where bschema ='TABSCHEMA' AND bname='EMPLOYEE'
select distinct tabschema,tabname from SYSCAT.TABDEP where bschema = 'TABSCHEMA' and bname = 'EMPLOYEE'
7.查询某表的授权信息
select * from SYSCAT.TABAUTH where tabname='EMPLOYEE'
8.查询某个表的外键信息
select * from syscat.references where tabname='EMPLOYEE'
9.查询失效的别名
select tabname, tabschema from syscat.tables where type = 'A' and status <> 'N'
10.查询失效的package
select PKGNAME, PKGSCHEMA , valid from syscat.packages where valid <> 'Y'
11.查询表的 分区 信息
SELECT * FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='EMPLOYEE'
12.查询一个表的统计信息
SELECT card, fpages, npages,OVERFLOW FROM SYSSTAT.TABLES WHERE TABSCHEMA='TABSCHEMA' and Tabname='TABLENAME' WITH UR
13.查询 数据库 的 用户 权限
select * from SYSCAT. DBA UTH fetch first 10 rows only with ur
14.查询buffpool信息
select * from syscat.bufferpools
15.查询 数据 库恢复信息
select START_TIME,END_TIME,BACKUP_ID,OPERATION from SYS IBM ADM.DB_HISTORY where OPERATION='R'
16.查询数据库表 空间 信息
select * from syscat.tablespaces
17.查询EVENTS信息
select * from syscat.events
select * from syscat.eventmonitors
18. 查询 系统 中的server信息
select * from syscat.serveroptions
select * from syscat.servers
19.查询系统的 环境 信息
select * FROM SYSIBMADM.ENV_SYS_RESOURCES
select * FROM SYSIBMADM.ENV_SYS_INFO
20. DB2 _HISTORY信息查询
SELECT * FROM SYSIBMADM.DB_HISTORY WHERE TABSCHEMA='TABSCHEMA' ORDER BY START_TIME DESC FETCH FIRST 10 ROWS ONLY WITH UR
21.查询缓冲池使用情况
Select * from sysibmadm.bp_hitratio
22.查询当前锁等待的信息
Select substr(tabschema,1,8) as tabschema, substr(tabname,1,15) as tabname,lock_object_type,lock_mode, lock_mode_requested, agent_id_holding_lk From sysibmadm.lockwaits

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7490392/viewspace-1059434/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7490392/viewspace-1059434/