I’ve been debugging a drop tablespace issue on Db2 – IBM/FHIR: 2354. The core issue was a timing problem with lots of partitions dettaching. This adds a delay to the dropTablespace so the async operation can complete, and cleanly exit with a specific error code so downstream consumers can work around the issue.
When debugging why a partition hasn’t dettached, I found that it’s worth checking the details when a drop tablespace fails:
[db2inst1@53fe3a4d3ad2 ~]$ db2 list utilities show detail
ID = 18435
Type = ASYNCHRONOUS PARTITION DETACH
Database Name = FHIRDB
Member Number = 0
Description = Finalize the detach for partition '3' of table 'FHIRDATA.PARAMETER_NAMES'
Start Time = 06/08/2021 16:31:05.526513
State = Executing
Invocation Type = Automatic
Progress Monitoring:
Description = Performing detach operation and
making the target table available; new compilations blocked
Start Time = 06/08/2021 16:31:10.836936
I’ve attached a useful partition.sql to demo partitions and check the system catalog.
Links
- Data Partition Range
- [Query the Syscat Datapartitions view)[https://www.ibm.com/docs/en/db2/11.5?topic=views-syscatdatapartitions)
- Dropping Table Space/Table
- SQL Error Codes
- PR 2487