Yeah, it’s all happening. Migrating a CMS to Java’s Seam framework using an existing DB2 schema/dataset, the Lucene search indexes on a few tables caused DB2 error SQL1584N. The bizarre part was it was only happening locally on my machine, but not for two others at work, who each have the same amount of RAM. It may have been because I was on Ubuntu and the other devs use Red Hat Enterprise Linux.

System temporary tablespace with page size of at least pagesize could not be found.
A system temporary table space was required to process the statement. There was no system temporary table space available that had a page size of pagesize or larger.

The statement cannot be processed.

User Response:
Create a system temporary table space with a page size of at least pagesize.

sqlcode: -1584

sqlstate: 57055

Following the instructions for ensuring system temporary table spaces page sizes meet requirements, I ran DB2 query:


…checking for the entry with Type = System managed space, Contents = System Temporary data, I confirmed the page size used was 32k, which is the highest possible with DB2.

After googling a bit, it seemed the solution was to increase the bufferpool size used by the tablespace giving me this error. The instructions for adjusting the bufferpool are to simply run DB2 query:

SELECT * FROM syscat.bufferpools

Confirm the bufferpool size, then increase the number of pages available until the Lucene indexing doesn’t shit the bed. e.g.,

ALTER BUFFERPOOL ibmdefaultbp SIZE 2000

During this process, you may get the error:

SQL20169W The buffer pool is not started.

sqlcode: +20169

sqlstate: 01654

This just means the change won’t take effect until DB2 is restarted, so execute:

db2 force applications all

Now, if you have the Seam observer Java annotation setup for Lucene indexing, this DB2 error will begone on the next deploy. And yes, this whole post makes me yearn for a non-Java project started from scratch.