Solved

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

We planned to use NetApp's SnapManager for Exchange (SME) to make three snapshots a day for quick recovery. Additional we planned to use Symantec NetBackup7 to backup to tape media for disaster recovery. We followed the best practice guide from NetA…
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
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…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

743 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

11 Experts available now in Live!

Get 1:1 Help Now