MustGather : How to collect available data to troubleshoot the SQL wrong result issue ?
Yi Lei Wang 2700069G84 Visits (6514)
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.
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
1> db2 ".db2service (Problematic SQL statement) "
2> collect sqll
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
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:
2)Create the EXPLAIN tables
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
- set event monitor <eventmon_name> state 1;
5) Enable collection of section data
- call wlm_
6)Run the problematic SELECT sqls
- select appl_id, uow_id, activity_id, STMT_TEXT from
8)The statement in step 7 will give you the appl_id, uow_id and
- call "exp
1) db2 update db cfg for sample using section_actuals base
6)db2 "select count(*) from test"
8)db2 "call expl