How to bring tablespace state to offline

I learned something interesting recently and I wanted to share in this blog post.

Recently, I was working with our enterprise monitoring team to setup monitoring/alerting for distributed DB2 databases. One of the alerts we wanted to setup was when a tablespace state is OFFLINE.
In this blog post, I will share how to bring a tablespace’s state to offline. Please know that doing so will put all the tables in such tablespace in inaccessible state. This could result in an outage situation. I have only used this approach for testing monitoring setup. You are cautioned from using this.

First, let us create a test tablespace.


db2 "CREATE TABLESPACE TEST1"
DB20000I The SQL command completed successfully.


Now, let us look at its state.


$ db2 list tablespaces show detail

.....................

Tablespace ID = 125
Name = TEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal


From the above output, the tablespace state is 'Normal'.

As a side note, you could go to this URL to learn about different tablespace states in DB2.

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.mon.doc/doc/r0007533.html

Now, let us figure out how to get the tablespace state to OFFLINE.

To do so, we will just rename the underlying container for this tablespace.

So, how to know the container name for the tablespace in question. We will use the tablespace id ‘125’ from the output above and use the following command.


$db2 list tablespace containers for 125

Tablespace Containers for Tablespace 125

Container ID = 0
Name = /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG
Type = File


Let us now rename the container file.


$mv /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG.bad


Note that when you do this, the tablespace immediately becomes offline. It is definitely not recommended to do this in a live database.


$ db2 list tablespaces show detail

.....................

Tablespace ID = 125
Name = TEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x4000
 Detailed explanation:
 Offline


Now, could we undo this by just using the 'alter tablespace' command?


db2 "alter tablespace TEST1 SWITCH ONLINE"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0293N Error accessing a table space container. SQLSTATE=57048


Well, we know why we got the error. This is because DB2 thinks the container file is /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG while we renamed it to /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG.bad

So, as we learned from above, a simple operation like renaming the tablespace’s container will put the tablespace in OFFLINE state.

Now, let us bring it back to NORMAL state by renaming the container to its original name.


$mv /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG.bad /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000125/C0000000.LRG


Even after doing this, the tablespace state is still not ONLINE.


$ db2 list tablespaces show detail

.....................

Tablespace ID = 125
Name = TEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x4000
Detailed explanation:
Offline


Now, let us alter the tablespace to bring it back to ONLINE state.


$ db2 "alter tablespace TEST1 SWITCH ONLINE"
DB20000I The SQL command completed successfully.

$ db2 list tablespaces show detail

.....................

Tablespace ID = 125
Name = TEST1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
 Detailed explanation:
 Normal

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s