Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Can I use RENAME TABLE

    Hi Guys,

    This is first time that I need to Extend a column size such that it has to be moved to bigger page size tablespace.

    The table is quite big 28GB.

    If I export and import it will require long time. But database cannot be scheduled such a big offline time.

    Estimated offline time will be 45 minutes to 65 minutes. This is not approvable in production.

    What are my other options.

    1. Load from cursor. or what ? 2. Can RENAME TABLE be used somehow ??


    Thanks

    DBFinder

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, Not sure if this will help much but a Load is faster than Import. I can't tell you how much faster but it would make you downtime less.

    What type of activity is going on with the table? Select only? Inserts? Updates?

    If it is Inserts or Updates, can you identify the rows changed (changed timestamp column maybe?)?

    Rename table may be able to help you but there are quite a few rules (restrictions) associate with it.
    From the V9.5 SQL Reference manual Vol 2:

    When renaming a table, the source table must not:
    v Be referenced in any existing view definitions or materialized query table definitions
    v Be referenced in any triggered SQL statements in existing triggers or be the subject table of an existing trigger
    v Be referenced in an SQL function
    v Have any check constraints
    v Have any generated columns other than the identity column
    v Be a parent or dependent table in any referential integrity constraints
    v Be the scope of any existing reference column
    v Be referenced by an XSR object that has been enabled for decomposition

    An error (SQLSTATE 42986) is returned if the source table violates one or more of these conditions.

    When renaming an index:
    v The source index must not be a system-generated index for an implementation table on which a typed table is based (SQLSTATE 42858).

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    create another table in a new TS (with bigger page size) and copy data. Your source table will be available all the time - no outage.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You would have to have an outage one way or another, even if it is a brief one to rename this table.

    If you can find a time when this table is a read only, you can create a new copy; use LOAD FROM CURSOR; Rename old; rename new and you are done.

    If not. you would have to find a way to identify those new records.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, the rename is done in a transaction (like all DDL). So you could do the renaming and DB2 will take care of the locking - no explicit outage is required.

    (Note that other DBMS do not have transaction concepts for DDL statements.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The problem is once the data is copied from the original table to the new/larger page size table (or even while the data is being copied if it is Read/Write), any changes to the original table will either be lost (if you just rename the tables) or have to be dealt with (applied to the new table). The only two choices I can see are:

    A) Put the original table in Read Only so NO changes are made from the point the copy starts until the tables are renamed.

    B) Leave the original table in Read/Write while the copy is processing, but you have to have a way to identify any of the changes that are being made and are NOT in the new table. This could be Inserts (missing rows), Deleted (extra rows) or Updates (changed rows). These would have to be identified and, prior to applying them to the new table, the original table would need to be in Read Only. (this is basically how the on-line reorgs work).

    B has the least amount of down time as it shouldn't take too long to get the changes applied to the new table and rename the tables.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    One way to keep track of the insert/update/delete activity is to set up triggers to put those affected rows into other table(s), then import that data to your new version of the table for the catch up. I've done similar on projects on both LUW and Z/OS. We used 3 shadow copies one for each type of activity. When we had 10 minute down time, renamed tables, inserted data from the insert shadow, updated data from the update shadow and, finally, deleted data from the delete shadow. Actually, only took about 3.5 minutes to finish up.
    Dave

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Excellent suggestion, Dave.

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Dave,

    This was what I was waiting for, But your response was little late. we did it with 1.4 hrs down time. Table was 28 GB and database took 1 hr 05 minutes to LOAD FROM CURSOR,

    Your experience was great and this was exactly I thought, but having no exp. doing it myself, I avoided to take risk.

    But your input did not get waste. I will try this in Staging for a hands on try.

    I appreciate you input.

    Thanks

    DBFinder

Posting Permissions

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