HomeОбразованиеRelated VideosMore From: DB2-LUW-ACADEMY

DB2 Tips n Tricks Part 90 - How Classic Table Reorganization using Temp Tablespace cannot be stopped

7 ratings | 627 views
How Classic Table Reorganization using Temporary Tablespace cannot be stopped.
Html code for embedding videos on your blog
Text Comments (6)
Lokesh M (6 months ago)
HI Harish Sir, User deleted lot of records from a table but we're unable to reclamin space. Need your help on this. We've table TABLE1 with 4 BLOB colums and table is having only one 8K tablespace{TABLESPACE1]. the table was created with below BLOB column datatypes: IMAGE SYSIBM BLOB 10485760 0 Yes IMAGE1 SYSIBM BLOB 10485760 0 Yes IMAGE2 SYSIBM BLOB 10485760 0 Yes IMAGE3 SYSIBM BLOB 10485760 0 Yes The table TABLE1 was created in TABLESPACE1 and we didn’t see any free pages to reclaim for this. TBSP_NAME TBSP_TYPE RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES(KB) ------------------------- ---------- ------------------------- -------------------------------------------------- QCASNKA DMS 1 2944 The below output from SYSPROC.ADMIN_GET_TAB_INFO for this table. All sizes are in KB: DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE LOB_OBJECT_L_SIZE LOB_OBJECT_P_SIZE -------------------- -------------------- -------------------- -------------------- 8451840 8451840 2191217704 2191217920 KB 1 record(s) selected. Physically disk space allocated for LOB data= 2191217920 KB [2089 GB] Currently allocated used LOB data for 4 BLOB columns[IMAGE+IMAGE1+IMAGE2+IMAGE3] is = 930 GB We can reclaim around 1159 GB by performing the Reorg on LOB columns. Need your suggestion how to reclaim space this space. 1) Do we need downtime for this table? 2) Still we've 1 TB free avaiable at data filesystem, Is that space good for performing the reorg on LOB columns ? or Do need additional space on data filesystem [ Around 2 TB] ? We need downtime for the table. 3) Please give syntax for reorg for LOB columns ? both offline and inplace 4) Is there any option to reclaim space on LOB columns ? like admin_move_table ( share commad as well ) ? Thanks Lokesh M
DB2-LUW-ACADEMY (6 months ago)
i will give my opinion, but have no data to corroborate. reorganizing LOBs are not going to be pleasant or useful. i will go with drop table, create new and load the data again, if it is the only table in the table space.
Naval Kishore Sarda (2 years ago)
Hello .. I am assuming that if reorg gets killed at REPLACE or RECREATE ALL INDEXES phase it will complete by its own. No further action is required.But What will happen if reorg gets killed at Sort of Build Phase. If we need to recover the table then how we can.
DB2-LUW-ACADEMY (2 years ago)
Hi,It is only in REPLACE Phase. The video is very specific to REPLACE Phase. For INDEXES Phase, it will mark the indexes as Invaild or Rebuild Required. For SORT or BILD Phase, Nothing will happen, every thing will be intact. The Real Question i have raised is different. You cannot stop a reorg operation - You just cannot conclude by looking at list applications that reorg is not running or any Activity is not running. An Activity is executing in server side even if there is no client application associated with it. How do i even stop such a Reorg Operation without risking Damaging or Corrupting the Table? Is it really an Intended Behavior? Those are the questions that i had when creating that Video.
skolanRu (2 years ago)
Thanks for video! what will happens if i use user tablespace?
DB2-LUW-ACADEMY (2 years ago)
Hi, you cannot specify user table space explicitly with the option USE in Reorg Command. However, the reorg itself can run in user table space instead of temporary table space. In such a scenario, the replace phase will be almost instantaneous, because it will only drop the old table and rename the table that was built during the build phase of reorg operation. this switching of tables should be very fast. so the concept explained does not apply in that case. hope my clarification was clear. thanks, harish pathangay

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.