This Db2 News from the Lab blog entry was originally published on 2019-11-22.
By Regina Liu, Long Tu, and Jennie Chang.
With Db2 12 function level 506 at application compatibility level V12R1M506 or higher, Db2 now automatically drops explicitly created universal (UTS) and LOB table spaces when the tables they contain are dropped. More specifically, the following enhancements are introduced:
- If you drop a base table that resides in an explicitly created universal table space, Db2 implicitly drops the table space along with the table, instead of returning an error in SQLCODE -669.
- If you drop a system-period temporal table or an archive-enabled table, Db2 also drops the associated history table or archive table. If the history table or archive table resides in an explicitly created universal table space, Db2 implicitly drops the table space along with the table, instead of returning an error in SQLCODE -669.
- If you drop an auxiliary table that resides in an explicitly created LOB table space, Db2 implicitly drops the table space along with the table. Previously, the LOB table space remained as an empty table space. An auxiliary table is dropped when you take one of the following actions:
- Issue the DROP TABLE statement on the auxiliary table
- Drop the associated base table
- Drop an associated LOB column
- Remove an associated trailing empty partition when running the REORG utility on a partition-by-growth table space
The new REORG utility behavior takes effect immediately after the activation of function level 506 or higher. For the new SQL statement behavior to take effect, the application compatibility level must be set to V12R1M506 or higher.
Before these enhancements, the DROP TABLE statement would drop both a table and its associated table space only if the table space was implicitly created. If the table space was explicitly created, on the other hand, you needed to separately drop it with the DROP TABLESPACE statement. With these enhancements, you no longer need to distinguish between implicitly created table spaces and explicitly created universal and LOB table spaces when dropping a table and its table space, eliminating the extra step of determining which statement to use.
After these enhancements become effective, keep in mind to take the following actions, if applicable:
- Any existing applications that use the DROP TABLESPACE statement to drop a universal table space with a table can now be changed to use the DROP TABLE statement instead.
- If an auxiliary table is dropped, any applications or tools that expect the LOB table space to remain for reuse must be modified accordingly.
- Regina Liu is a Senior Software Engineer for Db2 for z/OS RDS development.
- Long Tu is a software engineer for Db2 for z/OS RDS development.
- Jennie Chang is a technical writer for Db2 for z/OS.