By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.