Re: partitioning in V8

Phil Grainger

Re: partitioning in V8
(in response to Roy Boxwell)
As for the DSMAX problem, there is a way around it

There are a couple of products on the market that allow you to dynamically change the DSMAX value in dsnzparm (for the CA customers out there, it's part of your Value Pack)

When you need to shut down DB2, some little while before hand, adjust DSMAX downwards. Now DB2 will attempt to close unwanted datasets in advance of your -STOP DB2 command so that when you do finally shut him down, there will be fewer datasets to close

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Paul Ogborne
Sent: Fri 30/01/2009 10:04
To: [login to unmask email]
Subject: Re: [DB2-L] partitioning in V8


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 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 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 right 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 < http://info.aol.co.uk/email1 > for a free AOL Email account with unlimited storage today.


________________________________


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

Roy Boxwell

Antwort: Re: [DB2-L] SV: [DB2-L] Antwort: [DB2-L] partitioning in V8
I think the answer is "it depends"

All I say is what other people have shown me in real world scenarios. Most
places where I have seen nasties all mix and match IC types hence my
point. If you manage all your
IC's perfectly and keep them in sync with TMS and know what you are doing
then all is well. Sadly when I am contacted it is normally not the case
(otherwise I would not be contacted!)

Put another way: Keep the ICs upto date and make sure your LGRNX and
SYSCOPY are OK and your storage guys don't kill your datasets then all is
100% ok.
However I *still* don't like 4000 partition objects - !



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


______________________________________________________________________

* 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 Phil Grainger)

Hi Phil,

Yes, that works.? I generally?'turn down' DSMAX about an hour before the slot?and this speeds things up considerably.

Regards,
Paul.

-----Original Message-----
From: Grainger, Phil <[login to unmask email]>
To: [login to unmask email]
Sent: Fri, 30 Jan 2009 13:08
Subject: Re: [DB2-L] partitioning in V8



As for the DSMAX problem, there is a way around it

There are a couple of products on the market that allow you to dynamically
change the DSMAX value in dsnzparm (for the CA customers out there, it's part of
your Value Pack)

When you need to shut down DB2, some little while before hand, adjust DSMAX
downwards. Now DB2 will attempt to close unwanted datasets in advance of your
-STOP DB2 command so that when you do finally shut him down, there will be fewer
datasets to close

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Paul Ogborne
Sent: Fri 30/01/2009 10:04
To: [login to unmask email]
Subject: Re: [DB2-L] partitioning in V8


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

Subje
ct
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 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 right 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, Aus
tralia * 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 < http://info.aol.co.uk/email1 > for a free AOL Email account with unlimited
storage today.


________________________________


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


________________________________________________________________________
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

Roy Boxwell

Antwort: Re: [DB2-L] partitioning in V8
(in response to Paul Ogborne)
last thing before I go home today...and I just got a mail about TS and TP
image copies and Modify...unbelievable!!!

One thing I have not actually tried yet is a REBALANCE of 4095 partitions
with a LOB column and a dpsi, then ALTER ADD PART and ROTATE followed by
REBALANCE...
It would be "interesting" to try this out I think...ok ok it IS a
friday....and NO I will *not* be trying this out! I have real work to
do!!!


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


______________________________________________________________________

* 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

Dave Magnusen

Re: partitioning in V8
(in response to Roy Boxwell)
Thanks all for the input on this. Perhaps leads to more questions than
answers(!), but at least we have some direction to hurl the questions in.

Thank, again

dave


______________________________________________________________________

* 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