DB2数据库常用查询语句

DB2数据库常用查询:

1、DB2查找存储过程语句,存储过程名字大写

SELECT PROCNAME,TEXT,CREATE_TIME,A. * FROM SYSCAT.PROCEDURES  a WHERE  PROCNAME = 'TEST_AAA' with ur; 

SELECT TEXT, PROCNAME ,a.* from syscat.procedures a  where  TEXT like  '%TEST_AAA%' with ur;  

2、DB2数据库查找一个表的索引语句

SELECT * from syscat.indexes where tabname=upper('TEST_AAA') with ur;

3、DB2数据库查看属于哪个表空间语句

SELECT tabname, tbspace from syscat.tables where tabname=upper('TEST_AAA')with ur;

4、DB2数据库查看函数语句

SELECT * from syscat.functions where funcschema='TEST_AAA' and funcname='TEST_AAA'  with ur;

5、DB2数据库查看锁表语句

SELECT trim(substr(A.TABSCHEMA,1,8))||'.'||substr(A.TABNAME, 1,15) as TABNAME, A.LOCK_MODE,A.DBPARTITIONNUM,A.AGENT_ID, SUBSTR(B.APPL_ID,1,20) AS APPL_ID, B.CLIENT_PID,SUBSTR(CLIENT_PLATFORM,1,8) AS CPLATFORM, SUBSTR(B.CLIENT_NNAME,1,8) AS CLIENT_NAME FROM SYSIBMADM.SNAPLOCK A, SYSIBMADM.APPLICATIONS B WHERE A.AGENT_ID = B.AGENT_ID 
AND TABNAME=upper('TEST_AAA') with ur;

6、DB2数据库查看触发器语句

select TRIGNAME,TEXT,a.* from SYSCAT.TRIGGERS  a  where TEXT  like  '%TEST_AAA%';

7、DB2数据库查看 包绑定

SELECT c.pkgname,a.routinename from syscat.routines a,syscat.routinedep b,syscat.packages c where b.routinename=a.specificname and b.bname = c.pkgname and a.routinename in (select a.routinename from syscat.routines a,syscat.routinedep b where b.routinename=a.specificname and b.btype='T' and b.bname='表名') with ur; 

8、DB2数据库查看表空间大小

SELECT substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB, sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as FREE_MB,tbsp_page_size AS PAGE_SIZE  from SYSIBMADM.TBSP_UTILIZATION where tbsp_name  in ('表空间名') group by tbsp_name,tbsp_content_type,tbsp_page_size with ur;

9、DB2数据库根据报错找到指定的表和列
先查询报错的表

SELECT * FROM SYSCAT.TABLES WHERE TBSPACEID = 10 AND TABLEID =43 with ur;

再根据表名找到指定列

SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME= 'TEST_AAA' AND COLNO = 3  with ur;