When the column is NULL

OS/390 DB2 7.2 or 8

You put a negative 1 (-1) in the indicator variable. It does not matter what goes in the host variable if the indicator variable is -1.

i though the null indicator was a '?'

is -1 a unix installation thing?

OK, so I know you interogate the null indicator, but what is in the host variable? And it does matter if Developer doesn't know what they are doing

I thought it was low values HEX('00')

Someone else is saying what ever is in the register at the time

Anyone?

Also this is COBOL we are talking about

SELECT COL1 INTO :HOST_VAR:IND_VAR FROM table-name WHERE COL1 = :KEY_VAR;

Above is a Select statement that would be used in a COBOL program. Becasue COL1 is nullable, the INTO clause reference two variables in WORKING-STORAGE, the first being where DB2 will put the value of COL1 and the second being the indicator variable that DB2 will set. The two variables can be named whatever you want in WORKING STORAGE, but the indicator variable should be a half work binary which is COMP S9(4)..

If the indicator variable (in this case IND_VAR) in WORKING STORAGE is a -1, then the column is null.

Same applies to updates of the column (the program sets the indicator variable to tell DB2 whether it is null). A zero means not null, and -1 is null.

great, got it it, understand, so what's in the host variable?

Label me an idiot

OK, you are an idiot. But I made some mistakes in the above post since COBOL variables cannot have underscores, and I have changed a few other things in the query.

In the select statement:
SELECT COL2 INTO :HOST-VAR:IND-VAR FROM table-name WHERE COL1 = :KEY-VAR
assume that COL2 is defined as CHAR(4) in DB2, and COL1 is the Primary Key and is INTEGER in DB2.

You would need 3 variables in WORKING STORAGE;

01 HOST-VAR PIC X(4).
01 IND-VAR PIC S9(4) COMP.
01 KEY-VAR PIC S9(9) COMP.

MOVE 456 to KEY-VAR.

EXEC SQL.
SELECT COL2 INTO :HOST-VAR:IND-VAR FROM table-name WHERE COL1 = :KEY-VAR
END-EXEC.

The above statement has retrieved a row with Primary Key = 456 and put the value for COL2 in the COBOL variable HOST-VAR. To determine if the value of HOST-VAR is null, test IND-VAR for -1.

Guys, here we go.

CREATE TABLE VINAYTM.REXXTEST
( USERID CHAR(8) NOT NULL WITH DEFAULT,
ROW_NO INTEGER NOT NULL,
NAME CHAR(20));

Inserted 3 rows

SELECT * FROM REXXTEST
(here my userid is VINAYTM, so no need of table owner)

ROW
USERID NO NAME
-------- ----------- --------------------
1 -
vinay1 2 first
vinay2 3 -


** END ***

I wrote one simple db2 PL1 program for you.

TESTPGM:PROC OPTIONS(MAIN);
DCL IND FIXED BIN(15) INIT(0); -- for NULL INDICATOR
DCL MYNAME CHAR(20) INIT('VINAY KUMAR');
DCL ROWNO FIXED BIN(31) INIT(0); --> didnt use this one
EXEC SQL INCLUDE SQLCA;
PUT SKIP LIST ('BEFORE, NAME = '||MYNAME);
PUT SKIP LIST ('BEFORE, INDICATOR = '||IND);
EXEC SQL
SELECT NAME INTO :MYNAME:IND FROM VINAYTM.REXXTEST
WHERE ROW_NO = 1;

IF SQLCA.SQLCODE = 0 THEN
DO;
PUT SKIP LIST ('AFTER , NAME = '||MYNAME);
PUT SKIP LIST ('AFTER , INDICATOR = '||IND);
END;
ELSE
DO;
PUT SKIP LIST ('FAILED');
PUT SKIP LIST ('SQLCODE IS '||SQLCA.SQLCODE);
END;

END TESTPGM;

Here is the execution results:

BEFORE, NAME = VINAY KUMAR
BEFORE, INDICATOR = 0
AFTER , NAME = VINAY KUMAR
AFTER , INDICATOR = -1
***
So the conclusion part is, If DB2 encounters a null vaue then it doest touch the HOST variable, it just sets the value of NULL INDICATOR variable. Here we can see indicator variable is changed from 0 to -1 but the name is untouched.

So the host variable would still contain the value it was having earlier to this statement execution. I realise this concept from one of the manuals but wanted to confirm it today. By the way you are not an Idiot -) cheers

Result set is not displayes correctly. it would look like
1 -
vinay1 2 first
vinay2 3 -

my gosh. still the same

<blank> 1 -
vinay1 2 first
vinay2 3 -

So the answer is it does nothing, and if the host variable is referenced, it was whatever was there before?

You have a good chance that this is the behavior. However, you cannot and must not rely on it. Only the null indicator is useful. Treat the actual host variable as being not initialized.