Coping with Nulls in COBOL

1
Coping with Nulls in COBOL

• Pam Odden

2
Objectives

• Understand the concept of null values
• Learn how nulls are used in comparisons
• Learn how null columns are described in DB2
• Learn how to select, insert, and update nullable
  columns in COBOL programs

3
Using Nulls for Missing Data

• Because a database attempts to model a real-world
  situation, certain pieces of data may be missing,
  unknown, or dont apply.
• SQL supports missing or inapplicable data through
  the concept of a null value.
• A null value has an indicator that tells SQL (and
  the user) that the data is missing.
• Older data processing systems used spaces or
  zeros for data that is missing, and we still
  often do this for character and numeric data
  today.
• This is more awkward for DATE and TIME data
  types, which cant accept zeros or spaces, and
  have to use a default date such as 12/31/9999.

4
Nulls in Comparison Tests

• One of the most common SQL search conditions is
  the comparison test (,,,)
• We normally think of a comparison test as
  returning one of two possible values True or
  False. Not so when null values are involved!
• When SQL compares the values of two expressions
  in a comparison test
• If the comparison is true, the test yields a TRUE
  result
• If the comparison is false, the test yields a
  FALSE result
• If one or both of the expressions produces a null
  value, the test yields a NULL result
• Only rows where the search condition yields a
  TRUE result are included in query results.

5
Nulls in Compound Search Conditions

• Truth Table for comparison tests using AND

• Truth Table for comparison tests using OR

6
Null Value Test

• Sometimes it is useful to test for a null value
  directly. This can be done using the IS NULL
  test.
• select first_name, lic_issue_date from
  mhrmsdb1.sub_applicants
• where lic_issue_date is null
• ----------------------------------
• FIRST_NAME LIC_ISSUE_DATE
• ----------------------------------
• WILLIAM --------------
• KARRIE --------------
• SAL --------------
• MICHELE --------------
• The negative form (IS NOT NULL) selects values
  that are not null
• select first_name, lic_issue_date from
  mhrmsdb1.sub_applicants
• where lic_issue_date is not null
• ------------------------------------
• FIRST_NAME LIC_ISSUE_DATE
• ------------------------------------
• LORRIE 12/29/2000

7
Nulls in the Database

• For each column defined in a table, several
  decisions need to be made
• Column name
• Data type character, integer, date, etc.
• Length, precision, scale
• Whether to allow nulls
• There are 3 choices for handling nulls in a
  column
• NULL nulls are allowed, and if you dont
  explicitly set the value, null is the default
• NOT NULL nulls are never allowed in the column
• NOT NULL WITH DEFAULT nulls are never allowed,
  and if you dont explicitly set the value, it
  defaults to the value named when the column was
  created for the table.
• There are 2 choices for a default
• Use the DB2 default spaces for char columns,
  zeros for numeric columns, current date/time for
  date columns, string of length zero for varchar
  columns
• Explicitly name a default value when the column
  is created for the table

8
Examples of Nulls in our Databases

• Copybook for sub_applicants table
• EXEC SQL DECLARE MHRMSDB1.SUB_APPLICANTS TABLE
  
• ( SSN CHAR(9) NOT
  NULL,
• APP_DATE DATE NOT NULL,
  
• APP_GROUP CHAR(1) NOT
  NULL,
• APP_CAT CHAR(1) NOT
  NULL,
• INACTIVE_DATE DATE,
  
• LAST_NAME CHAR(17) NOT
  NULL,
• FIRST_NAME CHAR(12) NOT
  NULL,
• MIDDLE_NAME CHAR(10) NOT
  NULL,
• DATE_CREATED DATE NOT NULL,
• HIRE_DATE DATE,
• LIC_EXPIRE_SUBDATE DATE,
• LIC_EXPIRE_DATE DATE,

9
Examples of Nulls in our Databases

• Table Columns Report for sub_applicants table
• N D
• LEN U E
  DEFAULT
• COLUMN NAME COLTYPE GTH SCALE L F
  VALUE
• --------------------------------------------------
  --------
• SSN CHAR 9 0 N N
  
• APP_DATE DATE 4 0 N Y
  
• APP_GROUP CHAR 1 0 N Y
  
• APP_CAT CHAR 1 0 N Y
  
• INACTIVE_DATE DATE 4 0 Y Y
  
• LAST_NAME CHAR 17 0 N Y
  
• FIRST_NAME CHAR 12 0 N Y
• MIDDLE_NAME CHAR 12 0 N Y
• DATE_CREATED DATE 4 0 N Y
• HIRE_DATE DATE 4 0 Y Y
• LIC_EXPIRE_SUBDATE DATE 4 0 Y Y
  

10
The Null Indicator

• For each column that allows nulls, DB2 allocates
  one extra byte of storage for an indicator.
• In programming languages that do not use nulls,
  like COBOL or C, a host indicator variable is
  used together with a host variable to indicate
  when a column is null on retrieval, or when a
  column is to be set to null on update.
• In DB2
• A negative number indicates the column is null,
  usually -1
• A 2 indicates a data conversion error
• A positive number or zero indicates the column is
  not null
• If a column is defined as a CHAR data type, and
  if the value has been truncated on retrieval
  because the host variable is not large enough,
  the indicator variable contains the original
  length of the truncated column

11
Defining Null Indicators

• 01 WS-NULL-INDICATORS.
  
• 05 IND-NULL PIC S9(4) COMP
  VALUE -1.
• 05 IND-VALUE PIC S9(4) COMP
  VALUE 0.
• 
  
• 05 IND-APP-DATE PIC S9(4) COMP.
  
• 05 IND-BIRTH-DATE PIC S9(4) COMP.
  
• 05 IND-TCHIS-DATE PIC S9(4) COMP.
  
• 05 IND-TCHEX-DATE PIC S9(4) COMP.
  
• 05 IND-SUBIS-DATE PIC S9(4) COMP.
  
• 05 IND-SUBEX-DATE PIC S9(4) COMP.
  
• This program has the two constants, 1 and 0,
  clearly defined in working storage with
  descriptive names IND-NULL and IND-VALUE.
• Indicators are defined in COBOL as PIC S9(04)
  COMP.
• If a null value is retrieved and no null
  indicator is used, a 305 SQLCODE is returned.

12
Selecting with Null Indicators

• EXEC SQL
• SELECT SSN,
• LAST_NAME,
• FIRST_NAME,
• LIC_EXPIRE_SUBDATE,
• LIC_EXPIRE_DATE,
• LIC_ISSUE_DATE,
• LIC_ISSUE_SUBDATE
• INTO SUB-SSN,
• SUB-LAST-NAME,
• SUB-FIRST-NAME,
• SUB-LIC-EXPIRE-SUBDATE
  IND-SUBEX-DATE,
• SUB-LIC-EXPIRE-DATE IND-TCHEX-DATE,
• SUB-LIC-ISSUE-DATE IND-TCHIS-DATE,
• SUB-LIC-ISSUE-SUBDATE IND-SUBIS-DATE
  
• FROM MHRMSDB1.SUB_APPLICANTS
• The indicator appears immediately after its
  corresponding host variable, with no comma in
  between.

13
Checking the Null Indicator on Select

• IF IND-TCHIS-DATE IND-VALUE
• MOVE SUB-LIC-ISSUE-DATE TO WS-DB2-DATE
• MOVE WS-D-MM TO HA2TLIMMO
• MOVE WS-D-DD TO HA2TLIDDO
• MOVE WS-D-YY TO HA2TLIYYO
• ELSE
• MOVE ALL '_' TO HA2TLIMMO
• HA2TLIDDO
• HA2TLIYYO.
• If the indicator 0 (IND-VALUE), a valid date
  has been returned into the host variable
  (SUB-LIC-ISSUE-DATE).
• If the indicator -1 (ELSE in the code above),
  the date was null in the database, and the value
  in the host variable is not used.
• (Beware! Checking for a valid date in the host
  variable is not sufficient to detect a null! In
  CICS, the host variable may be initialized, but
  in a batch program processing a cursor, the value
  in the host variable is likely to be the last
  non-null value retrieved.)

14
Be Prepared for Unexpected Nulls

• In some cases, even when you havent defined the
  column to be NULL, a SQL query might return a
  NULL. Some reasons for unexpected nulls in query
  results include
• A missing row in an outer join
• A data exception
• Value overflow
• No row found for a selected columnar function
• A subselect of a columnar function when no row is
  found

15
Setting the Null Indicator for Insert or Update

• IF HA2TLIMMI NOT ALL '_'
• MOVE HA2TLIMMI TO WS-D-MM
• MOVE HA2TLIDDI TO WS-D-DD
• MOVE HA2TLIYYI TO WS-D-YY
• MOVE WS-DB2-DATE TO SUB-LIC-ISSUE-DATE
• MOVE IND-VALUE TO IND-TCHIS-DATE
• ELSE
• MOVE IND-NULL TO IND-TCHIS-DATE.
• EXEC SQL
  
• UPDATE MHRMSDB1.SUB_APPLICANTS
  
• SET ST_AT_SCHOOL_NO
  SUB-ST-AT-SCHOOL-NO,
• ST_AT_SCHOOL
  SUB-ST-AT-SCHOOL,
• LIC_EXPIRE_DATE
  SUB-LIC-EXPIRE-DATE
• IND-TCHEX-DATE,
  
• LIC_ISSUE_DATE
  SUB-LIC-ISSUE-DATE
• IND-TCHIS-DATE,
  
• UPDTTIMESTAMP CURRENT TIMESTAMP
  

16
Nice Date Validation Routine

• 01 WS-DATE-EDIT.
• 03 WS-DTEE-YY PIC X(4).
• 03 WS-DTEE-MM PIC X(2).
• 03 WS-DTEE-DD PIC X(2).
• 01 WS-WORK-DATE.
  
• 03 WS-S-MM PIC X(2).
  
• 03 F PIC X(1) VALUE
  '/'.
• 03 WS-S-DD PIC X(2).
  
• 03 F PIC X(1) VALUE
  '/'.
• 03 WS-S-YEAR PIC X(4).
  
• 01 VALID-DB2-DATE PIC X(10) VALUE
  SPACES.
• 00610-VALIDATE-DATE.
• MOVE WS-Y TO WS-DATE-SW.
• IF WS-DATE-EDIT NUMERIC AND
• WS-DTEE-YY '1900'
• NEXT SENTENCE
• ELSE
• MOVE WS-N TO WS-DATE-SW

17
Summary

• The concept of a null value is used in DB2 to
  represent data that is unknown or not applicable
• Nulls cause a third possibility for comparisons
  true, false, or null
• At creation, columns must be defined as null, not
  null, or not null with default
• Null indicators are used to convey information
  about null columns to programming languages
  without the concept of null
• Be alert for the possibility of null being
  returned from a query, even when the columns you
  are working with are not null.