How to control COBOL evaluate logic

The evaluate logic helps you to avoid multiple if statements in your program. This also avoids confusion in understanding COBOL logic.

We already know the syntax for evaluate is:

EVALUATE TRUE

    WHEN  CONDITION

     PERFORM  PARA1

END-EVALUATE;

Why “when other ” you need to mention in evaluate -the reason is very simple:

To avoid  ambiguity to the compiler, you need to mention it. 

The other way is , if the conditions are not matched, it helps what action to take. 

One more point is, during validations for Numeric or character, you may get scenario like spaces – so to handle spaces what action it should take you need to mention the when other condition.

How to use aggregate functions the best way in DB2 SQL

db2
[ Learning DB2 SQL functions ]
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';

Read the specific behaviour of “COUNT function” vs other aggregate functions.

Additional points on COUNT function

  • When no rows are present in the column COUNT functions returns zero. But all other functions returns NULL.

The best example for SUM:

Read full article about “SUM function” here.

The best example for AVG, MAX, MIN

SELECT DEPT, AVG(SALARY), MAX(SALARY), MIN(SALARY)
INTO :WS-DEPT, :WS-AVG, :WS-MAX, :WS-MIN
FROM TEST.EMPLOYEES;

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

Related: You can Try “Certificate in SQL”, a best way to prove your skills

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.