IBM FHIR Server – Debug Drop Tablespace

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

Example Code