Shulou(Shulou.com)06/01 Report--

Deleting a database partition from DPF is by no means as simple as editing db2nodes.cfg directly. The right thing to do is to use the command "db2stop drop partitionnum".

If you edit db2nodes.cfg directly to delete the row or rows of the corresponding partition, and there happens to be a database partition group (database partition group) still using the deleted partition or these partitions, it will cause a problem. Look at the following example:

[db2inst1@testhost ~] $cat sqllib/db2nodes.cfg

0 testhost 0

1 testhost 1

2 testhost 2

3 testhost 3

4 testhost 4

[db2inst1@testhost ~] $db2 "select TBSP_NAME,DBPARTITIONNUM,TBSP_UTILIZATION_PERCENT from SYSIBMADM.TBSP_UTILIZATION order by TBSP_NAME with ur"

[db2inst1@testhost ~] $db2 "create table T1 (i int not null primary key, c char (10)) in TBS_TEST"

DB20000I The SQL command completed successfully.

[db2inst1@testhost ~] $db2 terminate

DB20000I The TERMINATE command completed successfully.

[db2inst1@testhost] $. / insert.sh

[db2inst1@testhost ~] $db2stop

03/25/2018 18:54:41 10 SQL1064N DB2STOP processing was successful.

03/25/2018 18:54:41 20 SQL1064N DB2STOP processing was successful.

03/25/2018 18:54:42 0 0 SQL1064N DB2STOP processing was successful.

03/25/2018 18:54:43 3 0 SQL1064N DB2STOP processing was successful.

03/25/2018 18:54:43 4 0 SQL1064N DB2STOP processing was successful.

SQL1064N DB2STOP processing was successful.

[db2inst1@testhost] $ipclean-a

Application ipclean: Removing all IPC resources for db2inst1

[db2inst1@testhost ~] $db2_kill

Rah: primary monitoring process for db2nkill is 61762

Testhost: db2nkill: DB2 member 0 with PID 56900 does not exist.

Testhost: db2nkill: DB2 member 1 with PID 57016 does not exist.

Testhost: db2nkill: DB2 member 2 with PID 57113 does not exist.

Testhost: db2nkill: DB2 member 3 with PID 57236 does not exist.

Testhost: db2nkill: DB2 member 4 with PID 57269 does not exist.

Testhost: db2nkill [] completed ok

[db2inst1@testhost ~] $db2start

03/25/2018 19:06:23 3 0 SQL1063N DB2START processing was successful.

03/25/2018 19:06:25 20 SQL1063N DB2START processing was successful.

03/25/2018 19:06:25 10 SQL1063N DB2START processing was successful.

03/25/2018 19:06:26 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

[db2inst1@testhost ~] $db2 connect to testdb

Database Connection Information

Database server = DB2/LINUXX8664 10.5.7

SQL authorization ID = DB2INST1

Local database alias = TESTDB

[db2inst1@testhost ~] $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 was damaged, so all applications processing the

Database were stopped. SQLSTATE=58031

You can see that you cannot create the same table as T1.

[db2inst1@testhost ~] $db2 "select * from T1"

SQL0901N The SQL statement or command failed because of a database system

Error. (Reason "sqlno_node_set_add]: rc (0)".)

SQLSTATE=58004

X

2018-03-25-19.08.45.376940-420 I2972162E756 LEVEL: Info

PID: 64558 TID: 140661319788288 PROC: db2sysc 0

INSTANCE: db2inst1 NODE: 000 DB: TESTDB

APPHDL: 0-62 APPID: * N0.db2inst1.180326020755

AUTHID: DB2INST1 HOSTNAME: testhost

EDUID: 64 EDUNAME: db2agent (TESTDB) 0

FUNCTION: DB2 UDB, access plan manager, sqlra_dumpEventsForType, probe:3317

DATA # 1:

Dumped APM/RDS event recorder information to file "/ home/db2inst1/sqllib/db2dump/FODC_AppErr_2018-03-25-19.08.43.975256_64558_64_000/TESTDB.20180325190845.000.SQLRA.sqlraLOW.events.bin".

If IBM service is contacted to help resolve a recovery problem, this file may be requested.

2018-03-25-19.08.45.378378-420 I2972919E756 LEVEL: Info

PID: 64558 TID: 140661319788288 PROC: db2sysc 0

INSTANCE: db2inst1 NODE: 000 DB: TESTDB

APPHDL: 0-62 APPID: * N0.db2inst1.180326020755

AUTHID: DB2INST1 HOSTNAME: testhost

EDUID: 64 EDUNAME: db2agent (TESTDB) 0

FUNCTION: DB2 UDB, access plan manager, sqlra_dumpEventsForType, probe:3317

DATA # 1:

Dumped APM/RDS event recorder information to file "/ home/db2inst1/sqllib/db2dump/FODC_AppErr_2018-03-25-19.08.43.975256_64558_64_000/TESTDB.20180325190845.000.SQLRA.sqlraMED.events.bin".

If IBM service is contacted to help resolve a recovery problem, this file may be requested.

2018-03-25-19.08.45.379713-420 I2973676E757 LEVEL: Info

PID: 64558 TID: 140661319788288 PROC: db2sysc 0

INSTANCE: db2inst1 NODE: 000 DB: TESTDB

APPHDL: 0-62 APPID: * N0.db2inst1.180326020755

AUTHID: DB2INST1 HOSTNAME: testhost

EDUID: 64 EDUNAME: db2agent (TESTDB) 0

FUNCTION: DB2 UDB, access plan manager, sqlra_dumpEventsForType, probe:3317

DATA # 1:

Dumped APM/RDS event recorder information to file "/ home/db2inst1/sqllib/db2dump/FODC_AppErr_2018-03-25-19.08.43.975256_64558_64_000/TESTDB.20180325190845.000.SQLRA.sqlraHIGH.events.bin".

If IBM service is contacted to help resolve a recovery problem, this file may be requested.

2018-03-25-19.08.45.380027-420 I2974434E233 LEVEL: Severe

PID:64558 TID:140661319788288 NODE:000 Title: RDS DBCB

Dump File:/home/db2inst1/sqllib/db2dump/FODC_AppErr_2018-03-25-19.08.43.975256_64558_64_000/64558.64.000.dump.bin

……

Recreate the edit recovery node in db2nodes.cfg

Here is the correct method, using "db2stop drop partitionnum" and, of course, some other commands as assistance. Mainly through "drop dbpartitionnum verify" to confirm that the current partition does not exist in any database on any partition group (database partition group), if it does, return SQL6035W, otherwise return SQL6034W. If SQL6035W is returned, you need to use the command "redistribute database partition group" to redistribute the partition group to remove the use of the current partition. Here is the specific process:

[db2inst1@testhost ~] $db2 "select * from syscat.dbpartitiongroupdef"

DBPGNAME DBPARTITIONNUM IN_USE

IBMCATGROUP 0 Y

IBMDEFAULTGROUP 0 Y

IBMDEFAULTGROUP 1 Y

IBMDEFAULTGROUP 2 Y

IBMDEFAULTGROUP 3 Y

IBMDEFAULTGROUP 4 Y

DATAGROUP 0 Y

DATAGROUP 1 Y

DATAGROUP 2 Y

DATAGROUP 3 Y

10 record (s) selected.

[db2inst1@testhost ~] $export DB2NODE=4

[db2inst1@testhost ~] $db2 terminate

DB20000I The TERMINATE command completed successfully.

[db2inst1@testhost ~] $db2 drop dbpartitionnum verify

SQL6035W Database partition "4" is being used by database "TESTDB".

[db2inst1@testhost ~] $export DB2NODE=0

[db2inst1@testhost ~] $db2 terminate

[db2inst1@testhost ~] $db2 connect to testdb

[db2inst1@testhost ~] $db2 "redistribute database partition group DATAGROUP uniform drop dbpartitionnums (4)"

DB20000I The REDISTRIBUTE NODEGROUP command completed successfully.

[db2inst1@testhost ~] $db2 force applications all

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

[db2inst1@testhost ~] $export DB2NODE=4

[db2inst1@testhost ~] $db2 terminate

DB20000I The TERMINATE command completed successfully.

[db2inst1@testhost ~] $db2 drop dbpartitionnum verify

SQL6035W Database partition "4" is being used by database "TESTDB".

[db2inst1@testhost ~] $db2stop drop dbpartitionnum 4

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? (YBO) y

03/25/2018 23:02:06 3 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:07 10 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:07 0 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:07 20 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:07 4 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:11 3 0 SQL1063N DB2START processing was successful.

03/25/2018 23:02:11 10 SQL1063N DB2START processing was successful.

03/25/2018 23:02:11 4 0 SQL1063N DB2START processing was successful.

03/25/2018 23:02:11 20 SQL1063N DB2START processing was successful.

03/25/2018 23:02:11 0 0 SQL1063N DB2START processing was successful.

03x25 is not being used by any databases. 2018 23:02:14 4 0 SQL6034W Database partition "4".

03/25/2018 23:02:17 0 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:18 20 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:19 10 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:19 3 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:19 4 0 SQL1064N DB2STOP processing was successful.

03/25/2018 23:02:21 4 0 SQL1064N DB2STOP processing was successful.

SQL1064N DB2STOP processing was successful.

[db2inst1@testhost ~] $cat sqllib/db2nodes.cfg

0 testhost 0

1 testhost 1

2 testhost 2

3 testhost 3

[db2inst1@testhost ~] $db2start

03/25/2018 23:04:32 20 SQL1063N DB2START processing was successful.

03/25/2018 23:04:32 10 SQL1063N DB2START processing was successful.

03/25/2018 23:04:33 3 0 SQL1063N DB2START processing was successful.

03/25/2018 23:04:34 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

[db2inst1@testhost ~] $db2 connect to testdb

SQL1469N Instance "db2inst1" (database partition number "0") does not have

Node "4" specified in its db2nodes.cfg file. SQLSTATE=08004

[db2inst1@testhost ~] $export DB2NODE=0

[db2inst1@testhost ~] $db2 terminate

DB20000I The TERMINATE command completed successfully.

[db2inst1@testhost ~] $db2 create table T2 like T1

DB20000I The SQL command completed successfully.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.