If you want to establish the existance of a row in a DB2-table, you normally use a SELECT SQL-statement. SELECT returns one or more rows. Sometimes you are only interested in knowing the existance of one or more rows fulfulling your WHERE-clause and the contens of the row(s) doesn't matter.

After the introduction of the SYSIBM-table SYSIBM.SYSDUMMY1, it has become quite easy to solve this task. Let us look at an example:

SELECT 0 FROM SYSIBM.SYSDUMMY1
WHERE EXISTS
( SELECT 0 FROM MY_TABLE
  WHERE MY_NO = 3
)

If MY_TABLE contains one or more rows with column MY_NO containing the value 3, the SQL-statement will return sqlcode 0, otherwise it will return sqlcode 100. DB2 is designed in a way, that makes this form of SQL the fastest and most efficient way to establish the existance of rows fulfilling a WHERE-clause. DB2 simply stops scanning rows when it has found the first row matching the conditions in the WHERE-clause of the subselect, because of the use of EXISTS.

If you are still running version 4 of DB2, you can use the following trick, but it has some limitations and drawbacks:

UPDATE MY_TABLE
SET MY_NO = MY_NO
WHERE MY_NO = 3

Again sqlcode 0 is returned for one or more rows fulfilling the WHERE-clause and sqlcode 100 for no rows. The limitations are that no joins are possible, and the drawbacks are that locking will prevent others from reading the updated rows until a commit is issued. A funny thing is that DB2 doesn't write any log-records because the value of MY_NO doesn't change.