Antwort: [DB2-L] partitioning in V8

Roy Boxwell

Antwort: [DB2-L] partitioning in V8
and two little extra things to worry/think about...recovery and
reorg...With 1000's of partitions you would probably want to use TP level
image copies
now remember how Modify works with regard to deleting "old" data from
SYSIBM.SYSCOPY/LOGRNGX...That's right...it can only delete records when
there is a consistant point (Ie a DSNUM = 0 IC or perhaps all parts) this
means that you will have 1000's of basically useless image copies
filling up disk/tape space and SYSCOPY and LGRNGX as well...after a while
(Say a year) you have a HUGE mess and it takes *ages* to
do anything with it...As for reorg...well now with the "joy" of BUILD2
removal from 9 you cannot even do parallel REORGS anymore...yep..you gotta
serially
run your reorgs....if you have a NPI of course...but then who doesn't
still have a good-old-NPI hanging around the place? <sigh>



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert





Dave Magnusen <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
29.01.2009 16:01
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] partitioning in V8


As a relative newbie to DB2, and V8, please bear with me.

We have an application which would like to create many partitions.
Many thousand...
Has anyone partitioned to this level? Any horror stories, show-stoppers,
or
little gotcha's you can share are most appreciated.

Thanks,

dave

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html




______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Walter Jani&#223;en

AW: [DB2-L] partitioning in V8
(in response to Roy Boxwell)
Dave

I think, that's not necessary, If you are only interested that the creation goes fast. You can use DEFINE NO and the create will work great. But then, the first insert or load has to do all the work, but no performance problems during create-

Mit freundlichen Grüßen
Walter Janißen

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

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

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Rasmussen, Steen
Gesendet: Freitag, 30. Januar 2009 01:20
An: [login to unmask email]
Betreff: Re: [DB2-L] partitioning in V8

Hello Dave,

I would suggest you search the archives on this list. We have had many discussions related to this topic the past few years, but let me give you a couple of ideas:

1) You will definitely go with TCP instead of ICP (meaning the LIMITKEYS will be described on the create table statement as opposed to the create index statement).
2) Consider not to specify all partitions in one statement. It will run for a while since all the VSAM datasets need to be created and updated in the catalog. If the process fails somewhere, the entire thing need to be backed out. Instead specify a few in the create tablespace statement, create the table and index - and then use the ALTER command to add more partitions.
3) If you can avoid mixing ADD and ROTATE later on - I recommend this process since the physical and logical partitions will be out of sync and things can appear a little complicated when looking at utilities and display command output.
If you need to REBALANCE using REORG - practice this too. You will end in a RECP status if some partitions end up empty (at least this is how it used to be and I don't know if IBM changed this).

You can also use the IDUG website - I and others have presentations out there you might find useful.

Good luck

Steen Rasmussen
CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Dave Magnusen
Sent: Thursday, January 29, 2009 9:01 AM
To: [login to unmask email]
Subject: [DB2-L] partitioning in V8

As a relative newbie to DB2, and V8, please bear with me.

We have an application which would like to create many partitions.
Many thousand...
Has anyone partitioned to this level? Any horror stories, show-stoppers, or
little gotcha's you can share are most appreciated.

Thanks,

dave

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html