Online Tutorials & Training Materials | STechies.com
Register Login

Recreate the temporary tablespaces

|| 0

Recreate the temporary tablespaces
Stechies

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