Technical Blog Post
Abstract
MustGather : How to collect available data to troubleshoot the SQL wrong result issue ?
Body
Usually , we will suggest customer to collect following data :
1. db2support with catalog information
db2support -o db2support.zip -d <dbname> -sf ./problematic.sql -cl 1
2. Section dump
We prefer to use first method , we can choose method #2 if the EUD id is not easy to identify for the fast complete SQL statement.
Method #1:
1> Run problematic statement
2> Find out the corresponding EDU ID
3> db2pd -dump <eduid># We will have file with name like ..dump.bin generated under DIAGPATH
Method #2:
1> db2 ".db2service (Problematic SQL statement) "
2> collect sqllib/db2dump/serv* files
3. Section actuals for both correct and wrong situations
We can use db2caem tool to generate section actuals ,it automates the procedure of creating an activity event monitor. See following link for details : http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0057282.html
Specific command : db2caem –d <dbname> –st "(put problematic SQL here )"
While ,for a partitioned database environment, the table space should exist on all the database partitions where the SQL statement of interest will be run. otherwise , we will not collect the data successfully .
db2caem –d <dbname> –st "(put problematic SQL here )" -tbspname (tablespace_name)
4. db2trace if it's possible
Additional comments :
The other complex way to collect section actuals , FYI only .
1)Enable section actuals:
- update db cfg for <dbname> using section_actuals base;
2)Create the EXPLAIN tables
CALL SYSINSTALLOBJECTS( 'EXPLAIN', 'C', NULL, '<schema>' )
3) Create an activity event monitor, by issuing the following statement:
- create event monitor <eventmon_name> for activities write to table;
4) Activate the activity event monitor by executing the following
statement:
- set event monitor <eventmon_name> state 1;
5) Enable collection of section data
- call wlm_set_conn_env(NULL, '<collectactdata> WITH DETAILS, SECTION
AND VALUES</collectactdata>')";
6)Run the problematic SELECT sqls
7) Get the appl_id, uow_id, activity_id separately for above three
statements
- select appl_id, uow_id, activity_id, STMT_TEXT from
activitystmt_<eventmon_name> "
8)The statement in step 7 will give you the appl_id, uow_id and
activity_id for that three queries
- call "explain_from_activity('<appl_id>', <uow_id>, <activity_id>,
'<eventmon_name>','<schema>', ?,?,?,?,?)";
9)
- db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 -o
<db2exfmt_output>
10)
- set event monitor <eventmon_name> state 0;
Example:
1) db2 update db cfg for sample using section_actuals base
2) CALL SYSINSTALLOBJECTS( 'EXPLAIN', 'C', NULL, 'YILEIW' )
3)db2 "create event monitor ACTEVMON for activities write to table"
4)db2 set event monitor ACTEVMON state 1
5)db2 "call wlm_set_conn_env(NULL, '<collectactdata> WITH DETAILS,
SECTION AND VALUES</collectactdata>')"
6)db2 "select count(*) from test"
7)db2 "select appl_id, uow_id, activity_id, STMT_TEXT from
activitystmt_ACTEVMON "
*LOCAL.yileiw.151126090322 22 1 select count(*) from test
8)db2 "call explain_from_activity('*LOCAL.yileiw.151126090322', 22, 1,
'ACTEVMON','YILEIW', ?,?,?,?,?)"
9) db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o db2exfmt.out
10) db2 set event monitor ACTEVMON state 0
UID
ibm13286569