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