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
 

 

Issue with NULL records

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

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Nov 03, 2010 6:56 pm    Post subject: Issue with NULL records
Reply with quote

Hi all,

I am using a cursor like one is given below:

EXEC SQL
DECLARE CURSOR C1 FOR
SELECT COL1, COL2, COL3, COL4
FROM TAB FOR FETCH ONLY
END-EXEC.

COL1, COL2 & COL4 are defined not null whereas COL3 can havee null values. I am fetching this cursor through normal process. However I am not using null indicators or any other null handling.

While fetching I was getting -305 because of this. So I handled through hardcoding my code simuilar as +0 sqlcode. However I started facing a new issue.

Whenever COL3 has valid (non-null) values, everything is fine. However when we have COL3 as null, there is nulls in respective host-var3 which is understandable. However in such cases, host-var4 (which contains COL4 value) is also blank. I could not understand why this is affecting host variable related to COL4.

Please suggest. I hope I presented my case clearly.

Regards,
Back to top
View user's profile Send private message

Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 964
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 7:10 pm    Post subject:
Reply with quote

It's been a while, but I do not understand how host_var3 can be null - COBOL variables do not have null values.

Correct me if wrong, but isn't it your host indicator variable 3 that must have a value indicating that the corresponding host var does not have a value because its corresponding db2 column is null?

Let's make sure the question is clearly posed before analyzing the situation.

Please show the syntax where you populate the host vars.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Nov 03, 2010 7:28 pm    Post subject:
Reply with quote

1. use null indicators.

2. during population of host variables,
when there is no null-indicator for a column,
and that column is indeed null,
population of host variables stops and -305 is returned-
therefore no population of col4. (or col3)

the manual clearly states
the population of a host variable during a null condition is not accomplished.
any value contained in the host variable is not to be considered valid.

you are making an assumption that the host variable is populated when the column is null,

and you refusal to use the null-indicators is unprofessional and you have the additional problem of col4 not being populated.

you could change the select statement to be
SELECT COL1, COL2, COL4, COL3
which may provide you a solution.
since I don't work for the same company that employs you,
i really don't care what stupidity you code into the software.

Phrzby Phil,

as far as the population of col3 with 'null',
it is obvious that the TS is a very low skilled coder
and to expect that he would use proper terminology is a waste of time.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 9:49 pm    Post subject:
Reply with quote

reading the manual for functions Value(),coalesce() or nullif() is probably also expecting too much?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Nov 03, 2010 10:16 pm    Post subject:
Reply with quote

Anyone who uses the values resulting from a fetch when there is SQL error gets what he deserves.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 03, 2010 11:29 pm    Post subject: Reply to: Issue with NULL records
Reply with quote

Hi Craig,

But gee, the query runs so much faster when it fails / has no row(s) returned. . . icon_cool.gif

d
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 964
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 11:33 pm    Post subject:
Reply with quote

Anyone seen sharad_shanu lately?

I will, however, give the benefit of the doubt as regards coalesce, which from the name does not at all say what it does. Even diligently looking down a list of function names, a beginner would never know that coalesce would be useful.

/PET PEEVE
The idiotically named intnx. Couldn't SAS have put the word "date" somewhere in this function name?
/OFF PEEVE
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 964
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 11:35 pm    Post subject:
Reply with quote

Also, it's really not officially called "null indicator" I think, as it does have values indicating a few other (granted, less likely) odd situations.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8121
Location: East Dubuque, Illinois, USA

PostPosted: Wed Nov 03, 2010 11:35 pm    Post subject:
Reply with quote

Dick -- the old efficiency versus effectiveness debate pops up again! icon_biggrin.gif
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am


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