Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

sqlstate = 01003 + problem locating the offending code

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Fri Mar 30, 2007 11:25 am    Post subject: sqlstate = 01003 + problem locating the offending code
Reply with quote

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
Back to top
View user's profile Send private message

vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 1:03 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 1:12 pm    Post subject:
Reply with quote

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]
Back to top
View user's profile Send private message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Fri Mar 30, 2007 4:28 pm    Post subject:
Reply with quote

Hi again,

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

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...
Back to top
View user's profile Send private message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Fri Mar 30, 2007 6:15 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 30, 2007 7:37 pm    Post subject:
Reply with quote

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


Gentle Man, We are not here to proove our intelligence.
Well stated problem is half solved. Appreciate that, still people are being helpfull to others while they are working.

Whenever you post any questions, Pease make sure you give the enough info. This way people can more closely look into the issue and figure out what can be done. Finally you will be benefied. Hope you wont take this in other way. Thank you..![/code]
Back to top
View user's profile Send private message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Mon Apr 02, 2007 10:49 am    Post subject: Re: sqlstate = 01003 + problem locating the offending code
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 3 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us