Let's walk through the issues in your script and fix them—DB2 does have stricter syntax enforcement than SQL Server for certain constructs, so it's easy to trip up on these differences:

1. The Direct Cause of SQL0199: Unnecessary FROM sysibm.sysdummy1 #

Your subquery for the DTL table delete includes an extra FROM sysibm.sysdummy1 at the end, which makes no sense here. The subquery is already pulling data from TT.HDR, so adding this extra table reference is invalid syntax. This is exactly what's triggering the "Keyword FROM not expected" error.

2. Invalid DELETE Syntax on TT.HDR #

The line DELETE LOGID FROM TT.HDR is wrong for DB2. When you want to delete entire rows from a table, you don't specify a column name—just use DELETE FROM TT.HDR. Specifying a column in the DELETE clause only applies to very specific edge cases (like partitioned table deletes), which isn't what you're doing here.

3. SIGNAL Statement Breaking Execution Flow #

Your BEGIN ATOMIC block includes a SIGNAL SQLSTATE '70000'; right after the first DELETE. This will immediately throw an exception and terminate the stored procedure, so the second DELETE will never run. Unless this is intentional test code, you should remove this line entirely.

Corrected Stored Procedure Script #

Here's the fixed version with all syntax issues resolved, plus a more standard date interval format:

CREATE PROCEDURE TT.PURGE_LOGS ()
LANGUAGE SQL
SPECIFIC atomic_proc
ap: BEGIN ATOMIC
    -- Delete related child records from DTL first
    DELETE FROM TT.DTL 
    WHERE LOGID IN (
        SELECT LOGID 
        FROM TT.HDR 
        WHERE LOGTYPE <> 120 
          AND CRDATE < CURRENT_DATE - INTERVAL '30' DAY
    );
    
    -- Delete old parent records from HDR
    DELETE FROM TT.HDR 
    WHERE LOGTYPE <> 120 
      AND CRDATE < CURRENT_DATE - INTERVAL '30' DAY;
END ap

Quick Notes #

• Using CURRENT_DATE - INTERVAL '30' DAY is the SQL-standard way to calculate dates in DB2, and it works across all supported versions (the shorthand 30 DAY is also valid, but the interval syntax is more readable).
• The BEGIN ATOMIC block ensures both DELETE operations run as a single atomic transaction—either both succeed, or both roll back if an error occurs, which is perfect for this kind of purge operation.

内容的提问来源于stack exchange，提问作者Warrior