DELETE operations are much faster in case we disable constraints. This is because not all foreign keys affected by the DELETE are indexed.
We have to detect all the foreign keys that point to the table from where you delete data. The we can create temporary indices to help speed up the delete.
STEP 1. Detect all he foreign keys that point to the table on which DELETE will be executed TRANSACTION
Execute the following query
select substr(tabname,1,30) table_name,substr(constname,1,20) fk_name,substr(REFTABNAME,1,30) parent_table,substr(refkeyname,1,30) pk_orig_table,fk_colnames from syscat.references where tabname = 'TRANSACTION';
result is something like :
TRANSACTION FK76A35E5C2E6581E7 PARTYIDENTIFICATION SQL121207182559530 CREDITOR_ID TRANSACTION FK76A35E5C3291208D PARTYIDENTIFICATION SQL121207182559530 DEBTOR_ID
STEP 2. Create temporary indices
Create an index on column CREDITOR_ID on PARTYIDENTIFICATION and an index on column DEBTOR_ID on PARTYIDENTIFICATION
STEP 3. Execute the DELETE
Now we can perform a DELETE on TRANSACTION that will affect a big number of entries.
STEP 4. Delete the temporary indices
Delete all the indices created at STEP 2. We do not need unnecessary indices on the production environment.