How to analyze SQL Query, the best way

QUERYID in SYSQUERY Catalog Table
[QUERYID in SYSQUERY Catalog Table]
In DB2-COBOL program, you can write simple SQL queries to complex SQL queries that depending on the project. Almost all data intensive projects writing SQL queries is a usual practice.

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 will add a column name in SQL query. That is called QUERYNO. It says the query number you are using in that project.

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 SQL query is written into SYSIBM.SYSQUERY table. This catalog table assigns a row to each SQL Query you have written.

From this table, you will know, about the valid access path is assigned, PLAN name, Package name and Package version etc.

Related: Best SQL Tuning Tips to improve performance

Also, Collection name you can see for this SQL query. The useful information provided 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 , to know valid access path for this SQL Query is assigned or not.

Advertisements

Author: Srini

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