467,882 Members | 1,171 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,882 developers. It's quick & easy.

Very important table (partition) design question: DB2 UDB EEE V8.1 on AIX 5.2

Dear Group,

I have the task of designing a table with similar characteristics of
Oracle range partitioning.

Note: we are migrating from ORacle to DB2 and we have many tables with
range partitions. The success or failure of the migration depends
mainly on, how we can get the range partition works.

In Oracle we have one table with 13 partition each having one month
worth of data. The oldest patition is droped after the newest
partition is loaded successfully.

This is what I am thinking for DB2:
-----------------------------------
(Note: there will be large amount of data and delete is not an option)

Create 13 tables (12 for the active months and one to act as a
temporary load table )
Create a UNION ALL view on top of the 12 active tables.

I want to have these tables available almost all the time.

I load the latest month into table_13 and if sucsessful, truncate
table_1.

The issue here is, I have to drop and recreate the view, every time
when I load the data, to point to the 12 active tables.

Is there any way, I can create the views on top of all the 13 tables
(so that I do not have to drop and recreate the tables everytime) and
the access is restricted to only the active 12 tables? (In otherwords
make the DB2 to ignore the oldest table while accessing if there are
13 tables in the view)

Any other approaches to implement this effectively?

I am pretty sure, atleast few of you might have migrated from Oracle
to DB2. How did you implement range partitioning (with high
availability for these tables)?

Thanks,
Sandi.
Nov 12 '05 #1
  • viewed: 5466
Share:
2 Replies
Sandi,

perhaps this paper can help you.

http://www-106.ibm.com/developerwork...202zuzarte.pdf

regards,
Joachim Müller

"sandi" <pr******@yahoo.com> schrieb im Newsbeitrag
news:1e**************************@posting.google.c om...
Dear Group,

I have the task of designing a table with similar characteristics of
Oracle range partitioning.

Note: we are migrating from ORacle to DB2 and we have many tables with
range partitions. The success or failure of the migration depends
mainly on, how we can get the range partition works.

In Oracle we have one table with 13 partition each having one month
worth of data. The oldest patition is droped after the newest
partition is loaded successfully.

This is what I am thinking for DB2:
-----------------------------------
(Note: there will be large amount of data and delete is not an option)

Create 13 tables (12 for the active months and one to act as a
temporary load table )
Create a UNION ALL view on top of the 12 active tables.

I want to have these tables available almost all the time.

I load the latest month into table_13 and if sucsessful, truncate
table_1.

The issue here is, I have to drop and recreate the view, every time
when I load the data, to point to the 12 active tables.

Is there any way, I can create the views on top of all the 13 tables
(so that I do not have to drop and recreate the tables everytime) and
the access is restricted to only the active 12 tables? (In otherwords
make the DB2 to ignore the oldest table while accessing if there are
13 tables in the view)

Any other approaches to implement this effectively?

I am pretty sure, atleast few of you might have migrated from Oracle
to DB2. How did you implement range partitioning (with high
availability for these tables)?

Thanks,
Sandi.

Nov 12 '05 #2
Joachim,

Thanks for the URL. I will read it in detail. (On the surface, it
looks similar to the design I have). Here also they drop and recreate
the view, I am trying to avoid that.

Thanks.

"Joachim Müller" <jo*****@douglas-informatik.de> wrote in message news:<ce**********@news.dtag.de>...
Sandi,

perhaps this paper can help you.

http://www-106.ibm.com/developerwork...202zuzarte.pdf

regards,
Joachim Müller

"sandi" <pr******@yahoo.com> schrieb im Newsbeitrag
news:1e**************************@posting.google.c om...
Dear Group,

I have the task of designing a table with similar characteristics of
Oracle range partitioning.

Note: we are migrating from ORacle to DB2 and we have many tables with
range partitions. The success or failure of the migration depends
mainly on, how we can get the range partition works.

In Oracle we have one table with 13 partition each having one month
worth of data. The oldest patition is droped after the newest
partition is loaded successfully.

This is what I am thinking for DB2:
-----------------------------------
(Note: there will be large amount of data and delete is not an option)

Create 13 tables (12 for the active months and one to act as a
temporary load table )
Create a UNION ALL view on top of the 12 active tables.

I want to have these tables available almost all the time.

I load the latest month into table_13 and if sucsessful, truncate
table_1.

The issue here is, I have to drop and recreate the view, every time
when I load the data, to point to the 12 active tables.

Is there any way, I can create the views on top of all the 13 tables
(so that I do not have to drop and recreate the tables everytime) and
the access is restricted to only the active 12 tables? (In otherwords
make the DB2 to ignore the oldest table while accessing if there are
13 tables in the view)

Any other approaches to implement this effectively?

I am pretty sure, atleast few of you might have migrated from Oracle
to DB2. How did you implement range partitioning (with high
availability for these tables)?

Thanks,
Sandi.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by robin | last post: by
1 post views Thread by Mats Kling | last post: by
10 posts views Thread by Sumanth | last post: by
2 posts views Thread by shsandeep | last post: by
10 posts views Thread by shsandeep | last post: by
1 post views Thread by datapro01 | last post: by
15 posts views Thread by Piero 'Giops' Giorgi | last post: by
reply views Thread by Damir | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.