• Which reorg after column removal in large table

    From Troels Arvin@21:1/5 to All on Thu Dec 8 22:59:55 2016
    Hello,

    DB2 10.5 for LUW:
    I need to remove a column from a large table. According to db2top, the
    table's size is ½TB.

    After the column has been removed (ALTER TABLE ...), a table
    reorganization is needed, so that the table becomes writeable again.

    Can any reorganization type be used after the column has been dropped, or
    is it (for example) only the classical offline reorg which may be used
    after a column removal?

    I'm worried about transaction log usage. At http://www.ibm.com/support/ knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/
    c0024850.html I see contradictory information:
    In the "Disadvantages of CLASSIC reorganization" section, it states:
    A large active log might be required since the entire operation is
    handled in a single unit of work.

    But then later in the table which describes differences between the reorg types:
    For the "Logging storage space requirement" characteristic, it's "Not significant" for the classic reorganization.

    So if I choose classic reorg, then what is it: Am I facing extensive transaction log build-up for my reorg, or am I not? In the former case:
    Can I mitigate that somehow, such as putting the table in NOT LOGGED
    INITIALLY state before starting the reorg?


    I just tested a bit. I reorg'ed for a while, but the hit "CTRL-c", and everything seems fine. But then again, when I look at the table in
    db2top's table view, it states that the "Reorg% Progress" is 32.75 for
    the table. Does this mean that the table is in a bad state? (The table's
    access mode is full, "F", when looking at the table in syscat.tables.)

    --
    Regards,
    Troels Arvin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jeremy Rickard@21:1/5 to Troels Arvin on Fri Dec 9 20:38:44 2016
    Over in SAP-land I haven't manually dropped a column in a long time. However, the reorg cannot be an inplace reorg.

    Regarding logging, a classic reorg creates a shadow copy of the table, and if you want the reorg to be recoverable through rollforward then you will need to use the COPY clause. So, although the logging is trivial in sense of the amount of log space that
    is used for the table copy, that disregards the fact that the file created by COPY for recovery will be about the size of the table pages.

    Also, if you have LOGINDEXBUILD set ON then creation of indexes will be fully logged.

    If you want to avoid having your table go read-only after dropping columns, then ADMIN_MOVE_TABLE might be an option for dropping the columns online, depending on the table. The logging will be at least as heavy, but you can commit every specified
    number of rows during the shadow copy, so the greatest logging in a single transaction is that of your largest index (if logged).

    Regards,

    Jeremy Rickard

    On Friday, 9 December 2016 11:59:45 UTC+13, Troels Arvin wrote:
    Hello,

    DB2 10.5 for LUW:
    I need to remove a column from a large table. According to db2top, the table's size is 確B.

    After the column has been removed (ALTER TABLE ...), a table
    reorganization is needed, so that the table becomes writeable again.

    Can any reorganization type be used after the column has been dropped, or
    is it (for example) only the classical offline reorg which may be used
    after a column removal?

    I'm worried about transaction log usage. At http://www.ibm.com/support/ knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/ c0024850.html I see contradictory information:
    In the "Disadvantages of CLASSIC reorganization" section, it states:
    A large active log might be required since the entire operation is
    handled in a single unit of work.

    But then later in the table which describes differences between the reorg types:
    For the "Logging storage space requirement" characteristic, it's "Not significant" for the classic reorganization.

    So if I choose classic reorg, then what is it: Am I facing extensive transaction log build-up for my reorg, or am I not? In the former case:
    Can I mitigate that somehow, such as putting the table in NOT LOGGED INITIALLY state before starting the reorg?


    I just tested a bit. I reorg'ed for a while, but the hit "CTRL-c", and everything seems fine. But then again, when I look at the table in
    db2top's table view, it states that the "Reorg% Progress" is 32.75 for
    the table. Does this mean that the table is in a bad state? (The table's access mode is full, "F", when looking at the table in syscat.tables.)

    --
    Regards,
    Troels Arvin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Troels Arvin@21:1/5 to Jeremy Rickard on Mon Dec 12 07:09:26 2016
    Hello,

    Jeremy Rickard wrote:
    If you want to avoid having your table go read-only after dropping
    columns, then ADMIN_MOVE_TABLE might be an option for dropping the
    columns online, depending on the table.

    Ah, that's a good point.

    Does someone know what's quickest:
    - ALTER TABLE ... DROP COLUMN + classic REORG, or
    - ADMIN_MOVE_TABLE

    --
    Troels

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jeremy Rickard@21:1/5 to Troels Arvin on Mon Dec 12 02:19:19 2016
    On Monday, 12 December 2016 20:09:14 UTC+13, Troels Arvin wrote:
    Hello,

    Jeremy Rickard wrote:
    If you want to avoid having your table go read-only after dropping
    columns, then ADMIN_MOVE_TABLE might be an option for dropping the
    columns online, depending on the table.

    Ah, that's a good point.

    Does someone know what's quickest:
    - ALTER TABLE ... DROP COLUMN + classic REORG, or
    - ADMIN_MOVE_TABLE

    --
    Troels

    Almost certainly the classic reorg.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)