DB2 10 for z/OS Technical Overview

Readers' comments

Readers' comments (26) 

lockedThis discussion is now locked


Posted by Hari Shanmugadhasan on 28 December 2010 at 10:39

Page 103 says:

“...is placed in REORG pending (REORP) state. This state is a restrictive state. You must run a REORG before you can use it. The REORG must either be SHRLEVEL REFERENCE or SHRLEVEL CHANGE."

It would be helpful to mention if this means that in V10 any REORP state can reset by REORG with SHRLEVEL REFERENCE or CHANGE, instead of the previous restriction to just NONE.

Posted by Hari Shanmugadhasan on 28 December 2010 at 10:41

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to state if a SYSCOPY row is inserted for each partition range, each with its own appropriate STYP values.

Posted by Hari Shanmugadhasan on 28 December 2010 at 10:43

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to state if REORG parallelism is done separately for each partition range or if it is determined across the aggregate of partition ranges.

Posted by Hari Shanmugadhasan on 28 December 2010 at 10:45

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to state if this technique processes the REORG any differently than SCOPE PENDING, other than the determination of partitions to REORG.

Posted by Hari Shanmugadhasan on 28 December 2010 at 11:05

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to state if the REBALANCE restrictions still apply in V10. In particular, REBALANCE is not mentioned in the section discussing mismatched logical and physical partition numbers.

Posted by Hari Shanmugadhasan on 28 December 2010 at 11:22

Page 556 in "13.2.5 Aggressive view merge" says that with exceptions, correlated table expressions will be merged. It would be helpful to explain why this is desirable, since someone deliberately coded "TABLE(" typically in order to deliberately avoid merging. For example, they only want the first few rows of the answer set, so they want to deliberately force row by row materialization.

Posted by Hari Shanmugadhasan on 28 December 2010 at 11:23

Page 556 comment continued.

It is true that the listed merge exceptions do cover some of the most frequent uses of this technique. But OPTIMIZE FOR n ROWS and FETCH FIRST n ROWS ONLY are not mentioned in the exceptions. Further explanation of the reasons and implications would be helpful.

Posted by Hari Shanmugadhasan on 28 December 2010 at 11:39

In my earlier comment - 28 December 2010 at 10:41, I should have written "STYPE value", instead of "STYP values".

Posted by Hari Shanmugadhasan on 28 December 2010 at 11:48

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to state if REORG SHRLEVEL REFERENCE and CHANGE still do individual, unsorted, shadow NPI index entry inserts (only) for all the index entries corresponding to all the disjoint partition ranges. The implication being that after a REORG TS PART the NPIs can be left in quite a mess and REORG INDEX of the NPIs might be called for.

Posted by Hari Shanmugadhasan on 28 December 2010 at 12:12

In pages 550-551 "13.2.2 RID pool enhancements", it would be helpful to explain why RID pool processing now can be desirable if more than 25% of rows are expected or have been processed. List prefetch was historically slower than sequential prefetch, so list prefetch of over 25% of rows, suggests list prefetch of almost every page, so table space scan would be faster. New DASD techniques have made table space scan even faster.

Posted by Hari Shanmugadhasan on 28 December 2010 at 12:31

In section "13.2.4 IN-LIST enhancements" it would be helpful to state the in-memory technique's implications, if any, for: INLISTP, predicate push down, predicate push up, query parallelism, page range scan (limited partition scan, partition pruning, DPSI partition pruning), order of items in the list etc.

Posted by Hari Shanmugadhasan on 28 December 2010 at 12:39

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to explain the in-memory technique's use of the RID pool. Is it like index ORing, where each item in the list has a RID list and the RID lists are ORed and then the final combined sorted list drives list prefetch?

Posted by Hari Shanmugadhasan on 28 December 2010 at 12:56

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to state if the predicate type:

(COL1,...COLn) IN (noncor subq)

can use the new in-memory technique. In V9, it can use the N access type.

Posted by Hari Shanmugadhasan on 28 December 2010 at 13:58

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to state what In-list cases support page range screening when there is one or more
In-list predicate on one or more non-leading partitioning key column (no partitioning index). In particular, it would be helpful to know when page range screening is disabled as the fix for PM12074 does for its particular scenario or other V9 situations that disable it.

Posted by Hari Shanmugadhasan on 28 December 2010 at 15:05

More generally, it would be helpful to know what predicates, under what circumstances, will provide page range screening and any restrictions. Do they all work for DPSI, NPI and partition scan access with predicates on one or more non-leading partitioning key column (with no partitioning index), without
REOPT(VARS) for runtime pruning?

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:22

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to to clarify if the in-memory access path is supported if there are also non-IN-LIST matching predicates. The example and the words
"If IN-list predicate is selected as the matching predicate..." make it seem that it may only occur if IN-LIST predicates are the only matching predicates.

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:25

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to to clarify if the in-memory access path applies only to single table access or forces a particular join order. The example PLAN_TABLE shows the IN-LIST table as the inner table, with the in-memory table as the outer table.

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:29

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to to clarify if the in-memory access path can pick Hybrid Join instead of the NLJ method illustrated in the example. Hybrid Join would typically do more efficient List Prefetch given a badly clustered index.

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:31

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to to clarify if the in-memory access path can be obtained or influenced by using OPTHINTS. Including "forcing" Hybrid Join instead of NLJ.

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:47

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to state the limits on the number of values or value combinations that can fit in an in-memory table. All though it says: "There is no limit to the number of IN-list predicates that DB2 builds into an in-memory table for processing", it would seem likely that three IN-LISTS each with lists for 100 CHAR(20) values, would require at least 100 x 100 x 100 x20 or 20,000,000 bytes.

Posted by Hari Shanmugadhasan on 30 December 2010 at 11:50

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to state how the maximum
in-memory table size is determined. Is there a ZPARM or formula?

Posted by Hari Shanmugadhasan on 30 December 2010 at 12:02

In section "13.2.4 IN-LIST enhancements", page 554, it would be helpful to state if the PTC support is just for inner joins or whether it also applies to outer joins. Page 555 lists COL1=COL3, yet this predicate type does not get PTC for outer joins. Page 555 should indicate the restriction for COL1=COL3.

Posted by Hari Shanmugadhasan on 30 December 2010 at 12:04

In some of my earlier comments I made the typo "to to clarify", I intended "to clarify. "All though" should have been "Although".

Posted by Hari Shanmugadhasan on 30 December 2010 at 12:42

For section “10.4.3 Improved usability of REORG of disjoint partition ranges”, it would be helpful to explicitly state that V10 enhances LISTDEF PARTLEVEL to support parallel processing of partitions from multiple disjoint partition ranges for a variety of utilities. For example: COPY, RECOVER, UNLOAD or REORG TS.

Posted by Hari Shanmugadhasan on 31 December 2010 at 10:15

FYI, my previous Dec 28 and Dec 30 comments were based on the Dec 14 draft. As a result the page and some section number references do not match the Dec 30 final version.

Posted by Linda Claussen on 12 January 2012 at 16:36

In Figure 12-7 Catalog tables for security and auditing in DB2 10 you show SYSIBM.SYSEXEMPTIONS as a new catalog table but it was not created at install time. Was this a table to support granting exemtions and did not make GA?


Profile

Publish Date
30 December 2010

Last Update
16 July 2014


Rating:
(based on 3 reviews)


Author(s)

ISBN-10
0738435112

IBM Form Number
SG24-7892-00