Insert, Update, Delete, Select, and Refresh
SQL Performance Analyzer reports on INSERT, UPDATE, DELETE, SELECT, and REFRESH statements.
Users might want direct I/U/D processing against the tables, or to use “where current of cursor” techniques. Both forms of requests are handled in the Enhanced Explain reports. Other information, such as the number of columns updated, is also provided.
The Db2 SQL Performance Analyzer Enhanced Explain Report contains descriptions of processing under Db2®, with the emphasis on what steps Db2 is taking, and in what order. SQL PA provides the statistics accompanying the tables and indexes used for access. Other notations describe query transformation from subquery to join, subquery to simple predicate, or join to subquery access, by direct rowid.
Referential integrity relationships are also noted in the explain report for INSERT, UPDATE, and DELETE statements, notifying you of the additional processing caused by the relationships.
The following example shows Insert (query 12) processing.
EXPLAIN PLAN SET QUERYNO = 100000012 FOR
INSERT INTO TDT690.L1000
VALUES (100100, 'AAAAA', 'BBBBB', 'CCCCC', 'DDDDD', 'EEEEE', 'FFFFF',
'GGGG', 'HHHHH', 'IIIII', 100100, 'JJJJJ', 'KKKKK', 'LLLLL','MMMM',
'NNNNN', 'OOOOO', 'PPPP', 'QQQQQ', 'RRRRR', 100100, 'SSSS','TTTTT',
'UUUUU', 'VVVV', 'WWWWW', 'XXXXX', 'YYYY', 'ZZZZ', 'AAAAA', 100100,
'BBBB', 'CCCCC', 'DDDDD', 'EEEE', 'FFFF', 'GGGGG', 'HHHH', 'IIII',
'JJJJJ', 'KKKKK', 'LLLL', 'MMMM', 'NNNNN', 'OOOO', 100100, 'PPPPP',
'QQQQQ', 'RRRR', 'SSSS')
QUERYNO: 100000012 QBLOCKNO: 1 PLANNO: 0 MIXOPSEQ: 0
PROCESS ->
INSERT VIA CLUSTERING INDEX
---------------------------
CREATOR: TDT690
TABNAME: L1000
VERS: 0 KEY LEN: 6 PADDED: - C-ED: Y C-ING: Y CLUSRATIO: 99.9995
FULLKEY CARD: 99340 FIRSTKEY CARD: 99340
TYPE: 2 NLEAF PAGES: 412 NLEVELS: 3 UNIQUE: D DUPLICATE OK
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IS BPOOL: BP10
+------------------------------------------------------------------+
|ANL7012I *** GUIDELINE: |
|Make sure that you have defined adequate Free Space parms in this |
|index structure, and in the data table, to keep rows in the|
|clustered order and avoid fragmentation. Both Freepage and Pctfree|
|should be deployed, and Runstats periodically run to update stats.|
+------------------------------------------------------------------+
INSERT VIA IDX
--------------
CREATOR: TDT690
TABNAME: L1000
INSERT PROCESSING APPLIED TO A 4K SEGMENTED TSPACE WITH 3 TABLES
VERSION: 0 TABLE CONTAINS A TOTAL OF: 31680 4K PAGES
TABLE ROWS: 100000 COLUMNS: 50 REC LENGTH: 1008 BYTES
DSSIZE: 0 GB NUMBER OF MQTS: 0 LOG: Y AVG ROW LENGTH: 1006 BYTES
TYPE: T LOCK SIZE: A TS LOCK MODE: IX LOCK PART: N CLOSE TABLE: Y
PAGES WITH ROWS: 99% PCT COMPRESSED: 0% MAX ROWS: 255 BPOOL: BP11
ENCODE: E CCSIDS ARE SBCS: 833 DBCS: 834 MIXED: 933 VOLATILE: N
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000012 WILL REQUIRE 28.69939 SECONDS OF ELAPSED TIME *
* DURING WHICH 25.63702 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 3 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 1400 ESTIMATED PROCESSING COST $ 5.8068 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
ANL5025W *** WARNING:
ESTIMATE OF 25.637 EXCEEDS "CPU TIME" LIMIT OF 10 CPU SECONDS!
ANL5026W *** WARNING:
ESTIMATE OF 1400 EXCEEDS "SERVICE UNIT" LIMIT OF 200 QUNITS !
The following example shows Update (query 13) processing.
EXPLAIN PLAN SET QUERYNO = 100000013 FOR
UPDATE TDT690.L1000
SET C2 = 'NEW11', C7 = 7654321
WHERE CIKEY = 100100
QUERYNO: 100000013 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
UPDATE OPERATION HAS BEEN REQUESTED TO CHANGE THE VALUE OF 2 COLUMNS
+------------------------------------------------------------------+
|ANL7001I *** GUIDELINE: |
|Make sure that your frequently updated columns are being kept |
|together in the table row: Db2 logs from the first to last changed|
|byte. Also, put Varchar at the end of the row: Db2 logs from the |
|first changed byte to end of row. Length changes cause the entire |
|row to log, as length field is kept with the row header. Rows that|
|are compressed are variable length & logged in compressed format. |
+------------------------------------------------------------------+
CLUSTER MATCH IX SCAN
---------------------
IX CREATOR: TDT690
INDEX NAME: L1000CIN
VERS: 0 KEY LEN: 6 PADDED: - C-ED: Y C-ING: Y CLUSRATIO: 99.9995
FULLKEY CARD: 99340 FIRSTKEY CARD: 99340
TYPE: 2 NLEAF PAGES: 412 NLEVELS: 3 UNIQUE: D DUPLICATE OK
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IX BPOOL: BP10
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 CIKEY A DECIMAL N 9 Y 99340 10
THIS IS THE CLUSTERING ("INSERT & LOAD ORDER") INDEX FOR THIS TABLE
+------------------------------------------------------------------+
|ANL6020I *** NOTE: |
|This index is presently designated as allowing "duplicate values".|
|Make sure this is a nonunique index that does not fall into one of|
|the following "unique" categories: explicitly unique, primary key,|
|non-primary RI parent key, unique where not null, unique column |
|constraint. Unique indexes have definite advantages whenever they |
|can be declared by the user, so be sure it contains duplicates. |
+------------------------------------------------------------------+
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
UPDATE VIA IDX
--------------
CREATOR: TDT690
TABNAME: L1000
UPDATE PROCESSING APPLIED TO A 4K SEGMENTED TSPACE WITH 3 TABLES
VERSION: 0 TABLE CONTAINS A TOTAL OF: 31680 4K PAGES
TABLE ROWS: 100000 COLUMNS: 50 REC LENGTH: 1008 BYTES
DSSIZE: 0 GB NUMBER OF MQTS: 0 LOG: Y AVG ROW LENGTH: 1006 BYTES
TYPE: T LOCK SIZE: A TS LOCK MODE: IX LOCK PART: N CLOSE TABLE: Y
PAGES WITH ROWS: 99% PCT COMPRESSED: 0% MAX ROWS: 255 BPOOL: BP11
ENCODE: E CCSIDS ARE SBCS: 833 DBCS: 834 MIXED: 933 VOLATILE: N
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000013 WILL REQUIRE 52.35994 SECONDS OF ELAPSED TIME *
* DURING WHICH 46.63751 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 20 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 2547 ESTIMATED PROCESSING COST $ 10.7093 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
ANL5025W *** WARNING:
ESTIMATE OF 46.638 EXCEEDS "CPU TIME" LIMIT OF 10 CPU SECONDS!
ANL5026W *** WARNING:
ESTIMATE OF 2547 EXCEEDS "SERVICE UNIT" LIMIT OF 200 QUNITS !The following example shows Delete (query 14) processing.
EXPLAIN PLAN SET QUERYNO = 100000014 FOR
DELETE FROM TDT690.L1000
WHERE CIKEY = 100100 OR RIKEY2 = 100300
QUERYNO: 100000014 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
A MULTIPLE INDEX OPERATION HAS BEEN REQUESTED TO ACCESS THIS TABLE:
--------------------------------------------------------------------------------
QUERYNO: 100000014 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 1
PROCESS ->
RANDOM MATCH IX SCAN
--------------------
IX CREATOR: TDT690
INDEX NAME: L1000P2N
VERS: 0 KEY LEN: 6 PADDED: N C-ED: N C-ING: N CLUSRATIO: 70.0010
FULLKEY CARD: 100000 FIRSTKEY CARD: 100000
TYPE: 2 NLEAF PAGES: 359 NLEVELS: 3 UNIQUE: U DECLARE UNIQ
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IX BPOOL: BP10
+------------------------------------------------------------------+
|ANL6052I *** NOTE: |
|This index is specified as "Not Padded", allowing storage of a |
|varying length index key. Padded indexes use blanks to fill out |
|their fixed length keys and are not eligible for Index Only scan. |
|"Not Padded" indexes do not blank fill CHAR and VARCHAR columns, |
|allowing greater flexibility and better use of storage, packing |
|more entries into a single Leaf page. Index Only access allowed. |
+------------------------------------------------------------------+
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 RIKEY2 A DECIMAL N 9 Y 100000 0
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
--------------------------------------------------------------------------------
QUERYNO: 100000014 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 2
PROCESS ->
CLUSTER MATCH IX SCAN
---------------------
IX CREATOR: TDT690
INDEX NAME: L1000CIN
VERS: 0 KEY LEN: 6 PADDED: - C-ED: Y C-ING: Y CLUSRATIO: 99.9995
FULLKEY CARD: 99340 FIRSTKEY CARD: 99340
TYPE: 2 NLEAF PAGES: 412 NLEVELS: 3 UNIQUE: D DUPLICATE OK
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IX BPOOL: BP10
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 CIKEY A DECIMAL N 9 Y 99340 10
THIS IS THE CLUSTERING ("INSERT & LOAD ORDER") INDEX FOR THIS TABLE
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
--------------------------------------------------------------------------------
STEP 3. MULTIPLE INDEX UNION WAS PERFORMED (MU).
--------------------------------------------------------------------------------
MULTIPLE INDEX OPERATIONS ARE NOW COMPLETE: DATA ACCESS PERFORMED.
QUERYNO: 100000014 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 3
PROCESS ->
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000014 WILL REQUIRE 8.26157 SECONDS OF ELAPSED TIME *
* DURING WHICH 2.24247 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 33 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 15 ESTIMATED PROCESSING COST $ 9.6957 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*