EXPLAIN_FORMAT_STATS scalar function
This scalar function is used to display formatted statistics information which is parsed and extracted from explain snapshot captured for a given query.
Syntax
The schema is SYSPROC.
Function parameters
-
snapshot
- An input argument of type BLOB(10M) that is the explain snapshot captured for a given query. It is stored as snapshot column of explain table EXPLAIN_STATEMENT
Authorization
One
of the following authorities is required to execute the routine:
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- EXPLAIN authority
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
Information returned
The data type of the result is CLOB(50M).
Example
SELECT EXPLAIN_FORMAT_STATS(SNAPSHOT)
FROM EXPLAIN_STATEMENT
WHERE EXPLAIN_REQUESTER = 'DB2USER1' AND
EXPLAIN_TIME = timestamp('2006-05-12-14.38.11.109432') AND
SOURCE_NAME = 'SQLC2F0A' AND
SOURCE_SCHEMA = 'NULLID' AND
SOURCE_VERSION = '' AND
EXPLAIN_LEVEL = 'O' AND
STMTNO = 1 AND
SECTNO = 201
The following is a sample output of this function:
Tablespace Context:
-------------------
Name: USERSPACE1
Overhead: 7.500000
Transfer Rate: 0.060000
Prefetch Size: 32
Extent Size: 32
Type: Database managed
Partition Group Name: NULLP
Buffer Pool Identifier: 0
Base Table Statistics:
----------------------
Name: T1
Schema: DB2USER2
Number of Columns: 3
Number of Pages with Rows: 1
Number of Pages: 1
Number of Rows: 5
Table Overflow Record Count: 0
Width of Rows: 26
Time of Creation: 2006-06-16-11.46.53.041085
Last Statistics Update: 2006-06-26-12.23.44.814201
Statistics Type: Fabrication
Primary Tablespace: USERSPACE1
Tablespace for Indexes: USERSPACE1
Tablespace for Long Data: NULLP
Number of Referenced Columns: 2
Number of Indexes: 1
Volatile Table: No
Table Active Blocks: 1
Number of Column Groups: 0
Number of Data Partitions: 1
Average Row Compression Ratio: -9.000000
Percent Rows Compressed: -9.000000
Average Compressed Row Size: -9
Statistics Type: U
Column Information:
--------------------
Number: 1
Name: C1
Statistics Available: Yes
Column Statistics:
------------------
Schema name of the column type: SYSIBM
Name of column type: INTEGER
Maximum column length: 4
Scale for decimal column: 0
Number of distinct column values: 4
Average column length: 5
Number of most frequent values: 1
Number of quantiles: 5
Second highest data value: 3
Second lowest data value: 2
Column sequence in partition key: 0
Average number of sub-elements: -1
Average length of delimiters: -1
Column Distribution Statistics:
-------------------------------
Frequency Statistics:
Valcount Value
---------- -----------
2 1
Quantile Statistics:
Valcount Distcount Value
---------- ----------- ----------
0 1 1
2 1 1
3 2 2
4 3 3
5 4 4
Column Information:
--------------------
Number: 2
Name: C2
Statistics Available: Yes
Column Statistics:
------------------
Schema name of the column type: SYSIBM
Name of column type: INTEGER
Maximum column length: 4
Scale for decimal column: 0
Number of distinct column values: 4
Average column length: 5
Number of most frequent values: 1
Number of quantiles: 5
Second highest data value: 3
Second lowest data value: 2
Column sequence in partition key: 0
Average number of sub-elements: -1
Average length of delimiters: -1
Column Distribution Statistics:
-------------------------------
Frequency Statistics:
Valcount Value
---------- -----------
2 1
Quantile Statistics:
Valcount Distcount Value
---------- ----------- -----------
0 0 1
2 0 1
3 0 2
4 0 4
5 0 4
Indexes defined on the table:
-----------------------------
Name: IDX_T1C1C2
Schema: DB2USER2
Unique Rule: Duplicate index
Used in Operator: Yes
Page Fetch Pairs: Not Available
Number of Columns: 2
Index Leaf Pages: 1
Index Tree Levels: 1
Index First Key Cardinality: 4
Index Full Key Cardinality: 4
Index Cluster Ratio: 100
Index Cluster Factor: -1.000000
Time of Creation: 2006-06-16-11.46.53.596717
Last Statistics Update: 2006-06-26-12.23.44.814201
Index Sequential Pages: 0
Index First 2 Keys Cardinality: 4
Index First 3 Keys Cardinality: -1
Index First 4 Keys Cardinality: -1
Index Avg Gap between Sequences: 0.000000
Fetch Avg Gap between Sequences: -1.000000
Index Avg Sequential Pages: 0.000000
Fetch Avg Sequential Pages: -1.000000
Index Avg Random Pages: 1.000000
Fetch Avg Random Pages: -1.000000
Index RID Count: 5
Index Deleted RID Count: 0
Index Empty Leaf Pages: 0
Avg Partition Cluster Ratio: -1
Avg Partition Cluster Factor: -1.000000
Data Partition Cluster Factor: 1.000000
Data Partition Page Fetch Pairs: Not Available
Base Table Statistics:
----------------------
Name: T2
Schema: DB2USER2
Number of Columns: 3
Number of Pages with Rows: 1
Number of Pages: 1
Number of Rows: 2
Table Overflow Record Count: 0
Width of Rows: 26
Time of Creation: 2006-06-16-11.46.53.398092
Last Statistics Update: 2006-06-26-12.23.45.157028
Statistics Type: Synchronous
Primary Tablespace: USERSPACE1
Tablespace for Indexes: USERSPACE1
Tablespace for Long Data: NULLP
Number of Referenced Columns: 2
Number of Indexes: 1
Volatile Table: No
Table Active Blocks: -1
Number of Column Groups: 0
Number of Data Partitions: 1
Column Information:
--------------------
Number: 1
Name: C1
Statistics Available: Yes
Column Statistics:
------------------
Schema name of the column type: SYSIBM
Name of column type: INTEGER
Maximum column length: 4
Scale for decimal column: 0
Number of distinct column values: 2
Average column length: 5
Number of most frequent values: -1
Number of quantiles: 2
Second highest data value: 2
Second lowest data value: 1
Column sequence in partition key: 0
Average number of sub-elements: -1
Average length of delimiters: -1
Column Distribution Statistics:
-------------------------------
Quantile Statistics:
Valcount Distcount Value
---------- ----------- -----------
1 1 1
2 2 2
Column Information:
--------------------
Number: 2
Name: C2
Statistics Available: Yes
Column Statistics:
------------------
Schema name of the column type: SYSIBM
Name of column type: INTEGER
Maximum column length: 4
Scale for decimal column: 0
Number of distinct column values: 2
Average column length: 5
Number of most frequent values: -1
Number of quantiles: 2
Second highest data value: 2
Second lowest data value: 1
Column sequence in partition key: 0
Average number of sub-elements: -1
Average length of delimiters: -1
Column Distribution Statistics:
-------------------------------
Quantile Statistics:
Valcount Distcount Value
---------- ----------- -----------
1 0 1
2 0 2
Indexes defined on the table:
-----------------------------
Name : IDX_T2C1
Schema: DB2USER2
Unique Rule: Duplicate index
Used in Operator: No
Page Fetch Pairs: Not Available
Number of Columns: 1
Index Leaf Pages: 1
Index Tree Levels: 1
Index First Key Cardinality: 2
Index Full Key Cardinality: 2
Index Cluster Ratio: 100
Index Cluster Factor: -1.000000
Time of Creation: 2006-06-16-11.46.53.857520
Last Statistics Update: 2006-06-26-12.23.45.157028
Index Sequential Pages: 0
Index First 2 Keys Cardinality: -1
Index First 3 Keys Cardinality: -1
Index First 4 Keys Cardinality: -1
Index Avg Gap between Sequences: 0.000000
Fetch Avg Gap between Sequences: -1.000000
Index Avg Sequential Pages: 0.000000
Fetch Avg Sequential Pages: -1.000000
Index Avg Random Pages: 1.000000
Fetch Avg Random Pages: -1.000000
Index RID Count: 2
Index Deleted RID Count: 0
Index Empty Leaf Pages: 0
Avg Partition Cluster Ratio: -1
Avg Partition Cluster Factor: -1.000000
Data Partition Cluster Factor: 1.000000
Data Partition Page Fetch Pairs: Not Available