Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-07
4
Medium Priority
?
1,905 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
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…

636 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