Db2 – Use of SET INTEGRITY for DETACH PARTITION

db2

I have some partitioned tables with DB2 LUW 10. We have one partition for each data.I am reviewing a DB2 stored procedure that runs daily detaching old data partition and creating a new partition because some days this stored procedure takes some hours to complete but usually it only takes some seconds. We found that the delay occurs in the detach processing,

According to some logic the stored procedure calculates how many partitions are going to be detached (only one usually). For each partition to be detach the stored procedure issues

ALTER TABLE DETACH;
COMMIT;
SET INTEGRITY FOR TABLE XXX  IMMEDIATE ALL UNCHECKED;

Please can you explain me the function of the commit and set integrity commands in this stored procedure?

Under which conditions can this stored procedure takes some hours to complete the partition detach ?

Thanks you very much for your help

Best Answer

The COMMIT is fairly obvious; it's committing the effects of the prior ALTER TABLE DETACH... statement (and any statements prior to that and the preceding COMMIT/ROLLBACK - transactions are implicitly started in DB2). I assume there's a bit more to that DETACH statement but that it's omitted for brevity.

The SET INTEGRITY statement (I'd strongly recommend reading that page, especially the Notes section) is more complex. In this case it's bringing the table out of check-pending status by telling the database engine that the table's data still conforms to all the constraints on the table, but that the engine shouldn't bother checking that itself. This is as opposed to SET INTEGRITY ... IMMEDIATE CHECKED in which the database engine would check the data is correct.

The table has presumably been left in check-pending state by some earlier operation (I don't think detaching partitions causes check-pending, although attaching them certainly does - see ALTER TABLE for more information).

As the Notes section of the SET INTEGRITY reference page states:

The fact that the table was taken out of the set integrity pending state without performing the required integrity processing will be recorded in the catalog (the respective byte in the CONST_CHECKED column in the SYSCAT.TABLES view will be set to 'U'). This indicates that the user has assumed responsibility for data integrity with respect to the specific constraints...

I'd strongly recommend reading the rest of the Notes section for more info on using IMMEDIATE UNCHECKED.

Related Question