DB2 Support

在什么情况下访问计划不使用相应的index，怎样强制使用index
怎样导入存在换行自符的文件
DB2中Nickname与MQT的访问计划
DB2中当删除表空间的时候发生SQL0290

« |

DB2中当删除表空间的时候发生SQL0290

wangzhonnew | 28 六月, 2006 21:23

删除表空间是在数据库维护时经常使用的操作之一。但有的时候当表空间的容器出现问题的时候此操作会返回SQL0290的错误。

以下是一次真实的错误解决的纪录。

问题描述：

当运行drop tablespace命令的时候返回SQL293和SQL0290错误，容器为裸设备。

需要的分析文件：

db2support . –d <dbname> -c –g –s

db2 list tablespace containers for <tbspace id> show detail

解决步骤：

首先尝试Alter tablespace:

$ db2 "ALTER TABLESPACE <tbspace name> SWITCH ONLINE"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0293N Error accessing a table space container. SQLSTATE=57048

然后察看容器状态：

$ db2 list tablespace containers for <tbspace id> show detail

Tablespace Containers for Tablespace <tbspace id>

Container ID = 0
Name = <name>
Type = Disk
Total pages = 128000
Useable pages = 127968
Accessible = No

继续察看db2diag.log：

2006-02-28-11.31.57.575929-480 I1572223C461 LEVEL: Error
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
RETCODE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

2006-02-28-11.32.05.871504-480 I1572685C461 LEVEL: Error
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
RETCODE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

2006-02-28-11.32.09.110402-480 I1573147C2745 LEVEL: Severe
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx .060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbContainerTagIsValid,
probe:100
MESSAGE : *pTag(CONTAINER_TAG)
DATA #1 : Hexdump, 512 bytes
0x2015B200 : 4341 4C2E 6462 326C 6177 312E 3036 3032 CAL.db2law1.0602
0x2015B210 : 3237 3139 3235 3131 2020 2020 200A 3078 27192511 .0x
0x2015B220 : 3334 3835 3037 3030 2038 3220 2020 2020 34850700 82
0x2015B230 : 2020 5B30 3030 2D30 3030 3832 5D20 3120 [000-00082] 1
0x2015B240 : 2020 2020 2020 2020 2031 3130 3433 3620 110436
0x2015B250 : 2020 2020 4C6F 636B 2D77 6169 7420 2020 Lock-wait
0x2015B260 : 2020 2020 2020 2020 2020 2020 3137 3020 170
0x2015B270 : 2020 2020 2031 2020 2020 2020 2020 2020 1
0x2015B280 : 3135 3620 2020 2020 2031 2020 2020 2020 156 1
0x2015B290 : 2020 2020 4741 3844 3830 3538 2E41 3330 GA8D8058.A30
0x2015B2A0 : 442E 3031 3946 3437 3139 3133 3032 2020 D.019F47191302
0x2015B2B0 : 2020 2020 0A30 7833 3438 4243 4333 3020 .0x348BCC30
0x2015B2C0 : 3731 2020 2020 2020 205B 3030 302D 3030 71 [000-00
0x2015B2D0 : 3037 315D 2031 2020 2020 2020 2020 2020 071] 1
0x2015B2E0 : 3131 3235 3330 2020 2020 204C 6F63 6B2D 112530 Lock-
0x2015B2F0 : 7761 6974 2020 2020 2020 2020 2020 2020 wait
0x2015B300 : 2020 2032 3230 2020 2020 2020 3120 2020 220 1
0x2015B310 : 2020 2020 2020 2031 3635 2020 2020 2020 165
0x2015B320 : 3120 2020 2020 2020 2020 2047 4138 4438 1 GA8D8
0x2015B330 : 3035 382E 4733 3043 2E30 3139 3430 3731 058.G30C.0194071
0x2015B340 : 3833 3830 3220 2020 2020 200A 3078 3334 83802 .0x34
0x2015B350 : 3842 3641 3930 2036 3520 2020 2020 2020 8B6A90 65
0x2015B360 : 5B30 3030 2D30 3030 3635 5D20 3120 2020 [000-00065] 1
0x2015B370 : 2020 2020 2020 2031 3137 3437 3220 2020 117472
0x2015B380 : 2020 4C6F 636B 2D77 6169 7420 2020 2020 Lock-wait
0x2015B390 : 2020 2020 2020 2020 2020 3232 3220 2020 222
0x2015B3A0 : 2020 2031 2020 2020 2020 2020 2020 3020 1 0
0x2015B3B0 : 2020 2020 2020 2030 2020 2020 2020 2020 0
0x2015B3C0 : 2020 4741 3844 3830 3538 2E4A 3230 422E GA8D8058.J20B.
0x2015B3D0 : 3031 4245 4337 3138 3239 3233 2020 2020 01BEC7182923
0x2015B3E0 : 2020 0A30 7833 3438 4236 3233 3020 3634 .0x348B6230 64
0x2015B3F0 : 2020 2020 2020 205B 3030 302D 3030 3036 [000-0006

2006-02-28-11.32.09.110826-480 I1575893C2744 LEVEL: Severe
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbContainerTagIsValid,
probe:200
MESSAGE : iTag(CONTAINER_TAG)
DATA #1 : Hexdump, 512 bytes
0x2FF102D8 : 4442 3243 4F4E 5400 6177 312E 672A 60E8 DB2CONT.aw1.g*`.
0x2FF102E8 : 0000 000A 0000 0000 2020 2020 0000 0001 ........ ....
0x2FF102F8 : 0000 047B 8EFE 4442 324C 4157 3120 0064 ...{..DB2LAW1 .d
0x2FF10308 : 6232 6C61 7731 0043 0000 0000 0000 047B b2law1.C.......{
0x2FF10318 : 8EFE 2F68 6F6D 652F 6462 326C 6177 312F ../home/db2law1/
0x2FF10328 : 6462 326C 6177 312F 4E4F 4445 3030 3030 db2law1/NODE0000
0x2FF10338 : 2F53 514C 3030 3030 312F 0000 0000 0000 /SQL00001/......
0x2FF10348 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10358 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10368 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10378 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10388 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10398 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103A8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103B8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103C8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103D8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103E8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF103F8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10408 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10418 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10428 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10438 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10448 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10458 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10468 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10478 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10488 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF10498 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF104A8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF104B8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x2FF104C8 : 0000 0000 0000 0000 0000 0000 0440 21B3 .............@!.

2006-02-28-11.32.09.111155-480 I1578638C479 LEVEL: Severe
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx .060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbContainerTagIsValid,
probe:900
MESSAGE : sqloHasSameVnode returned false
DATA #1 : Hexdump, 4 bytes
0x2FF102B4 : 0000 0000 ....

2006-02-28-11.32.09.111361-480 I1579118C449 LEVEL: Error
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSDoContainerOp, probe:840
RETCODE : ZRC=0x8402001E=-2080243682=SQLB_CONTAINER_NOT_ACCESSIBLE
"Container not accessible"

2006-02-28-11.32.09.111590-480 I1579568C425 LEVEL: Error
PID : 133398 TID : 1 PROC : db2agent
(xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL. xxxx.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSDoContainerOp, probe:840
MESSAGE : Error checking container 0 (/dev/ xxxx) for tbsp 10. Rc =
8402001E

从这里可以清楚地看到pTag和iTag有明显的不同，说明容器的tag出现了问题。

在这里，iTag是期望得到的值，pTag是真实得到的值，当db2检查其中的一些数据位时发现不同的时候就会在db2diag.log里面现实这些信息，同时返回容器错误的信息。

既然知道了是容器的tag问题，我们只能删除这个表空间然后重建。这样就又回到我们的初始问题：删除表空间错误。

2006-02-28-11.31.57.575929-480 I1572223C461 LEVEL: Error
PID : 133398 TID : 1 PROC : db2agent (xxxx) 0
INSTANCE: xxxx NODE : 000 DB : xxxx
APPHDL : 0-277 APPID: *LOCAL.db2law1.060228193159
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
RETCODE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

在这种情况下我们可以使用db2untag移除tag然后restart database drop pending tablespace

$ db2untag -f /dev/retttbs

"restart database <dbname> drop pending tablespaces ( <tablespace name> )"

需要注意的是一定要同时移除数据表空间和索引表空间，否则就会出现错误报告。

当表数据库再次启动后表空间则处于drop pending状态，然后只需要删除掉就可以了。

is it sin? [url=][/url] [url=][/url] [url=][/url] [url=][/url] (c) [回复]

is it sin?
[url=][/url]
[url=][/url]
[url=][/url]
[url=][/url] (c)

ysmkg | 19/02/2008, 13:28

[回复]

the membrane. www.nationalwebmeds.com phentermine diet This potentiates the membrane. phentermine diet This potentiates the membrane. [url=www.nationalwebmeds.com]phentermine diet[/url] This potentiates the membrane. [URL]www.nationalwebmeds.com[/URL] phentermine diet This potentiates the inhibitory www.myinternetmedications.com phentermine no prescription effect of the inhibitory phentermine no prescription effect of the inhibitory [url=www.myinternetmedications.com]phentermine no prescription[/url] effect of the inhibitory [URL]www.myinternetmedications.com[/URL] phentermine no prescription effect of the available www.discountmedsplace.com buy phentermine online GABA leading the available buy phentermine online GABA leading the available [url=www.discountmedsplace.com]buy phentermine online[/url] GABA leading the available [URL]www.discountmedsplace.com[/URL] buy phentermine online GABA leading to sedatory www.onlinemedstoday.com buying viagra and anxiolytic to sedatory buying viagra and anxiolytic to sedatory [url=www.onlinemedstoday.com]buying viagra[/url] and anxiolytic to sedatory [URL]www.onlinemedstoday.com[/URL] buying viagra and anxiolytic effects. As www.medspointstore.com cheapest viagra mentioned, different effects. As cheapest viagra mentioned, different effects. As [url=www.medspointstore.com]cheapest viagra[/url] mentioned, different effects. As [URL]www.medspointstore.com[/URL] cheapest viagra mentioned, different

Eepnnj Eepnnjucb | 29/06/2008, 04:36

[回复]

needed]
It is www.onlinemedscentral.com phentermine cod suggested that needed]
It is phentermine cod suggested that needed]
It is [url=www.onlinemedscentral.com]phentermine cod[/url] suggested that needed]
It is [URL]www.onlinemedscentral.com[/URL] phentermine cod suggested that tramadol could www.bestmedsplace.com tramadol ultram be effective tramadol could tramadol ultram be effective tramadol could [url=www.bestmedsplace.com]tramadol ultram[/url] be effective tramadol could [URL]www.bestmedsplace.com[/URL] tramadol ultram be effective for alleviating www.bargainmedsplace.com generic phentermine symptoms of for alleviating generic phentermine symptoms of for alleviating [url=www.bargainmedsplace.com]generic phentermine[/url] symptoms of for alleviating [URL]www.bargainmedsplace.com[/URL] generic phentermine symptoms of depression and www.medsthrifty.com phentermine no prescription anxiety because depression and phentermine no prescription anxiety because depression and [url=www.medsthrifty.com]phentermine no prescription[/url] anxiety because depression and [URL]www.medsthrifty.com[/URL] phentermine no prescription anxiety because of its www.bestcheapmedsworld.com discount tramadol action on of its discount tramadol action on of its [url=www.bestcheapmedsworld.com]discount tramadol[/url] action on of its [URL]www.bestcheapmedsworld.com[/URL] discount tramadol action on

Lvfwnx Lvfwnxpok | 29/06/2008, 05:18

If You want to delete your site from my base [回复]

to: Admin - If You want to delete your site from my spam list, please sent url of your domain to my e-mail: stop.spam.today@gmail.com
And I will remove your site from my base within 24 hours
webmastegz

Crircavackash | 17/11/2008, 06:41

发表评论

标题

在此添加评论

称呼

邮箱地址（可选）

个人主页（可选）

七月 2006
六月 2006

一般分类 [4]