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

DB2 Cobol -- Null out work area


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

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 2:08 am
Reply with quote

I wasn't sure where to post this, but I will start here I guess...

I am trying to NULL out the whole area that I will be using to hold the data that will be inserted into a table...

I know there is move high-values/spaces/low-values/zeroes. But is there anything that I can use to let cobol know to NULL an area out? I will then populate just needed fields, and then insert field-by-field?

Thanks,
Nirav
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 2:31 am
Reply with quote

Hello,

I believe you will need to work field by field.

NULL is a concept rather than an actual value (it is the absense of any value).
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sun Feb 24, 2008 2:34 am
Reply with quote

Nirav721 wrote:
I wasn't sure where to post this, but I will start here I guess...

I am trying to NULL out the whole area that I will be using to hold the data that will be inserted into a table...

I know there is move high-values/spaces/low-values/zeroes. But is there anything that I can use to let cobol know to NULL an area out? I will then populate just needed fields, and then insert field-by-field?

Thanks,
Nirav


For DB2 you set the null indicators to -1, there is no such thing as a null value.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 3:02 am
Reply with quote

I see. So, lets say if a field is high values and I want to let DB2 know to treat it as NULL (this field could be a char or decimal comp-3 or date), how exactly can I do this?

psuedo:

If Field is not high-values
then move field to host variable
else move <????> to host variable


Not sure what to put into <????> ?

Thanks for all the help, appreciate it...
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sun Feb 24, 2008 3:07 am
Reply with quote

Nirav721 wrote:
I see. So, lets say if a field is high values and I want to let DB2 know to treat it as NULL (this field could be a char or decimal comp-3 or date), how exactly can I do this?

psuedo:

If Field is not high-values
then move field to host variable
else move <????> to host variable


Not sure what to put into <????> ?

Thanks for all the help, appreciate it...


The only DB2 fields you can set to null have a null indicator. If the field is not nullable you either provide a value or let it default to the default value.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 3:10 am
Reply with quote

Thanks...If we assume that a field is nullable, how can I set the fields to null? Sorry, I am a bit new to this whole thing...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 3:29 am
Reply with quote

Hello,

For each NULLable field, there will also be a NULL indicator. To tell DB2 the field is NULL, set the null indicator to -1 (as Craig mentioned).
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 3:31 am
Reply with quote

got it..Thanks. There is no quick way to move -1 to all of the indicators..is there? My tables will have all fields nullable except for the keys...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 3:40 am
Reply with quote

Hello,

Quote:
There is no quick way to move -1 to all of the indicators..is there?
Probably not, but i don't believe you would want to anyway. . .

I'd be quite suspicious of a row that had all null values. . .
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 3:48 am
Reply with quote

Thanks for the suggestion. this helps out a lot...

The reason why I would like to set all of the indicators to NULL is because we are doing full 'row' replacement each time data gets changed. We will have 200+ fields for each tables (60+ tables) and it is kind of processing headache if we go out to search for which field has been changed etc...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 4:02 am
Reply with quote

Hello,

If you are doing "full row replacement", why do you want to set all of the null indicators? I would expect that most of the existing or replacement data would not be null. I also do not understand why a changed/unchanged decision need be made for each field or how that would relate to NULL.

Null would not be used to indicate the value was unchanged.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 4:18 am
Reply with quote

I think I am thoroughly confused with this.

I had this in mind...

I would check if the field to be loaded into db2 was high-values, if it is, I would set the null indicator to -1. And this way, DB2 would null out the field, and not load it in...

This brings me to a different question..how do I use indicators when I am actually performing insert/update ? Is there a specific syntax?


I really appreciate the suggestions, but I am just little lost...
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 4:37 am
Reply with quote

Hello,

Quote:
but I am just little lost...
We'll work on that icon_smile.gif

As i continued with the topic, i realized how you wanted to use the "null indicator". This (unfortunately for your requirement) is not how NULL works.

NULL is a database concept to express the non-existance of a value. It is not for determining if some value is/was changed.

If your input data contain all of the data for an entire row, i believe you will be able to simply update existing rows with the new values and not need to include NULL code.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 4:41 am
Reply with quote

Thanks for putting up with this!

My dilemma is, my input record coming in would contain high values, and from my understanding, if high values are passed into DB2 for fields that can not handle them, this would cause problems..

So, I was trying to go field by field, checking if the field is high values, then turn null indicator on, and then use this null indicator when I am doing insert/update to let DB2 know to 'skip' the field...


Am I making little more sense now?
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Sun Feb 24, 2008 4:51 am
Reply with quote

If a column is to be nullable, it must be defined as nullable.
If you read a nullable column, you must provide the null indicator in addition to the host variable for the contents of that column. If it is currently null, there will be a -1 in the null indicator.
If you write a nullable column, you must set the null indicator to indicate if the host variable is to be used or not, i.e., null or actual data.
IIRC, high values and low values are valid data for a character column in a DB2 column. Null is not a value, it is the absence of any value......
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 4:56 am
Reply with quote

CICS Guy,

I am fine with everything you stated, and I have a question about this...


Lets say I have read a nullable column, and my indicator, lets call it, INDI01 has just got a 0 indicating there is data, so I move the data from that field into my workarea...

In processing the value of this field changed to high-values and I change INDI01 to -1.

Now, when I do my write back to DB2, how can I 'communicate' or use INDI01 in my update clause?


Thanks!!
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sun Feb 24, 2008 4:59 am
Reply with quote

DB2 can handle high values for char and integer columns, it can not handle it for decimal columns.

Code:

01  ws-field1       pic x(10).
01  ws-field2       pic s9(5)v9(4) comp-3.
01  ws-field3       pic x(10).
01  ws-field1-ind pic s(4) comp value 0.
01  ws-field2-ind pic s(4) comp value 0.
01  ws-field3-ind pic s(4) comp value 0.
 

EXEC SQL
INSERT (FIELD1, FIELD2, FIELD3)
VALUES (:WS-FIELD1 :WS-FIELD1-IND,
 :WS-FIELD2, :WS-FIELD2-IND,
 :WS-FIELD3 : WS-FIELD3-IND) INTO SOMETABLE
END-EXEC.


If you set the ind variable to -1 for any nullable column the value for that column is ignored.

If you have columns that are not nullable you must provide a valid value or leave the column out of the insert and allow it to take on the default value.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 5:04 am
Reply with quote

Thanks all, I think that clears it up for me for now.

I will go through each field, check if it is high value, if it is, set null indicator of that column to be -1, and then include the indicator inside my insert/update clause..(I am assuming I can use it in update the same way as insert?)

Thanks for all the help..sorry for dumb questions...
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Sun Feb 24, 2008 5:57 am
Reply with quote

Quote:
I am assuming I can use it in update the same way as insert?)
Yes....
IIRC, the host variable and the null indicator are 'sort of' a pair needed for nullable fields....
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sun Feb 24, 2008 6:15 am
Reply with quote

If you set the null indicator to -1 and use it in an update you may change the value of the column from something to NULL!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sun Feb 24, 2008 7:16 am
Reply with quote

I seem to remember this guy - Shakespeare - who summed it up. . .

"Much ado about nothing. . .". Oh, yeah, different nothing. icon_wink.gif

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

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Sun Feb 24, 2008 8:52 am
Reply with quote

Hahah...thanks all for the suggestions. I will definetly keep in mind the importance of setting the null value to -1 and getting rid of something that might already be in....
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 Replace each space in cobol string wi... COBOL Programming 3
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts isfline didnt work in rexx at z/OS ve... CLIST & REXX 7
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
Search our Forums:

Back to Top