470,834 Members | 1,559 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,834 developers. It's quick & easy.

tablespace move from 32K to 4K page size on same instance

How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?

Nov 12 '05 #1
6 3565
Ian
hi****@gmail.com wrote:
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?


No, you can't change page size. You have to export data, save DDL,
drop/create tablespaces, recreate objects, load data.

Nov 12 '05 #2
Why would you "reset back to circular logging"? Don't you need recovery? If
not, why not just drop everything and start from scratch?

Export, drop, create, load. Implement proper logging and log archiving.

<hi****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?

Nov 12 '05 #3
hi****@gmail.com wrote:
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?


Create a new table with the same structure in the 4K tablespace, copy the
data, drop the old table and rename the new one.

Assuming you want to change table T:

CREATE TABLE T_new LIKE T
INSERT INTO T_new SELECT * FROM T
DROP TABLE T
RENAME TABLE T_new TO T

You will also have to take care of indexes and constraints like
primary/foreign keys.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.

Thanks for your tips!!

Nov 12 '05 #5
Ian
hi****@gmail.com wrote:
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.


NONRECOVERABLE will prevent DB2 from putting the tablespace in backup
pending, but of course you won't be able to roll forward through the
load operation.

Nov 12 '05 #6
no more since Version 8. (online load)
<hi****@gmail.com> schrieb im Newsbeitrag
news:11**********************@o13g2000cwo.googlegr oups.com...
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.

Thanks for your tips!!

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Patrick Tan | last post: by
4 posts views Thread by mairhtin o'feannag | last post: by
2 posts views Thread by Kums | last post: by
2 posts views Thread by Pablo | last post: by
2 posts views Thread by db2dude | last post: by
4 posts views Thread by Alvin SIU | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.