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?

try SQL

"values current timestamp"

Regards

Harikumar

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

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.

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

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

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

I believe the performance difference is minor

yea i believe the difference is minor as well

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