COBOL and DB2

Most DB2 application programs on the mainframe are written in COBOL. A COBOL program talks to DB2 by embedding SQL statements directly inside the COBOL source code. This page explains the big picture of COBOL and DB2 working together.

What is a DB2 COBOL program

• A DB2 COBOL program is an ordinary COBOL program that also contains SQL statements for reading and updating DB2 tables.
• The SQL statements are written between EXEC SQL and END-EXEC, so the DB2 precompiler can find them.
• The program passes data between COBOL and DB2 through host variables - COBOL working-storage fields named with a colon (:) prefix inside SQL.
• After every SQL statement, the program must check SQLCODE in the SQLCA to know whether the statement succeeded.

How COBOL talks to DB2

• COBOL cannot execute SQL by itself. The DB2 precompiler first extracts the SQL and replaces it with CALL statements to the DB2 language interface.
• The extracted SQL goes into a DBRM, which is later bound into a plan or package. The plan holds the optimized access path DB2 will use at runtime.
• At runtime the load module runs, the CALL statements reach DB2, and DB2 executes the SQL that was bound in the plan.
• Input values go from COBOL host variables into SQL; result values come back from SQL into COBOL host variables through the INTO clause.

Static SQL vs dynamic SQL

• Static SQL is embedded in the application program. The SQL text is fixed; only the values of the host variables in the predicates can change from run to run.
• Dynamic SQL can change columns, tables and predicates during the program's execution. The SQL text is built at runtime.
• Two types of dynamic SQL: EXECUTE IMMEDIATE (move the SQL text into a host variable and execute it at once) and EXECUTE WITH PREPARE (prepare the SQL first, then execute it, so the executable form is kept).
• EXECUTE IMMEDIATE cannot be used for SELECT, and its executable form is deleted once executed. Static SQL is faster and is preferred whenever the SQL is known in advance.

Example of a small COBOL program reading one customer row from DB2:-