Question

这条sql语句太长了~是什么意思啊~新手求助

db2 "select timestampdiff(2,char(s.STMT_STOP - s.STMT_START)), substr(s.STMT_TEXT,1,1000),a.APPL_ID,a.PRIMARY_AUTH_ID ,a.APPL_STATUS,a.STATUS_CHANGE_TIME ,s.SNAPSHOT_TIMESTAMP,s.DB_NAME,s.AGENT_ID,s.ROWS_READ,s.ROWS_WRITTEN,s.NUM_AGENTS,s.AGENTS_TOP,s.STMT_TYPE,s.STMT_OPERATION,s.SECTION_NUMBER,s.QUERY_COST_ESTIMATE,s.QUERY_CARD_ESTIMATE,s.DEGREE_PARALLELISM,s.STMT_SORTS,s.TOTAL_SORT_TIME,s.SORT_OVERFLOWS,s.INT_ROWS_DELETED,s.INT_ROWS_UPDATED,s.INT_ROWS_INSERTED,s.FETCH_COUNT,s.STMT_START,s.STMT_STOP,s.STMT_USR_CPU_TIME_S,s.STMT_USR_CPU_TIME_MS,s.STMT_SYS_CPU_TIME_S,s.STMT_SYS_CPU_TIME_MS,s.STMT_ELAPSED_TIME_S,s.STMT_ELAPSED_TIME_MS,s.BLOCKING_CURSOR,s.STMT_NODE_NUMBER,s.CURSOR_NAME,s.CREATOR,s.PACKAGE_NAME,s.CONSISTENCY_TOKEN,s.PACKAGE_VERSION_ID,s.POOL_DATA_L_READS,s.POOL_DATA_P_READS,s.POOL_INDEX_L_READS,s.POOL_INDEX_P_READS,s.POOL_XDA_L_READS,s.POOL_XDA_P_READS,s.POOL_TEMP_DATA_L_READS,s.POOL_TEMP_DATA_P_READS,s.POOL_TEMP_INDEX_L_READS,s.POOL_TEMP_INDEX_P_READS,s.POOL_TEMP_XDA_L_READS,s.POOL_TEMP_XDA_P_READS,s.DBPARTITIONNUM from sysibmadm.snapstmt s, sysibmadm.SNAPAPPL_INFO a where a.agent_id=s.agent_id and s.stmt_text is not null " >> $LOG_DIR/sql_mon.${DD}.txt

Answer 1

db2 "select

timestampdiff(2,char(s.STMT_STOP - s.STMT_START)), --算时间差(db2没怎么用过，字面理解)
substr(s.STMT_TEXT,1,1000),----截取字段

a.APPL_ID,a.PRIMARY_AUTH_ID ,a.APPL_STATUS,a.STATUS_CHANGE_TIME ,s.SNAPSHOT_TIMESTAMP,s.DB_NAME,s.AGENT_ID,s.ROWS_READ,s.ROWS_WRITTEN,s.NUM_AGENTS,s.AGENTS_TOP,s.STMT_TYPE,s.STMT_OPERATION,s.SECTION_NUMBER,s.QUERY_COST_ESTIMATE,s.QUERY_CARD_ESTIMATE,s.DEGREE_PARALLELISM,s.STMT_SORTS,s.TOTAL_SORT_TIME,s.SORT_OVERFLOWS,s.INT_ROWS_DELETED,s.INT_ROWS_UPDATED,s.INT_ROWS_INSERTED,s.FETCH_COUNT,s.STMT_START,s.STMT_STOP,s.STMT_USR_CPU_TIME_S,s.STMT_USR_CPU_TIME_MS,s.STMT_SYS_CPU_TIME_S,s.STMT_SYS_CPU_TIME_MS,s.STMT_ELAPSED_TIME_S,s.STMT_ELAPSED_TIME_MS,s.BLOCKING_CURSOR,s.STMT_NODE_NUMBER,s.CURSOR_NAME,s.CREATOR,s.PACKAGE_NAME,s.CONSISTENCY_TOKEN,s.PACKAGE_VERSION_ID,s.POOL_DATA_L_READS,s.POOL_DATA_P_READS,s.POOL_INDEX_L_READS,s.POOL_INDEX_P_READS,s.POOL_XDA_L_READS,s.POOL_XDA_P_READS,s.POOL_TEMP_DATA_L_READS,s.POOL_TEMP_DATA_P_READS,s.POOL_TEMP_INDEX_L_READS,s.POOL_TEMP_INDEX_P_READS,s.POOL_TEMP_XDA_L_READS,s.POOL_TEMP_XDA_P_READS,s.DBPARTITIONNUM -------s表 a表 其他字段

from

sysibmadm.snapstmt s, sysibmadm.SNAPAPPL_INFO a ------用到的两个表 缩写成s a

where

a.agent_id=s.agent_id ----- s a 两表关联条件
and
s.stmt_text is not null " -----s 表中过滤调件

>> $LOG_DIR/sql_mon.${DD}.txt -----查询结果输出到文本文档

追问

主要是用这个两个表做什么的啊~sysibmadm.snapstmt s, sysibmadm.SNAPAPPL_INFO不太明白想要的是什么结果

追答

如果是db2的系统表就找找相关资料看看里面各字段什么意思 如果是业务系统里面的表 找找开发人员 问问字段意思 不就清楚了啊