Timing for iSeries DB2 SYSIBM/SYSDUMMY1 updates

I have a simple query to pull DAYOFWEEK from SYSIBM/SYSDUMMY1 to determine whether or not to clear a table that tracks yesterday sales.

 DELETE FROM DAILYSALES WHERE (CASE WHEN (SELECT DAYOFWEEK(CURRENT DATE) FROM SYSIBM/SYSDUMMY1) BETWEEN 3 AND 6 THEN 'YES' ELSE 'NO' END) = 'YES'; 

Since the report that runs on Monday (dayofweek=2) needs Friday (dayofweek=6), Saturday (dayofweek=7), Sunday (dayofweek=1) sales (today-1 to calculate date), it would seem logical that I should see sales figures for days 6, 7, and 1.

However, it seems to be retaining Thursday's (dayofweek=5) sales and not pulling Sunday sales (dayofweek=1) when it runs on Monday morning. This leads me to believe the date being used by SYSDUMMY1 doesn't flip until long after midnight, even though our system date flips at midnight EST.

It runs fine the rest of the week, providing the correct sales data for 'yesterday'. When I manually run Select DAYOFWEEK(current date) from SYSIBM.SYSDUMMY1, it always shows the correct dayofweek. What am I missing?

1 Answer

I don't see any correlation (usage) of a column in DAILYSALES..

Thus

(CASE WHEN (SELECT DAYOFWEEK(CURRENT DATE) FROM SYSIBM/SYSDUMMY1) BETWEEN 3 AND 6 THEN 'YES' ELSE 'NO' END) 

Is only evaluated one time and if the process runs on Monday, would return 2==>NO so no rows would be deleted.

I suspect instead of DAYOFWEEK(CURRENT DATE) you want DAYOFWEEK(SALESDATE) where SALESDATE is a column in the DAILYSALES table.

Lastly note that SYSIBM.SYSDUMMY1 has only a single char(1) column IBMREQ and a single row with the value of that column set to 'Y'. Nothing is ever updated in this table.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobW9rYW1%2Ben2OraCmoZ6ceqe70WagrJ2inrK0ecObaWarqai2o7mMrLCsnKWiurp9jK6nnZmkmsA%3D