Partitioning Global PS HR Database

Lori Bewley

Partitioning Global PS HR Database
We are running PS HR 7.5 for our US employees, but are planning to
implement PS HR 8 for US, England and Hong Kong, using a single DB2 V6.1
global database on OS/390.

To address potential online/batch concurrency issues, we are considering
partitioning the global database tables where necessary (perhaps using
SETID or EMPLID).

If your shop is running a global PS system from a single DB2 database
(rather than separate regional databases):
1. Are you partitioning your tables to address concurrency?
2. Are you partitioning by global regions (Americas/EMEA/AsiaPacific) or
some other scheme?
3. What benefits have you seen from partitioning?
4. If you're running HR, what tables did you partition/what keys did you
use?
5. If none of the above apply, did you consider partitioning but then rule
it out for any reason? If so, why?

Thank you!



db user

Partitioning
(in response to Lori Bewley)
Hi list,

I have a table with 4 million records with primary key as A_NO with column
length 5 CHAR.I was trying to partition it using the A_NO key range.

A_NO has the following range of values:
0000A - has 500000 recs
0000B - has 600000 rows
....
....
Similarly till 0000G.

I thought of having 7 parts with values for PART 1 as '9999A'and PART 2 as
'9999B' so on and so forth.Once i added recs to it,the records are added to
PART 1 no matter whatever values A_NO has.i.e for rec with A_NO 0888C
instead of placing this rec in PART 3 the record is inserted in PART 1.

How to partition this table with A_NO as the Partition key.


TIA
Kumar


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com



Thomas E. Faglon

Re: Partitioning
(in response to db user)
post the ddl for the tablespace/table
Tom F




db user <[login to unmask email]> on 12/25/2001 09:33:02 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Thomas E. Faglon/Telcordia)
Subject: Partitioning



Hi list,

I have a table with 4 million records with primary key as A_NO with column
length 5 CHAR.I was trying to partition it using the A_NO key range.

A_NO has the following range of values:
0000A - has 500000 recs
0000B - has 600000 rows
....
....
Similarly till 0000G.

I thought of having 7 parts with values for PART 1 as '9999A'and PART 2 as
'9999B' so on and so forth.Once i added recs to it,the records are added
to
PART 1 no matter whatever values A_NO has.i.e for rec with A_NO 0888C
instead of placing this rec in PART 3 the record is inserted in PART 1.

How to partition this table with A_NO as the Partition key.


TIA
Kumar


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com








James Campbell

Re: Partitioning
(in response to Thomas E. Faglon)
Kumar

Partitioning is based on collating sequence. If your partitioning
index vales are '9999A', '9999B', '9999C' etc then keys in the range
- low values to '9999A' go into the first partition. This range
includes, well almost everything apart from the actual values
'9999B', '9999C', ... '9999Z', '99990', '99991', .... '99999'. (although
there the keys with bytes in the range x'FA' to x'FF' included)
- ' after '9999A' up to and including '9999B' go into the second
partition. '9999B' is the only value in this range
- and so on.

Basically, you cannot do what you want with the key the way it is.

You will have to extract the 5'th character and put it either at the
front of the key, or into a column on its own. Or change your
partitioning strategy.

James Campbell

On 26 Dec 2001, at 2:33, db user wrote:

Date sent: Wed, 26 Dec 2001 02:33:02 +0000
Send reply to: DB2 Data Base Discussion List <[login to unmask email]>
From: db user <[login to unmask email]>
Subject: [DB2-L] Partitioning
To: [login to unmask email]

> Hi list,
>
> I have a table with 4 million records with primary key as A_NO with column
> length 5 CHAR.I was trying to partition it using the A_NO key range.
>
> A_NO has the following range of values:
> 0000A - has 500000 recs
> 0000B - has 600000 rows
> ....
> ....
> Similarly till 0000G.
>
> I thought of having 7 parts with values for PART 1 as '9999A'and PART 2 as
> '9999B' so on and so forth.Once i added recs to it,the records are added to
> PART 1 no matter whatever values A_NO has.i.e for rec with A_NO 0888C
> instead of placing this rec in PART 3 the record is inserted in PART 1.
>
> How to partition this table with A_NO as the Partition key.
>
>
> TIA
> Kumar
>
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
>
> DB2-L webpage at htt
p://www.ryci.com/db2-l. The owners of the list can


James A Campbell