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.
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.
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.