Aloha,

We just encountered a problem in using JPA to display some table information with a varchar(6000) field. Sorting causes the use of a TEMP storage table and IBMDEFAULTBP has the default 4096 pagesize. Obviously this causes a problem (SQL1585N)...

I'm considering increasing the size to 8K but after Googling, I'm a little concerned that there may be upgrade issues with DB2 in the future.
https://www-304.ibm.com/support/docv...id=swg1IO12416

So I'm wondering what opinions you DBA's have on this.

Is it possible to set up an alternate buffer pool and somehow have that used as the TEMP storage Table for sorts?
Or should I just take the risk and increase the IBMDEFAULTBP pagesize?


DB2 v9.7 on Linux-64

I always create a 32K bufferpool (of small size) and a 32K System Temporary Tablespace in every database. Shouldn't matter what default page size is if you do that. It is OK to leave the default 4K TEMPSPACE1 as it was created.

Aloha and thanks Marcus,

That's was a very reasonable suggestion, so that's what I did.
Works like a charm!

Clearly, since you have a table with a 6K column, you must have a tablespace with the page size larger than 4K to contain it and, subsequently, a bufferpool with the same page size.

The only thing that you are missing is a system temporary tablespace with that page size. If you follow Marcus' approach of 32K you'll need also a bufferpool with that page size.

Also, you can't change a bufferpool page size; you can only drop and re-create the bufferpool, so "increasing the size to 8K" won't work.