?
Solved

DB2 V9.1 Backup tablespace and restore to different tablespace with lower page size.

Posted on 2008-10-07
4
Medium Priority
?
1,907 Views
Last Modified: 2012-08-13
I have a db2 aix tablespace in which the tables requiired no more then 4k page size. Is it possible to create new tablespace(4k) / backup the 16k and then restore to the new tablespace?? Is there a implied constraint in the process.


Thanks.
0
Comment
Question by:excitable
  • 2
  • 2
4 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 22659092
Hi excitable,

I'm not sure where the '16k' figure came.  Is that the page size in the original table?

As long as the container (tablespace) doesn't change, you can always back up a DB2 database on a server and restore it (as is) to a similar server.  (You can't restore a windows backup to Z/OS, etc.)

If the underlying container is going to change (e.g. you're going to change the page size) you need to be a bit more creatiive.  DB2 will have to build the tables, not just restore them.

I'm curious about the decrease in page size.  If the database does a LOT of random I/O, the smaller page size could get you better performance.  But in my experience, the larger page size is almost always a winner.


Good Luck,
Kent
0
 

Author Comment

by:excitable
ID: 22660257
Sorry for being unclear. I have a 16k page size tablespace that I want converted to a 4k page size. I am getting a huge amount of logical i/o and bufferpool thrashing. None of the tables maxlength exceed 4k. Can I do what I want with a tablespace restore or do I have to do a database redirected restore? I guess another way to say this is there an 'into' clause for tablespace restores??
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 22660312
Restoring a tablespace 'into' another tablespace won't address the page size issue.

The biggest 'problem' (and it's not really a problem) is that if you're going to change page size, you're going to change the physical location of data.  Rows wind up in different pages and will be keyed differently within those pages.

I'm not aware of any IBM utility that will restore the data to a different page structure and rebuild all of the related entities (indexes, etc.).

I believe that you'll have to create a new table with a 4K page size and load the data into it.  Of course, you'll have to make sure that all of the indexes, triggers, RI, etc. are defined for the new table, too.


Kent
0
 

Author Closing Comment

by:excitable
ID: 31503824
Thank you for the help.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question