Solved

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

Posted on 2008-10-07
4
1,879 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 45

Expert Comment

by:Kdo
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 45

Accepted Solution

by:
Kdo earned 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Life is full of imperfections – we all know it. Sometimes bad things happen to people for no particular reason. As they say: “it happens”. All we can do is to find a way to make some unavoidable situations… avoidable. Every kind of hardware has i…
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now