In DB2 9.7, IBM changed the format of table spaces to be “reclaimable”. This has a ton of advantages, but conversion is ridiculously painful.
Why Was the Format Changed?
Previously, row addresses (referenced in indexes) were relative to the beginning of the table space. This has a number of implications, the main one being that if tables are moved within the table space, indexes need to be updated. This made moving tables or portions of tables within a table space much more difficult. Reclaimable table spaces note the address of rows relative to the start of tables. This makes movement of the table or portions of the table within the table space much easier to do – no need to rebuild indexes. This was probably important for ADMIN_MOVE_TABLE and refinements related to multi-temperature storage.
With the general acceptance of auto-resize in many DB2 databases, resizing table spaces to make them smaller is a more common occurrence. They regularly grow without any DBA involvement, so any significant pruning leads to the need to reduce. With non-reclaimable table spaces, this is incredibly tedious, and often involves unloading and loading a large number of tables (offline!), and re-running db2dart over and over again. There are so many pitfalls in the process, I avoid it whenever possible.
Reclaimable vs. Non-Reclaimable
To get into my normal level of excruciating detail, DB2 tracks the high water mark for each table space. This is true both with reclaimable and non-reclaimable storage. If you never deleted any data and never had any overflows, most of the space up to the high water mark would be actively used.
Non-Reclaimable
With non-reclaimable storage, when data is deleted, the high water mark rarely goes down. To lower the high water mark, you generally go though this process, manually and iteratively:
- Run db2dart to get a list of actions to perform to lower the high water mark
- Perform the actions suggested by db2dart. These ususally involve:
- Exporting data, dropping a table, re-loading the table (table is offline while performing)
- Performing reorgs
- GOTO 1
This can be extremely time consuming, and there are a vast number of details to be aware of when dropping and re-creating a table.
Reclaimable
With reclaimable storage, you can lower the high water mark and reduce the size of table spaces by engaging extent movement, often with a single easy command. While there can be conflicts with backups, the operation is generally online. You can lower the high water mark simply by using the LOWER HIGH WATER MARK keywords on the ALTER TABLESPACE command. If you also want to decrease the size of the containers, you can use the REDUCE keyword either with a specific number or the MAX keyword on the ALTER TABLESPACE command. DB2 will perform extent movement and reduce the size of your table space.
How to Tell if Existing Table Spaces are Non-Reclaimable
This is an item that I’ve added into my health check process. Often when I go into a health check, I don’t know if a database has been upgraded from an older version or not. Luckily it is easy to check with this query:
select substr(TBSP_NAME,1,18) as tbsp_name
, reclaimable_space_enabled
from table (MON_GET_TABLESPACE(NULL,-1)) as mgt
where tbsp_type='DMS'
with ur
TBSP_NAME RECLAIMABLE_SPACE_ENABLED
------------------ -------------------------
SYSCATSPACE 1
USERSPACE1 1
TAB8K 1
TAB16K 1
SYSTOOLSPACE 1
A value of 1 indicates that the table space is using reclaimable storage. a value of 0 indicates that it is non-reclaimable
Conversion
Conversion from non-reclaimable to reclaimable does NOT happen with upgrade. In fact, there is no way to convert an existing table space. The only option is to create a new table space on DB2 9.7 or later and move tables over one by one.
The ADMIN_MOVE_TABLE tool makes this possible, though it isn’t really fully usable until at least 10.1 fix pack 4 or later. Using ADMIN_MOVE_TABLE for a thousand or more tables is complicated and may entail concurrency issues. There are complicated issues that come into play here. Add in MQTs and this is a bit of a nightmare. I have a client who is spending hundreds of hours doing this for their 8 production WCS environments. It is taking multiple iterations of testing to get all the details correct for one of the meticulous DBAs I know.
It is also not a whole solution. What about the tables in SYSCATSPACE? These tables cannot be moved. The only way to convert SYSCATSPACE is to create a wholly new database and load data over to it.
Essentially what this means is that every database upgrade from something before 9.7 to anything after 9.7 must eventually involve completely dropping and recreating the database. In my opinion, this is not acceptable. I have a hard enough convincing some clients to take the outage to do a simple upgrade, but this takes so much longer and involves so much more risk.
Why is there no conversion utility available? I understand that such conversion likely involves changing every page for every table and every index in the database. Even if done offline, surely a conversion process would be faster than unloading and re-loading all tables in the database. There’s an RFE open to request this functionality – please go out and vote on it. The link is: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=66344. Unfortunately that link only works some of the time. It is most likely to work if you log in using your IBM ID first, and it also doesn’t seem to work in all browsers. When I open them on my phone, I have to select to open the link in a browser and make sure I’m logged in with my IBM ID. You can also just go to the general RFE site and search within the DB2 LUW product for “reclaimable”, and you’ll find it.
Another Good Article … Thanks Ember !
Can you please let me know, How to determine the tablespace which needs reclaim space or reduce HWM ? Do we have any formula to find it ?
This is a judgement call. Some free space is used by subsequent data addition in the table or tablespace (depending on where the free space exists). You can find tablespaaces with more space allocated than used with this query:
select substr(tbsp_name,1,18) as tbsp_name, tbsp_type, tbsp_content_type as type, (select count(*) from syscat.tables st where st.tbspace=t.tbsp_name) as tabcount, tbsp_using_auto_storage as auto_sto, tbsp_auto_resize_enabled as auto_resize, RECLAIMABLE_SPACE_ENABLED, tbsp_page_size, tbsp_used_pages, tbsp_total_pages from table(mon_get_tablespace('',-2)) as t order by tbsp_name; TBSP_NAME TBSP_TYPE TYPE TABCOUNT AUTO_STO AUTO_RESIZE RECLAIMABLE_SPACE_ENABLED TBSP_PAGE_SIZE TBSP_USED_PAGES TBSP_TOTAL_PAGES ------------------ ---------- ---------- ----------- -------- ----------- ------------------------- -------------------- -------------------- -------------------- SYSCATSPACE DMS ANY 144 1 1 1 4096 79604 81940 SYSTOOLSPACE DMS LARGE 5 1 1 1 4096 708 8220 SYSTOOLSTMPSPACE SMS USRTEMP 0 1 0 0 4096 5 5 TAB16K DMS ANY 62 1 1 1 16384 25088 26880 TAB8K DMS ANY 109 1 1 1 8192 37408 40960 TEMPSPACE1 SMS SYSTEMP 0 1 0 0 4096 5 5 TEMPSYS16K SMS SYSTEMP 0 1 0 0 16384 5 5 TEMPSYS32K SMS SYSTEMP 0 1 0 0 32768 5 5 TEMPSYS8K SMS SYSTEMP 0 1 0 0 8192 5 5 USERSPACE1 DMS LARGE 881 1 1 1 4096 1399776 1450240But you don’t generally want to free up all space – if there is more than a 20% difference between TBSP_USED_PAGES and TBSP_TOTAL_PAGES, and you don’t expect much growth in that tablespace, that’s the threshold that usually would trigger me to look into this.
If you’re not properly reorging your tables (allowing truncation), then there might be a lot of space still tied up in tables, too.
Thanks for the post, Ember. We recently moved 500+ tables from pre-9.7 tablespaces to new tablespaces. We agree it was painful! We don’t use auto-resize or automatic storage. We currently run a script to lower high water marks on all reclaimable storage tablespaces every week, but we don’t reduce the size of reclaimable storage tablespaces. Do you think lowering the HWM has any other benefit besides the ability to reduce reclaimable storage tablespace size?
Hi,
“The ADMIN_MOVE_TABLE tool makes this possible, though it isn’t really fully usable until at least 10.1 fix pack 4 or later”
Was this due to security labels (http://www-01.ibm.com/support/docview.wss?uid=swg1IC97824 / http://www-01.ibm.com/support/docview.wss?uid=swg1IC98017)
or memory corruption error (http://www-01.ibm.com/support/docview.wss?uid=swg1IC97828) or something else?
Mainly due to RI.
We had to migrate 1000+ Instances/Databases away from DB2 9.5. We decided to create a script that automatically create the ddl statments to referate the tablespaces and tablets amd indexes etc. Then all data was exported. When all exports wehre completed successfull the databases have eben backend up. Then the databases have ben dropped an the instanze as well. Then a new instance has been create. Then the databases have been create with automatic storage plus the tablespaces and tablespaces. Load the data, create indexes and finale runstats.
It is a complex script behaus of the differenz fall back situations. It worked great and so we could migrate all Instances from 9.5 to 10.1 without problems and earned all the benefits of automatic storage and non reclaimable dataspaces plus some otters.
That’s the only real workaround, but the complexity is a problem, and for many databases the offline time and increased duration is a problem.
Excellent article! We feel the pain of having to consider the best ways to do this with PeopleSoft tables (40000+; 260+ tablespaces), most of which were create in DB2 LUW 9.5. One good thing is that PS doesn’t use DB2’s RI, so ADMIN_MOVE_TABLE is a real option. One problem we’ve been encountering: it doesn’t seem to take care of views. Is there an option in ADMIN_MOVE_TABLE we’re not using that will save or recreate views? Otherwise, it becomes a process that requires an outage that will be very unpopular.
My understanding is that views are only unavailable during the swap phase. You can control when the swap phase occurs if you run each phase manually.
hi Ms Ember Crooks
do you happen to have any sql that identifies all the tabletspaces that are not used by any object (like table, index, lob etc) ? would you pls share it if you dont mind ?
thank you
vani
I don’t have that on hand. I would think you could join syscat.tablespaces to syscat.tables to find records in syscat.tablespaces that are not included in any of the TBSPACE, INDEX_TBSPACE, or LONG_TBSPACE columns.