KR-123 (DB2 FETCH CLAUSE):

Hi everyone,

We'll take a look at an interesting clause in DB2...

Sometimes we might want to check if a row exists in a table (we don't want to use any of the values but just want to check for existence).
For example we might just want to check if the year 2007 is present in a calendar table.

1.) If the conditions we use might retrieve more than one row from the table, then we will have to handle sqlcodes for multiple rows being fetched: -811.
2.) Or we could use a count(*) in the query and then check the value returned.
Ex: SELECT COUNT(*) FROM CALENDAR WHERE YEAR='2007';

A better choice is to use the latest DB2 feature which permits us to retrieve a particular number of records:
SELECT 1 FROM CALENDAR 
WHERE YEAR='2007'
FETCH FIRST 1 ROW ONLY;

If this were coded in COBOL then we would need to store 1 in a host variable. The advantage in this is that DB2 can stop processing once it gets a single row satisfying the criteria - it needn't process extra information.

(The FETCH clause is available from DB2 version 7 onwards).

That's all for this edition...suggestions and KRs welcome;

KR-124 (DB2 FETCH II):

Hi everyone,

A little more info on the FETCH FIRST clause in DB2:

The fetch clause can also be used when we want to fetch only the top 10 or 20 rows from a table based on some criteria (i.e. we want to order the data and then fetch "n" rows). Ex:

SELECT DAY FROM CALENDAR 
WHERE YEAR='2007'
AND MONTH = '05'
ORDER BY DAY DESC
FETCH FIRST 5 ROWS ONLY;

• This could be coded in a cursor to retrieve the last 5 days in the month.

• The cursor will return an SQLCODE of +100 when 5 rows have been retrieved (if it weren't for this fetch clause we might have used a counter to keep track of the number of days retrieved and then manually quit from the fetch loop). If there are less than 5 rows also we would get a +100.

What about OPTIMIZE FOR n ROWS clause?

• This is not the same as FETCH clause; the optimize clause is used to tell the optimizer "I am sure that approximately 'n' rows only will be returned by this query; so choose an appropriate access path". 

• But the FETCH FIRST clause tells DB2 "I want only n rows to be retrieved; nothing more that that". 

• If we code OPTIMIZE FOR n ROWS and if there happened to be more rows than n rows satisfying our criteria, we can still fetch those rows from the cursor (problem will be that performance might degrade because the optimizer would have decided on an access path based on the fact that only approximately "n" rows will be fetched). But in the FETCH FIRST clause we can't fetch more than "n" rows.

That's all for this edition...Suggestions and KRs welcome...