SQL1585N a system temporary tablespace with sufficient page size does not exist db2

December 9, 2018

This is the common error in DB2 and it occurs when a database doesn’t able to find the system temporary tablespace with sufficient pagesize.

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

Steps to resolve SQL1585N a system temporary tablespace with sufficient page size does not exist

To check temporary tablespace name: 
SQL>  select distinct(TBSPACE)  from syscat.tablespaces where TBSPACE like '%TEMP%';

TBSPACE
------------
TEMPSPACE1
To check pagesize of the temp tablespace: 
SQL> select TBSPACE,TBSPACEID,PAGESIZE from syscat.tablespaces where TBSPACE='TEMPSPACE1';

TBSPACE          TBSPACEID    PAGESIZE  
-----------------------------------------
TEMPSPACE1          1           16384
To check existng bufferpools and the pagesize: 
SQL> select bpname,pagesize from syscat.bufferpools;
Create required bufferpool and the temp tablespace: 
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

Must Read: