Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: What Value goes into a host vairable....

    When the column is NULL

    OS/390 DB2 7.2 or 8
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Also this is COBOL we are talking about
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    great, got it it, understand, so what's in the host variable?

    Label me an idiot
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brett Kaiser
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2006
    Posts
    82
    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.

    Originally Posted by Brett Kaiser
    great, got it it, understand, so what's in the host variable?

    Label me an idiot
    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

  9. #9
    Join Date
    May 2006
    Posts
    82
    Result set is not displayes correctly. it would look like
    1 -
    vinay1 2 first
    vinay2 3 -

  10. #10
    Join Date
    May 2006
    Posts
    82
    my gosh. still the same

    <blank> 1 -
    vinay1 2 first
    vinay2 3 -

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So the answer is it does nothing, and if the host variable is referenced, it was whatever was there before?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •