IBM Support

Db2: How to drop database partitions

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 alexx 0
1 alexx 1
2 alexx 2
3 alexx 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 alexx 0
1 alexx 1
2 alexx 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: e101q3b              NODE : 000           DB   : TEST
APPHDL  : 0-52                 APPID: *N0.e101q3b.170831085303
AUTHID  : E101Q3B              HOSTNAME: alexx
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: e101q3b              NODE : 000           DB   : TEST
APPHDL  : 0-52                 APPID: *N0.e101q3b.170831085303
AUTHID  : E101Q3B              HOSTNAME: alexx
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/e101q3b/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: e101q3b              NODE : 000           DB   : TEST
APPHDL  : 0-52                 APPID: *N0.e101q3b.170831085303
AUTHID  : E101Q3B              HOSTNAME: alexx
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 alexx 0
1 alexx 1
2 alexx 2
3 alexx 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 alexx 0
1 alexx 1
2 alexx 2

$ db2start
$ db2 connect to test
$ db2 create table t2 like t1
DB20000I  The SQL command completed successfully.

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140334