IBM Mainframe Forum

I got below error messages when I execute a sql query which will return a lot of records.

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000100, AND RESOURCE NAME DSNDB07. SQLCODE=-904, SQLSTATE=57011, DRIVER=3.53.95

I search db2 information center and got some explanation for the reason code 00C90084.
Seems that there is no sufficient space on DSNDB07. What i should do to increase the space for DSNDB07?


Below are some explanation for the reason 00C90084 from db2 information center:

DB2® cannot find space for creating a temporary file or for inserting data into a temporary file because the workfile database is full. DB2 cannot perform the requested actions because of one of the following reasons:
You are running a large number of concurrent transactions requiring the use of temporary files.
There is no space available in any of the tablespaces in the workfile database.
System action
The reason code appears in a DSNT501I message, and the requested operation is not performed, and SQLCODE -904 might be issued.

User response
To resolve the above situation, do one of the following:

Reduce the number of concurrent transactions.
Increase primary/secondary quantity defined for the table space(s) in the temporary file database.
Increase space allocated to temporary file database.

Hello,

Suggest you work with your dba. . .

It sounds like more space is needed or you need to find a different way to implement.

DSNDB07 is the sort temp database.
It is the database where db2 stores intermediate results for sorts (and from DB2 9 also declared global temporary tables).
It is shared for all transactions, jobs within the subsystem.
You could try at another time when less concurrent db2-sorts are running.

When changing from DB2 v8 to DB2 9, your dba probably has to review the space allocation for DSNDB07.