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)

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)

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)

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)