IBM Support

MustGather : How to collect available data to troubleshoot the SQL wrong result issue ?

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

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm13286569