I am very new to DB2.have installed Express C version and facing some problem while running a simple query.

 where A=9999  ORDER BY  D

Note: A through H are the only columns in the table. When I do this, I get this exception:

DB2 SQL error: SQLCODE: -1585, SQLSTATE: 54048, SQLERRMC: null Message: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.

More exceptions ...

DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-1585;54048;

This is just a simple query with where and order by clause. Why am I getting this error?


The ORDER BY, particularly in the absence of an index on column D, will require a sort operation. If there is not enough memory allocated in the sort heap for the statement, it will cause an overflow of data being sorted into a temporary table. The table width will be the sum of the column widths of the result set being sorted, plus some overhead.

There must be a system temporary tablespace with the page size appropriate to accomodate the temporary table width, and the error says that there is no such tablespace.

To create it you could run the following statement, as an example (assuming you use automatic storage in your database):

create system temporary tablespace TMPSYS32 pagesize 32 k

It may also happen that you don't have a bufferpool with that page size (each tablespace must have a bufferpool with matching page size). If so, you will need to create the bufferpool first:

create bufferpool BP32 pagesize 32 k
create system temporary tablespace TMPSYS32 pagesize 32 k bufferpool BP32
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.