DB2 for Linux, Unix, and
Windows
- Ravi
Performance Tuning
DB2EXFMT
To generate explain plan
For Example: create CUSTOMER table with out INDEX as below
#!/bin/bash
db2 connect to DB3;
db2 "drop table customer";
db2 "drop sequence seq1";
db2 create tablespace userspace1;
db2 create tablespace idxspace1
db2 "create table customer (cid integer, cname CHAR(20)) IN USERSPACE1 Index in
IDXSPACE1 LONG IN USERSPACE1";
db2 "create sequence seq1";
for i in {1..1000}
do
db2 "Insert into CUSTOMER values (NEXTVAL for seq1, 'Testing')";
done
db2 runstats on table customer and detailed indexes all
db2 commit;
db2 connect reset;
db2 terminate;
db2 connect to DB3
db2 select count(*) from customer
$ db2 set current explain mode explain
$ db2 "select cid from customer where cid > 100 and cid <999"
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
$ db2exfmt -d DB1 -o [Link]
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Connecting to the Database.
Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==>
Enter up to 128 character source name (SOURCE_NAME, Default %%) ==>
Enter source schema (SOURCE_SCHEMA, Default %%) ==>
Enter section number (0 for all, Default 0) ==>
Output is in [Link].
Executing Connect Reset -- Connect Reset was Successful.
$ db2 set current explain mode no
Access Plan: (from [Link])
----------Total Cost:
69.2765
Query Degree:
1
Rows
RETURN
( 1)
Cost
I/O
|
898
TBSCAN
( 2)
69.2765
10
|
1000
TABLE: DB2INST2
CUSTOMER
Q1
Now generate explain plan by creating Unique index
db2 "create unique index idx1_customer on customer(cid) "
db2 "runstats on table customer and detailed indexes all "
db2 set current explain mode explain
db2 "select cid from customer where cid > 100 and cid <999 "
db2exfmt -d testdb -o [Link]
db2 set current explain mode no
Access Plan: (from [Link])
----------Total Cost:
31.2309
Query Degree:
1
Rows
RETURN
( 1)
Cost
I/O
|
898
IXSCAN
( 2)
31.2309
4.49
|
1000
INDEX: DB2INST2
IDX1_CUSTOMER
Q1
DB2EXPLN
Quick method of getting a explain plan
It doesnt populate explain tables (../misc/[Link])
$ db2expln -database DB3 -statement "select cid from customer where cid > 100" -terminal
Estimated Cost = 31.300440
Estimated Cardinality = 900.000061
Access Table Name = [Link] ID = 2,256
| Index Scan: Name = DB2INST2.IDX1_CUSTOMER ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: CID (Ascending)
| #Columns = 1
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| #Key Columns = 1
| | Start Key: Exclusive Value
| | | 1: 100
| | Stop Key: Exclusive Value
| | | 1: NULL
| Index-Only Access
| Index Prefetch: Sequential(4), Readahead
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Return Data to Application
| | | #Columns = 1
Return Data Completion
DB2ADVIS
Advises users on creation of materialized query tables (MQT) and indexes,
repartitioning of multidimensional clustering (MDC) tables, and the deletion of unused
objects
The recommendations are based on one or more SQL statements provided by the user
For Example:
db2 => describe table student
Data type
Column
Column name
schema Data type name
Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- -----SID
SYSIBM INTEGER
4 0 Yes
SNAME
SYSIBM CHARACTER
30 0 Yes
2 record(s) selected.
db2 => select count(*) from student
1
----------712704
1 record(s) selected.
db2advis -d db3 -s "select count(sid) from student
---- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 5.153MB
CREATE INDEX "DB2INST2"."IDX1408251708510" ON "DB2INST2"."STUDENT"
("SID" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;
COMMIT WORK ;