Space announcement This documentation space provides the same content as before, but the organization of the content has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Debugging Db2 Program

This section discusses the special facilities available to Db2 programmers, including setting breakpoints and keeps, interpreting abends and SQL codes, and accessing Db2 storage.

Setting Breakpoints in SQL Code

Related Topics

Quick Reference

Advanced Configuration Guide

Getting Started

Reference

Your online source listing displays both commented-out SQL commands and the Db2 translated code. As a result, Code Debug CICS lets you set or delete breakpoints in all generated instructions. In addition, Code Debug CICS allows you to globally set breakpoints on all SQL calls.

  • To set breakpoints before every SQL statement, enter BEFORE ALL SQL in the COMMAND field and press Enter. Code Debug CICS dynamically sets before breakpoints on every SQL statement or call to DSNHLI.

You can also set breakpoints after EXEC SQL statements and counts of EXEC SQL statements. Breakpoints can be set on all returning SQL calls, or counts can be set to help in SQL analysis.

  • Db2 breakpoints are further qualified by specifying an SQL call type. For example, COUNT ALL SQL UPDATE sets counters only on EXEC SQL UPDATE calls. No other calls are counted. Of course, you can set or delete individual breakpoints using the BEFORE, AFTER, RUNTO, COUNT, and DELETE commands.

Setting Keeps on Db2-Specific Data

In Code Debug CICS, the KEEP command is used to continuously view a data field on the Source Listing screen (2.L). You can display various Db2 data items, such as working storage items, DFHCOMM fields, indices, and Db2 fields. You can add any Db2 specific data item, such as SQLCODE or SQLERRM, to the keep window, as long as it is defined to your program.

To display the current SQLCODE value, enter KEEP SQLCODE in the COMMAND field. Once displayed, you can modify data by replacing the contents of the field. This is an excellent way to test IF logic after an SQL call.

Interpreting DSNC Abends and SQL Codes

Code Debug CICS automatically traps all encountered abends, including Db2 DSNC abends. Code Debug CICS recognizes DSNC reason codes and treats them like any other abend. It intercepts the abend before it takes place and returns control to you.

DSNC Abends

For example, if your Db2 program abends with a DSNC AEY9 abend code, Code Debug CICS will:

  • Intercept the abend
  • Format the Source Listing screen
  • Point to the offending call
  • Flag the abend code as an AEY9.

If CICS Abend-AID is installed, additional Db2 information is available. Typing =7.1 in the COMMAND field and pressing Enter allows you to jump directly into CICS Abend-AID to diagnose an AEY9 as a call to Db2 prior to activation.

DSNC abend recognition is especially useful in test regions prone to Db2 attachment-facility errors. This Code Debug feature is designed to assist both the DBA and the Db2 application programmer.

SQL Codes

Programmers commonly complain about cryptic return codes. This is also true for Db2 SQL codes. Code Debug CICS has SQL support within its Help facility.

Enter HELP SQLERROR, HELP SQLCODE, or HELP SQL to access timely SQL diagnostics and warnings of the last SQL statement executed. Following figure is an example of help after Db2 returned a 100 SQLCODE.

Help Exit Screen

------------------------------------- HELP EXIT ---------------------------C024
COMMAND ===>                                                   SCROLL ===> CSR
MODULE: TRICDB2T    ******** HIT PF1 AGAIN FOR HELP ON USING TUTORIALS ********
    DSNT404I SQLCODE = 100, NOT FOUND:  ROW NOT FOUND FOR FETCH, UPDATE, OR
             DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE
    DSNT415I SQLERRP = DSNXRFCH SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 110  0  0  1  0  0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'FFFFFF92'  X'00000000'  X'00000000'  X'FFFFFFFF'
             X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

Accessing Db2 Storage

Code Debug CICS provides extensive access to any CICS table or control area. These areas are accessed in hexadecimal using the Memory Display screen (2.2) or mapped to a DSECT of the current IBM data area description using the DSECTs screen (2.D). Both screens allow keyword access.

Five Db2 related keywords are available for use in the TABLE/AREA field of the Memory Display (2.2) and DSECTs (2.D) screens. These commands are useful in diagnosing Db2 programs with the following storage problems:

RCT (DB2 Resource Control Table): Identifies the plan name, thread, and TCB information.

Since the RCT is no longer available, if RCT is specified, the CICS Resources screen (2.R) is displayed showing information for the DB2CONN entry.

SQLCA (SQL Communications Area): Identifies the SQL return code, error diagnostics, and warning indicators of the last SQL statement executed.

SQLDA (SQL Descriptor Area): Provides a pointer to the data received by a SELECT statement in a dynamic SQL call.

PLIST (DB2 Parameter List): List of parameters passed to the Db2 call generated by an EXEC SQL program statement.

CLOT (CICS Life of Task): Provides the Db2 connection authorization ID, and various error codes associated with the task.

Important

All of the above keywords (except RCT) can only be used at a break or abend with Db2 active.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*