By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.