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
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.
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
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 85:01:48 |
Calls: | 6,495 |
Calls today: | 6 |
Files: | 12,097 |
Messages: | 5,276,965 |