In DB2 there are five aggregate functions. The general thumb rule is aggregate functions can be applied on group of column values. The list is COUNT,SUM, AVG, MAX and MIN.These are typical functions being used across all the projects.
The best example for COUNT:
EXEC SQL SELECT COUNT(*) INTO :FEMALE FROM DSN8A10.EMP WHERE SEX = 'F';
SELECT DEPT, AVG(SALARY), MAX(SALARY), MIN(SALARY)
INTO :WS-DEPT, :WS-AVG, :WS-MAX, :WS-MIN
Two typical points to note on aggregate functions:
If a GROUP BY clause is specified in a query and the intermediate result from the FROM, WHERE, GROUP BY, and HAVING clauses is the empty set, then the aggregate functions are not applied and the result of the query is the empty set.
If the GROUP BY clause is not specified in a query and the intermediate result table of the FROM, WHERE, and HAVING clauses is the empty set, then the aggregate functions are applied to the empty set.
This is not allowed using one aggregate function in another aggregate function
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:
SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
INTO :MAX, :MIN, :AVG
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.
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.