How to fix DB2 Tablespace OFFLINE state issue?

Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.

Finding TableSpace State

To check which tablespace is OFFLINE, execute the below command:

SYNTAX:

db2 connect to <database_name>

db2 list tablespaces |egrep -i "ID|Name|State"

db2 list tablespaces show detail

db2inst1@Ubuntu06:~$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@Ubuntu06:~$ db2 list tablespaces |egrep -i "ID|Name|State"
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 State                                = 0x0000
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 State                                = 0x0000
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 State                                = 0x4000
 Tablespace ID                        = 3
 Name                                 = IBMDB2SAMPLEREL
 State                                = 0x0000
 Tablespace ID                        = 4
 Name                                 = IBMDB2SAMPLEXML
 State                                = 0x0000
 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 State                                = 0x0000
db2inst1@Ubuntu06:~$ 
db2inst1@Ubuntu06:~$ db2 list tablespaces show detail

           Tablespaces for Current Database
.....
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x4000
   Detailed explanation:
     Offline
.....
db2inst1@Ubuntu06:~$ 

Fixing TableSpace State

Once you find out which tablespace goes offline, we have two methods to fix this issue and make our Tablesapce state from OFFLINE to ONLINE.

Method 1 : We can execute below DB2 commands to take off tablespace's OFFLINE state.

SYNTAX:

db2 terminate

db2 force application all

db2 connect to <database_name>

db2inst1@Ubuntu06:~$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
db2inst1@Ubuntu06:~$ db2 force applications all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

db2inst1@Ubuntu06:~$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@Ubuntu06:~$ db2 list tablespaces |egrep -i "ID|Name|State"
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 State                                = 0x0000
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 State                                = 0x0000
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 State                                = 0x0000
 Tablespace ID                        = 3
 Name                                 = IBMDB2SAMPLEREL
 State                                = 0x0000
 Tablespace ID                        = 4
 Name                                 = IBMDB2SAMPLEXML
 State                                = 0x0000
 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 State                                = 0x0000
db2inst1@Ubuntu06:~$ db2 list tablespaces show detail 
           Tablespaces for Current Database
.....
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 4096
 Useable pages                        = 4064
 Used pages                           = 1824
 Free pages                           = 2240
 High water mark (pages)              = 1824
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
.....
db2inst1@Ubuntu06:~$

Method 2: Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up and deployed.

SYNATX:

db2 connect to <database_name> db2 "ALTER TABLESPACE <TablespaceName> SWITCH ONLINE"

db2 list tablespaces show detail

db2inst1@Ubuntu06:~$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@Ubuntu06:~$ db2 "ALTER TABLESPACE USERSPACE1 SWITCH ONLINE"
DB20000I  The SQL command completed successfully.
db2inst1@Ubuntu06:~$ db2 list tablespaces |egrep -i "ID|Name|State"
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 State                                = 0x0000
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 State                                = 0x0000
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 State                                = 0x0000
 Tablespace ID                        = 3
 Name                                 = IBMDB2SAMPLEREL
 State                                = 0x0000
 Tablespace ID                        = 4
 Name                                 = IBMDB2SAMPLEXML
 State                                = 0x0000
 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 State                                = 0x0000
db2inst1@Ubuntu06:~$
db2inst1@Ubuntu06:~$ db2 list tablespaces show detail 
           Tablespaces for Current Database
.....
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 4096
 Useable pages                        = 4064
 Used pages                           = 1824
 Free pages                           = 2240
 High water mark (pages)              = 1824
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
.....
db2inst1@Ubuntu06:~$

That's all in this post. If you liked this blog and interested in knowing more about IBM Db2. Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com.