Introduction

We will show you some commands to perform administrative tasks under partitioned databases, such as: increasing tablespaces; checking applications status; creating backups; performing restores, and other ones. Most of the commands in a partitioned environment are similar to the commands in a non-partitioned environment; the main difference is the use of the DB2_ALL which lets you to check information in all nodes of an instance.

Basic commands

Checking nodes and Navigating between them

To check the number of partitions your instance has, perform the following db2 command on AIX:

db2 get snapshot for dbm | grep –i partitions

You also can check the file db2nodes.cfg which is usually under $HOME/sqllib or perform the command:

db2 list nodes

Now, if you need to know which node you are, perform:

db2 “values (current dbpartitionnum)”

It will return the current node set in DB2.

To change to a specific node, execute the following commands:

export DB2NODE=<node_number>
db2 terminate

The TERMINATE must be executed in order to cancel the actual node database connection and then allow you to connect again into the new node.

Checking Applications

If you need to check all applications running into all nodes, perform the command:

db2_all “db2 connect to <db_name>; db2 list applications [show detail]”

Alternatively, you can use DB2PD tools, as following:

db2_all “db2 connect to <db_name>; db2pd –db <db_name> -applications”

The main problem of both solutions are the large output produced when you have a database with several partitions. So, you also can use the following command:

db2 list applications global [show detail]

And in case you want to list applications in a specific partition, issue the command:

db2 list applications at dbpartitionnum <dbpart_number> [show detail]

Checking Tablespaces

When you need to list tablespaces of all nodes, perform:

db2_all “db2 connect to <db_name>; db2 list tablespaces [show detail]”

Or

db2_all “db2 connect to <db_name>; db2pd –db <db_name> -tablespaces”

In case of you want to discover the tablespaces that belongs in a specific node, just execute a “db2 list tablespaces” as usual.

To check the state of your tablespaces (if they are all in Normal state, for example) perform the following statement:

db2_all “ db2 connect to <db_name>; db2 list tablespaces 
| grep -i state | wc -l ; db2 list tablespaces | grep -i normal | wc –l”

For example, consider this fragment of one output for the command:

db2_all "db2 connect to DBTEST; db2 list tablespaces | grep -i state | wc -l ;
db2 list tablespaces | grep -i normal | wc -l "

   Database Connection Information

 Database server        = DB2/6000 8.2.1
 SQL authorization ID   = SERVER1
 Local database alias   = DBTEST

     300
     300
appmd506: db2 connect to DBTEST completed ok


   Database Connection Information

 Database server        = DB2/6000 8.2.1
 SQL authorization ID   = SERVER1
 Local database alias   = DBTEST

       5
       5

As you can see, everything is fine because the two numbers are the same. However, if in your database there's a difference between the numbers, it means some tablespaces aren't in Normal state, so you should check it in detail.

If you have space issues in a tablespace and need to extend it, just enter:

db2 “alter tablespace <ts_name> extend (ALL <number_pages>) on 
dbpartitionnum[s] (<dbpart_numbers>)”

Where DBPARTITIONNUM can be a specific partition or a range specified using “<initial_dbpart> to <final_dbpart>”. You also can use regular statements of ALTER TABLESPACE to extend containers, to add containers, etc. The only detail you need to observe is to use the correct dbpartitionnum.

To find in which partition a tablespace is, use:

db2 "select substr(b.tbspace,1,20), a.DBPARTITIONNUM
from syscat.dbpartitiongroupdef as a, syscat.tablespaces as b
where a.DBPGNAME = b.DBPGNAME and b.tbspace = '<ts_name>' "

Example:

db2 " select substr(b.tbspace,1,20) as TBSNAME,a.DBPARTITIONNUM
from syscat.dbpartitiongroupdef as a, syscat.tablespaces as b
where a.DBPGNAME = b.DBPGNAME and b.tbspace = 'TABLEX' "

TBSNAME              DBPARTITIONNUM
-------------------- --------------
TABLEX                            0
TABLEX                            2
TABLEX                            3
TABLEX                            4
TABLEX                            5
TABLEX                            6
TABLEX                            7

  7 record(s) selected.