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

Walter Janißen

[DB2-L] [z/OS] "Clear out" partitions by LOAD dummy
Jorge

You can also try a REORG DISCARD SHRLEVEL CHANGE. It's new in DB2 V9, but I think, it's already available in CM. But nevertheless, the child-tables will get check pending.

I think, there is no solution only with utilities regarding to a 24x7 availability. The only solution would be to write your own programm to delete these rows and commit accordingly as Huang already mentioned.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jorge Martelanz
Gesendet: Sonntag, 12. Dezember 2010 15:08
An: [login to unmask email]
Betreff: [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 *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jorge Martelanz

Re: [z/OS] "Clear out" partitions by LOAD dummy
(in response to Walter Janißen)
Peter

Thanks for your comments, but basically for all you mention there we are not planning to do a REORG with DISCARD, but a LOAD with dummy input for the partition.

Huang

Thank you as well. Actually we did some tests and sometimes we got the "check pending" condition and sometimes not (?). For sure your suggestion is the safest one from the continuity of service point of view.


Any other suggestion/comment from the list members?

Take care

Jorge

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Thanikachalam "Billy" Sundarrajan

Re: AW: [DB2-L] [z/OS] "Clear out" partitions by LOAD dummy
(in response to Jorge Martelanz)
We typically purge the data using a COBOL/Assembler program prior to performing a LOAD on empty partition.
This reduces the time needed to update the NPSI entries.

Cheers.

_____________________________________________________________________
* 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! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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