将表标记为无效

字数 81346阅读 1567评论 0赞 0

<div id="blog_article" class="diaryViewC ">
<div class="resizeimg">
<div class="resizeimg2">
<div class="resizeimg3">
<div class="resizeimg4">
<div>DB2数据库表损坏无法删除故障处理一例<br><br>一、故障现象：<br>删除表空间过程提示有表空间无法正常删除：<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop tablespace tbs_dw<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL0282N  Table space "TBS_DW" cannot be dropped because at least one of the <br>tables in it, "PDW.T_DWU_PAR_IN_D", has one or more of its parts in another <br>table space.  SQLSTATE=55024</div>
<div>因为tbs_dw上表部分索引创建在tbs_idx表空间，且不能删除。在计划删除TBS_DW表空间上以下几张表分表报错：<br>[DWE3:/db2home/db2inst1/fengsh]db2
 "select substr(tabschema,1,20),substr(tabname,1,60) as tabname,'db2 
drop table '||rtrim(tabschema)||'.'||tabname||';' as sql from 
syscat.tables t1,syscat.tablespaces t2 where t1.tbspaceid=t2.tbspaceid 
and t2.tbspace='TBS_DW'"</div>
<div>1                    
TABNAME                                                      
SQL                                                                                                                                                                                                                                                                             
 <br>-------------------- 
------------------------------------------------------------ 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>PDW                  T_DWU_MARK_USER_TOT_M1009 <br>                                 db2 drop table PDW.T_DWU_MARK_USER_TOT_M1009 <br>;                                                                                                                                                                                                                                
 <br>PDW                  
T_DWU_PAR_IN_D                                               db2 drop 
table 
PDW.T_DWU_PAR_IN_D;                                                                                                                                                                                                                                              
 <br>PDW                  
T_DWU_PAR_USER_STAT_D                                        db2 drop 
table 
PDW.T_DWU_PAR_USER_STAT_D;                                                                                                                                                                                                                                       
 <br>PDW                  
T_DWU_PAR_PPS_D_HIS                                          db2 drop 
table 
PDW.T_DWU_PAR_PPS_D_HIS;                                                                                                                                                                                                                                         
 <br>PDW                  T_DWU_MARK_USER_TOT_M1008<br>                                  db2 drop table PDW.T_DWU_MARK_USER_TOT_M1008<br>;                                                                                                                                                                                                                                 
 </div>
<div>  5 record(s) selected.</div>
<div><br>其中有两种不同的错误，具体错误如下：<br>[DWE3:/db2home/db2inst1/fengsh] db2 drop table PDW.T_DWU_MARK_USER_TOT_M1009 <br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL0204N  "PDW.T_DWU_MARK_USER_TOT_M1009" is an undefined name.  <br>SQLSTATE=42704<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop table PDW.T_DWU_PAR_IN_D; <br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop table PDW.T_DWU_PAR_USER_STAT_D;<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop table PDW.T_DWU_PAR_PPS_D_HIS;<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop table PDW.T_DWU_MARK_USER_TOT_M1008<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL0204N  "PDW.T_DWU_MARK_USER_TOT_M1008" is an undefined name.  <br>SQLSTATE=42704</div>
<div> </div>
<div>问题1：表删除报坏页错误<br>错误：<br>[DWE3:/db2home/db2inst1/fengsh]db2 drop table PDW.T_DWU_PAR_USER_STAT_D;<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034</div>
<div>[DWE3:/db2home/db2inst1/sqllib/db2dump]db2 ? SQL1007N</div>
<div><br>SQL1007N Error in finding pages for an object in a table <br>          space.  </div>
<div>Explanation: </div>
<div>There are corrupted internal database pages or internal logic <br>error for a table space.  </div>
<div>User Response: </div>
<div>Discontinue use of the object or table space. Contact IBM service <br>to inspect the object and the table space.  </div>
<div> sqlcode :   -1007 </div>
<div> sqlstate :  58034 </div>
<div><br>日志：<br>2012-01-12-16.03.08.213769+480 I83658A434         LEVEL: Error<br>PID     : 663742               TID  : 1           PROC : db2agntp (BSSDB) 1<br>INSTANCE: db2inst1             NODE : 001         DB   : BSSDB<br>APPHDL  : 0-71                 APPID: *N0.db2inst1.120112071850<br>AUTHID  : DB2INST1<br>FUNCTION: DB2 UDB, buffer pool services, sqlbDMSMapObj2Pool, probe:840<br>MESSAGE : ZRC=0x8402001A=-2080243686=SQLB_EMP_MAP_INFO_END "EMP MAP INFO END"</div>
<div>2012-01-12-16.03.08.214143+480 I84093A516         LEVEL: Error<br>PID     : 663742               TID  : 1           PROC : db2agntp (BSSDB) 1<br>INSTANCE: db2inst1             NODE : 001         DB   : BSSDB<br>APPHDL  : 0-71                 APPID: *N0.db2inst1.120112071850<br>AUTHID  : DB2INST1<br>FUNCTION: DB2 UDB, buffer pool services, sqlbDMSMapObj2Pool, probe:840<br>DATA #1 : String, 132 bytes<br> Obj={pool:8;obj:1190;type:0}
 State=x27 Parent={8;1190}, EM=982552, PP0=982568 Page=1828 Cont=0 
Offset=4578027520 BlkSize=282493056</div>
<div>2012-01-12-16.03.08.214356+480 I84610A918         LEVEL: Error<br>PID     : 663742               TID  : 1           PROC : db2agntp (BSSDB) 1<br>INSTANCE: db2inst1             NODE : 001         DB   : BSSDB<br>APPHDL  : 0-71                 APPID: *N0.db2inst1.120112071850<br>AUTHID  : DB2INST1<br>FUNCTION: DB2 UDB, buffer pool services, sqlbDMSMapObj2Pool, probe:0<br>DATA #1 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 72 bytes<br>    Obj: {pool:8;obj:1190;type:0} Parent={8;1190}<br>  lifeLSN:       3A06544E2465<br>  tid:           0 0  0<br>  extentAnchor:              982552<br>  initEmpPages:                   0<br>  poolPage0:                 982568<br>  poolflags:                    122<br>  objectState:                   27<br>  lastSMP:                        0<br>  pageSize:                   16384<br>  extentSize:                     8<br>  bufferPoolID:                   2<br>  partialHash:             77987848<br>  bufferPool:    0x0780000233f696e0</div>
<div>2012-01-12-16.03.08.214715+480 I85529A460         LEVEL: Warning<br>PID     : 791048               TID  : 1           PROC : db2agent (BSSDB) 0<br>INSTANCE: db2inst1             NODE : 000         DB   : BSSDB<br>APPHDL  : 0-71                 APPID: *N0.db2inst1.120112071850<br>AUTHID  : DB2INST1<br>FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:310<br>RETCODE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT<br>          "SQLCA has already been built"</div>
<div>2012-01-12-16.03.08.215042+480 I85990A446         LEVEL: Warning<br>PID     : 791048               TID  : 1           PROC : db2agent (BSSDB) 0<br>INSTANCE: db2inst1             NODE : 000         DB   : BSSDB<br>APPHDL  : 0-71                 APPID: *N0.db2inst1.120112071850<br>AUTHID  : DB2INST1<br>FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:311<br>RETCODE : ZRC=0x8402001A=-2080243686=SQLB_EMP_MAP_INFO_END "EMP MAP INFO END"</div>
<div> </div>
<div>问题2：表删除报undefined name<br>[DWE3:/db2home/db2inst1/fengsh]db2 "drop 
table 
PDW.T_DWU_MARK_USER_TOT_M1008"                                                                               
 <br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL0204N  "PDW.T_DWU_MARK_USER_TOT_M1008" is an undefined name.  <br>SQLSTATE=42704</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 " select 
tabschema,rtrim(tabname),type,status,tbspace from  syscat.tables where 
tabname like 'T_DWU_MARK_USER_TOT_M1008%'"                    </div>
<div>TABSCHEMA                                                                                                                       
 
2                                                                                                                               
 TYPE STATUS 
TBSPACE                                                                                                                        
 <br>--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 ---- ------ 
--------------------------------------------------------------------------------------------------------------------------------<br>PDW                                                                                                                             
 T_DWU_MARK_USER_TOT_M1008<br>                                                                                                     
 T    N      
TBS_DW                                                                                                                         
 </div>
<div>  1 record(s) selected.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 " select 
tabschema,rtrim(tabname),type,status,tbspace from  syscat.tables where 
tabname = 'T_DWU_MARK_USER_TOT_M1008'"    </div>
<div>TABSCHEMA                                                                                                                       
 
2                                                                                                                               
 TYPE STATUS 
TBSPACE                                                                                                                        
 <br>--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 ---- ------ 
--------------------------------------------------------------------------------------------------------------------------------</div>
<div>  0 record(s) selected.</div>
<div><br>[DWE3:/db2home/db2inst1/fengsh]db2 list tables for schema PDW|grep -i T_DWU_MARK_USER_TOT_M1008<br>T_DWU_MARK_USER_TOT_M1008</div>
<div> </div>
<div> </div>
<div>   二、处理过程：</div>
<div>＝＝＝＝＝＝＝＝＝＝＝＝＝＝<br>单独删除索引也报错<br>＝＝＝＝＝＝＝＝＝＝＝＝＝＝<br>根据查询数据字典，发现此表上有一个索引，创建约束系统创建的，问题是删除约束时也报错，page错误</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 "select 
indschema,indname,tabschema,tabname from syscat.indexes where 
tabname='T_DWU_PAR_IN_D'"           </div>
<div>INDSCHEMA                                                                                                                       
 
INDNAME                                                                                                                         
 
TABSCHEMA                                                                                                                       
 
TABNAME                                                                                                                        
 <br>--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------<br>SYSIBM                                                                                                                          
 
SQL070518180304860                                                                                                              
 
PDW                                                                                                                             
 
T_DWU_PAR_IN_D                                                                                                                 
 </div>
<div>  1 record(s) selected.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 drop index 
sysibm.SQL070518180304860                                                          
 <br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL0669N  A system required index cannot be dropped explicitly.  <br>SQLSTATE=42917<br>[DWE3:/db2home/db2inst1/fengsh]db2 ? SQL0669N</div>
<div><br>SQL0669N A system required index cannot be dropped explicitly.  </div>
<div>Explanation: </div>
<div>The DROP INDEX statement attempted to drop an index required to: </div>
<div>o   enforce the primary key constraint on a table </div>
<div>o   enforce a unique constraint on a table </div>
<div>o   enforce the uniqueness of the object identifier (OID) column  <br>    of a typed table hierarchy </div>
<div>o   maintain a replicated materialized query table </div>
<div>o   maintain an XML column in the table.  </div>
<div> </div>
<div> A system required index cannot be dropped using the DROP INDEX <br>statement.  </div>
<div> The statement cannot be processed.  The specified index is not <br>dropped.  </div>
<div>User Response: </div>
<div>If you do not want to keep the primary or unique constraint, use <br>the DROP PRIMARY KEY clause or the DROP CONSTRAINT clause of the <br>ALTER TABLE statement to remove the primary key or unique <br>constraint.  If the index was created only for enforcing the <br>primary or unique key, then the index will be dropped.  If not, <br>the DROP INDEX statement could then be processed.  </div>
<div> The index for an OID column can only be dropped by dropping the <br>table.  </div>
<div> The index required to maintain a replicated materialized query <br>table can only be dropped by first dropping the replicated <br>materialized query table.  </div>
<div> The system-required indexes associated with one or more XML <br>columns in a table cannot be dropped explicitly.  Such indexes <br>are maintained by the database manager to support the XML column <br>in the table.  The index specified in the DROP INDEX statement <br>cannot be dropped without dropping the table.  </div>
<div> sqlcode :  -669 </div>
<div> sqlstate :  42917 </div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 "describe table pdw.T_DWU_PAR_IN_D<br>> "</div>
<div>Column                         Type      Type<br>name                           schema    name               Length   Scale Nulls<br>------------------------------ --------- ------------------ -------- ----- ------<br>DEAL_DATE                      SYSIBM    DECIMAL                   8     0 No    <br>REGION_CDE                     SYSIBM    CHARACTER                 2     0 Yes   <br>CITY_CDE                       SYSIBM    CHARACTER                 2     0 Yes   <br>SVC_TYPE_CDE                   SYSIBM    CHARACTER                 2     0 Yes   <br>MSISDN                         SYSIBM    CHARACTER                11     0 No    <br>USER_TYPE                      SYSIBM    DECIMAL                   3     0 Yes   <br>USER_STAT                      SYSIBM    DECIMAL                   6     0 Yes   <br>IN_DNNR_TYPE                   SYSIBM    DECIMAL                   6     0 Yes   <br>ACCT_BAL                       SYSIBM    DECIMAL                  12     3 Yes   <br>GUASHI_FLAG                    SYSIBM    VARCHAR                   3     0 Yes   <br>LOCK_STAT                      SYSIBM    VARCHAR                   3     0 Yes   <br>BLACK_STAT                     SYSIBM    VARCHAR                   3     0 Yes   <br>INVOLID_DATE                   SYSIBM    DECIMAL                   8     0 Yes   <br>ACTI_DATE                      SYSIBM    DECIMAL                   8     0 Yes   <br>COUT_LIMIT_FLAG                SYSIBM    VARCHAR                   3     0 Yes   <br>LAST_USER_SVC_STA              SYSIBM    DECIMAL                   6     0 Yes   <br>INNET_WAY                      SYSIBM    DECIMAL                   8     0 Yes   <br>NEW_UNUM_FLAG                  SYSIBM    DECIMAL                  16     0 Yes   <br>VALID_UNUM_FLAG                SYSIBM    DECIMAL                  16     0 Yes   <br>LOCK_UNUM_FLAG                 SYSIBM    DECIMAL                  16     0 Yes   <br>OVER_LOCK_UNUM_FLAG            SYSIBM    DECIMAL                  16     0 Yes   <br>CHARGE_UNUM_FLAG               SYSIBM    DECIMAL                  16     0 Yes   <br>BILL_UNUM_FLAG                 SYSIBM    DECIMAL                  16     0 Yes   <br>FIRST_ACT_DATE                 SYSIBM    DECIMAL                   8     0 Yes   <br>IN_RESV_DATE                   SYSIBM    DECIMAL                   8     0 Yes   <br>IN_LOCK_DATE                   SYSIBM    DECIMAL                   8     0 Yes   <br>BRAND_TYPE_CDE                 SYSIBM    VARCHAR                   2     0 Yes   </div>
<div>  27 record(s) selected.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2look -d bssdb -e -t T_DWU_PAR_IN_D<br>-- No userid was specified, db2look tries to use Environment variable USER<br>-- USER is: DB2INST1<br>-- The db2look utility will consider only the specified tables <br>-- Creating DDL for table(s)<br>-- This CLP file was created using DB2LOOK Version 9.1<br>-- Timestamp: Thu Jan 12 17:21:29 BEIST 2012<br>-- Database Name: BSSDB          <br>-- Database Manager Version: DB2/AIX64 Version 9.1.9       <br>-- Database Codepage: 1386<br>-- Database Collating Sequence is: UNIQUE</div>
<div><br>CONNECT TO BSSDB;</div>
<div>------------------------------------------------<br>-- DDL Statements for table "PDW     "."T_DWU_PAR_IN_D"<br>------------------------------------------------<br> </div>
<div>CREATE TABLE "PDW     "."T_DWU_PAR_IN_D"  (<br>                  "DEAL_DATE" DECIMAL(8,0) NOT NULL , <br>                  "REGION_CDE" CHAR(2) , <br>                  "CITY_CDE" CHAR(2) , <br>                  "SVC_TYPE_CDE" CHAR(2) , <br>                  "MSISDN" CHAR(11) NOT NULL , <br>                  "USER_TYPE" DECIMAL(3,0) , <br>                  "USER_STAT" DECIMAL(6,0) , <br>                  "IN_DNNR_TYPE" DECIMAL(6,0) , <br>                  "ACCT_BAL" DECIMAL(12,3) , <br>                  "GUASHI_FLAG" VARCHAR(3) , <br>                  "LOCK_STAT" VARCHAR(3) , <br>                  "BLACK_STAT" VARCHAR(3) , <br>                  "INVOLID_DATE" DECIMAL(8,0) , <br>                  "ACTI_DATE" DECIMAL(8,0) , <br>                  "COUT_LIMIT_FLAG" VARCHAR(3) , <br>                  "LAST_USER_SVC_STA" DECIMAL(6,0) , <br>                  "INNET_WAY" DECIMAL(8,0) , <br>                  "NEW_UNUM_FLAG" DECIMAL(16,0) , <br>                  "VALID_UNUM_FLAG" DECIMAL(16,0) , <br>                  "LOCK_UNUM_FLAG" DECIMAL(16,0) , <br>                  "OVER_LOCK_UNUM_FLAG" DECIMAL(16,0) , <br>                  "CHARGE_UNUM_FLAG" DECIMAL(16,0) , <br>                  "BILL_UNUM_FLAG" DECIMAL(16,0) , <br>                  "FIRST_ACT_DATE" DECIMAL(8,0) , <br>                  "IN_RESV_DATE" DECIMAL(8,0) , <br>                  "IN_LOCK_DATE" DECIMAL(8,0) , <br>                  "BRAND_TYPE_CDE" VARCHAR(2) )   <br>                 DISTRIBUTE BY HASH("MSISDN")   <br>                   IN "TBS_DW" INDEX IN "TBS_IDX" ; </div>
<div><br>-- DDL Statements for primary key on Table "PDW     "."T_DWU_PAR_IN_D"</div>
<div>ALTER TABLE "PDW     "."T_DWU_PAR_IN_D" <br>        ADD CONSTRAINT "P_KEY_1" PRIMARY KEY<br>                ("MSISDN",<br>                 "DEAL_DATE");</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<div>COMMIT WORK;</div>
<div>CONNECT RESET;</div>
<div>TERMINATE;</div>
<div><br>删除约束，也报数据页相关错误：<br>[DWE3:/db2home/db2inst1/fengsh]db2 alter table pdw.T_DWU_PAR_IN_D drop constraintP_KEY_1     <br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034</div>
<div> </div>
<div>根据IBM 800支持，可通过使用db2dart工具将表进行标记为不可用后，进行删除：</div>
<div>＝＝＝＝＝＝＝＝＝＝<br>mark table bad<br>＝＝＝＝＝＝＝＝＝＝</div>
<div>首先，将有问题的表在数据库里面查询出具体对象，表id，表空间id，后续要使用到：<br>[DWE3:/db2home/db2inst1/fengsh]db2
 -v "select substr(tabschema,1,20),substr(tabname,1,60) as 
tabname,t1.tableid,t2.tbspaceid from syscat.tables t1,syscat.tablespaces
 t2 where^J><br>select substr(tabschema,1,20),substr(tabname,1,60) as
 tabname,t1.tableid,t2.tbspaceid from syscat.tables 
t1,syscat.tablespaces t2 where<br>t1.tbspaceid=t2.tbspaceid and t2.tbspace='TBS_DW'</div>
<div>1                    TABNAME                                                      TABLEID TBSPACEID  <br>-------------------- ------------------------------------------------------------ ------- -----------<br>PDW                  T_DWU_MARK_USER_TOT_M1009 <br>                                    9344           8<br>PDW                  T_DWU_PAR_IN_D                                                   803           8<br>PDW                  T_DWU_PAR_USER_STAT_D                                           1190           8<br>PDW                  T_DWU_PAR_PPS_D_HIS                                             5143           8<br>PDW                  T_DWU_MARK_USER_TOT_M1008<br>                                     9358           8</div>
<div>  5 record(s) selected.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 "select TABNAME,TBSPACE,TABLEID,TBSPACEID from syscat.tables where tbspace='TBS_DW'"</div>
<div>TABNAME                                                                                                                         
 
TBSPACE                                                                                                                         
 TABLEID TBSPACEID<br>--------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------------------------
 ------- ---------<br>T_DWU_MARK_USER_TOT_M1009 <br>                                                                                                    
 
TBS_DW                                                                                                                             
 9344         8<br>T_DWU_PAR_IN_D                                                                                                                  
 
TBS_DW                                                                                                                              
 803         8<br>T_DWU_PAR_USER_STAT_D                                                                                                           
 
TBS_DW                                                                                                                             
 1190         8<br>T_DWU_PAR_PPS_D_HIS                                                                                                             
 
TBS_DW                                                                                                                             
 5143         8<br>T_DWU_MARK_USER_TOT_M1008<br>                                                                                                     
 
TBS_DW                                                                                                                             
 9358         8</div>
<div>  5 record(s) selected.</div>
<div> </div>
<div><br>停掉所有应用程序连接，停数据库实例<br>[DWE3:/db2home/db2inst1/fengsh]db2 force applications all<br>DB20000I  The FORCE APPLICATION command completed successfully.<br>DB21024I  This command is asynchronous and may not be effective immediately.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2stop<br>2012-01-13 14:35:31     0   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:31     1   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:32     2   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:33     3   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:34     5   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:34     4   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:35     7   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:35:35     6   0   SQL1064N  DB2STOP processing was successful.<br>SQL1064N  DB2STOP processing was successful.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]ipclean          <br>ipclean: Removing DB2 engine and client's IPC resources for db2inst1.</div>
<div> </div>
<div>使用db2dart /mt选项对表进行标记不可用，其中需输入IBM 800提供的操作密码：<br>[DWE3:/db2home/db2inst1/fengsh]db2dart bssdb /MT /tsi 8 /oi 1190</div>
<div>Please enter Table ID or name, tablespace ID, and password:<br>1190,8,<font color="#f00000">********（需IBM 800提供密码）</font></div>
<div><br>   Mark table phase start.<br>   Set drop-pending (unavailable) state for table objects.<br>    Set state for DAT object 1190 tablespace 8.<br>   Modification for page (obj rel 0, pool rel 982568) of pool ID (8) obj ID (1190), written out to disk successfully.<br>      Modification written out successfully.<br>   Mark table phase end.</div>
<div>         The requested DB2DART processing has completed successfully!<br>                        Complete DB2DART report found in:<br>/db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div><br>db2dart操作会自动生成一个日志：<br>[DWE3:/db2home/db2inst1/fengsh]more /db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div>______________________________________________________________________________</div>
<div> </div>
<div>        _______                    DART                   _______ </div>
<div>   D a t a b a s e   A n a l y s i s   a n d   R e p o r t i n g   T o o l</div>
<div>                           IBM    DB2    6000</div>
<div> </div>
<div> </div>
<div>______________________________________________________________________________</div>
<div>DART (V9.1)  Report:<br>2012-01-13-14.46.51.222837</div>
<div>            Database Name: BSSDB<br>            Report name: BSSDB.RPT<br>            Old report back-up: BSSDB.BAK<br>            Database Subdirectory: /db2inst1/NODE0001/SQL00001<br>            Operational Mode: Database Inspection Only (INSPECT)</div>
<div>______________________________________________________________________________<br>------------------------------------------------------------------------------</div>
<div><br>Action option: MT <br>Table-object-ID: 1190; Tablespace-ID: 8</div>
<div>Connecting to Buffer Pool Services...</div>
<div>   Mark table phase start.<br>   Set drop-pending (unavailable) state for table objects.<br>    Set state for DAT object 1190 tablespace 8.<br>   Modification for page (obj rel 0, pool rel 982568) of pool ID (8) obj ID (1190), written out to disk successfully.<br>      Modification written out successfully.<br>   Mark table phase end.</div>
<div>                     ______________________________________</div>
<div>         The requested DB2DART processing has completed successfully!<br>                     All operation completed without error;<br>                   no problems were detected in the database.<br>                     ______________________________________</div>
<div>                        Complete DB2DART report found in:<br>/db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div>    _______    D A R T    P R O C E S S I N G    C O M P L E T E    _______</div>
<div> </div>
<div><br>完成表可用性修改后，重新启动数据库实例：<br>[DWE3:/db2home/db2inst1/fengsh]db2start<br>2012-01-13 14:49:18     1   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:18     3   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:18     5   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:19     2   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:19     4   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:19     0   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:20     7   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:49:20     6   0   SQL1063N  DB2START processing was successful.<br>SQL1063N  DB2START processing was successful.<br>[DWE3:/db2home/db2inst1/fengsh]db2 connect to bssdb</div>
<div>   Database Connection Information</div>
<div> Database server        = DB2/AIX64 9.1.9<br> SQL authorization ID   = DB2INST1<br> Local database alias   = BSSDB</div>
<div> </div>
<div>再次执行删除表操作，表已经可正常完成删除：<br>[DWE3:/db2home/db2inst1/fengsh]db2 "drop table pdw.T_DWU_PAR_USER_STAT_D"<br>DB20000I  The SQL command completed successfully.<br>[DWE3:/db2home/db2inst1/fengsh]</div>
<div> </div>
<div>依此方法，对其他表使用db2dart修改标记后，进行表的删除。</div>
<div> </div>
<div>-----drop table </div>
<div>[DWE3:/db2home/db2inst1/fengsh]<br>[DWE3:/db2home/db2inst1/fengsh]db2 "drop table pdw.T_DWU_PAR_IN_D"<br>DB21034E  The command was processed as an SQL statement because it was not a <br>valid Command Line Processor command.  During SQL processing it returned:<br>SQL1007N  Error in finding pages for an object in a table space.  <br>SQLSTATE=58034<br>[DWE3:/db2home/db2inst1/fengsh]db2 force applications all<br>DB20000I  The FORCE APPLICATION command completed successfully.<br>DB21024I  This command is asynchronous and may not be effective immediately.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2stop force<br>2012-01-13 14:53:11     4   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:11     1   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:11     0   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:12     3   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:12     5   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:12     2   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:12     6   0   SQL1064N  DB2STOP processing was successful.<br>2012-01-13 14:53:12     7   0   SQL1064N  DB2STOP processing was successful.<br>SQL1064N  DB2STOP processing was successful.<br>[DWE3:/db2home/db2inst1/fengsh]db2dart bssdb /MT /tsi 8 /oi 803</div>
<div>Please enter Table ID or name, tablespace ID, and password:<br>803,8,IPXWNZUK</div>
<div><br>   Mark table phase start.<br>   Set drop-pending (unavailable) state for table objects.<br>    Set state for DAT object 803 tablespace 8.<br>   Modification for page (obj rel 0, pool rel 30787568) of pool ID (8) obj ID (803), written out to disk successfully.<br>      Modification written out successfully.<br>    Set state for INX object 463 tablespace 11.<br>   Modification for page (obj rel 0, pool rel 6224) of pool ID (11) obj ID (463), written out to disk successfully.<br>      Modification written out successfully.<br>   Mark table phase end.</div>
<div>         The requested DB2DART processing has completed successfully!<br>                        Complete DB2DART report found in:<br>/db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div>[DWE3:/db2home/db2inst1/fengsh]more /db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div>______________________________________________________________________________</div>
<div> </div>
<div>        _______                    DART                   _______ </div>
<div>   D a t a b a s e   A n a l y s i s   a n d   R e p o r t i n g   T o o l</div>
<div>                           IBM    DB2    6000</div>
<div> </div>
<div> </div>
<div>______________________________________________________________________________</div>
<div>DART (V9.1)  Report:<br>2012-01-13-14.54.06.182987</div>
<div>            Database Name: BSSDB<br>            Report name: BSSDB.RPT<br>            Old report back-up: BSSDB.BAK<br>            Database Subdirectory: /db2inst1/NODE0001/SQL00001<br>            Operational Mode: Database Inspection Only (INSPECT)</div>
<div>______________________________________________________________________________<br>------------------------------------------------------------------------------</div>
<div><br>Action option: MT <br>Table-object-ID: 803; Tablespace-ID: 8</div>
<div>Connecting to Buffer Pool Services...</div>
<div>   Mark table phase start.<br>   Set drop-pending (unavailable) state for table objects.<br>    Set state for DAT object 803 tablespace 8.<br>   Modification for page (obj rel 0, pool rel 30787568) of pool ID (8) obj ID (803), written out to disk successfully.<br>      Modification written out successfully.<br>    Set state for INX object 463 tablespace 11.<br>   Modification for page (obj rel 0, pool rel 6224) of pool ID (11) obj ID (463), written out to disk successfully.<br>      Modification written out successfully.<br>   Mark table phase end.</div>
<div>                     ______________________________________</div>
<div>         The requested DB2DART processing has completed successfully!<br>                     All operation completed without error;<br>                   no problems were detected in the database.<br>                     ______________________________________</div>
<div>                        Complete DB2DART report found in:<br>/db2home/db2inst1/sqllib/db2dump/DART0001/BSSDB.RPT</div>
<div>    _______    D A R T    P R O C E S S I N G    C O M P L E T E    _______</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2start<br>2012-01-13 14:54:29     3   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:29     1   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:30     0   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:30     5   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:30     2   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:30     7   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:30     4   0   SQL1063N  DB2START processing was successful.<br>2012-01-13 14:54:32     6   0   SQL1063N  DB2START processing was successful.<br>SQL1063N  DB2START processing was successful.</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 connect to bssdb</div>
<div>   Database Connection Information</div>
<div> Database server        = DB2/AIX64 9.1.9<br> SQL authorization ID   = DB2INST1<br> Local database alias   = BSSDB</div>
<div>[DWE3:/db2home/db2inst1/fengsh]db2 "drop table pdw.T_DWU_PAR_IN_D"<br>DB20000I  The SQL command completed successfully.<br>[DWE3:/db2home/db2inst1/fengsh]</div>
<div>[DWE3:/db2home/db2inst1/fengsh]<br>[DWE3:/db2home/db2inst1/fengsh]db2 "drop tablespace tbs_dw"<br>DB20000I  The SQL command completed successfully.<br>[DWE3:/db2home/db2inst1/fengsh]</div>
<div> </div>
<div><br>三、总结：</div>
<div>1）db2dart工具是非常使用的工具，可用于数据库数据完整性验证，可用于对数据库物理页的操作等。<br>2）db2dart工具涉及物理数据页的修改，需在数据库实例停止的情况下执行<br>3）db2dart /mt用于对表的进行标记时，需要使用IBM 800提供操作密码</div><div><br></div><div>崩溃恢复中要访问的页面已损坏，如何能启动数据库？？</div><div>今天<span href="http://www.db2china.net/club/tag.php?name=%E7%B3%BB%E7%BB%9F" class="t_tag">系统</span>碰到一个问题，经分析后确定问题原因如下：因<span href="http://www.db2china.net/club/tag.php?name=%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F" class="t_tag">操作系统</span>宕机而使<span href="http://www.db2china.net/club/tag.php?name=DB2" class="t_tag">DB2</span>崩溃，导致一个<span href="http://www.db2china.net/club/tag.php?name=%E7%B4%A2%E5%BC%95" class="t_tag">索引</span>在节点6上有页面损坏了。系统重启后，<span href="http://www.db2china.net/club/tag.php?name=%E6%95%B0%E6%8D%AE%E5%BA%93" class="t_tag">数据库</span>做崩溃恢复时刚好要访问到这个损坏了的索引，因此崩溃恢复不成功，<span href="http://www.db2china.net/club/tag.php?name=%E6%95%B0%E6%8D%AE" class="t_tag">数据</span>库的节点6不能连接。<br>
想<span href="http://www.db2china.net/club/tag.php?name=%E8%AF%B7%E6%95%99" class="t_tag">请教</span>下，有什么办法能够跳过崩溃恢复这步连上数据库，或者使崩溃恢复中不访问这个已损坏的索引？我想只要能启动节点6，重建这个索引问题就应该能<span href="http://www.db2china.net/club/tag.php?name=%E8%A7%A3%E5%86%B3" class="t_tag">解决</span>。盼指点！</div><div><br></div><div>答案解释：</div><div>可考虑将索引标记为无效的试试。<br>
db2dart <dbname> /MI /tsi <tbs-id> /oi <idx-id><br>
<br>
可参考我之前做的一个处理表的例子，处理表需ibm 800提供操作密码，索引好像不需要的。</div></div>
</div>
</div>
    </div></div>

数据库表 class command

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

将表标记为无效

添加新评论0 条评论

作者其他文章

相关问题