IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQLCODE +354 while doing a multifetch


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Wed Feb 29, 2012 8:13 pm
Reply with quote

Hi,

Am getting a SQLCODE +354 while doing a Multifetch from the ROWSET. However if i run the same SQL in SPUFI its going fine. I think the problem is with the OUTER JOIN which am using due to which some of the column values may not have data. To find this out i need to know how use GET diagnostics information. Can you please help me with this ?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Feb 29, 2012 9:16 pm
Reply with quote

Did you use null indicator??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 01, 2012 3:45 am
Reply with quote

get diagnostics
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 01, 2012 4:05 am
Reply with quote

Here is another example
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Thu Mar 01, 2012 9:45 am
Reply with quote

My another problem is that am preparing this SQL cursor dynamically. SO in this case can we use GET DIAGNOSTICS
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Thu Mar 01, 2012 9:47 am
Reply with quote

premkrishnan wrote:
Did you use null indicator??


Null indicator can be used only if the columns being retrieved can contain NULL. If they are defined as NOT NULL WITH DEFAULT then can we use Null indicator.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 01, 2012 9:56 am
Reply with quote

mushreyas wrote:
My another problem is that am preparing this SQL cursor dynamically. SO in this case can we use GET DIAGNOSTICS

and

premkrishnan wrote:
Did you use null indicator??
Null indicator can be used only if the columns being retrieved can contain NULL. If they are defined as NOT NULL WITH DEFAULT then can we use Null indicator.


mushrevas,

yes, the fact that the cursor is dynamic,
does not mean that you can not use imbedded get diagnostics.

null inidcators?
you can always code null indicators....always, always, always.
column defintion has nothing to do with it.
Back to top
View user's profile Send private message
mushreyas

New User


Joined: 18 Jul 2008
Posts: 59
Location: Bangalore

PostPosted: Thu Mar 01, 2012 9:22 pm
Reply with quote

Hi Dick,

All i know is that GET diagnostics is to be used to identify which row and what column has caused the error. However in my case i know the cause of error. Let me simplify this with the help of a simple SQL
Code:

SELECT A.EMP_NAM, B.EMP_ADDR1
FROM EMPLOYEE A
LEFT OUTER JOIN EMPLOYEE_ADDRESS B
ON A.EMP_ID=B.EMP_ID


The above query may or may not retrieve the 2nd column value and if i run the above query in SPUFI wherein no entry exists in table EMPLOYEE_ADDRESS for a given EMP_ID the 2nd column value will be shown as "------". But when i run the same query with a multifetch in embedded appln i get the SQLCODE +354. In case when entry is present in both the tables the SQLCODE will be ZERO.

How can i resolve this problem? Can using the null indicator provides the solution even though the above columns are declared as NOT NULL WITH DEFAULT.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Mar 01, 2012 9:35 pm
Reply with quote

I googled this link might help for basic concepts with simpler examples

Good read
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Thu Mar 01, 2012 10:21 pm
Reply with quote

mushreyas wrote:
Can using the null indicator provides the solution even though the above columns are declared as NOT NULL WITH DEFAULT.

It is not a null indicator; it is an indicator which shows among other things that the value is null.
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Thu Mar 01, 2012 10:26 pm
Reply with quote

Hi Shre,

When you have Left outer join you have to declare null indicator for those column.


Sai
Back to top
View user's profile Send private message
ChowHan

New User


Joined: 16 Oct 2009
Posts: 15
Location: India

PostPosted: Sun Mar 04, 2012 2:23 am
Reply with quote

As far as get diagnostics is concerned - I have heard (on some forums - so can not verify --- highly suspect/potentially misleading misinformation - mods please delete/edit this if not correct) - that it is pretty expensive and need not be used for fetch - but rather for inserts/updates where you can do something about the data you are trying to insert.

I got the same SQLCODE earlier this week- and it turned out there was a mismatch in what I had selected icon_sad.gif and what I had fetched into- it could probably be an issue with the query you are preparing and whether or not you are handling nulls properly. (Would COALESCE/IFNULL work in your case?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Mar 04, 2012 5:15 pm
Reply with quote

as ChowHan said, you may get away with using IFNULL/COALESE/VALUE

otherwise:

if you do not have an array of null indicators (to go along with each array for each column)
that may very well be the problem.
if you have null indicators, then you will receive an sql code of 0,
and
you will have to deal with the null indicators
to determine if the data is
complete,
truncated
or in fact a null value was return,
for each column,
of each row,

just means you need to know how to deal with parallel cobol internal tables.

you need to put in your get diagnostics,
so that you can determine the error
row by row.

also, anyone who suggests not to have the logic available
so that it can be executed when it is required (SQLCODE other than 0 or +100)
is a complete idiot.
how else are you going to determine what the error is?
it is worse than not having a CALL to DNSTIAR for singleton sql

if you don't have an sqlerror,
do not invoke the get diagnostics sql.

after thorough testing
the only time you are going to invoke the get diagnostics
is when you have sql code not equal to 0 and +100.
Back to top
View user's profile Send private message
ChowHan

New User


Joined: 16 Oct 2009
Posts: 15
Location: India

PostPosted: Sun Mar 04, 2012 6:13 pm
Reply with quote

MY bad... As I said it was most probably wrong ... wonder if I can edit it now - Just that i came across an old post someone saying that on idug (and didn't bother to follow up ) ...
Now that i think of it... it sounds silly - you would waste more cpu if you don't know where the issue is... I need to watch what I say when I am sleepy...
icon_redface.gif icon_sad.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Mar 04, 2012 7:42 pm
Reply with quote

ChowHan,

i did not mean you,
you were only repeating something that someone else said.

yes, get diagnostics eats up a lot of resources,
but as i said,
you should only invoke it when you are in an error situation,
not after a normal (SQLCODE = 0 or +100) return.

the goal is always to have your code tested and perfected
so that you only are an victim of 927's and other system related sqlcodes.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts SQLCODE=-204 SQLSTATE=42704 DB2 4
No new posts Getting sqlcode 805 while executing R... DB2 10
Search our Forums:

Back to Top