Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: move table to another tablespace and log changes

    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?
    Last edited by MarkhamDBA; 12-14-09 at 00:15.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •