adilsonrapozojr
10/24/2017 - 7:57 PM

Consultas DB2 LUW

Lista de consultas SQL para DB2 LUW

SELECT
	UOW.MEMBER
	,UOW.APPLICATION_HANDLE
	,UOW.UOW_ID
	,substr(UOW.CLIENT_USERID,1,10) AS OS_USER
	,substr(UOW.SESSION_AUTH_ID,1,10) AS DB_USER
	,CON.CONNECTION_START_TIME
	,UOW.UOW_START_TIME
	,SUBSTR(REPLACE(PKG.STMT_TEXT,X'0A',' '),1,250) AS STMT_TEXT
		FROM table(MON_GET_UNIT_OF_WORK(NULL,-2,1)) AS UOW
			JOIN table(MON_GET_CONNECTION(NULL,-2)) AS CON
				ON UOW.MEMBER = CON.MEMBER AND UOW.APPLICATION_HANDLE = CON.APPLICATION_HANDLE
			JOIN table(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS PKG
				ON UOW.MEMBER = PKG.MEMBER AND UOW.LAST_EXECUTABLE_ID = PKG.EXECUTABLE_ID
		WHERE UOW.workload_occurrence_state = 'UOWEXEC'
				AND CURRENT TIMESTAMP - UOW.UOW_START_TIME > 0.09
SELECT
	ACT.MEMBER
	,substr(con.SESSION_AUTH_ID,1,10) AS USER
	,ACT.APPLICATION_HANDLE
	,ACT.ACTIVITY_ID
	,ACT.UOW_ID
	,ACT.QUERY_COST_ESTIMATE as COST
	,ACT.QUERY_CARD_ESTIMATE as CARD
	,CON.CONNECTION_START_TIME
	,ACT.local_start_time as ACT_START_TIME
	,SUBSTR(rtrim(REPLACE(ACT.STMT_TEXT,X'0A',' ')),1,150) || ' (...)' AS STMT_TEXT
		FROM table(MON_GET_ACTIVITY(NULL,-2)) AS ACT
			JOIN table(MON_GET_CONNECTION(NULL,-2)) AS CON
				ON ACT.MEMBER = CON.MEMBER AND ACT.APPLICATION_HANDLE = CON.APPLICATION_HANDLE
		WHERE ACT.STMT_TEXT NOT LIKE '%PYROEXECPRO%'
		order by member
select member, APPLS_IN_DB2 as APPS_EXEC, APPLS_CUR_CONS - APPLS_IN_DB2 AS APPS_IDLE
		FROM TABLE(MON_GET_DATABASE(-2))
		order by 1
SELECT
    date(timestamp(START_TIME)) as START_DATE
    ,time(timestamp(START_TIME)) AS START_TIME
    ,timestampdiff ( 4, varchar(timestamp(end_time) - timestamp(start_time)) ) as duration
    ,SQLCODE
    , CASE OPERATIONTYPE
        WHEN 'D' THEN 'Delta Offline'
        WHEN 'E' THEN 'Delta Online'
        WHEN 'F' THEN 'Offline'
        WHEN 'I' THEN 'Incremental Offline'
        WHEN 'N' THEN 'Online'
        WHEN 'O' THEN 'Incremental Online'
    END AS BKP_TYPE
    , CASE OBJECTTYPE
        WHEN 'D' THEN 'DATABASE'
        WHEN 'P' THEN 'TBSP'
        ELSE OPERATIONTYPE
    END AS OPERATIONTYPE
    ,substr(FIRSTLOG,1,15) FIRSTLOG
    ,substr(LASTLOG,1,15) LASTLOG
    , CASE DEVICETYPE
        WHEN 'A' THEN 'TSM'
        WHEN 'C' THEN 'CLIENT'
        WHEN 'D' THEN 'DISK'
        WHEN 'F' THEN 'SNAPSHOT'
        WHEN 'K' THEN 'DISKETTE'
        WHEN 'L' THEN 'LOCAL'
        WHEN 'N' THEN 'INTERNAL'
        WHEN 'O' THEN 'OTHER'
        WHEN 'P' THEN 'PIPE'
        WHEN 'Q' THEN 'CURSOR'
        WHEN 'R' THEN 'REMOTE'
        WHEN 'S' THEN 'SERVER'
        WHEN 'T' THEN 'TAPE'
    END AS DEVICETYPE
    ,substr(LOCATION,1,100) as LOCATION
    FROM SYSIBMADM.DB_HISTORY
        WHERE OPERATION = 'B' AND SEQNUM=1
        ORDER BY 1 DESC, 2 DESC
    WITH UR
-- Informacoes de SQLs atualmente sendo executados
SELECT
    COORD_MEMBER
    ,APPLICATION_HANDLE
    ,substr(APPLICATION_NAME,1,30) as APPLICATION_NAME
    ,substr(SESSION_AUTH_ID,1,30) as SESSION_AUTH_ID
    ,ELAPSED_TIME_SEC/60 as ELAPSED_TIME_MIN
    ,ACTIVITY_STATE
    ,ACTIVITY_TYPE
    ,TOTAL_CPU_TIME
    ,ROWS_READ/(ROWS_RETURNED+1) as STMT_IXREF
    ,QUERY_COST_ESTIMATE
    ,substr(STMT_TEXT,1,300) as STMT_TEXT
    FROM SYSIBMADM.MON_CURRENT_SQL
select substr(evmonname,1,30) as evmonname, EVENT_MON_STATE(evmonname) as state, substr(target, 1 150) as target from syscat.eventmonitors with ur;

db2 flush event monitor EVMON

db2evmon -path TARGET > deadlocks.out
-- Queries que tiveram Deadlocks que foram executadas nos últimos 30 minutos
SELECT
    EXECUTABLE_ID
    ,NUM_EXECUTIONS
    ,DEADLOCKS
    ,last_metrics_update
      FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, '<modified_within>30</modified_within>', -2)) as T 
        WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME
        
SELECT
    EXECUTABLE_ID
    ,SUBSTR(STMT_TEXT,1,500) as STATEMENT
      FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, EXECUTABLE_ID, NULL, -2)) as T 
        WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME
SELECT
	MEMBER
	,SUBSTR(INSTANCENAME,1,8) AS INSTANCENAME
	,SUBSTR(DBNAME,1,8) AS DBNAME
	,TIMESTAMP
	,LEVEL
	,SUBSTR(IMPACT,1,10) AS IMPACT
	,EDU_ID
	,substr(EDUNAME,1,25) as EDUNAME
	,PID
	,substr(PROCESS_NAME,1,25) as PROCESS_NAME
	,TID
	,substr(APPLICATION_ID,1,35) as APPLICATION_ID
	,APPLHANDLE
	,substr(AUTH_ID,1,8) as AUTH_ID
	,OSERR
	,RETCODE
	,MSGNUM
  ,substr(rtrim(OBJNAME_QUALIFIER)||'.'||OBJNAME,1,65) as object
  ,substr(EVENTTYPE,1,15) as EVENTTYPE
	,CASE
		WHEN LENGTH(MSG) > 407 THEN
				SUBSTR(rtrim(REPLACE(MSG,X'0A',' ')),1,150) || ' (...) ' || SUBSTR(RIGHT(REPLACE(MSG,X'0A',' '),150),1,250)
		ELSE
				SUBSTR(rtrim(REPLACE(MSG,X'0A',' ')),1,407)
		END AS MSG
-- PD_GET_DIAG_HIST--(--facility--,--rectype--,--impact--,--start_time--,--end_time--,--member--)
	FROM TABLE(PD_GET_DIAG_HIST(NULL, NULL, NULL, TIMESTAMP('2007-09-23-15.53.37'), TIMESTAMP('2007-09-24-15.53.37'), -2))
--overview
SELECT
        SUBSTR(STMT_TEXT,1,10) as STATEMENT
        , ROWS_READ
        , ROWS_READ/ROWS_RETURNED as STMT_IXREF
        , ROWS_RETURNED/NUM_EXECUTIONS as ROWS_RETURNED_PER_EXEC
        , TOTAL_ACT_TIME as TOTAL_ACT_TIME_MS
        , decimal(float(TOTAL_ACT_WAIT_TIME)/float(TOTAL_ACT_TIME),5,2)*100 as PCT_WAIT
        , decimal(1 - ((float(pool_data_p_reads) + float(pool_xda_p_reads) +
                float(pool_index_p_reads) + float(pool_temp_data_p_reads)
                + float(pool_temp_xda_p_reads) + float(pool_temp_index_p_reads) )
                / (float(pool_data_l_reads) + float(pool_xda_l_reads) + float(pool_index_l_reads) +
                float(pool_temp_data_l_reads) + float(pool_temp_xda_l_reads)
                + float(pool_temp_index_l_reads) )) ,5,2) as stmt_bphr
        , TOTAL_SORTS/NUM_EXECUTIONS as SORTS_PER_EXEC
        , decimal(float(SORT_OVERFLOWS)/float(TOTAL_SORTS),5,2) * 100 as SORT_OVERFLOW_PCT
        , POST_THRESHOLD_SORTS+POST_SHRTHRESHOLD_SORTS as POST_THRESHOLD_SORTS
        , NUM_EXECUTIONS
        , DECIMAL(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS AVG_EXEC_TIME
        , DEADLOCKS
        , LOCK_TIMEOUTS
        , INSERT_TIMESTAMP
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', x'01000000000000001E1906000000000000000000020020170903070924368243', NULL, -2)) AS T
;
--wait time
SELECT
        decimal(float(TOTAL_ACT_WAIT_TIME)/float(TOTAL_ACT_TIME),9,4) as PCT_WAIT
        , TOTAL_ACT_WAIT_TIME
        , LOCK_WAIT_TIME
        , LOG_BUFFER_WAIT_TIME
        , LOG_DISK_WAIT_TIME
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', x'01000000000000001E1906000000000000000000020020170903070924368243', NULL, -2)) AS T
;
--processing time
SELECT
        TOTAL_ACT_TIME - TOTAL_ACT_WAIT_TIME as TOTAL_ACT_EXECUTING
        , PREP_TIME
        , TOTAL_CPU_TIME
        , POOL_READ_TIME
        , POOL_WRITE_TIME
        , DIRECT_READ_TIME
        , DIRECT_WRITE_TIME
        , TOTAL_SECTION_SORT_TIME
        , TOTAL_SECTION_SORT_PROC_TIME
        , WLM_QUEUE_TIME_TOTAL
        , TOTAL_ROUTINE_TIME
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', x'01000000000000001E1906000000000000000000020020170903070924368243', NULL, -2)) AS T
-- Lista todos backups realizados
select  date(timestamp(start_time)) as start_date 
        , time(timestamp(start_time)) as start_time 
        , timestampdiff ( 4, varchar(timestamp(end_time) - timestamp(start_time)) ) as duration 
        , case when objecttype = 'D' 
            then 'Database' 
                else objecttype end as object 
        , case operationtype 
            when 'D' then 'Delta Offline' 
            when 'E' then 'Delta Online' 
            when 'F' then 'Offline' 
            when 'I' then 'Incremental Offline' 
            when 'N' then 'Online' 
            when 'O' then 'Incremental Online' 
            else operationtype end as Type 
        , devicetype 
        , sqlcode 
from sysibmadm.db_history 
where operation='B' 
    and seqnum=1 
order by 1 DESC, 2 DESC
with ur
-- Listar LOBS, LONG e XDA
SELECT substr(rtrim(TABSCHEMA) || '.' || TABNAME,1,65) as TABELA
    ,decimal(float(COALESCE(LOB_OBJECT_L_PAGES,0) * TLOB.TBSP_PAGE_SIZE)/1073741824,5,2) as LOB_GB
    ,decimal(float(COALESCE(LONG_OBJECT_L_PAGES,0) * TLOB.TBSP_PAGE_SIZE)/1073741824,5,2) as LONG_GB
    ,decimal(float(COALESCE(XDA_OBJECT_L_PAGES,0) * TLOB.TBSP_PAGE_SIZE)/1073741824,5,2) as XDA_GB
        FROM TABLE(MON_GET_TABLE(NULL,NULL,-2)) AS A
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TLOB ON A.LONG_TBSP_ID = TLOB.TBSP_ID
        WHERE A.TABSCHEMA NOT IN ('SYSIBM','SYSIBMADM','SYSTOOLS')
select member
      ,agent_id_holding_lk
      ,agent_id
      ,timestampdiff ( 4, varchar(timestamp(current timestamp) - timestamp(LOCK_WAIT_START_TIME)) ) as duration
      ,substr(rtrim(tabschema)||'.'||tabname,1,40) as tabela, lock_mode
  from sysibmadm.lockwaits
    order by 4 desc
-- Queries executadas nos últimos 10 minutos
SELECT
    MEMBER
    ,SECTION_TYPE
    ,EXECUTABLE_ID
    ,NUM_EXECUTIONS
    ,TOTAL_CPU_TIME/(NUM_EXEC_WITH_METRICS+1) as AVG_CPU_TIME
    ,ROWS_READ
    ,ROWS_READ/(ROWS_RETURNED+1) as STMT_IXREF
    ,ROWS_RETURNED/(NUM_EXECUTIONS+1) as ROWS_RETURNED_PER_EXEC
    ,(TOTAL_ACT_TIME - TOTAL_ACT_WAIT_TIME) as TOTAL_ACT_EXECUTING
    ,PREP_TIME
    ,decimal(float(TOTAL_ACT_WAIT_TIME)/float(TOTAL_ACT_TIME+1),9,4) as PCT_WAIT
    ,decimal(float(STMT_EXEC_TIME)/float(NUM_EXECUTIONS+1),10,2) AS AVG_EXEC_TIME
    ,decimal(float(LOCK_WAIT_TIME)/float(TOTAL_ACT_WAIT_TIME+1),9,4) as PCT_LOCK_WAIT_TIME
    ,decimal(float(LOG_BUFFER_WAIT_TIME)/float(TOTAL_ACT_WAIT_TIME+1),9,4) as PCT_LOCK_WAIT_TIME
    ,decimal(float(LOG_DISK_WAIT_TIME)/float(TOTAL_ACT_WAIT_TIME+1),9,4) as PCT_LOCK_WAIT_TIME
    ,DEADLOCKS
    ,LOCK_TIMEOUTS
    ,last_metrics_update
    ,INSERT_TIMESTAMP
    ,decimal(float(SORT_OVERFLOWS)/float(TOTAL_SORTS+1),5,2) * 100 as SORT_OVERFLOW_PCT
    ,decimal(1 - ((float(pool_data_p_reads) + float(pool_xda_p_reads) +
                float(pool_index_p_reads) + float(pool_temp_data_p_reads)
                + float(pool_temp_xda_p_reads) + float(pool_temp_index_p_reads) )
                / (float(pool_data_l_reads) + float(pool_xda_l_reads) + float(pool_index_l_reads) +
                float(pool_temp_data_l_reads) + float(pool_temp_xda_l_reads)
                + float(pool_temp_index_l_reads) + 1)) ,5,2) as stmt_bphr
    ,SUBSTR(STMT_TEXT,1,500) as STATEMENT
      FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, '<modified_within>10</modified_within>', -2)) as T 
        WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME
VALUES NEXTVAL FOR ISSUER_IDENTIFIER_SEQ
-- listar particoes de uma tabela
SELECT
substr(DATAPARTITIONNAME,1,20) DATAPARTITIONNAME, CARD, substr(LOWVALUE,1,30) LOWVALUE, LOWINCLUSIVE,
substr(HIGHVALUE,1,30) HIGHVALUE, HIGHINCLUSIVE FROM SYSCAT.DATAPARTITIONS WHERE
TABSCHEMA =
'SLC_ATL' AND TABNAME = 'DELIVERABLE' ORDER BY
DATAPARTITIONID DESC

-- adicionar particao para tabela
ALTER TABLE SLC_ATL.DELIVERABLE  ADD PARTITION PART_024  STARTING(0,'2017-09-10')  ENDING (0,'2017-09-16')  IN TBS_ATL_4K_D INDEX IN TBS_ATL_4K_I
-- MOSTRA APENAS OS QUE ESTAO ATUALMENTE EM EXECUCAO
select
    	substr(rtrim(TABSCHEMA) || '.' || TABNAME,1,35) as TABELA
      ,reorg_phase
      ,reorg_max_phase
      ,reorg_current_counter
      ,reorg_max_counter
      ,reorg_status
      ,reorg_completion
      ,timestampdiff ( 4, varchar(timestamp(current timestamp) - timestamp(REORG_START)) ) as reorg_duration
      ,timestampdiff ( 4, varchar(timestamp(current timestamp) - timestamp(REORG_PHASE_START)) ) as phase_duration
      ,dbpartitionnum
      ,substr(reorg_type, 1, 60) as reorg_type
     from sysibmadm.snaptab_reorg
	 	   where reorg_status <> 'COMPLETED'
	 	   
-- ESSE NAO RETORNA INFORMAÇÕES DA TABELA, INICIO, ETC!!!!!!!
select member
  ,agent_id
  ,STMT_START
  ,STMT_STOP
  ,substr(STMT_TEXT,1,150) STMT_TEXT_TRUNC
    from table(SNAP_GET_STMT('',-2)) where STMT_OPERATION = 'REORG'
      order by member;
select member
  ,agent_id
  ,STMT_START
  ,STMT_STOP
  ,substr(STMT_TEXT,1,150) STMT_TEXT_TRUNC
    from table(SNAP_GET_STMT('',-2)) where STMT_OPERATION IN ('RUNSTATS','REORG')
      order by member;
-- Encontra todos agent_id que estão realizando RUNSTATS no momento
select agent_id from sysibmadm.snapstmt where STMT_OPERATION = 'RUNSTATS';


select member
  ,agent_id
  ,STMT_START
  ,STMT_STOP
  ,substr(STMT_TEXT,1,150) STMT_TEXT_TRUNC
    from table(SNAP_GET_STMT('',-2)) where STMT_OPERATION = 'RUNSTATS'
      order by member;
-- Pegar tamanhos de todas as tabelas
-- TA CAGADO PARA PURESCALE, CONSERTAR UM DIA
SELECT
	A.MEMBER
	,substr(rtrim(TABSCHEMA) || '.' || TABNAME,1,65) as TABELA
    ,decimal(float(COALESCE(DATA_OBJECT_L_PAGES,0) * TDATA.TBSP_PAGE_SIZE)/1073741824,5,2) as DADOS_GB
    ,decimal(float(COALESCE(INDEX_OBJECT_L_PAGES,0) * TINDEX.TBSP_PAGE_SIZE)/1073741824,5,2) as INDICE_GB
    ,decimal(float((COALESCE(LOB_OBJECT_L_PAGES,0) + COALESCE(LONG_OBJECT_L_PAGES,0) + COALESCE(XDA_OBJECT_L_PAGES,0)) * TLOB.TBSP_PAGE_SIZE)/1073741824,5,2) as LOB_GB
    ,decimal(float((COALESCE(DATA_OBJECT_L_PAGES,0)*TDATA.TBSP_PAGE_SIZE) + (COALESCE(INDEX_OBJECT_L_PAGES,0)*TINDEX.TBSP_PAGE_SIZE) + ((COALESCE(LOB_OBJECT_L_PAGES,0) + COALESCE(LONG_OBJECT_L_PAGES,0) + COALESCE(XDA_OBJECT_L_PAGES,0))*TLOB.TBSP_PAGE_SIZE)) / 1073741824,5,2) as TOTAL_GB
        FROM TABLE(MON_GET_TABLE(NULL,NULL,-2)) AS A
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TDATA ON A.TBSP_ID = TDATA.TBSP_ID AND A.MEMBER = TDATA.MEMBER
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TINDEX ON A.INDEX_TBSP_ID = TINDEX.TBSP_ID AND A.MEMBER = TINDEX.MEMBER
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TLOB ON A.LONG_TBSP_ID = TLOB.TBSP_ID AND A.MEMBER = TLOB.MEMBER
        WHERE A.TABSCHEMA NOT IN ('SYSIBM','SYSIBMADM','SYSTOOLS')
            AND A.TABNAME IN ('')
            ORDER BY 5 DESC
            
SELECT
	substr(rtrim(A.TABSCHEMA) || '.' || A.TABNAME,1,65) as TABELA
    ,decimal(float(SUM(COALESCE(A.DATA_OBJECT_L_PAGES,0)) * AVG(TDATA.TBSP_PAGE_SIZE))/1073741824,5,2) as DADOS_GB
    ,decimal(float(SUM(COALESCE(A.INDEX_OBJECT_L_PAGES,0)) * AVG(TINDEX.TBSP_PAGE_SIZE))/1073741824,5,2) as INDICE_GB
    ,decimal(float((SUM(COALESCE(A.LOB_OBJECT_L_PAGES,0)) + SUM(COALESCE(A.LONG_OBJECT_L_PAGES,0)) + SUM(COALESCE(A.XDA_OBJECT_L_PAGES,0))) * AVG(TLOB.TBSP_PAGE_SIZE))/1073741824,5,2) as LOB_GB
    ,decimal(float((SUM(COALESCE(A.DATA_OBJECT_L_PAGES,0))*AVG(TDATA.TBSP_PAGE_SIZE)) + (SUM(COALESCE(A.INDEX_OBJECT_L_PAGES,0))*AVG(TINDEX.TBSP_PAGE_SIZE)) + ((SUM(COALESCE(A.LOB_OBJECT_L_PAGES,0)) + SUM(COALESCE(A.LONG_OBJECT_L_PAGES,0)) + SUM(COALESCE(A.XDA_OBJECT_L_PAGES,0)))*AVG(TLOB.TBSP_PAGE_SIZE))) / 1073741824,5,2) as TOTAL_GB
        FROM TABLE(MON_GET_TABLE(NULL,NULL,-2)) AS A
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TDATA ON A.TBSP_ID = TDATA.TBSP_ID AND A.MEMBER = TDATA.MEMBER
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TINDEX ON A.INDEX_TBSP_ID = TINDEX.TBSP_ID AND A.MEMBER = TINDEX.MEMBER
            JOIN TABLE(MON_GET_TABLESPACE(NULL,-2)) TLOB ON A.LONG_TBSP_ID = TLOB.TBSP_ID AND A.MEMBER = TLOB.MEMBER
        WHERE A.TABSCHEMA NOT IN ('SYSIBM','SYSIBMADM','SYSTOOLS')
			GROUP BY A.TABSCHEMA, A.TABNAME
            ORDER BY 5 DESC
select 
	distinct substr(a.tbsp_name,1,50) as tbsp_name
	,a.tbsp_type as Type
	,(a.tbsp_total_size_kb / 1048576 ) as size_gb
		from sysibmadm.tbsp_utilization as a
			left join syscat.tablespaces as b
				on a.TBSP_ID = b.TBSPACEID
			where b.sgid in (3,4)
				order by 3 desc
SELECT MEMBER
    ,UTILITY_ID
    ,APPLICATION_HANDLE
    ,timestampdiff ( 4, varchar(timestamp(current timestamp) - timestamp(UTILITY_START_TIME)) ) as DURATION
    ,UTILITY_TYPE
    ,utility_operation_type
    ,OBJECT_TYPE
    ,substr(rtrim(OBJECT_SCHEMA) || '.' || OBJECT_NAME,1,65) as OBJECT
    FROM TABLE(mon_get_utility(-2))