SQLSTATE 01506 ?

Ulf-Otto Cihak

SQLSTATE 01506 ?
Dear List,

during execution of a DSNTEP2 (on DB2/OS390 Version 5) of

SELECT ...
FROM ...
WHERE VSTART_DATUM < CURRENT DATE - 3 MONTH
AND ( ... OR ...)
GROUP BY ...

I got the following SQL Warning, accompanied by CondCode 4 :

DSNT400I SQLCODE = 000, SUCCESSFUL ECUTION
DSNT418I SQLSTATE = 01506 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 9507 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00002523' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS
DSNT417I SQLWARN6-A = W,,,, SQL WARNINGS
.....

SQLSTATE 01506: An adjustment was made to a DATE or TIMESTAMP value
to correct an invalid date resulting from an arithmetic operation

This was a one-time occurrence, and not repeatable with same input
data (on a different CURRENT DATE though).

My search for a similar SQLCODE was unsuccessful (only SQLCODE -187
tells about a MVS TOD clock possibly being bad), in our case a SYSPLEX timer
is used, nothing unusual seen on the MVS syslog. Search in IBM
APAR Database also unsuccessful.

Has anybody seen this SQLSTATE before ?

Thank you,
Ulf Cihak
Nuernberg/Germany



Suresh Sane

Re: SQLSTATE 01506 ?
(in response to Ulf-Otto Cihak)
Ulf,

My guess is that the query was run on the 31st of December/July or on
30th/31st of May (where subtracting 3 months results in an invalid date) -
DB2 then adjusts to the next day. True?

HTH.

Suresh


>From: Ulf Cihak <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: SQLSTATE 01506 ?
>Date: Thu, 4 Jan 2001 11:28:37 -0600
>
>Dear List,
>
>during execution of a DSNTEP2 (on DB2/OS390 Version 5) of
>
>SELECT ...
>FROM ...
>WHERE VSTART_DATUM < CURRENT DATE - 3 MONTH
> AND ( ... OR ...)
>GROUP BY ...
>
>I got the following SQL Warning, accompanied by CondCode 4 :
>
>DSNT400I SQLCODE = 000, SUCCESSFUL ECUTION
>DSNT418I SQLSTATE = 01506 SQLSTATE RETURN CODE
>DSNT416I SQLERRD = 0 0 9507 -1 0 0 SQL DIAGNOSTIC INFORMATION
>DSNT416I SQLERRD = X'00000000' X'00000000' X'00002523' X'FFFFFFFF'
>X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>DSNT417I SQLWARN0-5 = W,,,,, SQL WARNINGS
>DSNT417I SQLWARN6-A = W,,,, SQL WARNINGS
>.....
>
>SQLSTATE 01506: An adjustment was made to a DATE or TIMESTAMP value
>to correct an invalid date resulting from an arithmetic operation
>
>This was a one-time occurrence, and not repeatable with same input
>data (on a different CURRENT DATE though).
>
>My search for a similar SQLCODE was unsuccessful (only SQLCODE -187
>tells about a MVS TOD clock possibly being bad), in our case a SYSPLEX
>timer
>is used, nothing unusual seen on the MVS syslog. Search in IBM
>APAR Database also unsuccessful.
>
>Has anybody seen this SQLSTATE before ?
>
>Thank you,
>Ulf Cihak
>Nuernberg/Germany
>
>
>
>
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com



Ulf-Otto Cihak

Re: SQLSTATE 01506 ?
(in response to Suresh Sane)
Suresh,

your guess is correct - the query run on December 31.
However, in this case, this SQLSTATE would be very
common - I have never noticed it before.

Also, the control query (in European Date Format)

SELECT DATE ('31.12.2000') - 1 MONTH
FROM SYSIBM.SYSDUMMY1;

gives me

30.11.2000

WITHOUT any Warning and SQLSTATE.

So, please try a second guess :-)
Thank you,
Ulf Cihak



Ulf-Otto Cihak

Re: SQLSTATE 01506 ?
(in response to Ulf-Otto Cihak)
Suresh (and all),

please discard my previous erroneous reply. My 'control query',
as discribed in my previous message, does indeed give back
SQLSTATE 01506 when issued through DSNTEP2. So, Suresh, you
are absolutely right, SQLSTATE 01506 seems to be the normal
DB2 correction message in this context.

The reason I never noticed it before is that normally I let my
DSNTEP2 Jobs pass through with Returncode 4. Only in this
particular case the Returncode 4 was cought by a subsequent
Abend step.

Sorry for bothering you,
Ulf Cihak