We do a lot of Truncate and loading of tables in our ETL.  Currently, we don't use indexes on almost anything.  I am looking to change that, but I want to understand how this might work.  Say I have a table that is created, loaded, and indexed.  I now truncate the table and start inserting records.

• what happens to my index when I truncate? Does it essentially become "empty"?  Is it still valid?
• When I start inserting records, does it just rebuild?
•  Are there any performance issues with having existing indexes and reloading millions of records?  Let's assume I have a lot of indexes.
  • It had been suggested to me that the indexes should be dropped and rebuild after.  Is that really necessary?  I could see some complications with that given primary/unique keys.  DB2 seems too friendly to require this of me!!

I am sure there is a best practice here.  I appreciate any advice.  Thanks

------------------------------
Phil Meador
------------------------------

#SQL

1. Indexes are access path, and depend on the data in the table. If the data is deleted the index is also reduced.
2. Indexes are always up to data, i.e. with each insert, update or delete on the tables all indexes (on the table) are updated, so there will never be a gap between real data and "index" data when accessing data an an index is used.
3. When inserting millions of rows it might be better to delete the indexes first, insert the data and then recreate the indexes. Then the indices are created/updated only once (at the end of the copy) and not after each row

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Thank you Birgitta - This is very helpful for me.

------------------------------
Phil Meador
------------------------------

Original Message:
Sent: Thu September 28, 2023 11:51 PM
From: Birgitta Hauser
Subject: Truncate/Load table with Index

1. Indexes are access path, and depend on the data in the table. If the data is deleted the index is also reduced.
2. Indexes are always up to data, i.e. with each insert, update or delete on the tables all indexes (on the table) are updated, so there will never be a gap between real data and "index" data when accessing data an an index is used.
3. When inserting millions of rows it might be better to delete the indexes first, insert the data and then recreate the indexes. Then the indices are created/updated only once (at the end of the copy) and not after each row

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Dear Phil

In IBM i, there are multiple ways available for index maintenance method.  The default (and most prevalent) one is "Immediate (on-line)  Maintenance" and what Birgitta describes is based on this method.

In IBM i release 7.x, a product name DB2 SMP (Symmetric Multiproceesing) is now free of charge (but need an active SWMA).  Index build process can utilize DB2 SMP for faster build time when the index is large. 

If you use SQL  CREATE INDEX, precede it with SET CURRENT DEGREE = 'N'   where N is an integer value.  My personal rule of thumb is to use N = 3 for each active POWER9 or 10 core that is available in the LPAR.

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
------------------------------

If you're loading lots of brand new rows into a table, then it would be faster to create the indexes after all of the data is loaded.  Using Db2 SMP would be a good way to speed up the index creation process after the data load is done.

------------------------------
Kent Milligan
IBM Technology Expert Labs
------------------------------