[DB2-L] Antwort: [DB2-L] AW: [DB2-L] partitioning in V8

Walter Janißen

[DB2-L] Antwort: [DB2-L] AW: [DB2-L] partitioning in V8
Roy

therefore there is an open requirement, that DEFINE NO operates on a partition level.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email] <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



________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Roy Boxwell
Gesendet: Freitag, 30. Januar 2009 11:32
An: [login to unmask email]
Betreff: [DB2-L] Antwort: [DB2-L] AW: [DB2-L] partitioning in V8



but a REALLY long wait for the first insert!! What would you prefer - 2 hours on a saturday when you create these monsters or 4 hours on a monday morning when a User does an insert???
I know what I prefer!!!



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




[login to unmask email]
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>

30.01.2009 10:00
Bitte antworten an DB2 Database Discussion list at IDUG



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



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




________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia < http://idug.org/lsAU >

IDUG.org <http://www.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 here. < 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

Phil Grainger

Re: partitioning in V8
(in response to Walter Janißen)
Nitpicking accepted - in V8 LOBs are the only issue

XML adds to the complexity in DB2 9

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Myron Miller
Sent: Thu 29/01/2009 22:07
To: [login to unmask email]
Subject: Re: [DB2-L] partitioning in V8


Phil,
I'm going to be a nitpicker here, but how can you have an XML column in V8? Your numbers are extremely accurate and make a point that many people don't consider when using these types of objects.

Excellent points.

Myropn


________________________________

From: "Grainger, Phil" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, January 29, 2009 2:45:36 PM
Subject: Re: [DB2-L] partitioning in V8

Oh, and one other thing

Heaven help you if you have LOB or XML columns

If you DO, you will need one additional (table space + table + index) for EACH LOB/XML column for EACH partition

and you are still limited to 64K objects per database. Remember, the numbers add up FAST

a 4,096 partitioned object with one partitioned index and one LOB column will account for:

4,096 table space page sets
1 table
4,096 index page sets
4,096 LOB table spaces
4,096 aux tables
4,096 aux index page sets

= 20,481 objects!!!

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Jack Campbell
Sent: Thu 29/01/2009 17:47
To: [login to unmask email]
Subject: Re: [DB2-L] partitioning in V8



In addition to Phil's comment on dataset opens......

how would so many partitions affect your MAX OPEN DATASETS for the DB2
sub-system. If there are a large number of partitions open concurrently this
could cause the close/re-open of datasets relating to other tablespaces.

If the table has a high hit-rate for multiple partitions concurrently, what
adverse affect would this have on other tables in the same bufferpool? You
may need to isolate the table into its own bufferpool

What would the effect be on utilities, such as COPY and REORG (assuming the
use of DPSI's), if a large number of parts need to be proicessed - VSAM
allocate overhead? Particular for 3rd party products which run at the VSAM
dataset level?




Regards

Jack

______________________________________________________________________

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




IDUG.org < http://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 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 - Europe * 5-9 October * Rome, Italy < http://idug.org/lseu >

IDUG.org <http://www.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 here. < 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