Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only

  • 1.  Truncate/Load table with Index

    Posted Thu September 28, 2023 03:00 PM

    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


  • 2.  RE: Truncate/Load table with Index

    Posted Thu September 28, 2023 11:52 PM
    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
    ------------------------------

    Original Message


  • 3.  RE: Truncate/Load table with Index

    Posted Fri September 29, 2023 12:10 AM

    Thank you Birgitta - This is very helpful for me.



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

    Original Message


  • 4.  RE: Truncate/Load table with Index

    Posted Fri September 29, 2023 02:38 AM
    Edited by Satid Singkorapoom Fri September 29, 2023 02:41 AM

    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.
    ------------------------------

    Original Message


  • 5.  RE: Truncate/Load table with Index

    Posted Fri September 29, 2023 11:11 AM

    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
    ------------------------------

    Original Message


Related Content