Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: select current timestamp

    quick question hopefully.

    we can get the current timestamp using sysibm.sysdummy1 and that works ok, but we were advised that after v8 implementation, this table/option wouldnt be available (presumably the advisor assumed it wouldnt work with unicode or something...the manual sais its still ebcidic). - I know this isnt true!!

    anyway, we assumed that using
    "select current timestamp" from any DB2 table would return the same result and it worked perfectly. What we didnt notice was that the result seems to be being obtained by doing a full index scan. This is ok in test where there's a low number of rows, but as soon as it went live, it tried to scan 200 million rows and obviously took a while..

    so the question is, why is it trying to do an index scan and not just returning the current timestamp as it would from sysdummy?

  2. #2
    Join Date
    Mar 2006
    Posts
    104
    try SQL

    "values current timestamp"

    Regards

    Harikumar

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    thanks for that Harikumar. I know we can just use sysdummy now, so the SQL isnt really a problem.

    What I'm really interested in is the nuts and bolts of why it does a full index scan. I just was just hoping there was a simple explanation.

    ie why doesnt DB2 realise the current timestamp on any table is just that, the current timestamp. If I inserted to the table and used default timestamp it wouldnt scan the index first and then decide what the timestamp should be...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What you are describing is standard SQL. If you run the following statement:

    select 1 from <table-name>

    You will get one row for each row of the table (each row with the value 1 being returned). This is what happens when you have no WHERE clause. Anything else would be a violation of the SQL standard.

    This is why sysibm.sysdummy1 has only one row in it, percisely so that you can select the current_timestamp and get one value back. Since this table was put in DB2 for LUW for compatibility with DB2 for z/OS, I don't think it is going away. If it does, just create your own table or view with the same name.

    BTW, if you don't want to use sysibm.sysdummy1 you can do the following:

    select current_timestamp from <table-name> fetch first 1 rows only

    This will achieve the objective you want without the full index or table scan.
    Last edited by Marcus_A; 02-20-08 at 01:10.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Posts
    113
    I believe you can still use sysibm.sysdummy1 as before.

    In case of difficult encoding(unicode vs. ebcdic), you can use sysibm.sysdummyu, which is in unicode

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Exactly - just use SYSIBM.SYSDUMMY1, that's what it is intended for. It is the way to deal with the situation that DB2 z/OS does not have a VALUES table constructor yet. It won't go away for two primary reasons:
    (a) IBM did not mark it as being "deprecated"
    (b) it is used in way too many applications to force customers to change those applications
    Last edited by stolze; 02-20-08 at 07:54.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2010
    Posts
    1

    set command vs select

    Is there a difference performance- or otherwise between set :hv = current timestamp and select current_timestamp from sysdummy1 ?
    Does anyone know ?

  8. #8
    Join Date
    Apr 2009
    Posts
    42
    I believe the performance difference is minor

  9. #9
    Join Date
    Aug 2010
    Posts
    1
    yea i believe the difference is minor as well

  10. #10
    Join Date
    Dec 2005
    Posts
    273
    Some time ago I did some performance checks regarding that issue.

    SELECT current date + :n days INTO :host FROM SYSIBM.SYSDUMMY1 versus
    SET :host = current date + :n days.

    The SET costs about half the CPU time and elapsed time.

    ( Maybe that changed in V9, as SYSDUMMY1 was moved from tablespace DSNDB06.SYSSTR to DSNDB06.SYSEBCDC. )


    If you still use SYSDUMMY1, SELECT WITH UR will save about 10% CPU

Posting Permissions

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