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

Olle Brostrom

SV: [DB2-L] Antwort: [DB2-L] partitioning in V8
Hi Roy,
I don't agree with you about the mess you talk about.
We have a lot of partitioned TS and we almost always take image copies on the part level and the running MODIFY RECOVERY once a week like this:
LISTDEF MODR0001 INCLUDE TABLESPACE XXXXXXX.YYYYYYY
MODIFY RECOVERY LIST MODR0001 DELETE AGE(40)
We then have no recovery info in SYSCOPY/SYSLGRNX older than 40 days
But if you run MODIFY RECOVERY on partlevel, then you will get problem......


Best Regards

Olle Broström


________________________________

Från: DB2 Data Base Discussion List [mailto:[login to unmask email] För Roy Boxwell
Skickat: den 30 januari 2009 07:46
Till: [login to unmask email]
Ämne: [DB2-L] 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 - 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

Paul Ogborne

Re: partitioning in V8
(in response to Olle Brostrom)
Good point about the limit keys.? I have implemented 254 parts and it takes some time to key in that many.? Perhaps we need a 'limit key generator'?

One other thing I have noticed is that as the number of datasets increases and DSMAX is raised by necessity, then it takes longer to bring subsystems down (for service) due to the number of SMF records written.

Regards,
Paul.


-----Original Message-----
From: [login to unmask email]
To: [login to unmask email]
Sent: Thu, 29 Jan 2009 22:51
Subject: Re: [DB2-L] partitioning in V8



Phil,

We have a new development with exactly the situation you just mentioned.

We have a table with LOB data that has a requirement to be stored for in
excess of 10 years. The individual LOB's aren't huge (estimated at between
300K and 500K per LOB). The only way we can see to allow for growth is to
allocate a 4096 partition base table with the accompanying AUX TS's and
AUX IX's. It would have been nice if IBM had extended the add partition to
include tablespace with LOB's but unfortunately they haven't, so you have
to allocate everything up front.

We're relatively lucky as we can define the partitioning key on an
identity column

PRTNBR INTEGER NOT NULL
GENERATED ALWAYS
AS IDENTITY
(
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 16384000
CYCLE
CACHE 20
NO ORDER
)
.........
PARTITION BY RANGE
(
PRTNBR ASC
)
(
PARTITION 1
ENDING AT (4000)
,PARTITION 2
ENDING AT (8000)
,PARTITION 3
ENDING AT (12000)
.......

So each partition get 4000 LOB's (approx 2G) and we have enough partitions
to give us around 10 years of storage. If we hit the max value before then
it just cycles round to partition 1 again and they all start to grow to
4G. This gives us manageable sized partitions, that don't take too long to
copy or reorg and because of the nature of the stored data we should only
ever be referencing a few of the partitions at any given time. Once a
partition is filled it gets reorged and never touched again. If any of the
apps guys get any bright Ideas about scanning this puppy we will of
course have to kill them slowly and painfully.

The down side is that even empty the table takes around 2 hours to drop
and redefine. Which means once it's created it doesn't get changed as any
changes which require the base table to be dropped and recreated will
take more time than we have in app upgrade window. The other interesting
thing with this tables definition is that it's the first table I've ever
created that required a purpose
built REXX exec just to create the DDL.
Try typing in 4096 partition ENDING AT values and see how many you get
wrong and then of course there are the 4096 AUX tablespaces and Index
spaces. I'm getting RSI just thinking about it.


Paul Fegan
DB2 Database Administrator
_____________________________________________________________
INFORMATION MANAGEMENT DIVISION | Queensland Transport
Creating business confidence

477 Boundary Street, Spring Hill QLD 4000
P: 07 3834 5022 F: 07 3834 2911
M: 0433 039 360
E: [login to unmask email]



"Grainger, Phil" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
30/01/2009 05:45 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

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

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


***********************************************************************
WARNING: This e-mail (including any attachments) may contain legally
privileged, confidential or private information and may be protected by
copyright. You may only use it if you are the person(s) it was intended
to be sent to and if you use it in an authorised way. No one is
allowed to use, review, alter, transmit, disclose, distribute, print
or copy this e-mail without appropriate authority.

If this e-mail was not intended for you and was sent to you by mistake,
please telephone or e-mail me immediately, destroy any hardcopies of
this e-mail and delete it and any copies of it from your computer
system. Any ri
ght which the sender may have under copyright law, and
any legal privilege and confidentiality attached to this e-mail is not
waived or destroyed by that mistake.

It is your responsibility to ensure that this e-mail does not contain
and is not affected by computer viruses, defects or interference by
third parties or replication problems (including incompatibility with
your computer system).

Opinions contained in this e-mail do not necessarily reflect the
opinions of the Queensland Department of Main Roads, Queensland
Transport or Maritime Safety Queensland, or endorsed organisations
utilising the same infrastructure.
***********************************************************************

______________________________________________________________________

* 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


________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.


______________________________________________________________________

* 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