Skip to content

BigSQL, useful commands

Jump to bottom
stanislawbartkowski edited this page Mar 20, 2019 · 29 revisions

Links

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

DB2, memory configuration

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 privileges

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'"

All privileges

db2look -d bigsql -xd

Group membership, how BigSQL resolves it

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

Roles

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

Dependency

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

Invalid objects

db2 "SELECT * FROM SYSCAT.INVALIDOBJECTS"

Output a list of invalid views

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

Revalidate objects

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

Extract BigSQL detailed error message

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

Query to discover tables managed by DB2, not Hive/HBase tables

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

List of database schemas

db2 "select * from syscat.schemata"

Body of the view

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

Discover the owner of the table

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

Partitions for hive table

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

Details on Hive table

db2 "select * from syshadoop.hcat_tables where tabname = 'table name'"

Columns in Hive table

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

Discover the partitioning column in Hive table

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

Table schema

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

View schema

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

Workflow queue

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

Treshold for long queries

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

Partition elimination

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

Reset Hive table cache

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

High cost queries currently running

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"

All queries currently running

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"

Current activity

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"

Current activity including some metrics

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"

Queries from statement cache (historical)

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"

Application handle for current connection

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"

Hive-BigSQL synchronization

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"

All database locks

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

Applications waiting

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

Identify the lock

Identify the agent

db2 list application show detail | grep Lock
Identify the owner of the lock
db2 get snapshot for locks for application agentid

Parquet schema

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

Memory statistics and info

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

Collect statistics

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

Check statistics

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

Clone this wiki locally