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.
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.