DB2 useful commands

List of containers in Storage Groups

SELECT VARCHAR(STORAGE_GROUP_NAME, 30) AS STOGROUP, VARCHAR(DB_STORAGE_PATH, 40) AS STORAGE_PATH FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T

Change transaction log size

db2 update db cfg for using logsecond 124
db2 update db cfg for using logprimary 125

Stop DB2 forcing off

db2stop force

Workload Management

SELECT * FROM SYSCAT.SERVICECLASSES
SELECT * FROM SYSCAT.THRESHOLDS
SELECT * FROM SYSCAT.WORKLOADS
SELECT * FROM TABLE(MON_GET_QUEUE_STATS('','','',-1))

All queries currently running

SELECT application_handle,CHAR(activity_state, 10) AS activity_state,CHAR(REGEXP_REPLACE(REGEXP_REPLACE(stmt_text,'\n|\r|\t',' ',1,0,'c'),' +',' ',1,0,'c'),150) AS stmt_text,query_cost_estimate FROM sysibmadm.mon_current_sql ORDER BY query_cost_estimate DESC

Current connections and activities

select APPLICATION_HANDLE,VARCHAR(ACTIVITY_STATE,10), ROWS_RETURNED,ROWS_READ, MEMBER, EXT_TABLE_READ_VOLUME,EXT_TABLE_RECV_VOLUME,EXT_TABLE_RECVS_TOTAL,EXT_TABLE_RECV_WAIT_TIME,FCM_TQ_RECV_WAIT_TIME,FCM_RECV_WAIT_TIME,VARCHAR(STMT_TEXT,50) AS STMT_TEXT FROM TABLE(MON_GET_ACTIVITY(NULL, -2))

Tablespaces and containers

db2 list tablespaces
db2 list tablespace containers for 2 (container id)
db2 list tablespaces show detail

Check statistics

db2 "SELECT stats_time FROM SYSCAT.TABLE WHERE TABNAME='table_name'"