Technical Blog Post
Abstract
Db2: How to drop database partitions
Body
When you drop database partitions from your DPF instance, you should not edit the db2nodes.cfg file manually but should use db2stop drop partitionnum command.
If you edit the db2nodes.cfg file manually to drop database partitions and there is a database partition group using the dropped partitions, it will cause a problem. Here is an example when editing the db2nodes.cfg file manually(bad case):
$ cat ~/sqllib/db2nodes.cfg
0 myhost1 0
1 myhost1 1
2 myhost1 2
3 myhost1 3
$ db2start
$ db2 create db test on $PWD
$ db2 connect to test
$ db2 "create table t1(i int not null primary key, c char(10)) in USERSPACE1"
$ db2 terminate ; db2stop
$ printf '4d\nw\nq\n' | ed ~/sqllib/db2nodes.cfg
$ cat ~/sqllib/db2nodes.cfg
0 myhost1 0
1 myhost1 1
2 myhost1 2
$ db2start
$ db2 connect to test
$ db2 create table t2 like t1
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1034C The database is damaged. All applications processing the database
have been stopped. SQLSTATE=58031
$ db2 list active databases
SQL1611W No data was returned by Database System Monitor.
$ db2diag
...
2017-08-31-17.53.23.970207+540 I3795107A571 LEVEL: Severe
PID : 10551804 TID : 4628 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TEST
APPHDL : 0-52 APPID: *N0.db2inst1.170831085303
AUTHID : DB2INST1 HOSTNAME: myhost1
EDUID : 4628 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, buffer dist serv, sqlkdDispatchRequest, probe:60
DATA #1 : String, 55 bytes
Sending to (1) more nodes than defined in db2nodes.cfg.
DATA #2 : Codepath, 8 bytes
1:3:4:6:7:9:11:14:15
...(snip)...
2017-08-31-17.53.24.147636+540 E3809337A799 LEVEL: Error
PID : 10551804 TID : 4628 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TEST
APPHDL : 0-52 APPID: *N0.db2inst1.170831085303
AUTHID : DB2INST1 HOSTNAME: myhost1
EDUID : 4628 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:30
MESSAGE : ADM14005E The following error occurred: "AppErr". First Occurrence
Data Capture (FODC) has been invoked in the following mode:
"Automatic". Diagnostic information has been recorded in the
directory named
"/work/db2users/db2inst1/db2dump/FODC_AppErr_2017-08-31-17.53.24.12828
5_10551804_4628_000/".
...(snip)...
2017-08-31-17.53.27.577386+540 I3890813A600 LEVEL: Severe
PID : 10551804 TID : 4628 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : TEST
APPHDL : 0-52 APPID: *N0.db2inst1.170831085303
AUTHID : DB2INST1 HOSTNAME: myhost1
EDUID : 4628 EDUNAME: db2agent (TEST) 0
FUNCTION: DB2 UDB, base sys utilities, sqeApplication::AppStopUsing, probe:6340
MESSAGE : ZRC=0xFFFFFBF6=-1034
SQL1034C The database is damaged. All applications processing the
database have been stopped.
Here is an example to reduce the number of database partitions using db2stop drop partitionnum command:
$ cat ~/sqllib/db2nodes.cfg
0 myhost1 0
1 myhost1 1
2 myhost1 2
3 myhost1 3
$ db2start
$ export DB2NODE=3
$ db2 drop dbpartitionnum verify
SQL6035W Database partition "1" is being used by database "TEST".
$ DB2NODE=0
$ db2 connect to TEST
$ db2 "select * from syscat.DBPARTITIONGROUPDEF"
DBPGNAME DBPARTITIONNUM IN_USE
------------------------------------------------ -------------- ------
IBMCATGROUP 0 Y
IBMDEFAULTGROUP 0 Y
IBMDEFAULTGROUP 1 Y
IBMDEFAULTGROUP 2 Y
IBMDEFAULTGROUP 3 Y
5 record(s) selected.
$ db2 "redistribute database partition group IBMDEFAULTGROUP uniform drop dbpartitionnums ( 3 )"
$ db2 "select * from syscat.DBPARTITIONGROUPDEF"
DBPGNAME DBPARTITIONNUM IN_USE
------------------------------------------------ -------------- ------
IBMCATGROUP 0 Y
IBMDEFAULTGROUP 0 Y
IBMDEFAULTGROUP 1 Y
IBMDEFAULTGROUP 2 Y
4 record(s) selected.
$ db2 terminate
$ export DB2NODE=3
$ db2 drop dbpartitionnum verify
SQL6034W Database partition "3" is not being used by any databases.
$ db2stop drop dbpartitionnum 3
SQL6076W If you continue, this command will remove all database files for the specified database partition from the current instance. Before continuing, ensure that the specified database partition is not in use.
Do you want to continue ? (y/n)y
2017-08-31 18:03:44 2 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:44 0 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:45 3 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:45 1 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:46 1 0 SQL1063N DB2START processing was successful.
2017-08-31 18:03:47 2 0 SQL1063N DB2START processing was successful.
2017-08-31 18:03:47 3 0 SQL1063N DB2START processing was successful.
2017-08-31 18:03:47 0 0 SQL1063N DB2START processing was successful.
2017-08-31 18:03:50 3 0 SQL6034W Database partition "3" is not being used by any databases.
2017-08-31 18:03:53 1 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:53 2 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:53 0 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:54 3 0 SQL1064N DB2STOP processing was successful.
2017-08-31 18:03:56 3 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ cat db2nodes.cfg
0 myhost1 0
1 myhost1 1
2 myhost1 2
$ db2start
$ db2 connect to test
$ db2 create table t2 like t1
DB20000I The SQL command completed successfully.
UID
ibm11140334