Steps You Need to Analyze DB2 SQL Query

In the DB2-COBOL program, you can write simple to complex SQL queries depending on the project. Almost all data related projects require SQL skills. In this post, I have explained how to analyze the 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:

EXEC SQL
     SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
       INTO :MAX, :MIN, :AVG
       FROM DSN8A10.EMP
       WITH UR
       QUERYNO 13
END-EXEC;

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.

Summary

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