如何正確刪除數(shù)據(jù)庫(kù)分區(qū)

從DPF刪除一個(gè)數(shù)據(jù)庫(kù)分區(qū)決不是像直接編輯db2nodes.cfg那么簡(jiǎn)單，正確的做法是使用命令“db2stop drop partitionnum”。

創(chuàng)新互聯(lián)主營(yíng)東營(yíng)網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都app軟件開發(fā),東營(yíng)h5小程序開發(fā)搭建,東營(yíng)網(wǎng)站營(yíng)銷推廣歡迎東營(yíng)等地區(qū)企業(yè)咨詢

如果你直接編輯db2nodes.cfg刪除對(duì)應(yīng)分區(qū)那行或者那些行，而恰好有數(shù)據(jù)庫(kù)分區(qū)組(database partition group)還在使用被刪除這個(gè)或者這些分區(qū)，那么會(huì)導(dǎo)致問題。看下面的例子：

[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 1 0 SQL1064N DB2STOP processing was successful.

03/25/2018 18:54:41 2 0 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 2 0 SQL1063N DB2START processing was successful.

03/25/2018 19:06:25 1 0 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

可以看到無法創(chuàng)建與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 [100]:rc( 0) ".)

SQLSTATE=58004

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 : <preformatted>

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 : <preformatted>

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 : <preformatted>

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

……

重新創(chuàng)建在db2nodes.cfg中編輯恢復(fù)節(jié)點(diǎn)

下面是正確的方法，使用的是“db2stop drop partitionnum”，當(dāng)然還有一些其它命令做為輔助。主要是通過"drop dbpartitionnum verify"去確認(rèn)當(dāng)前分區(qū)不存在任何數(shù)據(jù)庫(kù)的任何分區(qū)組(database partition group)上，如果存在，返回SQL6035W，否則返回SQL6034W。如果返回的是SQL6035W，你則需要使用命令“redistribute database partition group”重分布分區(qū)組，去掉對(duì)當(dāng)前分區(qū)的使用。下面是具體過程：

[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 ? (y/n)y

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

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

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

03/25/2018 23:02:07 2 0 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 1 0 SQL1063N DB2START processing was successful.

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

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

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

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

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

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

03/25/2018 23:02:19 1 0 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 2 0 SQL1063N DB2START processing was successful.

03/25/2018 23:04:32 1 0 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.

新聞標(biāo)題：如何正確刪除數(shù)據(jù)庫(kù)分區(qū)
網(wǎng)站URL：http://bm7419.com/article18/pschdp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、網(wǎng)站營(yíng)銷、Google、品牌網(wǎng)站制作、App設(shè)計(jì)、虛擬主機(jī)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容