| 1.查询tabschema select * from SYSCAT.SCHEMATA ORDER BY CREATE_TIME DESC select tabschema from syscat.tables group by tabschema 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 |
标签:
db2数据库
