DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
This new scalar function is used to display formatted statistics information which is parsed and extracted from explain snapshot captured for a given query. The data type of the result is CLOB(50M).
EXECUTE privilege on the EXPLAIN_FORMAT_STATS function.
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 = 201The following example 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