The last post about Db2 was about getting it up and running. As the issue we had to solve was about performance, getting an explain plan for the problematic statement(s) was the obvious step to do. In PostgreSQL you can just use EXPLAIN for that, and you’re done. In Db2 the procedure is a bit different, but once you know the tools, it is quite easy as well.

Let’s start by creating a new database:

[email protected]:~> . sqllib/db2profile 
[email protected]:~> db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.6.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => create database db1
DB20000I  The CREATE DATABASE command completed successfully.

As we need something to explain, we’ll copy the “tables” catalog table without any data:

db2 => connect to db1

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.6.0
 SQL authorization ID   = DB2
 Local database alias   = DB1
db2 => create table t like syscat.tables
DB20000I  The SQL command completed successfully.
db2 => select count(*) from t;

1          
-----------
          0

  1 record(s) selected.

Generate some data:

db2 => insert into t select * from syscat.tables
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from syscat.tables
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from t
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from t
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from t
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from t
DB20000I  The SQL command completed successfully.
db2 => insert into t select * from t
DB20000I  The SQL command completed successfully.
db2 => select count(*) from t

1          
-----------
      14016

  1 record(s) selected.

The structure of the table is like this:

db2 => describe table t

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TABSCHEMA                       SYSIBM    VARCHAR                    128     0 No    
TABNAME                         SYSIBM    VARCHAR                    128     0 No    
OWNER                           SYSIBM    VARCHAR                    128     0 No    
OWNERTYPE                       SYSIBM    CHARACTER                    1     0 No    
TYPE                            SYSIBM    CHARACTER                    1     0 No    
STATUS                          SYSIBM    CHARACTER                    1     0 No    
BASE_TABSCHEMA                  SYSIBM    VARCHAR                    128     0 Yes   
BASE_TABNAME                    SYSIBM    VARCHAR                    128     0 Yes   
ROWTYPESCHEMA                   SYSIBM    VARCHAR                    128     0 Yes   
ROWTYPENAME                     SYSIBM    VARCHAR                    128     0 Yes   
CREATE_TIME                     SYSIBM    TIMESTAMP                   10     6 No    
ALTER_TIME                      SYSIBM    TIMESTAMP                   10     6 No    
INVALIDATE_TIME                 SYSIBM    TIMESTAMP                   10     6 No    
STATS_TIME                      SYSIBM    TIMESTAMP                   10     6 Yes   
COLCOUNT                        SYSIBM    SMALLINT                     2     0 No    
TABLEID                         SYSIBM    SMALLINT                     2     0 No    
TBSPACEID                       SYSIBM    SMALLINT                     2     0 No    
CARD                            SYSIBM    BIGINT                       8     0 No    
NPAGES                          SYSIBM    BIGINT                       8     0 No    
MPAGES                          SYSIBM    BIGINT                       8     0 No    
FPAGES                          SYSIBM    BIGINT                       8     0 No    
NPARTITIONS                     SYSIBM    BIGINT                       8     0 No    
NFILES                          SYSIBM    BIGINT                       8     0 No    
TABLESIZE                       SYSIBM    BIGINT                       8     0 No    
OVERFLOW                        SYSIBM    BIGINT                       8     0 No    
TBSPACE                         SYSIBM    VARCHAR                    128     0 Yes   
INDEX_TBSPACE                   SYSIBM    VARCHAR                    128     0 Yes   
LONG_TBSPACE                    SYSIBM    VARCHAR                    128     0 Yes   
PARENTS                         SYSIBM    SMALLINT                     2     0 Yes   
CHILDREN                        SYSIBM    SMALLINT                     2     0 Yes   
SELFREFS                        SYSIBM    SMALLINT                     2     0 Yes   
KEYCOLUMNS                      SYSIBM    SMALLINT                     2     0 Yes   
KEYINDEXID                      SYSIBM    SMALLINT                     2     0 Yes   
KEYUNIQUE                       SYSIBM    SMALLINT                     2     0 No    
CHECKCOUNT                      SYSIBM    SMALLINT                     2     0 No    
DATACAPTURE                     SYSIBM    CHARACTER                    1     0 No    
CONST_CHECKED                   SYSIBM    CHARACTER                   32     0 No    
PMAP_ID                         SYSIBM    SMALLINT                     2     0 Yes   
PARTITION_MODE                  SYSIBM    CHARACTER                    1     0 No    
LOG_ATTRIBUTE                   SYSIBM    CHARACTER                    1     0 No    
PCTFREE                         SYSIBM    SMALLINT                     2     0 No    
APPEND_MODE                     SYSIBM    CHARACTER                    1     0 No    
REFRESH                         SYSIBM    CHARACTER                    1     0 No    
REFRESH_TIME                    SYSIBM    TIMESTAMP                   10     6 Yes   
LOCKSIZE                        SYSIBM    CHARACTER                    1     0 No    
VOLATILE                        SYSIBM    CHARACTER                    1     0 No    
ROW_FORMAT                      SYSIBM    CHARACTER                    1     0 No    
PROPERTY                        SYSIBM    VARCHAR                     32     0 No    
STATISTICS_PROFILE              SYSIBM    CLOB                  10485760     0 Yes   
COMPRESSION                     SYSIBM    CHARACTER                    1     0 No    
ROWCOMPMODE                     SYSIBM    CHARACTER                    1     0 No    
ACCESS_MODE                     SYSIBM    CHARACTER                    1     0 No    
CLUSTERED                       SYSIBM    CHARACTER                    1     0 Yes   
ACTIVE_BLOCKS                   SYSIBM    BIGINT                       8     0 No    
DROPRULE                        SYSIBM    CHARACTER                    1     0 No    
MAXFREESPACESEARCH              SYSIBM    SMALLINT                     2     0 No    
AVGCOMPRESSEDROWSIZE            SYSIBM    SMALLINT                     2     0 No    
AVGROWCOMPRESSIONRATIO          SYSIBM    REAL                         4     0 No    
AVGROWSIZE                      SYSIBM    SMALLINT                     2     0 No    
PCTROWSCOMPRESSED               SYSIBM    REAL                         4     0 No    
LOGINDEXBUILD                   SYSIBM    VARCHAR                      3     0 Yes   
CODEPAGE                        SYSIBM    SMALLINT                     2     0 No    
COLLATIONSCHEMA                 SYSIBM    VARCHAR                    128     0 No    
COLLATIONNAME                   SYSIBM    VARCHAR                    128     0 Yes   
COLLATIONSCHEMA_ORDERBY         SYSIBM    VARCHAR                    128     0 No    
COLLATIONNAME_ORDERBY           SYSIBM    VARCHAR                    128     0 Yes   
ENCODING_SCHEME                 SYSIBM    CHARACTER                    1     0 No    
PCTPAGESSAVED                   SYSIBM    SMALLINT                     2     0 No    
LAST_REGEN_TIME                 SYSIBM    TIMESTAMP                   10     6 Yes   
SECPOLICYID                     SYSIBM    INTEGER                      4     0 No    
PROTECTIONGRANULARITY           SYSIBM    CHARACTER                    1     0 No    
AUDITPOLICYID                   SYSIBM    INTEGER                      4     0 Yes   
AUDITPOLICYNAME                 SYSIBM    VARCHAR                    128     0 Yes   
AUDITEXCEPTIONENABLED           SYSIBM    CHARACTER                    1     0 No    
DEFINER                         SYSIBM    VARCHAR                    128     0 No    
ONCOMMIT                        SYSIBM    CHARACTER                    1     0 No    
LOGGED                          SYSIBM    CHARACTER                    1     0 No    
ONROLLBACK                      SYSIBM    CHARACTER                    1     0 No    
LASTUSED                        SYSIBM    DATE                         4     0 No    
CONTROL                         SYSIBM    CHARACTER                    1     0 No    
TEMPORALTYPE                    SYSIBM    CHARACTER                    1     0 No    
TABLEORG                        SYSIBM    CHARACTER                    1     0 No    
EXTENDED_ROW_SIZE               SYSIBM    CHARACTER                    1     0 No    
PCTEXTENDEDROWS                 SYSIBM    REAL                         4     0 No    
REMARKS                         SYSIBM    VARCHAR                    254     0 Yes   

  85 record(s) selected.

If we create an index on the “tabname” column and later filter on that column we should get an index access:

db2 => create index i on t(tabname)
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.

You have several options to create explain plans in Db2 and one of the options is to use the CURRENT EXPLAIN MODE special register, so let’s try that:

[email protected]:~> echo "select count(*) from t where tabname='t';" > 1.sql
[email protected]:~> db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
[email protected]:~> db2 -tvf  1.sql     
select count(*) from t where tabname='t'
SQL0219N  The required Explain table "DB2.EXPLAIN_INSTANCE" does not exist.  
SQLSTATE=42704

The error message is pretty clear, somehow we need to create the explain tables. There are two options for this and we’ll use the EXPLAIN.DDL script which comes with the Db2 installation:

[email protected]:~> ls -l ./sqllib/misc/EXPLAIN.DDL
-r--r--r-- 1 db2 db2 48371 Jun 11  2021 ./sqllib/misc/EXPLAIN.DDL
[email protected]:~> grep -i "create table" ./sqllib/misc/EXPLAIN.DDL
CREATE TABLE EXPLAIN_INSTANCE ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_STATEMENT ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_ARGUMENT ( EXPLAIN_REQUESTER   VARCHAR(128 OCTETS)  NOT NULL,
CREATE TABLE EXPLAIN_OBJECT ( EXPLAIN_REQUESTER    VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_OPERATOR ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_PREDICATE ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_STREAM ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_DIAGNOSTIC ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE EXPLAIN_DIAGNOSTIC_DATA ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,
CREATE TABLE OBJECT_METRICS ( EXECUTABLE_ID     VARCHAR(32 OCTETS) FOR BIT DATA NOT NULL,
CREATE TABLE ADVISE_INSTANCE (
CREATE TABLE ADVISE_INDEX(
CREATE TABLE ADVISE_WORKLOAD (
CREATE TABLE ADVISE_MQT (
CREATE TABLE ADVISE_PARTITION (
CREATE TABLE ADVISE_TABLE (
CREATE TABLE EXPLAIN_ACTUALS ( EXPLAIN_REQUESTER VARCHAR(128 OCTETS) NOT NULL,

Quite a few tables get created by this script and we’ll not go into the details of which table contains what (this is out of the scope of this post), but just execute it:

[email protected]:~> db2 -tvf ./sqllib/misc/EXPLAIN.DDL

******* IMPORTANT ********** 

USAGE: db2 -tf EXPLAIN.DDL   

******* IMPORTANT ********** 


UPDATE COMMAND OPTIONS USING C OFF
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
...
COMMIT WORK
DB20000I  The SQL command completed successfully.

All done, lets try again:

[email protected]:~> db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
[email protected]:~> db2 -tvf  1.sql
select count(*) from t where tabname='t'
SQL0217W  The statement was not executed as only Explain information requests 
are being processed.  SQLSTATE=01604

Looks better, but how do we get the explain plan? One option is to use db2exfmt:

[email protected]:~> db2exfmt -d DB1 -# 0 -w -1 -g TIC -n % -s % -o explain.txt
DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful
Output is in explain.txt.
Executing Connect Reset -- Connect Reset was Successful.
[email protected]:~> db2 set current explain mode no
DB20000I  The SQL command completed successfully.

This generates the “explain.txt” file with lots of information. What we are looking for is the explain plan and there is a graphical representation in the file, which is exactly what we need:

Access Plan:
-----------
        Total Cost:             6.77696
        Query Degree:           1


      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
        1 
     GRPBY 
     (   2)
     6.77689 
        1 
       |
     35.8378 
     IXSCAN
     (   3)
     6.77614 
        1 
       |
      14016 
 INDEX: DB2     
        I
       Q1

Another option is to use db2expln:

[email protected]:~> db2expln -database db1 -statement "select count(*) from t where tabname = 't'" -terminal

DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2017
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
                                   "DB2"


Statement:
  
  select count(*)
  from t 
  where tabname ='t' 


Section Code Page = 1208

Estimated Cost = 6.775021
Estimated Cardinality = 1.000000

Access Table Name = DB2.T  ID = 2,4
|  Index Scan:  Name = DB2.I  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: TABNAME (Ascending)
|  #Columns = 0
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  1: 't' 
|  |  Stop Key: Inclusive Value
|  |  |  1: 't' 
|  Index-Only Access
|  Index Prefetch: Sequential(1), Readahead
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Index Predicate(s)
|  |  Predicate Aggregation
|  |  |  Column Function(s)
Aggregation Completion
|  Column Function(s)
Return Data to Application
|  #Columns = 1

End of section

Compared to db2exfmt this gives a much more compact output.


Thumbnail [60x60]
by
Daniel Westermann