View previous topic :: View next topic
|
Author |
Message |
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
but I am just little lost... |
We'll work on that
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
I seem to remember this guy - Shakespeare - who summed it up. . .
"Much ado about nothing. . .". Oh, yeah, different nothing.
d |
|
Back to top |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
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 |
|
|
|