DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID.

Products

• Easytrieve Report Generator
• PAN/SQL

Issue/Introduction

Upgrading to Easytrieve 11.x from 6.x, using DQSCGEN to Generate static command program statements following an 11.x compile, the following SQL errors are produced at run-time (the -501 seems to continue infinitely).

WARNING, non-zero SQL code returned. SQL code is -180.
DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE *N. IS INVALID.
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE.
DSNT415I SQLERRP    = DSNXRIHB SQL PROCEDURE DETECTING ERROR.
DSNT416I SQLERRD    = -6805  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION.
DSNT416I SQLERRD    = X'FFFFE56B'  X'00000000'  X'00000000'.
X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC.
INFORMATION.
WARNING, non-zero SQL code returned. SQL code is -501.
DSNT408I SQLCODE = -501, ERROR:  THE CURSOR IDENTIFIED IN A FETCH OR.
CLOSE STATEMENT IS NOT OPEN.
DSNT418I SQLSTATE   = 24501 SQLSTATE RETURN CODE.
DSNT415I SQLERRP    = DSNXERT SQL PROCEDURE DETECTING ERROR.
DSNT416I SQLERRD    = -240  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION.
DSNT416I SQLERRD    = X'FFFFFF10'  X'00000000'  X'00000000'.
X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC.
INFORMATION.

The problem here is that DB2 thinks there is an invalid DATE value because the date fields checked in the SELECT have not been initialized. This is because the SELECT processing is done before the START proc runs, and the dates are set in the START proc.

When using SQL files on the JOB statement, the immediately following SELECT is executed before the START proc coded on the JOB statement.

 

Environment

Easytrieve Plus Report Generator, release 6.4

Resolution

 

Please use the DEFER parameter on the SQL FILE statement. For example FILE db2file SQL DEFER... Although the behavior in 11.x is different than in 6.x, it because 6.x behaved incorrectly. In 11.x, this has been corrected.

Please see the "Using DEFER with SELECT" section in the "SQL Database Processing" chapter of the Programming Guide for more information (Chapter 4, page 177).