A colleague had a REXX process in place that had been working with DB2 11. After the upgrade to DB2 12, the REXX exec started failing with RC=1 on a PREPARE statement. The SQL statement being prepared contained logic to generate the boundaries used in the predicates referencing a time stamp column in a table:
FROM creator.table_name
WHERE TIME_STAMP_COL BETWEEN
'EndTS' - 2 HOURS-01 MONTHS
AND 'EndTS' + 2 HOURS-01 MONTHS
OR TIME_STAMP_COL BETWEEN
'EndTS' - 2 HOURS-02 MONTHS
AND 'EndTS' + 2 HOURS-02 MONTHS
OR TIME_STAMP_COL BETWEEN
'EndTS' - 2 HOURS-03 MONTHS
AND 'EndTS' + 2 HOURS-03 MONTHS
OR TIME_STAMP_COL BETWEEN
'EndTS' - 2 HOURS-04 MONTHS
AND 'EndTS' + 2 HOURS-04 MONTHS
GROUP BY DATE(TIME_STAMP_COL)
The REXX code PREPAREs this statement with
address DSNREXX "EXECSQL PREPARE S40 FROM :SQL"
In DB2 11, this would return RC=0. After the upgrade to DB2 12, this started returning RC=1. The SQLCA contained:
SQLCODE = 0 SQLERRD.1 = 0 SQLERRD.2 = 0 SQLERRD.3 = 999 SQLERRD.4 = 1142028335 SQLERRD.5 = 0 SQLERRD.6 = 0 SQLWARN.1 = SQLWARN.2 = SQLWARN.3 = SQLWARN.4 = SQLWARN.5 = SQLWARN.6 = W SQLWARN.7 = SQLWARN.8 = SQLWARN.9 = SQLWARN.10 = SQLERRP = DSN SQLSTATE = 01506 <--- SQLERRMC =
Per IBM
01506 = An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation.
The issue was caused because the SQL would generate an invalid date for February. In the past, the PREPARE would have ignored this.