Hi,

In order to avoid duplicate records to be created, the program (COBOL)
requires user to check if the record they want to create already
exists in the database by searching on name (WSWA-SEARCH-STRING). The
following cursor is used. cust_id is a nine-digit field.

DECLARE CUST_CURSOR CURSOR FOR
SELECT
NAME,
CUST_ID
FROM CUST_TABLE
WHERE
SUBSTR(CUST_ID,1,3) IN ('000','888','999')
AND NAME LIKE :WSWA-SEARCH-STRING

Our DBA said the high CPU transactions occur when there are few (or
zero) records returned from the search. In that case DB2 looks
through the whole table for matches. That makes sense. But without
searching the whole table, how do you know if a record really does not
exist? I'm looking for ideas for reducing CPU usage.

Thanks much!

Bing

Just some ideas came in my mind.

1) Why didn't you use equal predicate?
Like this...
NAME = :WSWA-SEARCH-STRING

2) How about the following conditions instead of IN predicate?
(CUST_ID LIKE '000%'
OR
CUST_ID LIKE '888%'
OR
CUST_ID LIKE '999%'
)

3) Try to create proper indexes.

4) If there are some special meaning in '000', '888' and '999' in the
first three characters of CUST_ID,
are there any other equivalent conditions for some other columns?

5) Additional to above mentioned,
consider to include the query into the INSERT statement which
creates a new record
to simplify the program logic and design.

If the INSERT statement was like
INSERT INTO <table-name>
VALUES (...)

try
INSERT INTO <table-name>
SELECT ... /* same values in VALUES clause */
FROM sysibm.sysdummy1
WHERE NOT EXISTS
(SELECT statement in the cursor to check existence)