Help

Observation: COUNT(*) for Large Result Set

You are applying the COUNT column function against a potentially large result set. If you are using COUNT(*) only to determine the existence of at least one row, not the actual number of rows that qualify, consider recoding the statement to return a single row when the predicate list is satisfied. To do this, code a singleton SELECT and test the SQLCODE: if the SQLCODE is 0, a single row qualifies; and, if the SQLCODE is -811, more the one row qualifies. Another alternative is to count the number of distinct occurrences of a required value.

The example below determines whether any employee is an engineer:

SELECT 
COUNT(DISTINCT JOB) 
FROM 
EMP 
WHERE 
JOB = 'ENGINEER' 

Processing stops when DB2 finds the first EMP row containing ENGINEER in the JOB column, and DB2 returns the value 1. Also, you can replace the COUNT function with a literal constant, as shown below:

SELECT
1 
FROM 
EMP 
WHERE 
JOB = 'ENGINEER'