How to Backtrace SQL Query in COBOL Program

In the DB2-COBOL program, you can write simple to complex SQL queries depending on the project. All data related projects need SQL skills. In this post, I have explained how to check SQL query.

Before your analysis, you need to understand where the SQL query details will store in a catalog table.

SQL Analysis

The data stored in SYIBM.SYSQUERY catalog table helps you find information about each SQL Query.

This is the beginning step to validate before you are going to do EXPLAIN_PLAN on PLAN_TABLE (read more info here)

While writing Queries you you can add a column called QUERYNO. It helps to track its performance.

Example SQL Query with QUERYNO:

       INTO :MAX, :MIN, :AVG
       FROM DSN8A10.EMP
       WITH UR
       QUERYNO 13

After writing Query, and binding, the information about the SQL query is written into SYSIBM.SYSQUERY table.

This catalog table assigns a row for each SQL Query you have executed. And:

you can get information about:

  • Valid access path assigned
  • PLAN name
  • Package name
  • Package version.

Related: Best SQL Tuning Tips to improve performance

The Collection name you can see for this SQL query.

The information present in this catalog table is a reference if you want to tweak/influence the access path that already determined by DB2 as default.


The most important use of referring this table is you can find a valid access path for this SQL Query is assigned or not.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.