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
Solved

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

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

Accepted Solution

by:
Kent Olsen 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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…

809 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