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

Roy Boxwell

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 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

Michael Ebert

Re: Antwort: [DB2-L] AW: [DB2-L] partitioning in V8
(in response to Roy Boxwell)
Indeed. Remember that DEFINE NO was originally introduced for SAP, where a
DB might have tens of thousands of empty objects (table/indexspaces) that
would never hold data (which also means it was not really meant for
partitioned TSs as you can see from the inconsistency that the property is
stored in SYSTABLEPART but actually only works on a full-table level
[that's V7 knowledge...]). It was not meant for lazy DBAs who'd rather do
a quick-and-dirty job and leave the poor users to deal with the results :)

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roy Boxwell <[login to unmask email]>
To
[login to unmask email]
cc

bcc

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





Roy Boxwell <[login to unmask email]>
Please respond to : DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
30-01-09 11:31



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




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees. It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws. If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany


______________________________________________________________________

* 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