Troubleshoot query performance in DB2 for Linux, UNIX, and Windows

Enhancements in V9.7

Beginning with IBM® DB2® for Linux®, UNIX®, and Windows® V9.7 Fix Pack 1, the DB2 database manager has two new features for advanced query tuning: section explain and section actuals. This article walks you through detailed examples illustrating how to use these features to debug query plans and to get the most out of the DB2 cost-based optimizer.

Samir Kapoor, DB2 UDB Advanced Support Analyst, IBM  

Samir Kapoor photoSamir Kapoor is an IBM Certified Advanced Technical Expert for DB2. Samir currently works with the DB2 UDB Advanced Support -- Down system division (DSD) team and has in-depth knowledge in the engine area.



Ani Patel (anipatel@ca.ibm.com), DB2 Advance Support Specialist, IBM

Photo of Ani PatelAni Patel currently works at IBM Toronto Lab as a level 2 DB2 Advance Support Analyst. He has been working with DB2 support for over 7 years. His areas of expertise include storage, backup, recovery, and logging. He holds a Bachelor's Degree in Computer Engineering from Ryerson University in Toronto, Canada.



Tao Wang (taoewang@ca.ibm.com), DB2 Advanced Technical Support, IBM

Author Photo: Tao WangTao Wang is an IBM Certified Advanced Database Administrator - DB2 for Linux, UNIX, and Windows. Tao currently works with the DB2 Advanced Support - Down System Division (DSD) team and has in-depth knowledge in the engine area.



09 June 2011

Also available in Chinese

Introduction

Starting with V9.7 Fix Pack 1, the DB2 for Linux, UNIX, and Windows database manager has the ability to perform an explain directly from the contents of a runtime section. This functionality is known as a section explain. Runtime statistics can also be gathered for access plan operators during the execution of a section. These statistics are referred to as section actuals. Section actuals permit you to see what the real cardinalities are vs. estimated cardinalities in an access plan.

In this article, we provide step-by-step examples showing how to use various methods to get section explain information. In addition, we include a detailed example of how you can gather section actual information to see real cardinality vs. the cardinality estimated by the optimizer. Finally, we discuss a few enhancements to the db2support utility for gathering optimizer-related information. This is useful to know when you're gathering optimizer-related information for IBM DB2 Support.

For more detailed information on section explain and section actuals, please visit the DB2 Information Center (see Resources). This article will focus on step-by-step examples of how to use this functionality.


Setting up the environment and creating the test database

Before we begin, we need to create an environment where a query executes for a few minutes. The query used in the examples executed for close to two minutes in our testing environment. Furthermore, a single-partition environment on AIX was used:

Listing 1. OS level
Operating system is: AIX 64BIT 5.3.0.0

The output from the dblevel command shows the version of DB2 we used in our testing.

Listing 2. db2level
Server is running EE.
DB21085I  Instance "skapoor" uses "64" bits and DB2 code release "SQL09073"
with level identifier "08040107".
Informational tokens are "DB2 v9.7.0.3", "s101006", "IP23212", and Fix Pack
"3a". Product is installed at "/home/skapoor/sqllib".

Space requirement

Up to 20 GB of disk space is required for data and log files to recreate all test cases described in this article. Close to 13 GB of space is allotted for log files and the rest to data. Circular logging was used, with 100 primary logs allocated.

Listing 3. Database configuration for logs
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150

You can reduce the amount of disk space required for log files with some slight modifications to the scripts in order to populate data in the fact table. We discuss this later.

Configuration

The main database configuration parameter settings that affect the query execution plan were set as follows:

Listing 4. Configuration
Parallelism:          None
CPU Speed:            3.857478e-07
Comm Speed:           0
Buffer Pool size:     17083
Sort Heap size:       1241
Database Heap size:   2277
Lock List size:       7020
Maximum Lock List:    97
Average Applications: 1
Locks Available:      217900
SQL Type:             Dynamic
Optimization Level:   5
Blocking:             Block All Cursors
Isolation Level:      Cursor Stability
STMTHEAP:             8192

Main query for the test

Here is the query we use to test the various methods discussed in this article. This query performs an outer join to compare the sales information for 10 particular stores for January and November.

Listing 5. Main query [Query1.sql]
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
     D.MONTH AS MONTH, 
     S.STORE_ID AS STORE_ID,
     S.DISTRICT AS DISTRICT,
     S.REGION AS REGION,
     SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F1,
     SKAPOOR.DATE_DIM D,
     SKAPOOR.PRODUCT_DIM P,
     SKAPOOR.STORE_DIM S
     
  WHERE
     P.MODEL LIKE '%model%' AND
     F1.DATE_ID=D.DATE_ID AND
     F1.PRODUCT_ID=P.PRODUCT_ID AND
     F1.STORE_ID=S.STORE_ID AND
     F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND
     F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND  
     D.MONTH = 1 

  GROUP BY
     S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
     
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT 
     D1.MONTH AS MONTH,
     S1.STORE_ID AS STORE_ID,
     S1.DISTRICT AS DISTRICT,
     S1.REGION AS REGION,
     SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F2,
     SKAPOOR.DATE_DIM D1,
     SKAPOOR.PRODUCT_DIM P1,
     SKAPOOR.STORE_DIM S1

  WHERE
     P1.MODEL LIKE '%model%' AND
     F2.DATE_ID=D1.DATE_ID AND
     F2.PRODUCT_ID=P1.PRODUCT_ID AND
     F2.STORE_ID=S1.STORE_ID AND
     F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND
     F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND  
     D1.MONTH=11

  GROUP BY
     S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 

SELECT 
     A.*, 
     B.*
FROM
     TMP1 A LEFT OUTER JOIN TMP2 B ON
       (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

For this query, an index on DATE_ID, STORE_ID would prove beneficial. But for the purposes of illustrating the functionality described here, an index on the fact table on columns DATE_ID, STORE_ID was purposely not created in order for the query to execute for a longer period of time — in our testing environment, for example, for close to two minutes.

Setting up the database

STEP A: We assume you have created a database with the default table spaces. Create the fact and dimension tables as follows. You can adjust the schema name from SKAPOOR to a schema that fits your environment. If you do so, ensure that the query in Listing 5 is updated to reflect the appropriate schema name. You can copy the statements below into a file, TEST1.ddl, and execute it using:

db2 -tvf TEST1.ddl -z test1.log
Listing 6. Setting up the database
-- Replace <path> with your desired path with 20 GB of disk space.
create db section on <path>;

CREATE TABLE "SKAPOOR "."SALES_FACT"  (
    "DATE_ID" DATE , 
    "PRODUCT_ID" INTEGER , 
    "STORE_ID" INTEGER , 
    "QUANTITY" INTEGER , 
    "PRICE" INTEGER , 
    "TRANSACTION_DETAILS" CHAR(100),
    "U_ID" INT )  ; 
   
   
CREATE TABLE "SKAPOOR "."DATE_DIM"  (
    "DATE_ID" DATE NOT NULL , 
    "MONTH" INTEGER , 
    "QUARTER" INTEGER , 
    "YEAR" INTEGER )   ;
    
ALTER TABLE "SKAPOOR "."DATE_DIM" 
 ADD PRIMARY KEY
  ("DATE_ID");


CREATE TABLE "SKAPOOR "."PRODUCT_DIM"  (
    "PRODUCT_ID" INTEGER NOT NULL , 
    "PRODUCT_DESC" CHAR(20) , 
    "MODEL" CHAR(10) , 
    "MAKE" CHAR(10) )   ;


ALTER TABLE "SKAPOOR "."PRODUCT_DIM" 
 ADD PRIMARY KEY
  ("PRODUCT_ID");


CREATE TABLE "SKAPOOR "."STORE_DIM"  (
    "STORE_ID" INTEGER NOT NULL , 
    "LOCATION" CHAR(15) , 
    "DISTRICT" CHAR(15) , 
    "REGION" CHAR(15) )  ;

ALTER TABLE "SKAPOOR "."STORE_DIM" 
 ADD PRIMARY KEY
  ("STORE_ID");


COMMIT WORK;

CONNECT RESET;

STEP B: Once you create the tables, insert data into the three dimension tables as follows and adjust the schema to fit your environment:

Listing 7. Populating the DATE_DIM table
db2 -td@ -vf date_insert.txt -z date_insert.log
Listing 8. Populating the PRODUCT_DIM table
db2 -td@ -vf product_insert.txt -z product_insert.log
Listing 9. Populating the STORE_DIM table
db2 -td@ -vf store_insert.txt -z store_insert.log

The DATE_DIM table is populated with values from all 365 days in 2010.

Listing 10. Contents of date_insert.txt
connect to SECTION@

begin atomic
  declare cnt INT default 1;
  declare dat DATE default '01/01/2010';
  declare yer INT default 2010;
  declare quart INT default 1;

while (cnt <= 365) do    
    if (int(dat + cnt DAYS)/100) between 201001 and 201003 then
           set quart=1;
    elseif (int(dat + cnt DAYS)/100) between 201004 and 201006 then
           set quart=2;
    elseif (int(dat + cnt DAYS)/100) between 201007 and 201009 then
           set quart=3;
    elseif (int(dat + cnt DAYS)/100) between 201010 and 201012 then
           set quart=4;    
    end if;
    
    insert into SKAPOOR.DATE_DIM values (
 dat + cnt DAYS,
 (int(dat + cnt DAYS)/100) - 201000,
 quart,
 yer
    );
       
    set cnt=cnt+1;
end while;

end@

connect reset@

The PRODUCT_DIM table is populated with 60,000 products.

Listing 11. Contents of product_insert.txt
connect to SECTION@

drop sequence seq1@
drop sequence seq2@

create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@

begin atomic
   declare cnt INT default 1;

   while (cnt < 60001) do
 insert into SKAPOOR.PRODUCT_DIM values (
     nextval for SEQ2,
     'product desc' concat char(nextval for SEQ1),
            'model ' concat char(integer(rand()*1000)),
     'maker ' concat char(integer(rand()*500))
 );
  set cnt=cnt+1;
     end while;
end@

drop sequence seq1@
drop sequence seq2@

connect reset@

The STORE_DIM table is populated with 201 stores.

Listing 12. Contents of store_insert.txt
connect to SECTION@

drop sequence seq2@

create sequence seq2 as integer start with 0 increment by 1@

begin atomic
    declare cnt INT default 1;

    while (cnt < 202) do
       insert into SKAPOOR.STORE_DIM values (
     nextval for SEQ2,
     'location' concat char(integer(rand()*500)),
     'district' concat char(integer(rand()*10)),
     'region' concat char(integer(rand()*5))
       );
       set cnt=cnt+1;

    end while;
end@

drop sequence seq2@

connect reset@

STEP C: Insert data into the SALES_FACT table. Adjust the schema to fit your environment. It took approximately 1 1/2 hours to insert data into the fact table in our test environment.

Listing 13. Populating the SALES_FACT table
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log
Listing 14. Contents of sales_fact_insert.ddl
connect to SECTION@

VALUES (CURRENT TIMESTAMP)@

begin atomic

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2010';

   while (cnt <= 365) do    
    
  INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
         (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
          select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
          INT(RAND()*200 + 1), TRANSACTION_DETAILS, U_ID + 1
          from   v
          where  U_ID < 60000)
     select date_id, product_id, store_id, quantity, price, transaction_details, U_ID from v;


     set cnt1 = cnt1 + 1;
     set cnt  = cnt + 1;
   end while;

end@

VALUES (CURRENT TIMESTAMP)@

connect reset@

Note: In Listing 14, the SALES_FACT table is populated in a single transaction, which will require a large amount of disk space for logging. To reduce the impact of logging, you can create a stored procedure and commit the insert in stages.

Listing 15. Alternative method to populate the SALES_FACT table
connect to SECTION@

VALUES (CURRENT TIMESTAMP)@

-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging

create procedure salesFactPopulate()
specific salesFactPopulate
language sql

begin

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2010';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
    (
       values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
      union all
       select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
              INT(RAND()*200 + 1), TRANSACTION_DETAILS, U_ID + 1
         from v
        where U_ID < 60000
    )
    select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS, U_ID from v;

    commit work;

    set cnt1 = cnt1 + 1;
    set cnt=cnt+1;

   end while;

end@

-- populate the SALES_FACT table
invoke salesFactPopulate@

VALUES (CURRENT TIMESTAMP)@

connect reset@

STEP D: We need to explain the queries to view the access plans chosen by the DB2 query optimizer.

To do this, use the explain tool, which requires the existence of the EXPLAIN tables. To create the EXPLAIN tables, perform the following steps:

  1. Change the directory to the location of your sqllib/misc directory. In our test environment, it is "$HOME/sqllib/misc".
  2. Execute db2 connect to SECTION.
  3. Execute db2 -tvf EXPLAIN.DDL.

STEP E: Define appropriate referential integrity constraints between the fact table and the three dimension tables.

STEP Ea:Alter the fact table and create the proper FK relationships with the dimension tables.

Listing 16. Creating foreign key constraints and indexes in the SALES_FACT table
db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log
Listing 17. Contents of the alter_sales_fact.txt file
CONNECT TO SECTION;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CONNECT RESET;

STEP Eb: Collect statistics on all the tables.

The optimizer relies on statistics to properly cost alternative query execution plans (QEPs) and choose the most optimal plan. Before proceeding, we need to collect some statistics.

Listing 18. Collecting statistics on all tables
db2 -tvf runstats.ddl -z runstats.log
Listing 19. Contents of runstats.ddl
CONNECT TO SECTION;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;

STEP Ec:explain and run the full query.

To explain the query, follow the same steps as in STEP 1C:

db2 connect to SECTION
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset

Where the contents of QUERY1.SQL include just the query in Listing 5, ending with a semicolon.

The query execution plan can be viewed using the db2exfmt tool:

db2exfmt -d SECTION -g TIC -w -1 -n % -s % -# 0 -o test1.txt

To measure the initial performance of the query, db2batch was used as follows.

Note: Our test system was idle, with no other activity running, when we ran these tests.

Stop DB2 using db2stop force and restart it using db2start. Confirm that the query runs for close to two minutes or so. You can use db2batch to get the elapsed time information, as follows:

db2batch -d SECTION -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt

The results in our environment were:

* Prepare Time is:       7.278206 seconds
* Execute Time is:     107.729436 seconds
* Fetch Time is:         0.000102 seconds
* Elapsed Time is:     115.007744 seconds (complete)

Section explain

The advantage of a section explain over a traditional explain using an explain statement is that the explain statement will recompile the statement being explained. If the compilation environment or table statistics have changed when the explain statement is issued, the compiler may generate a different access plan.

Whereas, a section explain will always provide the exact access plan that was executed, since the access plan is reconstructed directly from the executable section. A section explain is similar to the functionality provided by the db2expln command, but provides a level of detail approaching that provided by the explain statement.

The section explain functionality is accessible through a set of stored procedures. The stored procedures take input parameters used to locate a section (either in memory, catalogs, captured by an event monitor, or provided directly as input) and perform the explain, populating the explain tables similar to the explain statement. The stored procedures output the key fields for the explain instance that was populated in the explain tables.

These key fields can be used as input to existing explain formatting tools, for example db2exfmt, which extract the information from the explain tables and present it in a formatted output.

The following procedures can perform a section explain:

  1. EXPLAIN_FROM_SECTION
  2. EXPLAIN_FROM_ACTIVITY
  3. EXPLAIN_FROM_DATA
  4. EXPLAIN_FROM_CATALOG

Let's look at each one individually with a detailed example.

EXPLAIN_FROM_SECTION

Takes executable ID and location as input, where location is specified by using in-memory package cache or package cache event monitor name. The procedure searches for the section in the given location.

An executable ID uniquely and consistently identifies a section. The executable ID is an opaque binary token generated at the data server for each section that has been executed. The executable ID is used as input to query monitoring data for the section, and to perform a section explain.

explain a statement using package cache or package cache event monitor

Syntax:

>>-EXPLAIN_FROM_SECTION--(--executable_id--,--section_source_type
 >--section_source_name--,--member--,--explain_schema--,->
 >--explain_requester--,--explain_time--,--source_name--,-->
 >--source_schema--,--source_version--)->

executable_id— Uniquely identifies a section to be explained. SQL2032 is returned if null.

section_source_type— Input argument of type CHAR(1) that specifies the source of the section to be explained. Valid values are:

  • M — Section is obtained from the in-memory package cache
  • P — Section is obtained from a package cache event monitor

To find out the executable-id to explain the section, we will use table function mon_get_pkg_cache_stmt in this example.

Example:

One will need two sessions here. In the first session (we will refer to it as SESSION1), one will be executing the query. From the second session (SESSION2), one will execute commands to get executable_id, then run commands to get an explain from section.

SESSION1:

We first issue the main query from one session (SESSION1 in this example) that we would like to see the runtime section explain for.

SESSION2:

While the query is running, from another session (SESSION2 in this example) connect to the database and find out the executable-id associated to the statement. We can see it is:

1. Get executable ID of the running query.

db2 "select executable_id, varchar(stmt_text,50) as stmt_text 
from table(mon_get_pkg_cache_stmt(NULL,NULL,NULL,-1)) as T"

EXECUTABLE_ID                                                        
STMT_TEXT
------------------------------------------------------------------- 
-------------------------------------------------
x'0000000100000000000000000000000100000000000220110412082630627501' 
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1

SQL0445W  Value "WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1" 
has been truncated.  SQLSTATE=01004

Note: The executable ID is unique to each executing query and would be different in your environment.

2. Gather the explain information by calling the explain_from_section procedure:

db2 "call explain_from_section (x'000000010000000000000000000000010000
0000000220110412082630627501','M',NULL,0,'SKAPOOR',?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-04-12-09.16.24.384509

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

The stored procedure output parameters EXPLAIN_REQUESTER, EXPLAIN_TIME, SOURCE_NAME, SOURCE_SCHEMA, and SOURCE_VERSION comprise the key used to look up the information for the section in the explain tables. Use these parameters with any existing explain tools (for example, db2exfmt) to format the explain information retrieved from the section.

3. Generate explain output (see Downloads).

db2exfmt -d sample -1 -o exfmt_explain_from_section.out

Exfmt.out contents:
******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.3
SOURCE_NAME:       SQLC2H21
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2011-04-12-09.16.24.384509
EXPLAIN_REQUESTER: SKAPOOR

4) Examine the contents of exfmt_explain_from_section.out. You will notice a new section added to the plan, as follows:

Explain level:    Explain from section

This tells us that the explain was used to explain the query. Snippet of the plan:

               Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                10
              TBSCAN
              (   2)
            1.55994e+06
                NA
                |
                10
              SORT
              (   3)
            1.55994e+06
                NA  
                |
                10
             HSJOIN<
             (   4)
           1.55994e+06
               NA
             ........

Differences between section explain and explain statement output

The results obtained after issuing a section explain are similar to those collected after running the explain statement. There are slight differences between section explain and explain statement output in explain table and by the implications to the output generated by the db2exfmt utility.

The I/O cost is not collected, along with a few other statistics discussed in the information center that are not collected due to certain performance reasons. Please visit the information center to learn more about which fields are not collected.

EXPLAIN_FROM_ACTIVITY

In some cases, a user may want to capture the access plan for query(s) that have run in the past. To do this, the user can use the WLM_CAPTURE_ACTIVITY_IN_PROGRESS API (discussed further in EXPLAIN_FROM_DATA section) to capture the running job or set up an activity monitor and WLM_SET_CONN_ENV in order to enable collection of activity data and capture section explain information.

In this example, we will be using WLM_SET_CONN_ENV to enable collection of activity data.

The syntax for this section explain procedure is as follows:

>>-EXPLAIN_FROM_ACTIVITY---------------------------------------->

>--(--appl_id--,--uow_id--,--activity_id--,--activity_evmon_name-->,--explain_schema-->

>--,--explain_requester--,--explain_time--,--source_name--,--source_schema-->

>--,--source_version--)->
Table 1. Details of the arguments
NameDirectionData Type
appl_idInputVARCHAR(64)
uow_idInputINTEGER
activity_idInputINTEGER
activity_evmon_nameInputVARCHAR(128)
explain_schemaInputVARCHAR(128)
explain_requesterOutputVARCHAR(128)
explain_timeOutputTIMESTAMP
source_nameOutputVARCHAR(128)
source_schemaOutputVARCHAR(128)
source_versionOutputVARCHAR(64)

The last five arguments of this stored procedure are output parameters. These output parameters can be used to locate EXPLAIN information for the section in the explain tables. Please note that this procedure does not issue COMMIT following the inserts in the explain tables. The caller must perform COMMIT after calling this procedure to save the explain information. User is required EXECUTE permission on the EXPLAIN_FROM_ACTIVITY and INSERT permission on the explain tables.

The following steps should be performed in order to catch the access plan by using EXPLAIN_FROM_ACTIVITY procedure:

  1. Create activity monitor.
  2. Set up Workload Manager env.
  3. Enable activity monitor.
  4. Run the query.
  5. Query the statement activity monitor table to get information for appl_id, uow_id and activity_id.
  6. Call EXPLAIN_FROM_ACTIVITY to populate the explain tables.
  7. Use the db2exfmt tool to generate the access plan.

Note:

  • You must have the explain tables created before you follow this example. Again, you can use EXPLAIN.DDL script located in the ~sqllib/misc/ directory to create the explain tables.

a) Connect to your database.

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) Create activity event monitor.

db2 "CREATE EVENT MONITOR DB2ACTIVITIES FOR ACTIVITIES \
WRITE TO TABLE ACTIVITY (TABLE ACTIVITY_DB2ACTIVITIES IN \
USERSPACE1 PCTDEACTIVATE 100), ACTIVITYSTMT (TABLE \
ACTIVITYSTMT_DB2ACTIVITIES IN USERSPACE1 PCTDEACTIVATE 100), \
ACTIVITYVALS (TABLE ACTIVITYVALS_DB2ACTIVITIES IN USERSPACE1 \
PCTDEACTIVATE 100), CONTROL (TABLE CONTROL_DB2ACTIVITIES IN \
USERSPACE1 PCTDEACTIVATE 100) AUTOSTART"
DB20000I  The SQL command completed successfully.

c) Set up WLM env.

 db2 "call wlm_set_conn_env(null,'<collectactdata>with details, section and \
 	values</collectactdata><collectsectionactuals>base</collectsectionactuals>')"

  Return Status = 0

Note that in this example, section actuals collection is also enabled, which is further explained in the Section Actuals section.

d) Activate the event monitor.

db2 set event monitor db2activities state 1
DB20000I  The SQL command completed successfully.

e) Run the query:

db2 "WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS ( \
	SELECT \
		D.MONTH AS MONTH, \
		S.STORE_ID AS STORE_ID, \
		S.DISTRICT AS DISTRICT, \
		S.REGION AS REGION, \
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT \
	FROM \
		SKAPOOR.SALES_FACT F1, \
		SKAPOOR.DATE_DIM D, \
		SKAPOOR.PRODUCT_DIM P, \
		SKAPOOR.STORE_DIM S \
	WHERE \
		P.MODEL LIKE '%model%' AND \
		F1.DATE_ID=D.DATE_ID AND \
		F1.PRODUCT_ID=P.PRODUCT_ID AND \
		F1.STORE_ID=S.STORE_ID AND \
		F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND \
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND \
		D.MONTH = 1 \
	GROUP BY \
		S.STORE_ID,\
		S.DISTRICT,\
		S.REGION,D.MONTH \
) , \
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS ( \
	SELECT \
		D1.MONTH AS MONTH, \
		S1.STORE_ID AS STORE_ID, \
		S1.DISTRICT AS DISTRICT, \
		S1.REGION AS REGION, \
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT \
	FROM \
		SKAPOOR.SALES_FACT F2, \
		SKAPOOR.DATE_DIM D1, \
		SKAPOOR.PRODUCT_DIM P1, \
		SKAPOOR.STORE_DIM S1 \
	WHERE \
		P1.MODEL LIKE '%model%' AND \
		F2.DATE_ID=D1.DATE_ID AND \
		F2.PRODUCT_ID=P1.PRODUCT_ID AND \
		F2.STORE_ID=S1.STORE_ID AND \
		F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND \
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND \
		D1.MONTH=11 \
	GROUP BY \
		S1.STORE_ID,\
		S1.DISTRICT,\
		S1.REGION,\
		D1.MONTH\
) \
SELECT \
	A.*, B.* \
FROM \
	TMP1 A LEFT OUTER JOIN \
	TMP2 B ON (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT) \
ORDER BY \
	A.AMOUNT_1 DESC, \
	B.AMOUNT_11 DESC"

f) Deactivate the event monitor.

db2 set event monitor db2activities state 0
DB20000I  The SQL command completed successfully.

g) Find the appl_id, uow_id, and activity_id from ACTIVITYSTMT_DB2ACTIVITIES table.

db2 "select substr(appl_id,1,30), uow_id, activity_id, substr(stmt_text,1,15) \
from ACTIVITYSTMT_DB2ACTIVITIES where stmt_text like 'WITH TMP1%'"

1                              UOW_ID      ACTIVITY_ID          4
------------------------------ ----------- -------------------- ---------------
*LOCAL.skapoor.110518172020             49                    1 WITH TMP1 (MONT

  1 record(s) selected.

h) Call the explain_from_activity procedure to populate the explain tables:

db2 "call explain_from_activity('*LOCAL.skapoor.110518172020', 49, 1, \
'DB2ACTIVITIES','SKAPOOR',?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-05-18-14.52.37.654737

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

i) Generate the access plan using db2exfmt. In this example, we'll feed in the EXPLAIN_TIME generated from the output of the stored procedure in step h) above — for example:

db2exfmt -d SECTION -1 -w 2011-05-18-14.52.37.654737 -o exfmt_explain_from_activity.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_data.out.
Executing Connect Reset -- Connect Reset was Successful.

You can further examine the output of file exfmt_explain_from_activity.out in the current working directory.

EXPLAIN_FROM_DATA

The EXPLAIN_FROM_DATA stored procedure populates the explain tables, using executable ID, section, and statement text as its input parameters. The input section information (executable ID, section, and statement text) can be obtained from various sources such as:

  • Activity event monitor
  • Package cache event monitor
  • Catalog tables

Like the other section explain procedures, it stores the explain output in the explain tables for processing using any existing EXPLAIN tools, such as db2exfmt. The optional input or output argument explain_schema can be used to specify the schema for the explain tables where EXPLAIN information should be saved. If no value or NULL is specified for this argument, it will first look for explain tables under current authorization ID, then under SYSTOOLS schema.

The syntax for this section explain procedure is as follows:

>>-EXPLAIN_FROM_DATA-------------------------------------------->
>--(--section--,--stmt_text--,--executable_id--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name-->
>--,--source_schema--,--source_version--)->
Table 2. Details of the arguments
NameDirectionData type
sectionInputBLOB(134M)
stmt_textInputCLOB(2M)
executable_idInputVARCHAR(32)
explain_schemaInput/OutputVARCHAR(128)
explain_requesterOutputVARCHAR(128)
explain_timeOutputTIMESTAMP
source_nameOutputVARCHAR(128)
source_schemaOutputVARCHAR(128)
source_versionOutputVARCHAR(64)

The last five arguments of this stored procedure are output parameters. These output parameters can be used to locate explain information for the section in the explain tables. Please note that this procedure does not issue COMMIT following the inserts in the explain tables. The caller must ensure to perform COMMIT after calling this procedure to save the explain information. User is required EXECUTE permission on the EXPLAIN_FROM_DATA and INSERT permission on the explain tables.

Lets walk through an example on how to use this procedure to generate section explain information. There are five steps involved in generating section explain using this procedure. This method of generating the explain data is very useful when debugging a long-running query.

  1. Prepare to capture the information about the running SQL using one of the three procedure mentioned above. In our example, we will use Activity Event Monitor, which will provide us with the required input arguments.
  2. Run the query you would like to collect section explain for.
  3. Capture activity information for our example query using WLM_CAPTURE_ACTIVITY_IN_PROGRESS.
  4. Run the EXPLAIN_FROM_DATA stored procedure using the information collected in step 3 to generate explain information.
  5. Use the db2exfmt tool to format the explain data generated for the section in step 3.

Note that you must have the explain tables created before this example. You can use EXPLAIN.DDL script located in the ~sqllib/misc/ directory to create the explain tables. Furthermore, please open two sessions for this example: SESSION1 and SESSION2.

From SESSION1:

Step 1— Create the Activity Event Monitor to gather the input arguments for our stored procedure for the query of interest.

a) Connect to your database.

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) Create activity event monitor.

db2 "CREATE EVENT MONITOR mymon FOR ACTIVITIES WRITE TO TABLE AUTOSTART"
DB20000I  The SQL command completed successfully.

c) Activate the event monitor.

db2 "SET EVENT MONITOR mymon STATE 1"
DB20000I  The SQL command completed successfully.

d) Determine the application handle for the current connection using the following command. Application Handle is the third column in this output. Application handle will be used in Step 3 to gather required information to capture activity in progress.

db2 "LIST APPLICATIONS"

Auth Id  Application    Appl.      Application Id                DB       # of
         Name           Handle                                   Name    Agents
-------- -------------- ---------- ----------------------------- -------- -----
SKAPOOR  db2bp          58         *LOCAL.skapoor.110510181205   SECTION  1

In the output above, the application handle is 58.

Step 2— Run the main query in Query1.sql. This is a long-running to make sure you can complete Step 3 in time.

From SESSION2:

Step 3— While the query is running, capture the activity information for the query using the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure.

a) Connect to your database.

db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) Find the UOW ID and Activity ID using the following query by substituting the application handle found in Step 1:

db2 "select APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID from  
TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(NULL, -2)) as 
WLOACTS where APPLICATION_HANDLE=58" 

APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID
-------------------- ----------- -----------
                  58           3           1

  1 record(s) selected.

c) Capture the activity for our main query by the substituting information we have found in previous step in the following query:

db2 "CALL WLM_CAPTURE_ACTIVITY_IN_PROGRESS(58,3,1)"   

Return Status = 0

Step 4— Create a file called exp_data_sp.sql that contains the following. Please substitute UOW_ID, ACTIVITY_ID, and EXPLAIN_SCHEMA accordingly. This SQL file will generate explain data from section information recoded by activity event monitor using EXPLAIN_FROM_DATA for the example query.

SET SERVEROUTPUT ON;

BEGIN
  DECLARE EXECUTABLE_ID VARCHAR(32) FOR BIT DATA; --
  DECLARE SECTION BLOB(134M); --
  DECLARE STMT_TEXT CLOB(2M); --
  DECLARE EXPLAIN_SCHEMA VARCHAR(128); --

  DECLARE EXPLAIN_REQUESTER VARCHAR(128); --
  DECLARE EXPLAIN_TIME TIMESTAMP; --
  DECLARE SOURCE_NAME VARCHAR(128); --
  DECLARE SOURCE_SCHEMA VARCHAR(128); --
  DECLARE SOURCE_VERSION VARCHAR(128); --

  SET EXPLAIN_SCHEMA = 'SKAPOOR'; --

  SELECT A.SECTION_ENV, A.STMT_TEXT, A.EXECUTABLE_ID INTO
         SECTION, STMT_TEXT, EXECUTABLE_ID
  FROM ACTIVITYSTMT_MYMON A
  WHERE UOW_ID=3 and ACTIVITY_ID=1 and substr(stmt_text,1,80) like ('WITH TMP1%'); --

  CALL EXPLAIN_FROM_DATA( SECTION,
                          STMT_TEXT,
                          EXECUTABLE_ID,
                          EXPLAIN_SCHEMA,
                          EXPLAIN_REQUESTER,
                          EXPLAIN_TIME,
                          SOURCE_NAME,
                          SOURCE_SCHEMA,
                          SOURCE_VERSION ); --

  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_REQUESTER = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_REQUESTER ); --
  CALL DBMS_OUTPUT.PUT( 'EXPLAIN_TIME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( EXPLAIN_TIME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_NAME = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_NAME ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_SCHEMA = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_SCHEMA ); --
  CALL DBMS_OUTPUT.PUT( 'SOURCE_VERSION = ' ); --
  CALL DBMS_OUTPUT.PUT_LINE( SOURCE_VERSION ); --
END;

a) Run this SQL file as following:

db2 -tvf exp_data_sp.sql

DB20000I  The SQL command completed successfully.

EXPLAIN_REQUESTER = SKAPOOR
EXPLAIN_TIME = 2011-05-10-14.28.34.119918
SOURCE_NAME = SQLC2H22
SOURCE_SCHEMA = NULLID
SOURCE_VERSION =

Step 5— Use the db2exfmt tool to generate the explain format data for the explain data generated in step 4 using the EXPLAIN_TIME.

db2exfmt -d SECTION -1 -w 2011-05-10-14.28.34.119918 -o exfmt_explain_from_data.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_data.out.
Executing Connect Reset -- Connect Reset was Successful.

The explain output is saved in exfmt_explain_from_data.out in the working directory. Access plan and other very detailed information can be found in this file for our example query.

EXPLAIN_FROM_CATALOG

The EXPLAIN_FROM_CATALOG procedure produces explain information about a statement using the content of the section from the catalog tables. The explain information is stored in the explain tables, which can be further processed by explain formatting utility, such as db2exfmt. The optional input or output argument explain_schema can be used to specify the schema for the explain tables where explain information should be saved. If no value or NULL is specified for this argument, it will first look for explain tables under current authorization ID then under the SYSTOOLS schema.

The syntax for EXPLAIN_FROM_CATALOG procedure is as follows:

>>-EXPLAIN_FROM_CATALOG----------------------------------------->
>--(--pkgschema--,--pkgname--,--pkgversion--,--sectno--,--explain_schema-->
>--,--explain_requester--,--explain_time--,--source_name-->
>--,--source_schema--,--source_version--)->
Table 3. Details of the arguments
NameDirectionData Type
pkgschemaInputVARCHAR(128)
pkgnameInputVARCHAR(128)
pkgversionInputVARCHAR(64)
sectnoInputSMALLINT
explain_schemaInput/OutputVARCHAR(128)
explain_requesterOutputVARCHAR(128)
explain_timeOutputTIMESTAMP
source_nameOutputVARCHAR(128)
source_schemaOutputVARCHAR(128)
source_versionOutputVARCHAR(64)

The last five arguments of this stored procedure are output parameters. These output parameters can be used to locate explain information for the section in the explain tables. Please note that this procedure does not issue COMMIT following the inserts in the explain tables. The caller must ensure to perform COMMIT after calling this procedure to save the explain information. User is required EXECUTE permission on the EXPLAIN_FROM_DATA and INSERT permission on the explain tables.

Let's walk through an example on how to use this procedure to generate section explain information. There are five steps involved in generating section explain using this procedure. This method of generating the explain data is very useful when debugging a static query whose packages are stored in the catalog tables.

  1. Create an SQC file to execute the main query.
  2. Prepare and bind the SQC file from step 1.
  3. Find the package and section information for the main query from the catalog tables.
  4. Use the package and section information found in step 3 and call EXPLAIN_FROM_CATALOG procedure to generate the explain information for the main query.
  5. Use the db2exfmt tool to format the explain data generated for the query in step 4.

Note: You must make sure that the explain tables are created before following the steps in this example. You can create them by running the EXPLAIN.DDL script under the ~sqllib/misc/ directory.

Step 1— Create an SQC file with the following content and save it as expcat.sqc.

struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
        short month;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE c2 CURSOR FOR WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS ( 
	SELECT 
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID, 
		S.DISTRICT AS DISTRICT, 
		S.REGION AS REGION, 
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT 
	FROM 
		SKAPOOR.SALES_FACT F1, 
		SKAPOOR.DATE_DIM D, 
		SKAPOOR.PRODUCT_DIM P, 
		SKAPOOR.STORE_DIM S 
	WHERE 
		P.MODEL LIKE '%model%' AND 
		F1.DATE_ID=D.DATE_ID AND 
		F1.PRODUCT_ID=P.PRODUCT_ID AND 
		F1.STORE_ID=S.STORE_ID AND 
		F1.DATE_ID BETWEEN '2010-01-01' AND '2010-01-31' AND 
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND 
		D.MONTH = 1 
	GROUP BY 
		S.STORE_ID,
		S.DISTRICT,
		S.REGION,D.MONTH 
) , 
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS ( 
	SELECT 
		D1.MONTH AS MONTH, 
		S1.STORE_ID AS STORE_ID, 
		S1.DISTRICT AS DISTRICT, 
		S1.REGION AS REGION, 
		SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT 
	FROM 
		SKAPOOR.SALES_FACT F2, 
		SKAPOOR.DATE_DIM D1, 
		SKAPOOR.PRODUCT_DIM P1, 
		SKAPOOR.STORE_DIM S1 
	WHERE 
		P1.MODEL LIKE '%model%' AND 
		F2.DATE_ID=D1.DATE_ID AND 
		F2.PRODUCT_ID=P1.PRODUCT_ID AND 
		F2.STORE_ID=S1.STORE_ID AND 
		F2.DATE_ID BETWEEN '2010-11-01' AND '2010-11-30' AND 
		F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND 
		D1.MONTH=11 
	GROUP BY 
		S1.STORE_ID,
		S1.DISTRICT,
		S1.REGION,
		D1.MONTH
) 
SELECT 
	A.*, B.* 
FROM 
	TMP1 A LEFT OUTER JOIN 
	TMP2 B ON (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT) 
ORDER BY 
	A.AMOUNT_1 DESC, 
	B.AMOUNT_11 DESC;
EXEC SQL OPEN c2;
EXEC SQL FETCH c2 INTO :month;


while (sqlca.sqlcode !=100)
{
        EXEC SQL FETCH c2 INTO :month;
}
EXEC SQL CLOSE c2;

Step 2— Prepare and bind the expcat.sqc file, which will create the packages for the main query.

a) Connect to your database.

 db2 "CONNECT TO SECTION"

   Database Connection Information

 Database server        = DB2/AIX64 9.7.5
 SQL authorization ID   = SKAPOOR
 Local database alias   = SECTION

b) Prepare the expcat.sqc file. This will create a bind file called expcat.bnd in the working directory.

 db2 prep expcat.sqc bindfile

LINE    MESSAGES FOR expcat.sqc
------  -------------------------------------------------------
        SQL0060W  The "C" precompiler is in progress.
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "0" warnings.

c) Bind the expcat.bnd file generated by the previous step.

db2 bind expcat.bnd explain all

LINE    MESSAGES FOR expcat.bnd
------  ------------------------------------------------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.

Step 3— Find the package and section input information required by the EXPLAIN_FROM_CATALOG procedure.

db2 "SELECT pkgschema, pkgname, version, Sectno FROM SYSCAT.STATEMENTS \
WHERE TEXT like ('%WITH TMP1 (MONTH_1%')"

PKGSCHEMA   PKGNAME    VERSION  SECTNO 
----------- ---------- -------- ------ 
SKAPOOR     EXPCAT                   1 
                                       
  1 record(s) selected.

Step 4— Use the package and section information found in step 3 and call EXPLAIN_FROM_CATALOG procedure to generate the explain information for the main query.

db2 "call explain_from_catalog('SKAPOOR', 'EXPCAT','', 1, 'SKAPOOR', ?,?,?,?,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-05-24-20.12.45.788476

  Parameter Name  : SOURCE_NAME
  Parameter Value : EXPCAT

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

Step 5— Use the db2exfmt tool to generate the explain format data for the explain data generated in step 4 using the EXPLAIN_TIME.

db2exfmt -d SECTION -1 -w 2011-05-24-20.12.45.788476 -o exfmt_explain_from_catalog.out

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in exfmt_explain_from_catalog.out.
Executing Connect Reset -- Connect Reset was Successful.

The explain output is saved in exfmt_explain_from_catalog.out in the working directory.


Section actuals

Starting with the V9.7 Fix Pack 1, runtime statistics can be gathered for access plan operators during the execution of a section. These statistics are referred to as section actuals. With this method, we can collect real cardinality for access plan operators.

The explain facility output conveniently displays the real cardinality (section actuals) and the estimated cardinality plan values for your comparison. The result of this comparison can potentially point to out-of-date statistics — for example, used by the optimizer to select an incorrect access plan. Further action can be taken to update the statistics using the RUNSTATS command, then retrying the application with an up-to-date access plan in place. Section actuals are only available when a section explain is performed and the section was captured using an activity event monitor.

Here are the steps to collect section actuals:

  1. Enable section actuals
  2. Create workload and event monitor for activity
  3. Collect section actuals for statement of interest
  4. Locate the application, UOW and activity ID for the data
  5. Explain the data into explain tables
  6. Run db2exfmt to generate access plan
  7. Examine the output

Let's look at an example.

1. Enable section actuals using:

db2 update db cfg for SECTION using SECTION_ACTUALS BASE

NOTE: You can also use the WLM_SET_CONN_ENV stored procedure discussed in the Section explain section — for example:

 db2 "call wlm_set_conn_env(null,'>collectactdata<with details, section and \
 		 values>/collectactdata<>collectsectionactuals<\
         base>/collectsectionactuals<')"

2. Create workload and event monitor

connect to SECTION;
create workload MYWORKLOAD current client_acctng('MYWORKLOAD') 
   service class sysdefaultuserclass collect activity data on all  
   database partitions with details, section;

Here we create a workload to collect activity information and specify keyword SECTION to collect section actual information.

create event monitor MYMON for activities write to table ;

This would create ACTIVITYSTMT_MYMON table and capture the statement information.

3. Collect section actuals for statement of interest

call wlm_set_client_info(null, null, null, 'MYWORKLOAD', null); 
set event monitor MYMON state 1; 
-- Execute the main query used in this article.
set event monitor MYMON state 0; 
call wlm_set_client_info(null, null, null, null, null);

The CURRENT CLIENT_ACCTNG (or CLIENT ACCTNG) special register contains the value of the accounting string from the client information specified for this connection. The data type of the register is VARCHAR(255). The default value of this register is an empty string.

The WLM_SET_CLIENT_INFO procedure sets client information associated with the current connection at the DB2® server. Here we use this procedure to set the client workload information.

4. Locate the application, UOW, and activity ID for the data.

Since section actuals uses explain_from_activity stored procedure to populate the explain tables, we need to identify the appl_id, uow_id, activity_id. This can be gathered from the activity information recorded in the activitystmt_mymon event monitor table.

SELECT appl_id, uow_id, activity_id, substr(stmt_text,1,80) as stmt FROM
ACTIVITYSTMT_MYMON where substr(stmt_text,1,50) like ('WITH TMP1%')

APPL_ID                     UOW_ID  ACTIVITY_ID  STMT
--------------------------  ------- -----------  ----------- 
*LOCAL.skapoor.110412133000 7    1    WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1

  1 record(s) selected.

5. Call EXPLAIN_FROM_ACTIVITY to populate the explain tables:

call explain_from_activity ('*LOCAL.skapoor.110412133000 ',7,1,'MYMON',
'SKAPOOR',?,?,?,?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : EXPLAIN_SCHEMA
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_REQUESTER
  Parameter Value : SKAPOOR

  Parameter Name  : EXPLAIN_TIME
  Parameter Value : 2011-04-12-09.34.44.082039

  Parameter Name  : SOURCE_NAME
  Parameter Value : SQLC2H21

  Parameter Name  : SOURCE_SCHEMA
  Parameter Value : NULLID

  Parameter Name  : SOURCE_VERSION
  Parameter Value :

  Return Status = 0

6. Run db2exfmt to generate access plan (see Downloads).

db2exfmt –d section -1 –o exfmt_section_actuals.out

Screen Output:
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in eexfmt_section_actuals.out.
Executing Connect Reset -- Connect Reset was Successful.

7. Examine the output of file exfmt_section_actuals.out.

Explain level:    Explain from section


Snippet of the output:


      Rows 
  Rows Actual
     RETURN
     (   1)
      Cost 
      I/O 
       |
      10    <- Estimated cardinality
      10    <- Actual cardinality
    TBSCAN
    (   2)
  1.55994e+06 
      NA 
      |
      10 
      10 
     SORT  
   (   3)
  1.55994e+06 
      NA 
      |
      10 
      10 
    HSJOIN<
    (   4)
  1.55994e+06 
      NA 
..

Enhancement in db2support command

Along with section explain features, V9.7 also introduced related db2support enhancements. Most of these enhancements are related to collecting activity event monitoring data. The syntax for these options is as follows. Please refer to the online information center for DB2 for full db2support command with these options.

>--+------------------------------------------------------------------------------+-->
  -| event-monitor-options |----------------------------------------------------+  
   '- -aem--+---------------------------------+--+------------------------------+-'  
            '- -compenv--compilation-env-file-'  '- -tbspname--table-space-name-'

event-monitor-options

|-- -actevm--event-monitor-name-- -appid--application-id-------->

>-- -uowid--uow-id-- -actid--activity-id------------------------

The two examples described below show how we can exploit the new db2support options to collect activity event monitoring information. Please note that our database name in this article is SECTION.

1. db2support –d section –sf badquery.sql –aem

Place the query you would like to collect activity event monitoring information for in a file called badquery.sql. Besides collecting optimizer information at level 2 (-cl 2), this command also invokes db2caem. This invocation will create an activity event monitor and collect detailed activity information including section explain and section actuals for the specified query in badquery.sql.

2. db2support -d section -actevm mymon –appid *LOCAL.skapoor.110510181205 -uowid 3 –actid 1

In this example, there is an existing activity event monitor called mymon. This command will use this existing event monitor to collect the required activity monitoring data. This command will not create a new activity event monitor. It will collect all the detailed activity information including section explain and section actuals for the given combination of application ID, UOW ID, and activity ID.

Both of these commands will create a directory called DB2CAEM under OPTIMIZER directory in the resulting db2support.zip file. Under DB2CAEM folder, you will see another folder called DB2CAEM_<timestamp_of_collection>, where <timestamp_of_collection> is the time when db2caem was executed by db2support. In our example, the directory is called DB2CAEM_2011-05-10-17.45.04.967082. Under this folder, you will see following:

  • EXPORTS — This is a directory that contains exported data from the activity event monitor tables used by the db2support command.
  • db2caem.exfmt — This is a section explain format collected using the db2exfmt tool. The explain information is generated from the activity event monitor used by db2support command.
  • db2caem_options.in — This file shows you the exact command and options used with db2caem command invoked through db2support command.

The db2support command enhancement introduced for activity event monitors provide an easier way to collect section explain information for a query. This includes section data, values, actuals, etc, which are crucial during investigating a query performance issues.


Conclusion

Section explains and section actuals are very powerful features to investigate problems with a query execution plan. By using these features, it can greatly reduce investigation time when dealing with a poor-performing query.


Download

DescriptionNameSize
Format exampleexfmt_section_examples.zip15KB

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 for Linux, UNIX, and Windows.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Download a trial version of Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=678614
ArticleTitle=Troubleshoot query performance in DB2 for Linux, UNIX, and Windows
publish-date=06092011