DB2数据库常用查询语句

2023-08-29 17:10| 来源: 网络整理| 查看: 265

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;

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章