DB2 9 for z/OS: Using the Utilities Suite

Readers' comments

Readers' comments (57) 

lockedThis discussion is now locked


Posted by Vatsal Raicha on 22 December 2009 at 2:10

Very useful book

Posted by John Weil on 28 December 2009 at 9:57

Good book. Thanks.

Please include an official syntax example for cross-loader partition parallelism. I've received conflicting advice on the best way to do this from IBM.

Posted by Hari Shanmugadhasan on 29 December 2009 at 17:44

The page 40 explanation of the REORG REBALANCE logic appears to be a bit outdated and self-contradictory. It seems to combine the current logic with the deficient old REBALANCE logic. It starts with the objective of evenly distributing rows across partitions, but then switches to describing the previous logic of trying for evenly distributing pages:

"When reloading the data, REORG TABLESPACE notes the value of the partitioning columns as each partition reaches the page count threshold."

Posted by Hari Shanmugadhasan on 29 December 2009 at 17:48

More on the page 40 self-contradition described previously:

"REBALANCE specifies that REORG TABLESPACE should set new partition boundaries so that all the rows participating in the reorganization are evenly distributed across the partitions being reorganized ...Then REORG TABLESPACE with REBALANCE calculates the approximate number of pages expected ... When reloading the data, REORG TABLESPACE notes the value of the partitioning columns as each partition reaches the page count threshold."

Posted by Hari Shanmugadhasan on 29 December 2009 at 18:22

The page 42 section "Implications for PIT Recovery" seems at best misleading and incomplete. "...RECOVER will ensure that all partitions affected are listed in the RECOVER utility statement" seems misleading in the sense that the RECOVER will fail with RC 8, which is important to know and not what I would have expected from the sentence, though failing does "ensure". "partitions affected" implies that it is the just the partitions that were ALTERed that need to be listed.

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:02

Page 40 oddly states:
"The second reorganization (without the REBALANCE keyword) orders each data row in clustering order (based on the partitioning index) within the appropriate partition."

It should actually state "(based on the clustering index)", not the "(...partitioning index)"

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:03

Page 41 oddly states:

"You can modify the limit keys for table-based partitioning table partitions. with the ALTER
TABLE ALTER PART VALUES statement"

which seems odd since the V8 and V9 manuals don't indicate that there is such a statement. Also there appears to be an unnecessary period in the middle of the sentence.

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:05

Page 42 oddly states:

"The traditional way to change boundaries is to issue the following commands:
ALTER TABLE table-name ALTER PART partno VALUES (value)..."

which seems odd since it was V8 that first provided table-controlled partitioning, so there is not much of a basis for a "traditional way". Plus table-controlled boundary changes was introduced through new DDL SQL syntax, not a "command". The specific DDL SQL syntax for V8 and V9 is not as it is provided in the above quotation.

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:06

Page 42 oddly states:
"The traditional way to change boundaries is to issue the following commands:
ALTER TABLE table-name ALTER PART partno VALUES (value)
and
ALTER INDEX index-name ALTER PARTpartno VALUES (value)
when you use an index controlled partitioned table space."

which seems odd since they are both DDL SQL, not "commands", plus they are linked with "and" which would seem to mean both need to be executed, which isn't true.

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:07

Page 42 oddly states:

"....ALTER INDEX index-name ALTER PARTpartno VALUES (value)
when you use an index controlled partitioned table space."

which seems odd since the SQL has syntax errors in addition to using a syntax style that has not been preferred starting with V8.

Posted by Hari Shanmugadhasan on 30 December 2009 at 9:28

p41 states:

"Do not run REORG REBALANCE on a partitioned table space where a subset of partitions have the COMPRESS YES attribute and the remaining partitions have the COMPRESS NO attribute."

Do you get an error or warning message? It is not clear why there should be any problem after the REBALANCE logic has shifted to just dealing with the number of rows, instead of the number of pages.

Posted by Hari Shanmugadhasan on 4 January 2010 at 13:17

Towards the end of the V9 ALTER TABLE description (.../db2z_sql_altertable.htm) I see:

"PART integer VALUES can be specified as an alternative to PARTITION integer ENDING AT."

which indicates that several of my earlier comments are invalid. However, I suggest that it would be better to use the "modern" syntax "TABLE ALTER .... PARTITION integer ENDING AT..." syntax, even if the other syntax is allowed.

Posted by Hari Shanmugadhasan on 4 January 2010 at 13:26

My earlier comments were based on the Dec draft. As a result, some of my earlier page references are now off by a page in the Jan 4 draft.

Posted by Hari Shanmugadhasan on 1 February 2010 at 15:08

In the Jan 30, 2010 draft, on page 42, I see that 5 of my earlier comments have been addressed by replacing the ALTER... VALUES syntax with the "modern" ENDING AT syntax, and also by cleaning up some odd/confusing associated wording. Thank you.

But there are still 2 cases of using PART syntax instead of the "modern" PARTITION syntax, which is now used once on page42.

Posted by Hari Shanmugadhasan on 1 February 2010 at 15:25

In the Jan 30, 2010 draft, page 42 has
two cases of "...ALTER PART partno...", however the actual syntax diagram in the V9 manual uses "integer" instead of "partno".

If you wanted to deviate from the standard syntax, in order to provide greater clarity, then it might be better to do something like:
ALTER PARTITION physical_partition_number ...

Posted by Hari Shanmugadhasan on 1 February 2010 at 15:27

Continuing the above comment about page 42 having
two cases of "...ALTER PART partno...".

But it might be better stil to move these statements to just before the earlier paragraph that states the need to use the physical partition number and stick to the standard syntax of "...ALTER PARTITION integer..."

Posted by Hari Shanmugadhasan on 1 February 2010 at 15:46

In the Jan 30, 2010 draft, the page 42 paragraph mentioning "physical partition number" would be enhanced by pointing out that all utiltiy and DDL partition references are to the physical partition number, not just ALTER PARTITION.

It would be further enhanced by pointing out that ROTATE causes the difference between logical and physical partition numbers and how ADD PARTITION further complicates things.

Posted by Hari Shanmugadhasan on 1 February 2010 at 15:55

Continuing the comment on enhancing the page 42 paragraph mentioning "physical partition number".

Since the distinction between physical and logical partition numbers is important. It makes sense that this material be referenced for each and every utility and DDL that has a partition reference. It might makes sense to have a separate section: Physical vs. Logical Partition Issues and Considerations, that each utility and DDL statement, with a partition reference, then references.

Posted by Hari Shanmugadhasan on 1 February 2010 at 16:15

Page 42 says incorrectly: "Any ALTER, related to a partitioned table has to specify the physical partition number."

Actually, most ALTERs on a partitioned table do NOT need to specify the physical partition number. Most cannot specify it. For example: ALTER TABLE.... AUDIT NONE cannot .

It is only ALTER statements that are actually using "PARTITON integer" syntax, that need to specify the physical partition number.

Posted by Hari Shanmugadhasan on 1 February 2010 at 16:25

The page 42 ALTER examples with
"...ENDING AT (constant)", are incorrect for situations involving more than one partitioning column. Each is also missing the final semi-colon.

"...ENDING AT (constant1, constant2,....);" or even better, using the syntax in the V9 manual, with MINVALUE and MAXVALUE, would make the general case clearer.

Posted by Hari Shanmugadhasan on 1 February 2010 at 16:36

On page 42, the two references to ALTER TABLE...ALTER, imply they are only for table controlled partitioning, when in fact they also work for index controlled partitioning,

It would then be worth mentioning that in that case, index controlled gets converted to table controlled and what the implications are of that change and whether it is recommended to convert that way.

Posted by Hari Shanmugadhasan on 1 February 2010 at 16:43

Page 42 does have a sentence that starts
"If the table uses index-controlled partitioning..." but it is located in a place where it doesn't make much sense.

Posted by Hari Shanmugadhasan on 1 February 2010 at 16:52

Page 42 would be improved by using consistent terminology. One place says "table-based", another uses "table controlled", while the V9 manual uses "table-controlled". Matching the V9 manual would probably be best.

One place says "index-controlled" another: "index controlled". One place says "limit keys", others: "partition boundaries". There is also the spelling mistake: "partitionend"

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:17

Page 42, the sentence:
"The high limit key for the last partition is set to the highest possible value for ascending key columns..."

is not true in general. It would be good to state the particular context in which it is true and desirable. A reference to a common section disussing other implications, options and recommendations might makes sense.

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:23

Page 42 says: "...except for the last..." but doesn't say what happens in that exception case.

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:34

The page 42 section "Implications for PIT recovery' has been improved reflecting my earlier RC 8 comment.

However it is still using "partitions affected" which is a dangerously incomplete phrase, since it implies that it is just the partitions that were ALTERed that need to be listed (based on the earlier use of "affected" on page 42).

Also "If not affected all..." is garbled.

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:41

The page 42 section "Implications for PIT Recovery", is missing the implications of different choices of time for the PIT recovery and the implications of various actions having occured or not occurred between a given ALTER and the present.

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:45

My earlier "(based on the clustering index)" correction has been applied to page 41. Thank you.

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:47

The page 41 explanation of the REORG REBALANCE logic is still outdated and self-contradictory. It seems to combine the current logic with the deficient old REBALANCE logic. It starts with the objective of evenly distributing rows across partitions, but then switches to describing the previous logic of trying for evenly distributing pages:

"When reloading the data, REORG TABLESPACE notes the value of the partitioning columns as each partition reaches the page count threshold."

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:55

Some more illustration of the page 41 outdatedness:

"REBALANCE specifies that REORG TABLESPACE should set new partition boundaries so that all the rows participating in the reorganization are evenly distributed across the partitions being reorganized ...Then REORG TABLESPACE with REBALANCE calculates the approximate number of pages expected ... When reloading the data, REORG TABLESPACE notes the value of the partitioning columns as each partition reaches the page count threshold."

Posted by Hari Shanmugadhasan on 1 February 2010 at 17:58

p41 still does not justify or explain a bullet that seems based on the old logic:

"Do not run REORG REBALANCE on a partitioned table space where a subset of partitions have the COMPRESS YES attribute and the remaining partitions have the COMPRESS NO attribute."

Do you get an error or warning message? It is not clear why there should be any problem after the REBALANCE logic has shifted to just dealing with the number of rows, instead of the number of pages.

Posted by Hari Shanmugadhasan on 1 February 2010 at 18:09

The page 40 paragraph: "For index established partitions for the table space you can use the ALTER INDEX index-name PART x VALUES (`new-limit-key') ..."

should either be deleted or be changed to reflect several of my above Feb 1 page 42 comments, along with using the "modern" ENDING AT syntax.

Posted by Hari Shanmugadhasan on 1 February 2010 at 18:20

It would be more understandable if pages 40-43 were reordered so that a section "Manual partition boundary changes" discusses ALTER....ENDING AT...fully and then is followed by a section "Automated partition rebalancing of rows" which is just about REBALANCE and its potential advantages over ALTER.

Posted by Hari Shanmugadhasan on 19 February 2010 at 12:44

It looks like the February 19, 2010 final version did not make changes to address the issues contained within my previous 20 posted comments. Some of the page references have changed slightly, but otherwise it appears that the issues are not resolved.

Posted by Mr. Roy Boxwell on 23 February 2010 at 7:50

on page 394 of the book (422 of the pdf) there is an interesting paragraph

Merely set the REORP (REORG
PENDING) flag on these partitions by running the REPAIR utility

Now I know how to do this but as this is an undocumented feature of DB2 I think its unfair on the general readership! Either the undocumented feature should be documented or this paragraph must be deleted!

Posted by Hari Shanmugadhasan on 27 February 2010 at 13:21

I agree with Mr. Roy Boxwell's Feb 23 posting. I would add that the fact that page 394 makes no mention of the trick's nasty results/implications, makes the paragraph dangerous.

Posted by Hari Shanmugadhasan on 27 February 2010 at 13:22

Pages 42-43 of the Feb 19, 2010 final version, do not provide a clear and complete discussion of the nasty results/implications of ALTERing partition boundaries.

Posted by Hari Shanmugadhasan on 27 February 2010 at 13:24

The Feb 19, 2010 final version, does not provide a clear and complete discussion of the nasty results/implications of REORP. Such a discussion should be referred to whenever the possibility of REORP rears its ugly head. Such a discussion should also discuss how to avoid or reduce the occurrence or impact of REORP.

Posted by Hari Shanmugadhasan on 27 February 2010 at 13:25

Page 42 of the Feb 19, 2010 final version, has a paragraph starting: "If you change partition boundaries...". This paragraph is confusingly worded, incomplete and misleading. This paragraph suffers from similar issues as those previously posted about "Implications for PIT Recovery" in section 2.4.4.

Posted by Hari Shanmugadhasan on 28 April 2010 at 17:52

The April 27, 2010 updated version has some modifications that address a few of my earlier unaddressed postings. The rest remain unaddressed. Please note that page numbers may have changed due to the updates. Please look for the change bars on the left, to located modified areas in the redbook.

Posted by Hari Shanmugadhasan on 28 April 2010 at 17:55

The page 41 description of the REBALANCE logic, in the April 27, 2010 updated version, has been corrected to remove the outdated and
self-contradictory talk of "pages", that I commented on previously. It is now consistent in talking about "rows". Thank you.

Posted by Hari Shanmugadhasan on 28 April 2010 at 17:56

Page 42 of the April 27, 2010 updated version has eliminated the bullet:

"Do not run REORG REBALANCE on a partitioned table space where a subset of partitions have the COMPRESS YES attribute and the remaining partitions have the COMPRESS NO attribute."

In earlier postings, I had asked questions about it and expressed doubts about its correctness. Thank you for eliminating this bullet.

Posted by Hari Shanmugadhasan on 28 April 2010 at 17:59

Sadly, the March 2010 V9 Utilities manual still has this out of date info (from the eliminated bullet, see previous post). Please see http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.ugref/db2z_beforerunningreorgtablespace.htm.

The older V8 Utilities manual also has this problem.

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:00

Page 43 of the April 27, 2010 updated version, has been changed to correct the garbled sentence syntax of: "If not affected,...". Thank you.

Unfortunately the previously noted problem with "affected partitions" and "partitions affected" remains unaddressed, along with other previously noted problems with the "Implications for PIT recovery" section.

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:02

Page 43 of the April 27, 2010 updated version, has been changed to use the "ALTER PARTITION integer" syntax, in two places, instead of the "ALTER PART partno" syntax. This addresses one of my earlier issues. Thank you.

Sadly, both of these ALTERs are still missing the final semi-colon and still specify "(constant)".

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:03

Page 43 of the April 27, 2010 updated version, has been changed to add the hyphen to one case, to give "index-controlled". Thank you.

Sadly, the hyphen wasn't added to a case of "table controlled".

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:06

Page 43 of the April 27, 2010 updated version, has added what appears to be an incorrect or misleading sentence:

"In both cases, if data exists in the partition where the range was decreased, the partitions on both sides of the boundary are placed in REORP status."

It also contradicts the first paragraph of page 43.

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:09

Page 43 of the April 27, 2010 updated version, has replaced "... PART(partno:partno) ...",
with "... PART(n:m) ...", which appears to attempt to communicate the partition range concept.

Unfortunately, m comes before n, in the English alphabet, so the syntax illustrates the wrong order.

It would be better to use the V9 Utilities manual syntax:
:
"... PART integer1:integer2 ..."

Posted by Hari Shanmugadhasan on 28 April 2010 at 18:10

Page 396 of the April 27, 2010 updated version, has eliminated the paragraph that suggested the use of an undocumented trick that Mr. Roy Boxwell and I commented on. Thank you.

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:05

Sadly, part of the March 2010 V9 Utilities manual:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.ugref/db2z_utl_reorgtablespace.htm

does use " PART n:m " (but at least no parentheses). Some other sources also use it. But that is no reason to repeat the mistake in this Redbook.

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:08

The REBALANCE logic on page 41 of the April 27, 2010 updated version, retains a component of the outdated page based logic:

"..., taking into account the percentage of free space allowed on each page, as well as the free pages specified (can differ between partitions)."

It should be deleted. With the current row based logic, the partition level values of PCTFREE and FREEPAGE should not be a factor in determining the number of rows that should be in a partition.

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:13

Page 42 of the April 27, 2010 updated version, mentions this possibility:

"...or cause one or more partitions to have no rows."

Unfortunately it doesn't mention the nasty results/implications when this happens.

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:19

Page xxvii of the April 27, 2010 updated version makes this odd claim:

"The purpose of REBALANCE has always been to balance the number of rows per partition."

This would seem to be contradicted by the first two sentences of a May 30 2006 DB2-L posting "Item #87674 (30 May 2006 11:24) - Re: improper REBALANCE", "From: Jim Ruddy": "Just a point of correction. The purpose of REORG REBALANCE is to achieve relative balancing of pages across partitions, not rows."

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:27

The page xxvii claim: "The purpose of REBALANCE has always been to balance the number of rows per partition." also seems to be contradicted by:

page 25 of http://www.hoadb2ug.org/Docs/ruddy_060903.pdf

and page 8 of ftp://service.boulder.ibm.com/software/data/db2zos/SmithUtilityUpdate.pdf

Note the specific issue is with: "always has been".

Posted by Hari Shanmugadhasan on 29 April 2010 at 12:34

Page xxvii would be more accurate, if instead it said something like:

"REORG REBALANCE was changed via V8 and V9 maintenance, after GA, so that its new purpose is to attempt to balance the number of rows per partition, instead of pages."

Posted by Hari Shanmugadhasan on 29 April 2010 at 13:37

I made a mistake in my 29 April 2010 at 12:27 post. I should have written:

Note the specific issue is with: "has always been".

I incorrectly quoted: "always has been".

Sorry. The basic issue remains.

Posted by Stephen Baker on 10 September 2010 at 10:09

Section 8.2 page 216: The unload/reload of the whole NPI during a REORG SHRLEVEL CHANGE (or
REFERENCE) PART is essentially equivalent to a REORG INDEX of the NPI. If you currently
run REORG INDEX on all NPIs following a REORG PART, this action should no longer be
needed, and we recommend changing your existing jobs.

Per listserv discussion this week, partition level reorg does not Reorg NPIs.


Profile

Publish Date
19 February 2010

Last Update
27 April 2010


Rating:
(based on 7 reviews)


Author(s)

ISBN-10
0738434051

IBM Form Number
SG24-6289-01