BigSQL, useful commands - stanislawbartkowski/wikis GitHub Wiki

BigSQL 5, knowledge centre https://www.ibm.com/support/knowledgecenter/en/SSCRJT_5.0.4/com.ibm.swg.im.bigsql.welcome.doc/doc/welcome.html
DB2 11.1 https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.welcome.doc/doc/welcome.html
DB2, catalog views https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0011297.html
DB2, monitoring views https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/c0053963.html

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0051495.html

db2pd -mempools
db2pd -memsets
db2 "select substr(DB_NAME,1,10) as DBNAME, MEMORY_SET_TYPE, MEMORY_POOL_TYPE, MEMORY_POOL_ID, APPLICATION_HANDLE, EDU_ID, MEMORY_POOL_USED, MEMORY_POOL_USED_HWM from table(MON_GET_MEMORY_POOL(null,null,member number))"
db2 "select SUM(MEMORY_POOL_USED)/1000 as TOT_MEMORY_USED_MB from table(MON_GET_MEMORY_POOL(null,null,0))"

db2 "select * from syscat.dbauth"
db2 "select * from sysibmadm.privileges"
db2 "select * from sysibmadm.privileges where authidtype = 'G'"
db2 "select * from sysibmadm.privileges where authidtype = 'R'"
db2 "SELECT AUTHID, PRIVILEGE, OBJECTNAME, OBJECTSCHEMA, OBJECTTYPE FROM SYSIBMADM.PRIVILEGES"
db2 "select * from sysibmadm.privileges where objectname=''"
db2 "select * from syscat.tabauth where tabname='table name'"
db2 "selectarchar(grantor,20), varchar(tabschema,20), varchar(grantee,35), granteetype, controlauth, alterauth, deleteauth, insertauth, selectauth from syscat.tabauth where tabname = 'table name'"

db2look -d bigsql -xd

SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('user name'))

db2 "select * from syscat.roles"
db2 "select * from SYSCAT.ROLEAUTH"

db2 "select dtype, varchar(owner,20), btype, varchar(bschema,20), varchar(bname,35) from syscat.tabdep where tabname='table name'"

db2 "SELECT * FROM SYSCAT.INVALIDOBJECTS"

db2 "SELECT 'DROP VIEW', TRIM(OBJECTSCHEMA) || '.' || TRIM(OBJECTNAME) || ';' FROM SYSCAT.INVALIDOBJECTS WHERE objecttype = 'V'"

db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, 'SYSIBMADM', NULL)"

db2 "SELECT * FROM table(SYSHADOOP.LOG_ENTRY('BSL-4-3affb758a', 30, 30))"

select rtrim(tabschema) || '.' || tabname, tbspace from syscat.tables where tbspaceid > 1 and tbspace <> 'SYSTOOLSPACE'

db2 "select * from syscat.schemata"

db2 "SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWNAME = 'view name' and viewschema = 'view schema'"

db2 "SELECT VARCHAR(TABSCHEMA,20),VARCHAR(OWNER,20) FROM SYSCAT.TABLES WHERE TABNAME = 'table name'"

db2 "SELECT location FROM SYSHADOOP.HCAT_TABLEPARTS WHERE TABNAME='table name'"

db2 "select * from syshadoop.hcat_tables where tabname = 'table name'"
db2 "select varchar(PROP_NAME,30),varchar(prop_value,50) from syshadoop.hcat_tableprops where tabname = 'table name'"

db2 "select * from SYSHADOOP.HCAT_COLUMNS where tabname = 'table name'"

db2 "select varchar(col_name,20),varchar(col_type,20),partition from SYSHADOOP.HCAT_COLUMNS where tabname = 'table name'"

db2look -d bigsql -z (table name) -nofed -e -x

db2look -d bigsql -z GMCO_CSA -v (view name) -nofed -e -x

db2 "SELECT * FROM TABLE(MON_GET_QUEUE_STATS('','','',-1))"
db2 "SELECT VARCHAR(THRESHOLD_NAME,20) AS THRESHOLD_NAME,THRESHOLD_PREDICATE,QUEUE_SIZE_CURRENT,QUEUE_SIZE_TOP,THRESHOLD_MAX_CONCURRENCY FROM TABLE(MON_GET_QUEUE_STATS('','','',-1))"

db2 "select varchar(WORKCLASSNAME,30),type,cast(value1 as integer),cast(value2 as integer) from SYSCAT.WORKCLASSATTRIBUTES"

$BIGSQL_HOME/libexec/sched internal getPartitionEliminationInfo 2>/dev/null
/usr/ibmpacks/bigsql/4.2.0.0/bigsql/libexec/sched internal getPartitionEliminationInfo

db2 "CALL SYSHADOOP.HCAT_CACHE_SYNC ('bigsql')"

db2 +w "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 WHERE 150000 < query_cost_estimate ORDER BY query_cost_estimate DESC"

db2 +w "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"

db2 "select APPLICATION_HANDLE,ACTIVITY_STATE, ROWS_READ, MEMBER, VARCHAR(STMT_TEXT,3500) AS STMT_TEXT, STMT_EXEC_TIME FROM TABLE(MON_GET_ACTIVITY(NULL, -2))"
db2 +w "select APPLICATION_HANDLE,VARCHAR(ACTIVITY_STATE,10), ROWS_RETURNED,ROWS_READ, MEMBER, VARCHAR(STMT_TEXT,50) AS STMT_TEXT FROM TABLE(MON_GET_ACTIVITY(NULL, -2)) where application_handle = app number"

db2 +w "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)) where application_handle = app number ORDER BY MEMBER"

db2 +w "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)) WHERE MEMBER=0"

db2 +w "select INSERT_TIMESTAMP,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_PKG_CACHE_STMT(NULL, NULL,NULL,NULL)) WHERE MEMBER=0"

db2 "select application_handle, application_name, application_id, member, rows_read from table(sysproc.mon_get_connection(sysproc.mon_get_application_handle(), -1)) as conn"

db2 "SELECT DISTINCT PROCEDURE_NAME from SYSTOOLS.ADMIN_TASK_LIST"
db2 "SELECT TASKID, STATUS, SQLCODE, SQLSTATE, RC,VARCHAR( SQLERRM( 'SQL' || CHAR( ABS(SQLCODE) ),SQLERRMC, x'FF', 'en_US', 1 ), 256) AS MSG_TXT FROM SYSTOOLS.ADMIN_TASK_STATUS"

db2 "select * from table(MON_GET_LOCKS(NULL,-2))"

db2 "SELECT * FROM TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2))"

Identify the agent

db2 list application show detail | grep Lock

Identify the owner of the lock

db2 get snapshot for locks for application agentid

hadoop jar /var/iophome/bigsql/eh2krjc/parquet-tools-1.6.0-IBM-7.jar schema -d hdfs://(parquet HDFS file).parquet

db2pd -dbptnmem
db2 select * from syscat.bufferpools
db2pd -db bigsql -bufferpools
db2mtrk -i -d -a -r 10

db2 "RUNSTATS ON TABLE (table name with schema) WITH DISTRIBUTION"

db2 "SELECT * FROM SYSSTAT.COLUMNS WHERE TABNAME='table name'"

db2 "SELECT VARCHAR(BP_NAME,20), DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT,XDA_HIT_RATIO_PERCENT,COL_HIT_RATIO_PERCENT FROM SYSIBMADM.MON_BP_UTILIZATION"

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0056919.html

db2 list applications
db2 "force application (number)"

bigsql status -failoverController
db2 get db cfg for BIGSQL | grep -i hadr

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"
db2 "explain plan for <query>"
db2exfmt -d bigsql -1

db2 UPDATE DBM CFG USING DIAGLEVEL 4

Important: reduce the debug log level to default 3 when the problem is sorted, otherwise you can end up with "out of space" problem.

select d.bschema SCHEMA, p.routinename PROCEDURE, d.bname PACKAGE from sysibm.sysdependencies d, sysibm.sysroutines p where d.dtype = 'F' and d.btype = 'K' and p.specificname = d.dname and p.routineschema = d.dschema AND p.routinename='SP_NAME'

db2 "select sectno, text from syscat.statements where pkgschema='SCHEMA_NAME' and pkgname='PACKAGE_NAME'"

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.explain.doc/doc/t0059594.html

db2 "call EXPLAIN_FROM_CATALOG( 'PACKAGE_SCHEMA_NAME', 'PACKAGE_NAME', ' ', SECTION_NUMBER, '', ?, ?, ?, ?, ? )"

db2exfmt -d DATABASE_NAME -g TIC -w % -s PACKAGE_SCHEMA_NAME -n PACKAGE_NAME -# 0 -e DB2INST1 -o exfmt.out