Coping with Nulls in COBOL

1. 5/01/2002 Staff Development Session 2002.11 1 Coping with Nulls in COBOL Pam Odden

2. 5/01/2002 Staff Development Session 2002.11 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. 5/01/2002 Staff Development Session 2002.11 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 don�t 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 can�t accept zeros or spaces, and have to use a �default date� such as 12/31/9999. A database makes an attempt to model a real-world situation. Some data exists, but we don�t know what it is, for example, an employee�s birthdate. Everyone has one, but we may not know what it is. Some doesn�t exist, for example a license date for an employee who doesn�t yet have a license. Older data processing systems used spaces, or occasionally zeros, to deal with this. We often still do, for character and numeric data. For example, if a student�s middle name is unknown or he doesn�t have one, we use spaces. If a student�s cum gpa is unknown, we use zeros. (Sometimes this is not really fair or accurate, ie the student who has straight A�s in another school district). I have seen systems which use the concept of a �default date� like 12/31/9999 or 01/01/1900. There is nothing wrong with this - it�s part of the design decision. A database makes an attempt to model a real-world situation. Some data exists, but we don�t know what it is, for example, an employee�s birthdate. Everyone has one, but we may not know what it is. Some doesn�t exist, for example a license date for an employee who doesn�t yet have a license. Older data processing systems used spaces, or occasionally zeros, to deal with this. We often still do, for character and numeric data. For example, if a student�s middle name is unknown or he doesn�t have one, we use spaces. If a student�s cum gpa is unknown, we use zeros. (Sometimes this is not really fair or accurate, ie the student who has straight A�s in another school district). I have seen systems which use the concept of a �default date� like 12/31/9999 or 01/01/1900. There is nothing wrong with this - it�s part of the design decision.

4. 5/01/2002 Staff Development Session 2002.11 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. Sounds complicated, but the logic usually makes sense. For example, if you want all the substitutes who�s license expires before a certain date, if you said where exp date < some date, you would get the ones who HAVE dates that are less than the target. You would not get the ones whose exp dates are null. Your query doesn�t apply to them anyway. This can produce some unexpected results. For example, say you ran two queries, One to list all substitute teachers whose hire date was less than their license date, and one to list all substitutes whose hire date was greater than or equal their license date. You might expect that all substitute teachers would appear on one of the two lists, right? Wrong. Those with null license dates would not appear on either. Sounds complicated, but the logic usually makes sense. For example, if you want all the substitutes who�s license expires before a certain date, if you said where exp date < some date, you would get the ones who HAVE dates that are less than the target. You would not get the ones whose exp dates are null. Your query doesn�t apply to them anyway. This can produce some unexpected results. For example, say you ran two queries, One to list all substitute teachers whose hire date was less than their license date, and one to list all substitutes whose hire date was greater than or equal their license date. You might expect that all substitute teachers would appear on one of the two lists, right? Wrong. Those with null license dates would not appear on either.

5. 5/01/2002 Staff Development Session 2002.11 5 Nulls in Compound Search Conditions Truth Table for comparison tests using AND: True and Null � substitutes currently assigned whose license exp dates are < some date. True OR null � substitutes with license flag = N OR exp date < some date. True and Null � substitutes currently assigned whose license exp dates are < some date. True OR null � substitutes with license flag = N OR exp date < some date.

6. 5/01/2002 Staff Development Session 2002.11 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 SOCORRO 12/27/2001 RASHID 10/02/2001 Note there is no such avoidance of the = sign when using UPDATE or INSERT. update table-name set column-name = null; Nothing ever = NULL. NULL cannot be included in an IN list of values. List if subs with no license or exp date coming soon � Where license_date IS NULL OR Exp_date < some date. Note � this syntax avoids saying something = nulls. However, when using SET to set a column to null, the syntax is Update table-name set column-name = null;Nothing ever = NULL. NULL cannot be included in an IN list of values. List if subs with no license or exp date coming soon � Where license_date IS NULL OR Exp_date < some date. Note � this syntax avoids saying something = nulls. However, when using SET to set a column to null, the syntax is Update table-name set column-name = null;

7. 5/01/2002 Staff Development Session 2002.11 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 don�t 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 don�t 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 It�s very common to use current date or timestamp value for a �create date� and �last update date�. Just don�t set the column to anything, and if it has been defines as not null with default, you will always get the current time or date.It�s very common to use current date or timestamp value for a �create date� and �last update date�. Just don�t set the column to anything, and if it has been defines as not null with default, you will always get the current time or date.

8. 5/01/2002 Staff Development Session 2002.11 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, LIC_ISSUE_DATE DATE, LIC_ISSUE_SUBDATE DATE Default is NULL (nulls allowed). Notice only date columns are allowed to be null. This is not an issue specific to date columns! Here, we only use nulls for date columns, because we primarily code in COBOL, which doesn�t use the null data type. If we were coding in other languages, we would have occasion to use nulls in other columns. Could have used it in middle name, for example, but probably wouldn�t. Might use it for BILINGUAL LANGUAGE 1 � 4. Here, someone, probably Aaron, decided not to use nulls for columns other than dates � more hassle than it is worth. When I coded in Visual Age for Smalltalk, we left everything null that wasn�t required.Default is NULL (nulls allowed). Notice only date columns are allowed to be null. This is not an issue specific to date columns! Here, we only use nulls for date columns, because we primarily code in COBOL, which doesn�t use the null data type. If we were coding in other languages, we would have occasion to use nulls in other columns. Could have used it in middle name, for example, but probably wouldn�t. Might use it for BILINGUAL LANGUAGE 1 � 4. Here, someone, probably Aaron, decided not to use nulls for columns other than dates � more hassle than it is worth. When I coded in Visual Age for Smalltalk, we left everything null that wasn�t required.

9. 5/01/2002 Staff Development Session 2002.11 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 LIC_EXPIRE_DATE DATE 4 0 Y Y LIC_ISSUE_DATE DATE 4 0 Y Y LIC_ISSUE_SUBDATE DATE 4 0 Y Y I didn�t see an example of a default date in a table definition. I saw a place one could be used, in Fixed Assets, eqp_trans table. Disposition Date for a fixed asset is defined as Not Null with Default. However, the CICS program that inserts these rows puts 01/01/0001 in the disposition date. This is not wrong! It has the benefit of showing a maintenance programmer exactly what is going on, and, if some rows do have a disposition date at entry, it�s easier to set up a host variable with the default date, and then overlay it if there is a real date, than to cope with nulls. But, it could have been accomplished using Not Null with default �01/01/0001�.I didn�t see an example of a default date in a table definition. I saw a place one could be used, in Fixed Assets, eqp_trans table. Disposition Date for a fixed asset is defined as Not Null with Default. However, the CICS program that inserts these rows puts 01/01/0001 in the disposition date. This is not wrong! It has the benefit of showing a maintenance programmer exactly what is going on, and, if some rows do have a disposition date at entry, it�s easier to set up a host variable with the default date, and then overlay it if there is a real date, than to cope with nulls. But, it could have been accomplished using Not Null with default �01/01/0001�.

10. 5/01/2002 Staff Development Session 2002.11 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 The user doesn�t see the indicator used in the db, but DB2 uses it.The user doesn�t see the indicator used in the db, but DB2 uses it.

11. 5/01/2002 Staff Development Session 2002.11 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. Names come from two different programs, app and birth are updated in one, license issue and expire dates in another. What�s one thing we notice here? Usually null indicators would only be defined for nullable columns. Here we have an indicator for app_date, which is defined as not null with default. It�s not wrong to define the indicator or check it, but it�s unnecessary here. Later we�ll see circumstances where it IS necessary to define and use a null indicator for a not null column. Names come from two different programs, app and birth are updated in one, license issue and expire dates in another. What�s one thing we notice here? Usually null indicators would only be defined for nullable columns. Here we have an indicator for app_date, which is defined as not null with default. It�s not wrong to define the indicator or check it, but it�s unnecessary here. Later we�ll see circumstances where it IS necessary to define and use a null indicator for a not null column.

12. 5/01/2002 Staff Development Session 2002.11 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. Sometimes this is coded with no space in between, sometimes on the next line.Sometimes this is coded with no space in between, sometimes on the next line.

13. 5/01/2002 Staff Development Session 2002.11 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.) Is it safe here to just say ELSE to check for the �1? Yes, because the programmer has seen in testing there is no data conversion problem. The host variable is the proper size for a date. There is no char column that might be truncated.Is it safe here to just say ELSE to check for the �1? Yes, because the programmer has seen in testing there is no data conversion problem. The host variable is the proper size for a date. There is no char column that might be truncated.

14. 5/01/2002 Staff Development Session 2002.11 14 Be Prepared for Unexpected Nulls In some cases, even when you haven�t 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 Outer join � ie Students and their test scores. A new student with no test scores would have null in these columns, even though the column is not null in the database. They don�t have any rows. You would need to define and use a null indicator. Data exception � say you are selecting a calculation using one or more columns and you run into a divide by zero situation. That would be the �2 in the null indicator and no valid value in the host variable. Value overflow � if you do a sum of a column and the host variable is too small. No row for columnar function � ie avg ( ) where something, and no rows qualify. Count returns zero, other functions return null. If you code �SELECT EMPID, NAME FROM EMP WHERE WORKDEPT = �A01� AND HIREDATE = (SELECT MAX(HIREDATE) FROM EMP WHERE WORKDEPT = �A01�)�. Hiredate is defined on the table as not NULL. However, max(hiredate) is �nullable�. This comparison causes a mismatch of data types. DB2 is comparing a column that is not nullable to one that is.Outer join � ie Students and their test scores. A new student with no test scores would have null in these columns, even though the column is not null in the database. They don�t have any rows. You would need to define and use a null indicator. Data exception � say you are selecting a calculation using one or more columns and you run into a divide by zero situation. That would be the �2 in the null indicator and no valid value in the host variable. Value overflow � if you do a sum of a column and the host variable is too small. No row for columnar function � ie avg ( ) where something, and no rows qualify. Count returns zero, other functions return null. If you code �SELECT EMPID, NAME FROM EMP WHERE WORKDEPT = �A01� AND HIREDATE = (SELECT MAX(HIREDATE) FROM EMP WHERE WORKDEPT = �A01�)�. Hiredate is defined on the table as not NULL. However, max(hiredate) is �nullable�. This comparison causes a mismatch of data types. DB2 is comparing a column that is not nullable to one that is.

15. 5/01/2002 Staff Development Session 2002.11 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 If a valid value is in the host variable, the indicator is set to zero. If not, the indicator is set to �1 and the host variable is not used. Both the host variable and the indicator are used together to set the value of the column. Here the indicator is on the next line after the host variable, without a comma. What about updttimestamp? If we look in our table columns report, it is not null with default. What would happen if we comment out this line of code?Both the host variable and the indicator are used together to set the value of the column. Here the indicator is on the next line after the host variable, without a comma. What about updttimestamp? If we look in our table columns report, it is not null with default. What would happen if we comment out this line of code?

16. 5/01/2002 Staff Development Session 2002.11 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 GO TO 00610-EXIT. MOVE WS-DTEE-DD TO WS-S-DD. MOVE WS-DTEE-MM TO WS-S-MM. MOVE WS-DTEE-YY TO WS-S-YEAR. MOVE WS-WORK-DATE TO VALID-DB2-DATE. EXEC SQL SELECT DATE(:VALID-DB2-DATE) INTO :VALID-DB2-DATE FROM M7535DB1.DUMMY_TABLE END-EXEC. IF SQLCODE NOT = 0 MOVE WS-N TO WS-DATE-SW. 00610-EXIT. EXIT.

17. 5/01/2002 Staff Development Session 2002.11 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.