As a database administrator sometimes we definitely face query slowness issues. For this IBM provided tools to analyse the query and get few details about the plan how it is accessing the table and indices of the table. The main tools provided by IBM in DB2 are db2expln & db2exfmt. Both these tool gives the same output such as how the table and index scans are? how much time it is taking to get the results back. But the which one is better and what is difference between these two tools? why db2 provided two tools for single purpose.
Lets see how these two works.
Lets see how these two works.
DB2EXPLN:
Before using db2expln we need to check whether the tables related to this tool are exists or not. If these tables does not exist we need to create the tables as below:
connect to <database>
db2 -tvf C:\IBM\SQLLIB\MISC\EXPLAIN.DDL
The above script creates the below explain related tables in instance owner schema.
ADVISE_INDEX
ADVISE_INSTANCE
ADVISE_MQT
ADVISE_PARTITION
ADVISE_TABLE
ADVISE_WORKLOAD
EXPLAIN_ACTUALS
EXPLAIN_ARGUMENT
EXPLAIN_DIAGNOSTIC
EXPLAIN_DIAGNOSTIC_DATA
EXPLAIN_FORMAT_OUTPUT
EXPLAIN_INSTANCE
EXPLAIN_OBJECT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STATEMENT
EXPLAIN_STREAM
OBJECT_METRICS
Here to use this db2expln tool I'm creating a file with below select query. File name is select.sql
SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept = d.deptno AND e.workdept = p.deptno
Now let use analyse the select statement with db2expln.
db2expln -database SAMPLE -stmtfile select.sql -g -o explain.out
My database name is SAMPLE. Explain result will be stored in "explain.out" file.
Let see the output.
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
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",
"ADMINISTRATOR"
Statement:
SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept =d.deptno AND e.workdept =p.deptno
Section Code Page = 1208
Estimated Cost = 20.474272
Estimated Cardinality = 105.000000
Access Table Name = ADMINISTRATOR.PROJECT ID = 2,9
| #Columns = 2
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Inner Row
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.DEPARTMENT ID = 2,5
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.EMPLOYEE ID = 2,6
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 5
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
105
RETURN
( 1)
20.4743
|
105
HSJOIN
( 2)
20.4743
/---/ \--\
42 20
TBSCAN HSJOIN
( 3) ( 4)
6.82855 13.6424
| / \
42 14 20
Table: TBSCAN TBSCAN
ADMINISTRATOR ( 5) ( 6)
EMPLOYEE 6.81944 6.82139
| |
14 20
Table: Table:
ADMINISTRATOR ADMINISTRATOR
DEPARTMENT PROJECT
At the end we can see the access plan.
Now lets see how db2exfmt tool will work.
EXPLAIN_ARGUMENT
EXPLAIN_DIAGNOSTIC
EXPLAIN_DIAGNOSTIC_DATA
EXPLAIN_FORMAT_OUTPUT
EXPLAIN_INSTANCE
EXPLAIN_OBJECT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STATEMENT
EXPLAIN_STREAM
OBJECT_METRICS
Here to use this db2expln tool I'm creating a file with below select query. File name is select.sql
SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept = d.deptno AND e.workdept = p.deptno
Now let use analyse the select statement with db2expln.
db2expln -database SAMPLE -stmtfile select.sql -g -o explain.out
My database name is SAMPLE. Explain result will be stored in "explain.out" file.
Let see the output.
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
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",
"ADMINISTRATOR"
Statement:
SELECT e.lastname, e.job, d.deptname, d.location, p.projname
FROM employee AS e, department AS d, project AS p
WHERE e.workdept =d.deptno AND e.workdept =p.deptno
Section Code Page = 1208
Estimated Cost = 20.474272
Estimated Cardinality = 105.000000
Access Table Name = ADMINISTRATOR.PROJECT ID = 2,9
| #Columns = 2
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Inner Row
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.DEPARTMENT ID = 2,5
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 4000
| Access Table Name = ADMINISTRATOR.EMPLOYEE ID = 2,6
| | #Columns = 3
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 5
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
105
RETURN
( 1)
20.4743
|
105
HSJOIN
( 2)
20.4743
/---/ \--\
42 20
TBSCAN HSJOIN
( 3) ( 4)
6.82855 13.6424
| / \
42 14 20
Table: TBSCAN TBSCAN
ADMINISTRATOR ( 5) ( 6)
EMPLOYEE 6.81944 6.82139
| |
14 20
Table: Table:
ADMINISTRATOR ADMINISTRATOR
DEPARTMENT PROJECT
In the above output you can see the below values. These values gives the time taken to retrieve the data. This is better if it is less. Depends on the data quantity we have.
Estimated Cost = 20.474272
Estimated Cardinality = 105.000000
Now lets see how db2exfmt tool will work.
DB2EXFMT:
What is db2exfmt?
This tool gives us the result to understand better in a simple format.
This tool retrieves the result of a query once it has the explain information in the explain tables or if we run the query on database with explain mode is explain.
Lets see how it works.
We already has the explain for the query above on the explain tables. Lets run the db2exfmt now.
db2exfmt -d SAMPLE -1 -o select.sql.exfmt
Output:
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
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.05.3
FORMATTED ON DB: SAMPLE
SOURCE_NAME: SQLC2K26
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2018-10-13-18.41.03.686000
EXPLAIN_REQUESTER: ADMINISTRATOR
Database Context:
----------------
Parallelism: None
CPU Speed: 1.889377e-007
Comm Speed: 100
Buffer Pool size: 250
Sort Heap size: 256
Database Heap size: 600
Lock List size: 4096
Maximum Lock List: 22
Average Applications: 1
Locks Available: 28835
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 6
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT
e.lastname,
e.job,
d.deptname,
d.location,
p.projname
FROM
employee AS e,
department AS d,
project AS p
WHERE
e.workdept =d.deptno AND
e.workdept =p.deptno
Optimized Statement:
-------------------
SELECT
Q3.LASTNAME AS "LASTNAME",
Q3.JOB AS "JOB",
Q2.DEPTNAME AS "DEPTNAME",
Q2.LOCATION AS "LOCATION",
Q1.PROJNAME AS "PROJNAME"
FROM
ADMINISTRATOR.PROJECT AS Q1,
ADMINISTRATOR.DEPARTMENT AS Q2,
ADMINISTRATOR.EMPLOYEE AS Q3
WHERE
(Q3.WORKDEPT = Q1.DEPTNO) AND
(Q1.DEPTNO = Q2.DEPTNO)
Access Plan:
-----------
Total Cost: 20.4743
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
105
HSJOIN
( 2)
20.4743
3
/----------+----------\
42 20
TBSCAN HSJOIN^
( 3) ( 4)
6.82855 13.6424
1 2
| /--------+--------\
42 14 20
TABLE: ADMINISTRATOR TBSCAN TBSCAN
EMPLOYEE ( 5) ( 6)
Q3 6.81944 6.82139
1 1
| |
14 20
TABLE: ADMINISTRATOR TABLE: ADMINISTRATOR
DEPARTMENT PROJECT
Q2 Q1
Operator Symbols :
------------------
Symbol Description
--------- ------------------------------------------
>JOIN : Left outer join
JOIN< : Right outer join
>JOIN< : Full outer join
xJOIN : Left antijoin
JOINx : Right antijoin
^JOIN : Left early out
JOIN^ : Right early out
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0062W The following MQT or statistical view was
not eligible because one or more columns or
expressions referenced in the query were not found
in the MQT: "ADMINISTRATOR"."ADEFUSR".
Diagnostic Identifier: 2
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "ADMINISTRATOR".
"ADEFUSR".
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 20.4743
Cumulative CPU Cost: 313719
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 20.4743
Cumulative Re-CPU Cost: 313719
Cumulative Re-I/O Cost: 3
Cumulative First Row Cost: 20.4743
Estimated Bufferpool Buffers: 1
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v10.5.300.125 : s140203
HEAPUSE : (Maximum Statement Heap Usage)
128 Pages
PREPTIME: (Statement prepare time)
378 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
8) From Operator #2
Estimated number of rows: 105
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.PROJNAME+Q4.LOCATION+Q4.DEPTNAME+Q4.JOB
+Q4.LASTNAME
2) HSJOIN: (Hash Join)
Cumulative Total Cost: 20.4743
Cumulative CPU Cost: 313719
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 20.4743
Cumulative Re-CPU Cost: 313719
Cumulative Re-I/O Cost: 3
Cumulative First Row Cost: 20.4743
Estimated Bufferpool Buffers: 1
Arguments:
---------
BITFLTR : (Hash Join Bit Filter used)
FALSE
EARLYOUT: (Early Out flag)
NONE
HASHCODE: (Hash Code Size)
24 BIT
HASHTBSZ: (Number of hash table entries)
8
TEMPSIZE: (Temporary Table Page Size)
8192
TUPBLKSZ: (Tuple Block Size (bytes))
4000
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0714286
Predicate Text:
--------------
(Q2.DEPTNO = Q3.WORKDEPT)
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 42
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT
7) From Operator #4
Estimated number of rows: 20
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.PROJNAME+Q1.DEPTNO+Q2.LOCATION+Q2.DEPTNAME
+Q2.DEPTNO
Output Streams:
--------------
8) To Operator #1
Estimated number of rows: 105
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.PROJNAME+Q4.LOCATION+Q4.DEPTNAME+Q4.JOB
+Q4.LASTNAME
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 6.82855
Cumulative CPU Cost: 124639
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0139901
Cumulative Re-CPU Cost: 74046
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.81488
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object ADMINISTRATOR.EMPLOYEE
Estimated number of rows: 42
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$RID$+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 42
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.JOB+Q3.LASTNAME+Q3.WORKDEPT
4) HSJOIN: (Hash Join)
Cumulative Total Cost: 13.6424
Cumulative CPU Cost: 171474
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 13.6424
Cumulative Re-CPU Cost: 171474
Cumulative Re-I/O Cost: 2
Cumulative First Row Cost: 13.6424
Estimated Bufferpool Buffers: 1
Arguments:
---------
BITFLTR : (Hash Join Bit Filter used)
FALSE
EARLYOUT: (Early Out flag)
RIGHT
HASHCODE: (Hash Code Size)
24 BIT
HASHTBSZ: (Number of hash table entries)
8
JN INPUT: (Join input leg)
INNER
TEMPSIZE: (Temporary Table Page Size)
8192
TUPBLKSZ: (Tuple Block Size (bytes))
4000
Predicates:
----------
4) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0714286
Predicate Text:
--------------
(Q1.DEPTNO = Q2.DEPTNO)
Input Streams:
-------------
4) From Operator #5
Estimated number of rows: 14
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO
6) From Operator #6
Estimated number of rows: 20
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.PROJNAME+Q1.DEPTNO
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 20
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.PROJNAME+Q1.DEPTNO+Q2.LOCATION+Q2.DEPTNAME
+Q2.DEPTNO
5) TBSCAN: (Table Scan)
Cumulative Total Cost: 6.81944
Cumulative CPU Cost: 76423
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00488026
Cumulative Re-CPU Cost: 25830
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.81488
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
3) From Object ADMINISTRATOR.DEPARTMENT
Estimated number of rows: 14
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.$RID$+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO
Output Streams:
--------------
4) To Operator #4
Estimated number of rows: 14
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LOCATION+Q2.DEPTNAME+Q2.DEPTNO
6) TBSCAN: (Table Scan)
Cumulative Total Cost: 6.82139
Cumulative CPU Cost: 86755
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00683237
Cumulative Re-CPU Cost: 36162
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.81488
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
5) From Object ADMINISTRATOR.PROJECT
Estimated number of rows: 20
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.PROJNAME+Q1.DEPTNO
Output Streams:
--------------
6) To Operator #4
Estimated number of rows: 20
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.PROJNAME+Q1.DEPTNO
Objects Used in Access Plan:
---------------------------
Schema: ADMINISTRATOR
Name: ADEFUSR
Type: Materialized View (reference only)
Schema: ADMINISTRATOR
Name: PROJACT
Type: Table (reference only)
Schema: ADMINISTRATOR
Name: DEPARTMENT
Type: Table
Time of creation: 2018-08-24-23.02.37.749002
Last statistics update: 2018-10-13-18.38.58.822002
Number of columns: 5
Number of rows: 14
Width of rows: 57
Number of buffer pool pages: 1
Number of data partitions: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate: 0.080000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Schema: ADMINISTRATOR
Name: EMPLOYEE
Type: Table
Time of creation: 2018-08-24-23.02.38.313005
Last statistics update: 2018-10-13-18.38.58.817000
Number of columns: 14
Number of rows: 42
Width of rows: 60
Number of buffer pool pages: 1
Number of data partitions: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate: 0.080000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Schema: ADMINISTRATOR
Name: PROJECT
Type: Table
Time of creation: 2018-08-24-23.02.39.427012
Last statistics update: 2018-10-13-18.38.58.801000
Number of columns: 8
Number of rows: 20
Width of rows: 73
Number of buffer pool pages: 1
Number of data partitions: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate: 0.080000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
One of the great things with db2exfmt is it gives us the optimized query for the query which we are trying to analyze. We easily understand the analyze the result.
Please leave you instructions and advises in the comments of my post.
Nice one.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDelete