sufficient page size does not exist - DB2 insert

Question

I am having a DB2 query(simple insert statement) which is trying to insert some 27 columns. Out of those columns 1 is Clob and the issue is there. For my Clob column, sometimes the value might even contain 28K characters. And in such extreme cases, I am getting the below error,

 A system temporary table space with sufficient page size does not exist .. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.64.82

As I googled and gone through some pages, there is an entity called System Temporary Table Space which will be used by the DB when executing the query.(I am not sure, but few pages says that it will be used only for sorting and joining, but i don't have either in my query).

After going through few suggestions I created a System Temporary Table Space with the page size of 32K, using the below query,

CREATE SYSTEM TEMPORARY TABLESPACE STB PAGESIZE 32K MANAGED BY SYSTEM USING ( 'C:\DB2\NODE0005') BUFFERPOOL BP32K

Still my issue continues. What would be the proper way to have my query executed. I am trying to understand the importance of System Temporary Table Space, Page Size,etc.. But any help that could fix this issue for now would be greatly appreciated.

Answer 1

AngocA's soultion seems to work in principle it goes along the lines of http://www-01.ibm.com/support/docview.wss?uid=swg21529563.

For the 4K system temporary table space the command

CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K 

will work immediately if 4K is your default page size. To avoid an SQL 1582N error http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01582n.html

you might need to create 8K,16 and 32 K bufferpools:

CREATE BUFFERPOOL BP8K pagesize 8K
CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K BUFFERPOOL BP8K
CREATE BUFFERPOOL BP16K pagesize 16K
CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K BUFFERPOOL BP16K
CREATE BUFFERPOOL BP32K pagesize 32K
CREATE SYSTEM TEMPORARY TABLESPACE STB_32 PAGESIZE 32K BUFFERPOOL BP32K

Answer 2

You can create a system temporary tablespace for each page as SMS (System Managed). In that case, your query will always find a tablespace with the appropriate page size.

CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K 
CREATE SYSTEM TEMPORARY TABLESPACE STB_32 PAGESIZE 32K 

When creating SMS, the tablespace will not preallocate space in the disk, and they only growth as they are used.