15 COBOL DB2 Interview Questions and Answers – CLIMB

COBOL (Common Business-Oriented Language) remains a critical language in the financial and administrative systems of many large organizations. Paired with DB2, IBM’s database management system, COBOL continues to be a powerful tool for handling large-scale data processing and transaction management. Despite its age, COBOL’s reliability and efficiency ensure its ongoing relevance in maintaining legacy systems and supporting business operations.

This article offers a curated selection of interview questions focused on COBOL and DB2. Reviewing these questions will help you deepen your understanding of these technologies and demonstrate your proficiency to potential employers.

COBOL DB2 Interview Questions and Answers

1. What is a DB2 cursor and why is it used?

A DB2 cursor in COBOL allows you to fetch rows from a result set one at a time, which is useful for handling large volumes of data. Cursors enable row-by-row processing within a COBOL program.

Example:

EXEC SQL
    DECLARE cursor_name CURSOR FOR
    SELECT column1, column2
    FROM table_name
    WHERE condition
END-EXEC.

EXEC SQL
    OPEN cursor_name
END-EXEC.

PERFORM UNTIL SQLCODE <> 0
    EXEC SQL
        FETCH cursor_name INTO :host_variable1, :host_variable2
    END-EXEC.

    IF SQLCODE = 0
        * Process the fetched row
    END-IF
END-PERFORM.

EXEC SQL
    CLOSE cursor_name
END-EXEC.

2. How do you handle SQLCODE -911 in a COBOL-DB2 program?

SQLCODE -911 indicates a deadlock or timeout. To handle it, implement a retry mechanism where the program waits briefly before reattempting the transaction. This can resolve temporary issues.

Example:

       EXEC SQL
           WHENEVER SQLERROR CONTINUE
       END-EXEC.

       PERFORM UNTIL SQLCODE = 0 OR RETRY-COUNT > MAX-RETRIES
           EXEC SQL
               UPDATE EMPLOYEE
               SET SALARY = SALARY + 1000
               WHERE EMP_ID = 'E123'
           END-EXEC

           IF SQLCODE = -911
               ADD 1 TO RETRY-COUNT
               CALL 'CBLTDLI' USING DELAY-ROUTINE
           ELSE
               EXIT PERFORM
           END-IF
       END-PERFORM.

       IF SQLCODE NOT = 0
           DISPLAY 'Error: Unable to update salary'
       END-IF.

3. What is the purpose of the COMMIT statement?

The COMMIT statement in COBOL DB2 finalizes the current transaction, making all changes permanent and releasing database locks. This ensures data integrity and allows other transactions to access updated data.

Example:

EXEC SQL
    UPDATE EMPLOYEE
    SET SALARY = SALARY * 1.10
    WHERE DEPT = 'SALES'
END-EXEC.

EXEC SQL
    COMMIT
END-EXEC.

4. How would you optimize a SELECT query in a COBOL-DB2 program?

Optimizing a SELECT query involves strategies like creating appropriate indexes, rewriting queries for efficiency, and using DB2-specific features. Regularly updating database statistics and using parameter markers can also enhance performance.

5. Describe the steps to handle null values in a COBOL-DB2 program.

Handling null values in a COBOL-DB2 program involves using indicator variables. These variables indicate whether a column value is null, allowing the program to take appropriate action.

Example:

EXEC SQL
    SELECT column_name
    INTO :host_variable :indicator_variable
    FROM table_name
    WHERE condition
END-EXEC.

IF indicator_variable = -1
    MOVE 'Value is NULL' TO display_variable
ELSE
    MOVE host_variable TO display_variable
END-IF.

6. Explain the difference between static and dynamic SQL.

Static SQL is embedded in the COBOL program and precompiled, offering optimized performance and security. Dynamic SQL is constructed at runtime, providing flexibility but with additional overhead.

7. How do you implement error handling for SQLCODE -805?

SQLCODE -805 indicates a problem with the program’s plan or package. Ensure the correct plan or package is used and properly bound to the DB2 database. Implement error handling using the SQLCA structure to manage this error.

8. Explain the concept of isolation levels in DB2.

Isolation levels in DB2 determine data visibility during transactions. The primary levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable, each offering different degrees of isolation and concurrency control.

9. How do you handle concurrency issues in a COBOL-DB2 program?

Concurrency issues arise when multiple transactions access the same data simultaneously. Strategies to manage this include using appropriate locking mechanisms, choosing the right isolation levels, and employing optimistic concurrency control.

10. Describe the use of the ROLLBACK statement.

The ROLLBACK statement undoes changes made during a transaction, maintaining data integrity by discarding updates if an error occurs.

Example:

EXEC SQL
    UPDATE EMPLOYEE
    SET SALARY = SALARY * 1.10
    WHERE EMP_ID = 'E12345'
END-EXEC.

IF SQLCODE NOT = 0
    EXEC SQL
        ROLLBACK
    END-EXEC
    DISPLAY 'Transaction rolled back due to error.'
END-IF.

11. Explain the concept of plan and package in DB2.

In DB2, a plan contains executable code for SQL statements and can include multiple packages. A package is a collection of precompiled SQL statements, allowing for modularization and optimization.

12. How do you perform a distributed join between two tables located in different DB2 databases?

Performing a distributed join between tables in different DB2 databases involves using DB2 Connect or similar middleware, DRDA protocol, and setting up a federated database system. This allows SQL operations across databases.

Example SQL Query:

SELECT A.column1, B.column2
FROM local_table A
JOIN remote_table B ON A.id = B.id;

13. Explain the concept of DB2 locking mechanisms.

DB2 locking mechanisms control how transactions interact with data concurrently. Locks can be shared, exclusive, or intent-based, and are applied at different levels of granularity. Lock escalation reduces overhead by consolidating fine-grained locks.

14. What are the different types of DB2 indexes and their uses?

DB2 indexes improve query performance by providing efficient access paths. Types include unique, non-unique, clustered, non-clustered, composite, bitmap, and expression-based indexes, each serving different query optimization purposes.

15. How do you manage DB2 tablespaces in a COBOL-DB2 environment?

Managing DB2 tablespaces involves creation, maintenance, and optimization. Tasks include defining storage parameters, performing regular maintenance like REORG and RUNSTATS, and monitoring performance to ensure efficient data retrieval and storage.