MVSFORUMS.com

[ROBERTLa]

Gents:

What's the best way to code Cobol-SQL to delete all rows where Timestamp (ccyy-mm-dd part only) > 60 days old ?

I understand that timestamps have a special internal DB2 format (which apperently includes the hyphens) and Cobol has no timestamp data-type as such.

I'd like to use the timestamp in the SQL where block (ie: make the timestamp an indexed value) to be able to do mass deletes based on its value. Do you see any issues with that ?

Robert

[sriramla]

EXEC SQL
DELETE FROM <Tabe>
WHERE col1 + 60 DAYS < CURRENT DATE
END-EXEC
DISPLAY SQLERRD(3) --> To know how many records have been deleted.

[kolusu]

Robetla,

Sriramla has the right idea but his syntax is wrong.Robert has the column in a timestamp format.You don't really need cobol code for your case.you can code a simple sql statement shown below to delete all the rows which are are 60 days old.

[sriramla]

Kolusu, You are right. The syntax is not correct. Anyway, you got the idea. Instead of col1 + 60 days < current date, we can use DATE(col1) + 60 days < current date. This will work. (This time, I've done some home work on syntax before writing this..)

[SureshKumar]

Kolusu, Just for clarification - what was the reason behind using a sub-query ? will it not suffice to use just the date function ? like
DELETE FROM TABLE
WHERE DATE(T_STMP_COL) < DATE(CURRENT DATE - 60 DAYS)

[kolusu]

Sureshkumar,

The query you have shown without the use of subquery works with DB2 version 7 and higher. I am not sure that every shop has upgraded the latest version of DB2. My query works for DB2 v5 and higher. I am assuming that most shops will have atleast version 5 of db2 as DB2 V4 is an unsupported version from DB2.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu