Handling SQLCODE=-305 in COBOL Db2 Program

Hiya! 👋 In this post, let's have a look at a common issue that Mainframe Developers face when writing a COBOL program that interacts with Db2. 

Introduction

When dealing with COBOL Db2 programs, encountering SQLCODE=-305 can be a common issue. This error occurs when a null value is fetched from the database into a COBOL variable. COBOL does not know what nulls are. 

A null value is a special value that Db2 interprets to mean that no data is present. Null value is an unknown value and is not zero or blank

To handle this gracefully, there are several strategies you can employ. In this post, we will explore different methods to manage null values effectively, ensuring your COBOL Db2 programs run smoothly.

Understanding SQLCODE=-305

SQLCODE=-305 indicates that a null value was encountered in a column that was not expected to be null. This often happens during fetch operations when the program tries to retrieve data from the database. Without proper handling, this can cause the program to fail.

Click here to learn more about SQLCODE=-305 from Db2 manuals. 

Some options I recommend to handle nulls in COBOL DB2 Programs

1. Using Null Indicators
2. Using the VALUE Function
3. Using the COALESCE Function

Let's see them one by one. 

1. Using Null Indicators 

Null indicators are special variables used to determine whether a database column contains a null value. When fetching data, the null indicator variable captures the null status of the corresponding column, allowing the program to handle null values appropriately.

Declaring Null Indicators

In COBOL, a null indicator is typically declared as a half word binary field:

01  ACTUAL-VARIABLE        PIC X(20).
01  NULL-INDICATOR-VAR     PIC S9(4) COMP.

ACTUAL-VARIABLE is the variable that will store the fetched data.

NULL-INDICATOR-VAR is the null indicator variable.

Fetching Data with Null Indicators

When you retrieve a column with an indicator variable, DB2 puts the appropriate value in the indicator. To refer to an indicator variable in the INTO clause of a SELECT or FETCH statement, you need to code a colon, the appropriate Db2 column's host variable name, a space (optional), a colon, and the name of the indicator variable as shown below.

EXEC SQL
    FETCH NEXT FROM cursor-name
    INTO :ACTUAL-VARIABLE :NULL-INDICATOR-VAR
END-EXEC.

Handling Null Values

After fetching a row, you need to check the null indicator variable to determine if the column contains a null value:

IF NULL-INDICATOR-VAR < 0 THEN
    MOVE SPACES TO ACTUAL-VARIABLE
ELSE
    CONTINUE
END-IF.

Following table shows the value of the indicator variable and the corresponding column’s value:

Complete Example

Here’s a complete example of how to handle SQLCODE=-305 using null indicators in a COBOL DB2 program:

WORKING-STORAGE SECTION.
01  ACTUAL-VARIABLE        PIC X(20).
01  NULL-INDICATOR-VAR     PIC S9(4) COMP.

PROCEDURE DIVISION.
    EXEC SQL
        DECLARE cursor-name CURSOR FOR
        SELECT column-name FROM table-name
    END-EXEC.

    EXEC SQL
        OPEN cursor-name
    END-EXEC.

    PERFORM UNTIL SQLCODE NOT = 0
        EXEC SQL
            FETCH NEXT FROM cursor-name
            INTO :ACTUAL-VARIABLE :NULL-INDICATOR-VAR
        END-EXEC.

        IF SQLCODE = 0 THEN
            IF NULL-INDICATOR-VAR < 0 THEN
                MOVE SPACES TO ACTUAL-VARIABLE
            ELSE
                DISPLAY ACTUAL-VARIABLE
            END-IF
        END-IF
    END-PERFORM.

    EXEC SQL
        CLOSE cursor-name
    END-EXEC.

    STOP RUN.

2. Using the VALUE Function

The VALUE function allows you to provide a default value for columns that might contain nulls, thus avoiding the need for additional null indicator checks in your COBOL code. Remember, it takes exactly two arguments.

Example with VALUE Function

Here’s how you can use the VALUE function in your SQL query:

SELECT VALUE(column-name, 'default-value')
FROM table-name

In a COBOL DB2 program, it would look like this:

EXEC SQL
    DECLARE cursor-name CURSOR FOR
    SELECT VALUE(column-name, 'default-value')
    FROM table-name
END-EXEC.

3. Using the COALESCE Function

The COALESCE function can take multiple arguments and returns the first non-null value from the list. This function is also useful for handling nulls directly within your SQL queries.

Example with COALESCE Function

Here’s how you can use the COALESCE function in your SQL query:

SELECT COALESCE(middle_name, nickname, alias, 'N/A') AS name_or_default
FROM employees

In the above SQL, If all the columns in the COALESCE argument list are null, it should return 'N/A'.

In a COBOL DB2 program, it would look like this:

EXEC SQL
    DECLARE cursor-name CURSOR FOR
    SELECT COALESCE(column-name, 'default-value')
    FROM table-name
END-EXEC.

Conclusion

Handling null values in COBOL Db2 programs can be approached in various ways, each suitable for different scenarios. Besides using null indicators, VALUE, and COALESCE functions, you can leverage IFNULL/NVL functions, CASE statements, default values in table definitions, and additional application logic.

By understanding and utilizing these techniques, you can prevent SQLCODE=-305 error. Should you have any questions/suggestions, please add them in the Comments section below. 

Thx for reading! 😀