DB2 v9.5.1 ESE on AIX v6.1
We have a large table (72M rows, 63.5GB size + indexes) in the same TS as other tables with 4K page size. It’s growing and going to exceed the table size limit for 4K tablespaces so we need to move it to another tablespace with larger page size. Production DB has archive logging activated (logarchmeth1=DISK:<some_path>) . As this is a production DB I need to make sure all changes are logged. We need to minimize downtime as well. I do not have experience working with DBs with archival logging so have to ask for help.

Option 1 – export/import as it is logging all changes: create new table in new TS, export/import, drop old table, rename new table, create indexes, runstats table and indexes. When I do it this way (tried on DEV server) log files are getting huge and I run out of space (file system full).

Option 2 – load from cursor not logging the changes: create new table in new TS, load from cursor with nonrecoverable option, delete old table, rename new table, create indexes, runstats table and indexes. What do we do to log DB changes in this case?

I think I am missing something here. How do you normally would perform this task on DB with archival logging?

With option number 2, you should take a new backup after the load and so long as you don't ever try to restore from a backup prior to the load, you should be OK.

If you do a nonrecoverable load, and restore a backup taken prior to the load and try to roll the logs forward through the time when the nonrecoverable load took place, that table will be marked as bad (table must be dropped and recreated--yes that is correct--dropped).

If you're just moving one table, then you can go with:
Option 3 - load from cursor with copy yes (kind of similar to option 2 + backup).


You may also look at large tablespaces / converting to large tablespaces.

thank you Bella and Mr.Feldman

we will go with 2nd option - it takes 3.3 hrs incl. backups before and after (instead of 5.3 hrs for export/import option) and does not take so much disk space for logging.

can not use large tablespace as we are using mostly SMSs.