Does your SQL statement sometimes terminate with a SQLCODE -905 then this tip might help you overcome the problem. SQLCODE -905 means that DB2 Governor is active on the DB2 system executing the SQL statement and that your SQL statement has violated one of the rules defined in DB2 Governor. DB2 Governor abends a SQL statement using too much CPU time according to the rules. When you receive a -905 it is because your SQL statement has been using too much CPU time. DB2 Governor is also known as Resource Limit Facility (RLF).
If you face a problem with SQLCODE -905 and you desperately need to execute your SQL statement you can circumvent the problem in several ways. Here is a list containing most of the options:
I will now give you further details regarding the two last options. Firstly it is important to understand that the CPU limitation is a limitation for each call to DB2. A SELECT statement is excecuted as an OPEN call followed by a number of FETCH calls. If your SELECT contains an ORDER BY which cannot take advantage of an index then all CPU will be consumed by the OPEN call. By removing the ORDER BY the CPU consumption will be distributed on each individual FETCH call. Each FETCH will very likely use far less CPU than the limit you otherwise would run into. Now your SQL statement gets processed, but unsorted. Now you can use either the SORT command in ISPF VIEW/EDIT or DFSORT and you are a happy man. Or maybe a sorted result after all is not that important for you.
Whether a SELECT statement will use all its CPU time in OPEN or not may be hard to find out. You have to look into an EXPLAIN of your statement and see if there are any METHOD 3 rows in it. If there is then all CPU time will be used during OPEN. Another problem might be that your SQL statement performs a tablespace scan on a very large table and only a few rows fulfill your WHERE clause. In this case a FETCH may grow expensive enough to hit a limit in DB2 Governor, because DB2 has to scan a lot of rows before it finds the next row to return. In cases like this you might consider to use the DB2 UNLOAD utility, FAST UNLOAD from CA or a similar product. These utilities are able to bypass the DB2 SQL processor and consequently DB2 Governor.
You might also investigate the DB2 Governor rules imposed on the DB2 system giving you problems. The DB2 Governor rules are stored in a single DB2 table. The table name is starting with DSNRLST followed by two alfanumeric characters free of choice. The creator of the table is also free of choice. The exact name of the DSNRLST table is present in the SQLCA returned by a -905. Using this table name you can execute a SELECT against this table in order to find out which rule your SQL statement violated. Afterwards you can find out if you are able to let your SQL statement fall under a less restrictive rule.
You can read all about the rules in DB2 Administration Guide. Please note that this link is for the DB2 version 7 manual, but there is no significant changes with respect to version 8. There are no changes in the specification and interpretation of the rules. I do not know whether there are changes in this area regarding version 9, but my guess is that there are no changes.