Using temporary indices to optimize DELETE operations on big tables in #DB2

By | December 18, 2019

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.