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.
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.
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 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.