SQL0955C - Empty Table

[atoi]

Environment: DB2-UDB for AIX version 7.2.4

Have a process that encounters SQL0955C errors on occasion when executing a SELECT from an empty table while nothing much else is going on in the database instance. The table in question has NEVER been populated with data. The table resides in a tablespace with other populated tables. The SELECT statement has a WHERE predicate that specifies columns NOT included in an index. RUNSTATS have been run for the table. The process is part of a packaged application, consequently, the client wants to leave the software in it's vanilla state. The DBA has adjusted SORTHEAP/SHEAPTHRES parameters in efforts to address the problem.

Does anyone have an explanation as to what might be causing this? Would creating an index for the columns in the WHERE predicate help? The problem does not happen regularly and can not be replicated on command making it difficult to debug.

TIA for any insight you may provide.

AO

 

[gregsimpson]

AO,

is this SELECT as part of a CURSOR or a singleton select? Perhaps you can paste the actual statement?

Also try taking a look at the following

Increase the database configuration parameter (dbheap) to allow a larger database heap. If the number of I/O servers is near the high limit, reducing this number may also help.

Cheers
Greg

 

[atoi]

Greg:

The select is part of a cursor . . . the process is executing a proprietary product (SQR) that creates dynamic SQL based on it's coding language. The basic syntax of the SQL statement that is being prepared/executed is not too complex and looks something like this:

SELECT A.FLDA
, A.FLDB
, A.FLDC
, A.FLDD
FROM A_TBLNAME A
WHERE A.FLDC = ?
AND A.FLDD = ?

I'll pass along your comments to the DBA.

Thanks for your suggestion.

AO

 

[gregsimpson]

AO,

does it have an ORDER BY, or GROUP by etc in it?

Greg

 

[atoi]

Greg:

No sir, no ORDER BY or GROUP BY clause.

AO

 

[gregsimpson]

AO,

is it using an index for this search or doing a table space scan?

Greg

 

[atoi]

Greg:

Additional tweaking of the SORTHEAP/SHEAPTHRES parameters appears to have addressed the problem.

Thanks for your input,

AO