DB2 - L

  • 1.  Temporal table indexing

    Posted Oct 06, 2021 05:53 PM
    Edited by Peter Schwarcz Oct 06, 2021 06:41 PM
    Hi All,

    As more organizations use Db2 Temporal Tables we will see performance issues caused by poor or missing indexes. It may be obvious that we need to index the history tables similarly to the primary tables but is that happening?

    I have seen situations on z/OS where organizations have set their BIND options 
    BUSTIMESENSITIVE YES
    SYSTIMESENSITIVE YES
    ARCHIVESENSITIVE YES
    even if the application is not interested in the history and  cause performance issues

    David Beulke wrote a nice article recommending that we index our history tables ( Three Essential DB2 Performance Design Points about DB2 Temporal Table Indexes )

    Should we create our indexes on our history tables with  VALID_FROM ASC, VALID_UNTIL DESC columns at the start or end of indexes ?

    Yes I know it depends. I am interested in your opinions and your experience implementing temporal tables.

    ------------------------------
    Peter Schwarcz
    ------------------------------