[z/OS] "Clear out" partitions by LOAD dummy

Jorge Martelanz

[z/OS] "Clear out" partitions by LOAD dummy
I hope you can contribute to the "brain storming" we are doing here with this scenario:

1. DB2 9 for z/OS in CM
2. Table A has 100M rows and is partitioned
3. Table A has 10 NPIs
3. Tables B and C are "childs" of table A (RI) with "delete restrict"
4. Tables are used 24x7 in online transactional environment.
5. We plan to "clear out" some partitions of A by "LOAD REPLACE" with dummy input (4M rows aprox.)
6. We know that B and C have no rows related to the ones to be "cleared" in A

Now the questions

1. What type of contention can we expect on the tables and their indexes?
2. Any chance of getting any on the tables in "Check pending"?
3. Any other consideration for concurrent access on the other table A partitions and tables B and C?

Thanks in advance
Jorge

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Peter Vanroose

Re: [z/OS] "Clear out" partitions by LOAD dummy
(in response to Jorge Martelanz)
> 1. What type of contention can we expect on the tables and their indexes?
> 2. Any chance of getting any of the tables in "Check pending"?
> 3. Any other consideration for concurrent access on the other table A partitions and tables B and C?

The easiest way to get answers to these questions (esp. 2.) is to try out your scenario on test copies of tables A, B, C.
I would indeed expect a check pending state on tables B and C (not verifies this, though) --
you could consider running a "-start DB SP(..name..) access(force)" immediately after the REORG, in the same JCL.

Also expect lots of log records to be written during the REORG. (But I wouldn't use LOG NO since that would leave the tablespace in COPY pending.)

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

[login to unmask email]

Re: [z/OS] "Clear out" partitions by LOAD dummy
(in response to Peter Vanroose)
Jorge,

10 NPIs is a real nightmare for such a partitioned tablespace with 100M+
rows.
And your case is almost the worst one, I mean RI is involved.

My answer,
1.LOAD DUMMY REPLACE will definitely hit your 24x7 availability, let alone
the contentions.
2.The child tables, B and C, will be set CHECK PENDING on, typically.
3.What I can suggest is,
1£©Use SQL to clear your table A partitions, you can make it by batch job,
pay very attention
to its commit frequency. Even running long time, your 24x7 availability
won't be hit.
2) Use Online REORG to reorg the whole tablespace A, to clear your
paritions and all NPIs will
be rebuilt, you don't need to worry about contentions, availability, no
CHECK PENDING at all.



Huang Hao
Senior Manager, Dept. System
ICBC Data Center(Shanghai)
Tel :(+86)021-28989825
Mail:[login to unmask email]



Jorge Martelanz <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2010-12-12 22:07
Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>


ÊÕ¼þÈË
[login to unmask email]
³­ËÍ

Ö÷Ìâ
[DB2-L] [z/OS] "Clear out" partitions by LOAD dummy






I hope you can contribute to the "brain storming" we are doing here with
this scenario:

1. DB2 9 for z/OS in CM
2. Table A has 100M rows and is partitioned
3. Table A has 10 NPIs
3. Tables B and C are "childs" of table A (RI) with "delete restrict"
4. Tables are used 24x7 in online transactional environment.
5. We plan to "clear out" some partitions of A by "LOAD REPLACE" with
dummy input (4M rows aprox.)
6. We know that B and C have no rows related to the ones to be "cleared"
in A

Now the questions

1. What type of contention can we expect on the tables and their indexes?
2. Any chance of getting any on the tables in "Check pending"?
3. Any other consideration for concurrent access on the other table A
partitions and tables B and C?

Thanks in advance
Jorge

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv



_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv