-
Notifications
You must be signed in to change notification settings - Fork 7
/
Copy pathdb2script.txt
94 lines (64 loc) · 7.75 KB
/
db2script.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
db2LockWaitInfo
//TAB,LOCK_MODE,REQ_APPLICATION_NAME,REQ_APPLICATION_HANDLE,REQ_STMT_TEXT,HLD_CURRENT_STMT_TEXT,LOCK_WAIT_ELAPSED_TIME from sysibmadm.MON_LOCKWAITS order by LOCK_WAIT_ELAPSED_TIME
db2ErrorInfo
//SELECT timestamp, instancename, dbname, appl_id, msg FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS ORDER BY TIMESTAMP DESC
strOSVersion="select member,varchar(HOST_NAME,12) as HOST_NAME, varchar(OS_NAME,8) as OS_NAME, varchar(OS_VERSION,8) as OS_VERSION, varchar(OS_RELEASE,18) as OS_RELEASE from table(SYSPROC.ENV_GET_SYSTEM_RESOURCES()) order by MEMBER";
//
strDB2Set=" SELECT REG_VAR_NAME,REG_VAR_VALUE from SYSIBMADM.REG_VARIABLES where level='I'";
strDB2_DBM_CFG="SELECT NAME,VALUE, VALUE_FLAGS FROM SYSIBMADM.DBMCFG where value is not null"
strDB2_DB_CFG=" SELECT NAME, VALUE,DEFERRED_VALUE_FLAGS as VALUE_FLAGS FROM SYSIBMADM.DBCFG where value is not null";
strDB2_BP_HITRATIO ="SELECT c.BUFFERPOOLID, SUBSTR(A.BP_NAME,1,20) AS BP_NAME, A.TOTAL_HIT_RATIO_PERCENT, A.DATA_HIT_RATIO_PERCENT, A.DATA_LOGICAL_READS,A.DATA_PHYSICAL_READS , A.INDEX_HIT_RATIO_PERCENT , A.INDEX_LOGICAL_READS,A.INDEX_PHYSICAL_READS ,(B.BP_CUR_BUFFSZ * c.PAGESIZE )/1048576 as BP_SIZE_MB, b.BP_TBSP_USE_COUNT FROM SYSIBMADM.BP_HITRATIO A, SYSIBMADM.SNAPBP_PART B, syscat.bufferpools C WHERE a.bp_name = b.bp_name and b.bp_name = c.bpname";
//Scan Lock wait
strDB2_LockWait ="select TABSCHEMA||'.'||TABNAME as TAB,LOCK_MODE,APPLICATION_ID as IPAddr,REQ_APPLICATION_NAME,REQ_APPLICATION_HANDLE,REQ_STMT_TEXT,LOCK_WAIT_ELAPSED_TIME,HLD_APPLICATION_HANDLE from sysibmadm.MON_LOCKWAITS A, sysibmadm.MON_CONNECTION_SUMMARY B WHERE A.REQ_APPLICATION_HANDLE = B.APPLICATION_HANDLE order by LOCK_WAIT_ELAPSED_TIME desc,REQ_APPLICATION_HANDLE asc,HLD_APPLICATION_HANDLE asc fetch first 50 rows only";
strDB2_DeadLock="select A.TABSCHEMA||'.'||A.TABNAME as TAB,a.LOCK_MODE,APPLICATION_ID as IPAddr,a.REQ_APPLICATION_NAME,A.REQ_STMT_TEXT,A.REQ_APPLICATION_HANDLE,a.LOCK_WAIT_ELAPSED_TIME,A.HLD_APPLICATION_HANDLE from sysibmadm.MON_LOCKWAITS A inner join sysibmadm.MON_LOCKWAITS C on A.REQ_APPLICATION_HANDLE =C.HLD_APPLICATION_HANDLE and A.HLD_APPLICATION_HANDLE= C.REQ_APPLICATION_HANDLE join sysibmadm.MON_CONNECTION_SUMMARY B on A.REQ_APPLICATION_HANDLE = B.APPLICATION_HANDLE order by a.LOCK_WAIT_ELAPSED_TIME, HLD_APPLICATION_HANDLE asc fetch first 50 rows only";
//Scan Top Sql
strDB2_TopTimeSql="select * from SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY AVERAGE_EXECUTION_TIME_S DESC,NUM_EXECUTIONS DESC FETCH FIRST 50 ROWS ONLY";
strDB2_TopExesNumSql="select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS DESC,AVERAGE_EXECUTION_TIME_S DESC FETCH FIRST 50 ROWS ONLY";
strDB2_TopSortSql="select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY STMT_SORTS DESC, NUM_EXECUTIONS DESC FETCH FIRST 50 ROWS ONLY";
//Scan TableSpace
strDB2_Tcb="select A.*,b.TBSP_PAGE_SIZE ,b.TBSP_TYPE,b.TBSP_CONTENT_TYPE,C.BPNAME from SYSIBMADM.SNAPTBSP_PART A, SYSIBMADM.SNAPTBSP B, syscat.bufferpools C WHERE A.TBSP_ID = B.TBSP_ID AND B.TBSP_CUR_POOL_ID = C.BUFFERPOOLID";
strDB2_Containers="select * from SYSIBMADM.SNAPCONTAINER order by TBSP_ID asc, CONTAINER_ID asc";
//Scan error Logs
strDB2_Problems = "SELECT timestamp, instancename,dbname,SUBSTR(MSG,1,400) AS MSG FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS WHERE msgseverity = 'C' or msgseverity = 'E' ORDER BY TIMESTAMP DESC";
//Scan Tables Info
strDB2_HotTableInfo ="select A.TABSCHEMA||'.'||A.TABNAME as TAB,DATA_OBJECT_PAGES,INDEX_OBJECT_PAGES,ROWS_READ,ROWS_WRITTEN,CARD,STATS_TIME,LASTUSED,CREATE_TIME,ALTER_TIME,TBSPACE,INDEX_TBSPACE,TAB_TYPE,COMPRESSION FROM SYSIBMADM.SNAPTAB A,syscat.tables B where OWNERTYPE='U' AND TYPE='T' and A.TABSCHEMA=B.TABSCHEMA AND A.TABNAME = B.TABNAME order by (2.5* ROWS_WRITTEN + ROWS_READ) desc, STATS_TIME asc fetch first 50 rows only with ur";
strDB2_LostIdxInfo="select TABSCHEMA||'.'||TABNAME as TAB,TAB_TYPE, ROWS_READ,table_scans FROM TABLE(MON_GET_table('','',-2)) AS T WHERE TAB_TYPE = 'USER_TABLE' or TAB_TYPE = 'TEMP_TABLE' order by table_scans desc, ROWS_READ desc fetch first 50 rows only with ur";
strTableOrphanInfo="select TABSCHEMA||'.'||TABNAME as TAB,CARD,COLCOUNT,STATS_TIME,LASTUSED,TBSPACE,INDEX_TBSPACE,CREATE_TIME,ALTER_TIME,COMPRESSION from syscat.tables WHERE OWNERTYPE='U' AND TYPE='T' order by LASTUSED asc,CARD desc, COLCOUNT desc fetch first 50 rows only with ur ";
strTableRunStatsInfo="select TABSCHEMA||'.'||TABNAME as TAB,CARD,COLCOUNT,STATS_TIME,LASTUSED,TBSPACE,INDEX_TBSPACE,CREATE_TIME,ALTER_TIME,COMPRESSION from syscat.tables WHERE OWNERTYPE='U' AND TYPE='T' and LASTUSED > '0001-01-01' order by STATS_TIME asc,LASTUSED desc,CARD desc fetch first 50 rows only with ur ";
strTableMaxRowsInfo="select TABSCHEMA||'.'||TABNAME as TAB,CARD,COLCOUNT,STATS_TIME,LASTUSED,TBSPACE,INDEX_TBSPACE,CREATE_TIME,ALTER_TIME,COMPRESSION from syscat.tables WHERE OWNERTYPE='U' AND TYPE='T' order by CARD desc ,COLCOUNT desc,LASTUSED asc fetch first 50 rows only with ur";
strTableMinRowsInfo="select TABSCHEMA||'.'||TABNAME as TAB,CARD,COLCOUNT,STATS_TIME,LASTUSED,TBSPACE,INDEX_TBSPACE,CREATE_TIME,ALTER_TIME,COMPRESSION from syscat.tables WHERE OWNERTYPE='U' AND TYPE='T' order by CARD asc ,COLCOUNT asc,LASTUSED asc fetch first 50 rows only with ur";
//Scan Indexes Info
strDB2_IdxOrphanInfo="select TABSCHEMA||'.'||TABNAME as TAB, INDNAME,COLNAMES,COLCOUNT,INDEXTYPE,STATS_TIME,LASTUSED,fullkeycard,INDCARD,COMPRESSION,CREATE_TIME FROM SYSCAT.INDEXES WHERE OWNERTYPE='U' ORDER BY LASTUSED ASC fetch first 50 rows only with ur";
strDB2_IdxStatsInfo="select TABSCHEMA||'.'||TABNAME as TAB, INDNAME,COLNAMES,COLCOUNT,INDEXTYPE,STATS_TIME,LASTUSED,fullkeycard,INDCARD,COMPRESSION,CREATE_TIME FROM SYSCAT.INDEXES WHERE OWNERTYPE='U' AND LASTUSED > date('2001-01-01') ORDER BY STATS_TIME ASC,LASTUSED desc FETCH FIRST 50 ROWS ONLY WITH UR";
// 性能信息
detail = "select * from sysibmadm.SNAPDB";
// db2应用程序连接信息
app = "select * from sysibmadm.APPL_PERFORMANCE";
// 表空间信息
tableSpace = "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 group by tbsp_name,tbsp_content_type,tbsp_page_size order by 1";
// 锁信息
lock = "select * from sysibmadm.LOCKS_HELD";
// Sql执行成本
sql = "select * from sysibmadm.QUERY_PREP_COST";
// 内存池信息
memPool = "select * from sysibmadm.SNAPDBM_MEMORY_POOL";
--table space check
SELECT * FROM SYSIBMADM.TBSP_UTILIZATION WHERE TBSP_NAME = '';
--check db2 Version
SELECT * FROM SYSIBMADM.ENV_INST_INFO;
-- Retrieving the snapshot statistics about the progress
-- of all active utilities per partition.
SELECT u1.UTILITY_DBNAME,
u1.DBPARTITIONNUM,
u1.UTILITY_ID,
u1.UTILITY_PRIORITY,
u1.UTILITY_DESCRIPTION,
u2.UTILITY_STATE,
u2.PROGRESS_WORK_METRIC,
u2.PROGRESS_COMPLETED_UNITS,
u2.PROGRESS_TOTAL_UNITS,
DEC( ( FLOAT( u2.PROGRESS_COMPLETED_UNITS ) / FLOAT( u2.PROGRESS_TOTAL_UNITS ) ) * 100, 4, 2 )
AS PERCENT_SEQ_COMPLETE
FROM SYSIBMADM.SNAPUTIL as u1, SYSIBMADM.SNAPUTIL_PROGRESS as u2
WHERE u1.UTILITY_ID = u2.UTILITY_ID and u1.DBPARTITIONNUM = u2.DBPARTITIONNUM
ORDER BY u1.UTILITY_DBNAME, u1.DBPARTITIONNUM, u2.PROGRESS_SEQ_NUM;