SQLCODE +354 In Multi Row Fetch

Tommy Petersen

SQLCODE +354 In Multi Row Fetch

We recently upgrade to DB2 11 for z/OS.

We have one program that uses multi row fetch, and after the upgrade it started getting an SQLCODE of +354.
The program looks only for SQLCODE 0 and +100, all other SQLCODE vales are considered an error.

SQLCODE +354 should indicate that one or more rows in a multi row fetch has a warning, and we need to look at the diagnostics to find out what that warning is.  However, in order to quickly fix the issue, we rewrote the program to do a single row fetch, and we get no warnings, so everything is OK.

The question is, have anybody else seen similar issues? 

Is it just us, because we do not check for specific warnings?

Are we the only ones using multi row fetch?

It s quite possible that we should get a warning as some of the rows use the aggregate SUM(amount) function and rows that are summed may contain null in the amount column.  But is that gives a warning, should we not see it when using single row fetch as well.

 

Thank you for any response you may have.

If anybody find it helpful I can post the query, but I do not doubt that it sums rows where the amount may be null.  We are just surprised by the sudden issue, and that IBM apparently have not heard about it before.

 

 

 

Roy Boxwell

SQLCODE +354 In Multi Row Fetch
(in response to Tommy Petersen)
I am surprised that you got that after going to 11 this is not a new sql code.
I must admit that in all my multi row processing I have not used an aggregation function without using coalesce on it first... I hate NULL values...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 13 Sep 2017, at 12:25, Tommy Petersen <[login to unmask email]<mailto:[login to unmask email]>> wrote:


We recently upgrade to DB2 11 for z/OS.

We have one program that uses multi row fetch, and after the upgrade it started getting an SQLCODE of +354.
The program looks only for SQLCODE 0 and +100, all other SQLCODE vales are considered an error.

SQLCODE +354 should indicate that one or more rows in a multi row fetch has a warning, and we need to look at the diagnostics to find out what that warning is. However, in order to quickly fix the issue, we rewrote the program to do a single row fetch, and we get no warnings, so everything is OK.

The question is, have anybody else seen similar issues?

Is it just us, because we do not check for specific warnings?

Are we the only ones using multi row fetch?

It s quite possible that we should get a warning as some of the rows use the aggregate SUM(amount) function and rows that are summed may contain null in the amount column. But is that gives a warning, should we not see it when using single row fetch as well.



Thank you for any response you may have.

If anybody find it helpful I can post the query, but I do not doubt that it sums rows where the amount may be null. We are just surprised by the sudden issue, and that IBM apparently have not heard about it before.







-----End Original Message-----

Robin Zalud

RE: SQLCODE +354 In Multi Row Fetch
(in response to Tommy Petersen)

Hi

QMF/TSO can be started run end user queries using multi row fetch. For that support we had to add the special handling for the multi row fetch SQL code +354 (which we did quite a few years ago way before DB2 11). That SQL code +354 can be triggered for positive and negative SQL codes during fetch. We retrieve the assorted errors and warnings through GET DIAGNOSTICS.. I would agree that if you are getting a positive SQL code during multi row fetch that seems like you would also get it through single row fetch (if you are checking for positive sQL codes during single row fetch).

 

Robin

Suresh Sane

SQLCODE +354 In Multi Row Fetch
(in response to Roy Boxwell)
Tommy/Roy,


The sqlcode is not new, but the behavior may be different for multi-row fetch (perhaps even in V10).


Unfortunately, I cannot test right now, but here is what I think is happening. When a null is encountered for sum, a warning is always issued.


For single row fetch, this returns a sqlcode 0, sqlstate 01003. You see the warnings, but since logic checks for sqlcode, this is treated as "successful".


For multi-row fetch, all errors/warnings return a sqlcode +354 (the real error/warning occurs from #2 onwards in "get diagnostics"). Since the logic is based on sqlcode, this is considered "failure".


If someone has a short app program to test my theory (I may well be wrong), we will learn something.


Thx

Suresh

________________________________
From: Boxwell, Roy <[login to unmask email]>
Sent: Wednesday, September 13, 2017 5:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SQLCODE +354 In Multi Row Fetch

I am surprised that you got that after going to 11 this is not a new sql code.
I must admit that in all my multi row processing I have not used an aggregation function without using coalesce on it first... I hate NULL values...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 13 Sep 2017, at 12:25, Tommy Petersen <[login to unmask email]<mailto:[login to unmask email]>> wrote:


We recently upgrade to DB2 11 for z/OS.

We have one program that uses multi row fetch, and after the upgrade it started getting an SQLCODE of +354.
The program looks only for SQLCODE 0 and +100, all other SQLCODE vales are considered an error.

SQLCODE +354 should indicate that one or more rows in a multi row fetch has a warning, and we need to look at the diagnostics to find out what that warning is. However, in order to quickly fix the issue, we rewrote the program to do a single row fetch, and we get no warnings, so everything is OK.

The question is, have anybody else seen similar issues?

Is it just us, because we do not check for specific warnings?

Are we the only ones using multi row fetch?

It s quite possible that we should get a warning as some of the rows use the aggregate SUM(amount) function and rows that are summed may contain null in the amount column. But is that gives a warning, should we not see it when using single row fetch as well.



Thank you for any response you may have.

If anybody find it helpful I can post the query, but I do not doubt that it sums rows where the amount may be null. We are just surprised by the sudden issue, and that IBM apparently have not heard about it before.







-----End Original Message-----

-----End Original Message-----

Tommy Petersen

RE: SQLCODE +354 In Multi Row Fetch
(in response to Suresh Sane)

Thank you, that explanation makes sense.

I have already asked the programmer to add code to test and display the SQLCODE and STATE for each row.

 

Tommy Petersen

RE: SQLCODE +354 In Multi Row Fetch
(in response to Roy Boxwell)

Hi Roy,

Unfortunately the query has an outer join and the column to be summarized is in the outer table, there will always be a valid amount in the column, so I guess that when there are no rows returned for the table the sum issues the warning.