Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183

    Unanswered: Slow search on timestamp with date

    Hi,

    We are using DB2 9.7 on Linux. We are redesigning our datamodel. From the old days we had a date files (as char defines) and a time files (also a char). We are now modernizing the datamodel and changed these field into a timestamp.
    We index the timestamp for fast query response. Multiple tables have over 150 Mln rows.

    We nog have to access this database with cognos and search on a date. In cognos we created a calculated field so that the outcoming query looks something like this:
    CAST (<field> AS DATE) = "2012-01-01"

    But (of course) the database is not using an index. Does anyone has any ideay how to overcome this. I ve seen an expression based index on an cast in a DB2 z?OS example, but that does not seam to work on LUW.

    Anyone any ideas, besides going back to the old method (but then with a real date field)??

    I appriciate any help

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    RobP, You can allow Index access by removing the Function from the Column and using it on the Data. Here is one example:
    Code:
    WITH TEST_TAB (TSTMP_COL, COL2)
      AS (
          SELECT CAST('2012-01-02-01.02.03.000000' AS TIMESTAMP)
               , CAST('A'                          AS CHAR(1)  ) 
          FROM SYSIBM.SYSDUMMY1
            UNION ALL
          SELECT CAST('2012-02-04-11.02.03.000000' AS TIMESTAMP)
               , CAST('B'                          AS CHAR(1)  ) 
          FROM SYSIBM.SYSDUMMY1
            UNION ALL
          SELECT CAST('2012-03-05-20.02.03.000000' AS TIMESTAMP)
               , CAST('C'                          AS CHAR(1)  )
          FROM SYSIBM.SYSDUMMY1
         )
    
    SELECT *
    FROM TEST_TAB
    WHERE TSTMP_COL BETWEEN TIMESTAMP('2012-03-05','00.00.00')
                        AND TIMESTAMP('2012-03-05','24.00.00')
    
    TSTMP_COL                  COL2
    -------------------------- ----
    2012-03-05-20.02.03.000000 C   
    
      1 record(s) selected.

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183

    Datetime re-explained

    Hi,

    Thanks for the reply, and apologies for my late review.

    THe problem in the given solution is that we cannot use this solution. The proble is when in Cognos we want to use the date as a filter instead of a datetime we have to create an attribute in the cognos framework manager for the date. In the generated queries the cast is sent to the database, but of course no index is used.

    I've seen that in DB2 z/OS an index can be built on the expression of the cast. But is it seems this doesn't work on DB2 9.7 on LUW.

    Again anyone any ideas??

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Serge Rielau's paper gives some options if you use generated-columns
    https://www.ibm.com/developerworks/m...olumns?lang=en

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    db2 "select count(*) AS COUNT from syscat.tables where tabschema LIKE 'SYS%' and STATS_TIME < TIMESTAMP('2012-05-11')"

    COUNT
    -----------
    130


    Note that STATS_TIME is defined as a TIMESTAMP column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •