Reg INSERT and UPDATE in DB2

srini_igsi · New User Joined: 09 Dec 2005 Posts: 30 Location: Pune

Hi,

We are facing the following problem after UPDATE a column in DB2.

By default some column's of the table always contain data.
First the program issues a SELECT in the pgm, after this we have a Null Indicator check for the column's we select, if nulls are present, we will INSERT a row into the table. Here my doubt is, after the first SELECT all the Null Indicator varibles will be set to '-1' as applicable, so after moving the data into all the Host Variables and before INSERT, should we also set the Indicator Variables to '0' to avoid inserting the nulls(i think, yes because for the first SELECT all the applicable Indicator variables are set to '-1'). After this again the program issues an UPDATE, but the column's(two) are not getting updated.
As these columns are declared as VARCHAR, we are getting the length of zero for these columns.
So what cud be the reason..??

tx in advance

KS · New User Joined: 28 Feb 2006 Posts: 91 Location: Chennai

You need not set the null indicator values to 0 explicitly. Once you populate the values it shouldn't be null anymore. While not updating, is it giving an error or warning?? can I get the exact error message and syntax?? When columns are null, u only have to update I guess.

Thanks,
KS

martin9 · Posted: Mon Mar 20, 2006 5:03 pm

hy srini_igsi,

note: your need indicator variables for NULL-able fields
and for VARCHAR fields, which are never NULL.
VARCHAR with a length of ZERO are like NULL implicitly,
but in DB2 terms there is a difference.
therefore for NULL-able fields you get -1 for NULL in the field
and 0 for not NULL in the field.
for VARCHAR you get always the existing length as a value,
if it is 0 then means the length is 0, what has for you in the
program the same effect (empty). but the indicator value is treated
different...

martin9

srini_igsi · New User Joined: 09 Dec 2005 Posts: 30 Location: Pune

Thx Martin

Do i need to reset all the Null Indicator Variables (Not VARCHAR) to zero before the pgm issues the INSERT or UPDATE after the first SELECT..?? This is because at the time of first SELECT all the applicable Null Indicator Variables set to '-1'.

guptae · Posted: Wed Mar 22, 2006 10:17 am

No You need not to be reset Null Indicator Variables (Not VARCHAR) to zero before the pgm issues the INSERT or UPDATE
It will be set auto matically once u insert some value to the variable

Hope it helps