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

hikums

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

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

Mark Yudkin

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

Knut Stolze

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

hikums

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

Joachim Müller

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