Posted by Hari Shanmugadhasan on 29 November 2013 at 12:58
p86 of the Nov 8 draft states:
"DB2 automatically deletes pseudo-empty index pages and pseudo deleted index entries independently of the SQL
But it doesn't seem to mention SQL UPDATE activity that causes of the delete and insert of index entries when index key values change.
It would be helpful to state whether the DB2 11 cleanup occurs regardless of how the pseudo-deleted index entries were created.
Posted by Hari Shanmugadhasan on 29 November 2013 at 13:12
Page 86 states:
"Index cleanup is performed only on the indexes that have been opened for INSERT/DELETE/UPDATE by other DB2 processes."
It would be helpful to clarify if this actually means that cleanup is only performed on the index PIECEs that are currently still in R/W (no R/O switch) after being opened by an I/D/U when the cleanup process starts. And if not at the PIECE level then it would be helpful to clarify if it is at the index PART level.
Posted by Hari Shanmugadhasan on 29 November 2013 at 13:22
Page 86 states:
"The presence of the pseudo deleted entries can be detected by SQL queries or INSERT/DELETE/UPDATE processes."
It would be helpful to elaborate on what this means and what it has to do with the DB2 11 cleanup process. It would be helpful to mention the existing re-use of pseudo-deleted index entries by INSERT and UPDATE and the supposed cleanup of entries with SELECT WITH RR (which was supposed to be in V4) when above some threshold.
Posted by Hari Shanmugadhasan on 29 November 2013 at 13:27
Page 86 states:
"Attention: This function is not a substitute for the REORG INDEX utility."
Please clarify this statement since the function seems to eliminate one of the reasons for REORG INDEX. DB2 10 expanded the use of list prefetch and earlier changes seemed designed to reduce the need for REORG INDEX. So it would be helpful to state what would motivate running REORG INDEX in a DB2 11 environment exploiting this function. Especially if REORG TS was occurring periodically..
Posted by Hari Shanmugadhasan on 29 November 2013 at 13:44
The Nov 24 draft of SG24-8182 states:
"DB2 11 has added a new capability of asynchronously removing pseudo-empty indexes pages and pseudo-deleted index entries by DB2 system tasks independently from SQL DELETE transaction, this function removes needs for REORG."
It seems to contradict page 86 of this Redbook which states:
"Attention: This function is not a substitute for the REORG INDEX utility."
It would be helpful if the two Redbooks were consistent.
Posted by Hari Shanmugadhasan on 29 November 2013 at 13:49
Page 95 of the Nov 24 draft of SG24-8182 states:
"When the SQL query reads a pseudo deleted index entry . . . which could cause a deadlock situation."
Yet page 85 of this Redbook states a different scenario:
" . . . result in time-outs and deadlocks for applications that insert data into tables with unique indexes."
It would be helpful if both Redbooks were consistent, correct and complete in describing the problem scenarios caused by pseudo-deleted index entries.
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:07
Page 87 states:
"When there is an index that needs to be cleaned up, DB2 checks the SYSIBM.SYSINDEXCLEANUP catalog table"
It would be helpful if it was explained how it was decided that the index "needs to be cleaned up" since there seems to be no catalog column or ZPARM that allows one to provide a threshold value.
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:16
It would be helpful if the page 86 description of INDEXCLEANUP_THREADS described how the threads are used. If the value is 128 will this mean that 128 partitions of a PI or DPSI can be processed in parallel?
It would also help if the prefetch and BP activity were described. Does each thread do 64 or 128 page sequential prefetches using FIFO or MRU (assuming 4K pages)?
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:33
It would be helpful if it were explained if there is any connection between the DB2 11 pseudo-delete function and RTS or the RTS counts (in-memory or externalized). And whether it invalidates any DSNACCOX REORG INDEX recommendations since page 315 doesn't list any changes related to this function.
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:39
Page 87 describes END_TIME but doesn't describe what happens if cleanup for an index has started but has not completed by the time the window has closed. Does some sort of error message occur? Are there periodic commits? Is there a final commit when the window closes? Does cleanup start in the next window at the last commit if it didn't finish the first time?
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:43
It would be helpful if page 86 provided information on how to gather information to decide on an appropriate value of INDEXCLEANUP_THREADS and how well the processing is working. Do some threads fail to complete in the window? Do some indexes fail to get start cleanup? Are some threads restarting from the previous window because they failed to complete?
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:49
It would help if page 86 indicated a starting point for the INDEXCLEANUP_THREADS value. Something like having the value less that n x (# zIIP engines) x (available zIIP capacity at the time of the window) including in the prefetch activities that the threads will drive on the zIIPs. With n depending on whether the index pages are in the BP, in DASD cache or on DASD. It would good to have this info in SG24-8182 also.
Posted by Hari Shanmugadhasan on 29 November 2013 at 14:57
It would be helpful if Page 90 provided more detail than just:
"Note: You can activate IFCID 0377 to monitor the cleanup processing for pseudo-empty index pages and pseudo-deleted index entries."
And if SG24-8182 discussed the reporting in detail.
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:12
Page 410 does provide a description of IFCID 377 but with no reporting mentioned. With no granularity, It does state:
"The IFCID 377 record is written once per each index page being cleaned up."
Which seems ridiculously frequent. One would typically be interested in a summary record per index, per part or per PIECE.
There is the inadequate warning:
"its volume can be large."
Why is it even being run if the number of pages to be cleaned isn't large, very large or enormous?
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:16
It would be helpful if p86 provided application scenarios illustrating when it made sense to do use this function instead of REORG INDEX PART n or REORG INDEX or even REORG TS.
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:26
It would be helpful if there was further explanation of the page 86 statement:
"If some of the pseudo-deleted entries in the page were not committed during the SQL DELETE processing, cleanup could not be performed."
The statement seems odd since if they weren't committed shouldn't those changes have been rolled back leaving them no longer pseudo-deleted? And thus the index page would no longer be pseudo empty?
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:29
It would be helpful if page 86 explained more about:
"The cleanup rate depends on several factors such as the rate that the pseudo deleted entries are generated, the number of threads allowed to run cleanup concurrently and the commit frequency of the unit of work which generates the pseudo deleted index entries."
The statement gives the impression that the cleanup is intended to run concurrently with DELETE activity deleting a massive number of rows.
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:32
Normally one would think that the proportion and distribution of existing, accumulated, pseudo deleted entries and pseudo empty index pages would be a key factor along with the prefetch rate that was possible.
Posted by Hari Shanmugadhasan on 29 November 2013 at 15:47
My earlier RTS question is partially answered on page 395, which explains that the pseudo delete process is driven by RTS and its counters.
The criteria seem to be oddly the absolute numbers instead of the proportion of rows or of pages. It seems an odd choice and criteria oddly different from the REORG INDEX criteria.
It would be helpful if page 85 cleanup section mentioned that there was greater detail on page 395.
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:10
It would be more helpful if most of the material starting on page 395 was consolidated with the material on page 85 to provide a common description of the pseudo-delete problem and solution without duplication, starting on page 85. This common description should be referenced by page in other parts of the Redbook when PD elements come up.
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:18
Figure 13-7 mentions REORGPSEUDODELETES on page 396 but there is no other mention of it. In particular, there is no mention of the cleanup process adjusting the value of REORGPSEUDODELETES to reflect the cleanup. Also no mention of the pseudo empty counter.
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:32
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:35
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:38
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:47
Page 396 states in stark contrast for the DB2 11 PD cleanup process:
"The RTS information is checked periodically to identify the indexes with the most pseudo-deletes . . . the index can only be cleaned up when a thread is freed up, and the index candidates are sorted based on the number of pseudo-deletes, so the ones with the most pseudo-deletes get cleaned up first."
This appears to answer my earlier questions about PART/ PIECE-level parallel function. There appears to be none, sadly.
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:51
As a general comment, a new version should explain in detail the choice to deviate substantially from previous recommendations in automated or other functions.
Especially when those recommendations are still in place for the version in other areas. Even more so when the logic seems more limited and inferior to long standing recommendations.
Posted by Hari Shanmugadhasan on 29 November 2013 at 16:59
Two posts back the quote was from page 395, not 396.
Page 395 says:
"which checks the RTS by looping through RTS blocks for all objects in the system, and identifies the candidate indexes for cleanup."
The word "looping" is disturbing since it suggests unnecessary repetition especially since there is no mention of thresholds or of REORGPSEUDODELETES being corrected (especially the externalized counts).
Posted by Hari Shanmugadhasan on 29 November 2013 at 17:14
Figure 13-9 on page 397 shows a very odd performance comparison given the "Simultaneous index only query" workload.
If DB211 PD cleanup worked correctly the PDs and PEs should be eliminated each window, so the DB2 11 line should be just rippling along the bottom with a max of say a 2% CPU increase instead of curving up to 20%.
Also strange is how it eventually slowly curves down to 15%. How could this happen? It would be helpful to explain and provide the CPU cost tradeoff of the cleanup.
Posted by Hari Shanmugadhasan on 29 November 2013 at 17:28
Figure 13-7 on page 396 states:
"Child cleanup thread only started if Index already open for INSERT,UPDATE or DELETE
* ‘X’-type P-lock already held"
It would be helpful to explain the implications. Does this mean that the thread doing PD cleanup for an index serially checks each partition/PIECE for the member p-lock, processing it if the p-lock is there or skipping it if it isn't, then checking the next partition/PIECE for the p-lock?
What happens if an IX p-lock? Or non-datasharing?
Posted by Hari Shanmugadhasan on 29 November 2013 at 17:37
It would be helpful if the page 85 DB2 11 PD cleanup section discussed the implications for concurrent utilities (especially single part with NPI or NPSI) and other workloads including the cleanup commit frequency, timeout wait, deadlock wait and retry logic (if any).
Posted by Hari Shanmugadhasan on 29 November 2013 at 17:45
Page 324 states:
"Autocommit performance improvements for procedures and cursors"
It would be helpful to describe these improvements and how to achieve them.
Reducing the number of DRDA network messages with AUTOCOMMIT(YES) was mentioned in the recent DB2 11 webcast, but not even that appears to be mentioned here.
Posted by Hari Shanmugadhasan on 30 November 2013 at 13:29
Since the area of pseudo empty index page cleanup has been a source of previous difficulties, it would be helpful if the section on DB2 11 PD and PE cleanup discussed the issues involved in the APARS PM13667 (V8/V9), PM43695 (v9/V10), and PM56537 (v9/v10) which involve previous forms of pseudo empty cleanup.
Explaining the impact of data sharing on cleanup would also be helpful.
Posted by Hari Shanmugadhasan on 30 November 2013 at 13:40
It would be helpful if the section on PD cleanup (page 85+) discussed how the DB2 11 function affects the discussion in APAR PM17194 that involves various lock related ZPARMs
Posted by Hari Shanmugadhasan on 1 December 2013 at 12:36
It would be helpful to include the changes implemented by PM97243 and any other related DB2 11 APARS affecting PD cleanup.
Posted by Hari Shanmugadhasan on 1 December 2013 at 12:39
It would be helpful to make clear if DB2 11 retained, changed or eliminated the previous PD and PE cleanup logic including the changes provided by APARS PM13667 (V8/V9), PM43695 (V9/V10), and PM56537 (V9/V10).
Posted by Hari Shanmugadhasan on 1 December 2013 at 14:36
The Nov 30 draft has consolidated the two major sections on the DB2 11 pseudo delete cleanup function. The material that previously started on page 85 has been consolidated with the page 395+ material and now starts on page 395. This is an improvement.
There are also change bars which is also good.
I haven't checked anything else.
Posted by Hari Shanmugadhasan on 2 December 2013 at 9:59
There is now a Dec 1 draft. The change bars that appeared in the Nov 30 draft have disappeared which makes the document harder to review.
My #28 comment about the performance chart with "Simultaneous index only query" workload has been addressed by removing the chart instead of explaining the odd/poor PD cleanup results. This is unfortunate.
I don't see any changes relating to my #31 comment on AUTOCOMMIT performance.
Posted by Hari Shanmugadhasan on 2 December 2013 at 10:18
Page 396 states:
"In DB2 11, in addition to the cleanup that was previously done . . ."
Which deals with much of my #35 comment except it doesn't reveal what that cleanup specifically did and what combination of APARS were applied since PM43695 (V9/V10), and PM56537 (V9/V10) seemed to be intended for only special cases when IBM Service recommended them.
Posted by Hari Shanmugadhasan on 2 December 2013 at 10:58
My comment #4 regarding "not a substitute for the REORG INDEX" was addressed by removing the statement and by the p394 "Reduced need for REORG" discussion. Which also addresses #5 with the Nov 30 SG24-8182.
#6 is still a problem because although the Nov 30 SG24-8182 draft is better in terms of explanation it's inconsistent because it fails to mention "time-outs" (now on p395 of this Redbook). Both Redbooks fail to mention undesirable lock delays that don't reach timeout.
Posted by Hari Shanmugadhasan on 2 December 2013 at 11:10
My comment #9 was partially addressed, but "in-memory or externalized" remains unresolved. Figure 13-6 shows the externalized RTS table being used, but also on page 397 it states:
"which checks the RTS by looping through RTS blocks for all objects in the system"
With "blocks" sounding like in-memory and not the externalized values in the table.
Also the question about DSNACCOX remains unresolved (now pages 313-314).
Posted by Hari Shanmugadhasan on 2 December 2013 at 11:22
Most of #19 and #20 were fixed by consolidation of the page 85+ and page 395+ PD cleanup sections into a consolidated section starting page 394.
But the criteria seem to be oddly the absolute numbers instead of the proportion of rows or of pages. It seems an odd choice and criteria oddly different from the REORG INDEX criteria.
"The RTS information is checked periodically to identify the indexes with the most pseudo-deletes." And Fig 13-6 indicates something similar.
Posted by Hari Shanmugadhasan on 2 December 2013 at 11:27
The rest of my pre-Dec 2 comments still seem to apply except that the page numbers and Figure numbers have changed since the Nov 8 draft with the page 85+ references moving to page 395. There may be slight wording differences.
Posted by Hari Shanmugadhasan on 2 December 2013 at 11:55
Page 394 oddly states:
"The benefit of clustering is normally associated with the performance of a range scan"
The associated paragraph neglects the importance of effective sequential detection for skip sequential singleton SQL workloads and the importance of avoiding synch I/O.
On the same page it discusses DB2 10 RLSD which improved sequential detection but still needs clustering sequence, though less perfect.
Discussing the use of the V10 RTS column REORGCLUSTERSENS might be helpful.
Posted by Hari Shanmugadhasan on 2 December 2013 at 12:07
Page 394 states:
"However, when your query uses a screening predicate, it is often true that REORG will not reduce the number of GETPAGEs for such queries."
If I get MATCHCOLS = 3 on the clustering index and also have "a screening predicate" then a REORG will typically still reduce I/O times and GETPAGEs. Especially if the predicate screens out some partitions.
Even if MATCHCOLS=1 it can produce better results. I/O type is also important. So " it is often true" seems misleading.
Posted by Hari Shanmugadhasan on 2 December 2013 at 12:13
Page 394 states:
"If you reorganize the index and use PCTFREE 10, you will shrink the index and increase the likelihood of more index splits. Thus, do not try to use REORG for the purpose of avoiding index splits."
But if you REORG INDEX or REORG INDEX PART every time the index pages are getting close to full then you will avoid index splits. This is the reason for having a regular REORG schedule or monitoring to proactively REORG.
Posted by Hari Shanmugadhasan on 2 December 2013 at 12:23
Page 394 states:
"it will tend to have about 25% free space."
This is the long term average since the pages move between 50% after split to 0% just before split, given random inserts. But it is not considered desirable to have repeated periods where almost every random insert causes a index page split resulting in double the number of index pages. This might happen at peak periods.
25% is not the steady state, so the sentence seems misleading.
Posted by Hari Shanmugadhasan on 2 December 2013 at 12:27
Page 394 states:
"If you never reorganize an index and randomly insert keys into it"
And your other accesses are also random then avoid the indexes by considering a V10 hash access tables (which still need REORGs).
Posted by Hari Shanmugadhasan on 2 December 2013 at 13:01
I botched my previous comment about hash access tables. It was meant to suggest that page 394 might suggest considering hash access for certain workloads since those seem like workloads that don't need REORG INDEX (random insert, random access, no benefit from clustering sequence). So avoid the problem by not having the index.
Posted by Hari Shanmugadhasan on 9 January 2014 at 15:20
The December 31, 2013 final version of the Redbook does not appear to address any of the outstanding issues that I commented on previously.
The page and figure numbers of my previous comments are now out of date, but most of my comments relate to material that is now on pages 390-393 ("13.3 Reduced need for REORG" and especially "13.3.1 Asynchronous removal of pseudo-deleted indexes").
Posted by Mr. Henrik Henriksen on 27 January 2014 at 8:40
6.10 CUBE, ROLLUP is not correct. Group By rollup is not equivelent to the sets of GROUP BY GROUPING SETS. It is equivelent to the set of GROUP BY. Eg: GROUP BY ROLLUP(a,b) is Is equivalent to
GROUP BY ((a,b),
And NOT Is equivalent to
GROUP BY GROUPING SETS((a,b),
GROUP BY and GROUP BY GROUPING SETS is totally different.
Same Error in CUBE.
Posted by Mr. Perry Shindle on 10 May 2016 at 12:01
The latest update of this Redbook dated 05 May 2016 does not appear to contain any new changes. There are no changes documented under the "Summary of Changes" heading for May 2016. Perhaps the updated edition did not get properly uploaded. Please investigate. Thanks.