In the DB2-COBOL program, you can write simple to complex SQL queries depending on the project.
Before your analysis, you need to understand where the SQL query details will store in a catalog table.
About Query
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.
Srinimf 