How to recreate the temporary tablespaces?
User noticed the following temporary tablespaces are offline
- PSAPTEMP16
- SYSTOOLSTMPSPACE
db2 list tablespaces show detail
Tablespace ID = 1
Name = PSAPTEMP16
Type = System managed space
Contents = System Temporary data
State = 0x'00004000'
Detailed explanation: Offline
Tablespace ID = 2
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x'00004000'
Detailed explanation: Offline
Now the user wants to re-create these temporary tablespaces
Following error might occur due to this offline status:
- Backup fails with SQL2048N Reason code: '6'.
- SQL1585N A temporary table could not be created because there is no available system temporary tablespace that has a compatible page size.
SOLUTION
PSAPTEMP16
Please follow the steps below in order to re-create PSAPTEMP16:
First, create a new system temporary tablespace.
DBACOCKPIT => tablespaces => Add =>
Name: PSAPTEMP16_NEW
Database Partition Group: IBMTEMPGROUP
Contents: Temporary System Objects
Click on the "Add" Button in order to create a new system temporary tablespace.
Now please delete the offline PSAPTEMP16 by:
DBACOCKPIT => tablespaces => Choose "PSAPTEMP16" => Delete
Now recreate the PSAPTEMP16.
At last. delete the PSAPTEMP16_NEW.
SYSTOOLSTMPSPACE
Please follow the steps below in order to re-create SYSTOOLSTMPSPACE
First, delete the offline SYSTOOLSTMPSPACE.
DBACOCKPIT => tablespaces => Choose "SYSTOOLSTMPSPACE" => Delete