DB2常用系統檢視小結

guyuanli發表於2012-09-14
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,servername 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.DBAUTH fetch first 10 rows only with ur
14.查詢buffpool資訊
select * from syscat.bufferpools
15.查詢資料庫恢復資訊
select START_TIME,END_TIME,BACKUP_ID,OPERATION from SYSIBMADM.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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章