sqlstate = 01003 + problem locating the offending code

nuck · New User Joined: 09 Dec 2005 Posts: 33

Hi all,

First off: platform z/os, db2 v8.

I've written a (monstrous) sql which returns a sqlstate of 01003 when executed. I did the development in QMF, and plugged the sql into a cobol program. In QMF there doesn't seem to be any way to get the sqlstate (please correct me if I am wrong), so I assumed everything was ok.

During testing of the batch module, the execution of the sql came back with the sqlstate 01003 (Null values were eliminated from the argument of an aggregate function.).

The only column functions that I'm using are SUM. And they all look like:
sum(coalesce(col_a,0)). Now I'm no expert, but I'd have to say that it's a given that nulls will be eliminated thru the coalesce. Does DB2 give this sqlstate in this case, or am I looking in the wrong place?

Any ideas anyone?

Thanks in advance.
Ralph

vini_srcna · Posted: Fri Mar 30, 2007 1:03 pm

I suspect the issue could be with the MAX function.

I see that there is a where predicate and chances are there a NULL value could be passed to MAX function. I'm not so sure thought. I didnt had time to dig into the query, I just took a cursory look.
Eg: MAX(DEF.FL_SC_CALC)

vini_srcna · Posted: Fri Mar 30, 2007 1:12 pm

The only column functions that I'm using are SUM

It seems like your query contains MAX function without your knowledge.

Moreover its just a warning message. This should not affect your pgm (provided host programming language) execution if you are checking for SQLCODE. Because SQLCODE would still be ZERO.
If you are checing for SQLSTATE then you may have to ignore that. This is to let you know that, NULL value was passed as an argument to the col function and it was ignored by DB2.

Here is the example..: xxxx is defined as timestamp and is nullable.
[img]---------+---------+---------+---------+---------+---------+---------+----
SELECT MAX(xxx) FROM tablename;
---------+---------+---------+---------+---------+---------+---------+----

---------+---------+---------+---------+---------+---------+---------+----
2007-01-16-05.02.19.934059
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 01003 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,W,,, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,,, SQL WARNINGS
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+----[/img][/code]

nuck · New User Joined: 09 Dec 2005 Posts: 33

Hi again,

yup, there's also a MIN in there somewhere - just wanted to check if everybody was paying attention

To continue the story,
I commented out all of the SUMs and replaced them with for eg.
old: sum(coalesce(col_a,0) as col_a_sum
new: 0 as col_a_sum

additionally, I added coalesces to the MAX stuff
old: SELECT MAX(B.ZEITP_GENER)
new: SELECT MAX(COALESCE(B.ZEITP_GENER, '0001-01-01-00.00.00.000000'))

and the same idea for the MIN....

and I STILL get a SQLSTATE 01003. Huh? How can that be possible, other than DB2 automagically sets the SQLSTATE to 01003 whenever there's a COALESCE....

still scratching my head...

nuck · New User Joined: 09 Dec 2005 Posts: 33

so, I found where the problem is coming from....

SELECT 'RS1'||A.FL_SC_CALC AS FL_SC_CALC
FROM TBKU104 A
, DSN9.TBCN105 C
WHERE A.ZEITP_GENER =
(SELECT MAX(B.ZEITP_GENER)
FROM TBKU104 B
WHERE B.KZ_ORTS_KV = A.KZ_ORTS_KV
AND B.HPT_KTO_NR = A.HPT_KTO_NR
AND B.UNTER_KTO_NR = A.UNTER_KTO_NR
AND DATE(B.ZEITP_GENER) <=
:TBKU102N.CASH-SETT-DATE)

AND A.KZ_ORTS_KV_CP = 0
AND A.HPT_KTO_NR_CP = 0
AND C.KZ_ORTS_KV = :TBKU102N.KZ-ORTS-KV
AND C.HPT_KTO_NR = :TBKU102N.HPT-KTO-NR
AND A.KZ_ORTS_KV = C.GELDKTO_KZ_ORTS_KV
AND A.HPT_KTO_NR = C.GELDKTO_HPT_KTO_NR
AND A.UNTER_KTO_NR = C.GELDKTO_UNT_KTO_NR

why it behaves like that in this constellation is a mystery....at least to me...

vini_srcna · Posted: Fri Mar 30, 2007 7:37 pm

nuck · New User Joined: 09 Dec 2005 Posts: 33

so, I know where the problem is coming from. And it's not a problem, it's the way I was looking at it (I guess).

When a correlated subselect looks like this:
select ....
from ...
where time_created=(select max(time_created).....

but doesn't return any rows, then the 01003 pops up. Up until now, all of the sites I've been on check the SQLCODE first, and when it's not=0, then the SQLSTATE. At my current site, both of them are checked, and lo and behold, out pops a warning.

ok, problem (or more correctly: perception of said problem) solved.
thanks guys.