Table space access is not allowed.环境：DPF 环境，SUSE LINUX 11 +db2 V9.7目前重启库后，连库报 SQL0290N，查看db2diaglog发现存在bad pages。db2inst1@dm-data-8:/db2diaglog> db2levelDB21085I&&Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075" with level identifier "08060...环境：DPF 环境，SUSE LINUX 11 +db2 V9.7目前重启库后，连库报 SQL0290N，查看db2diaglog发现存在bad pages。> db2levelDB21085I&&Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075" with level identifier "".Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", and Fix Pack "5".Product is installed at "/opt/ibm/db2/V9.7".> db2 connect to hebdmSQL0290N&&Table space access is not allowed.&&SQLSTATE=55039==================DB2DIAGLOG日志如下================-11.27.32. I1E1563& && && && & LEVEL: EventPID& &&&: 3723& && && && && &&&TID&&: 368PROC : db2diagINSTANCE: db2inst1& && && && & NODE : 015FUNCTION: DB2 UDB, RAS/PD component, pdLogInternal, probe:120START& &: New Diagnostic Log fileDATA #1 : Build Level, 152 bytesInstance "db2inst1" uses "64" bits and DB2 code release "SQL09075"with level identifier "".Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", Fix Pack "5".DATA #2 : System Info, 440 bytesSystem: Linux dm-data-8 6 2 x86_64CPU: total:8 online:8 Cores per socket:4 Threading degree per core:1Physical Memory(MB): total:48172 free:35201Virtual&&Memory(MB): total:97324 free:84353Swap& &&&Memory(MB): total:49152 free:49152Kernel& &Params: msgMaxMessageSize:65536 msgMsgMap:65536 msgMaxQueueIDs:48128& && && && && &&&msgNumberOfHeaders:65536 msgMaxQueueSize:65536& && && && && &&&msgMaxSegmentSize:16 shmMax:4775807 shmMin:1& && && && && &&&shmIDs:12032 shmSegments:12032 semMap:256000 semIDs:12032& && && && && &&&semNum:256000 semUndo:256000 semNumPerID:1250 semOps:100& && && && && &&&semUndoSize:20 semMaxVal:32767 semAdjustOnExit:32767Cur cpu time limit (seconds)&&= 0xFFFFFFFFCur file size limit (bytes) = 0xFFFFFFFFCur data size (bytes)&&= 0xFFFFFFFFCur stack size (bytes)&&= 0xCur core size (bytes)&&= 0xCur memory size (bytes) = 0xFFFFFFFFnofiles (descriptors)&&= 0xInformation in this record is only valid at the time when this file wascreated (see this record's time stamp)-11.27.32. I& && && &&&LEVEL: EventPID& &&&: 3723& && && && && &&&TID&&: 368PROC : db2diagINSTANCE: db2inst1& && && && & NODE : 015FUNCTION: DB2 UDB, RAS/PD component, pdDiagArchiveDiagnosticLog, probe:88CREATE&&: DB2DIAG.LOG ARCHIVE : /db2diaglog/db2diag.log_-11.27.32 : successIMPACT&&: Potential-11.27.45. I& && && &&&LEVEL: WarningPID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, base sys utilities, sqledint, probe:30MESSAGE : Crash Recovery is needed. -11.27.51. I& && && & LEVEL: SeverePID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page, probe:2MESSAGE : ZRC=0x=SQLB_BADP "page is bad"& && && & DIA8400C A bad page was encountered.DATA #1 : String, 64 bytesError encountered trying to read a page - information follows : DATA #2 : String, 23 bytesPage verification errorDATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes31DATA #4 : Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 88 bytes& & Obj: {pool:6;obj:65534;type:14} Parent={6;65534}&&lifeLSN:& && & 0000&&tid:& && && &&&0 0&&0&&extentAnchor:& && && && && && & 0&&initEmpPages:& && && && && && & 0&&poolPage0:& && && && && && && & 0&&poolflags:& && && && && && &&&122&&objectState:& && && && && && &&&0&&lastSMP:& && && && && && && && &0&&pageSize:& && && && && && & 32768&&extentSize:& && && && && && &&&16&&bufferPoolID:& && && && && && & 2&&partialHash:& && && &&&&&bufferPool:& & 0xb97a0&&pdef:& && && & 0xc024120DATA #5 : Bitmask, 4 bytes0xDATA #6 : Page header, PD_TYPE_SQLB_PAGE_HEAD, 48 bytespageHead: {tbspSeed:0;obj:0;type:0} PPNum:0 OPNum:0&&begoff:& && && && && && && &&&0&&datlen:& && && && && && && &&&0&&pagebinx:& && && && && && && &0&&revnum:& && && && && && && &&&0&&pagelsn:& &&&0000&&flag:& && && && && && && && & 0&&future32:& && && && && && &&&0&&cbits1to31:& && && && && && & 0&&cbits32to63:& && && && && && &0DATA #7 : Bufferpool globals, PD_TYPE_SQLB_GLOBALS, 1256 bytes&&dbcb:& && && && && && &0xdb0e0&&bpcb:& && && && && && &0xb1a40&&ptbl:& && && && && && &0xb1bc0&&eduType:& && && && && &SQLB_EDU_AGENT&&eduCB:& && && && && &&&0xe0080&&qryData:& && && && && &0x0000&&qryDataSz:& && && && && && && && && && &0&&qryDataPos:& && && && && && && && && &&&0&&contQryData:& && && &&&0x0000&&contQryDataSz:& && && && && && && && &&&0&&contQryDataPos:& && && && && && && && & 0&&bglobals_flags:& && && && && && && &&&203&&dbmon_cb:& && && && &&&0x0000&&eduId:& && && && && &&&37&&curPfQ:& && && && && && && && && && && &2&&curRemHateListCounter:& && && && && &&&91&&debugInfo:& && && && & 0x0000&&pCurRecLsn:& && && && &0x0000&&clnrCB:& && && && && & 0x0000&&prefetchPriority:& && && && && && && &&&1&&agentPriorityWeight:& && &00&&staticStr:& && && && & &&numPFRequestsHeld:& && && && && && && & 0&&memAccessCount:& & m_bIsInitialized:& && && && && &&&false& & m_iDisabled:& && && && && && && && &&&0& & m_iAccessCount:& && && && && && && &&&0CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)&&[0] 0x3E80 pdLog + 0x398&&[1] 0xD8F71E _Z25sqlbLogReadAttemptFailurejP16SQLB_OBJECT_DESCjP9SQLB_PAGEibmjPcP12SQLB_GLOBALS + 0x1BE&&[2] 0xBF65 sqlbReadPage + 0xA17&&[3] 0x2C8E _Z19sqlbGetPageFromDiskP11SQLB_FIX_CBi + 0x474&&[4] 0x6458 _Z7sqlbfixP11SQLB_FIX_CB + 0x1076&&[5] 0x2A45 _Z24sqlbFindNewHighWaterMarktjP9SQLP_LSN8mP12SQLB_GLOBALS + 0x561&&[6] 0x1A09 _Z16sqlbDMSStartPoolP12SQLB_GLOBALSP12SQLB_POOL_CB + 0x28D&&[7] 0xD0A47 _Z14sqlbStartPoolsP12SQLB_GLOBALS + 0x21F&&[8] 0xFF57F sqlbinit + 0xB51&&[9] 0xF78E7 /nfs_db2home1/db2inst1/sqllib/lib64/libdb2e.so.1 + 0xEB78E7-11.27.51. E& && && &&&LEVEL: CriticalPID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbLogReadAttemptFailure, probe:10MESSAGE : ADM14001C&&An unexpected and critical error has occurred: "BadPage". & && && & The instance may have been shutdown as a result. "Automatic" FODC & && && & (First Occurrence Data Capture) has been invoked and diagnostic & && && & information has been recorded in directory & && && & "/db2diaglog/FODC_BadPage_-11.01.38.5/". Please & && && & look in this directory for detailed evidence about what happened and & && && & contact IBM support if necessary to diagnose the problem.-11.27.59. E& && && & LEVEL: WarningPID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbReadPage, probe:1199MESSAGE : ADM6006E&&DB2 encountered an error while reading page "31" from table & && && & space "6" for object "65534" (located at offset "31" of container & && && & "/db2fs2p15/HEBDM/TSD_DATA_CZ.dat").-11.27.59. I& && && & LEVEL: SeverePID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbFindNewHighWaterMark, probe:20MESSAGE : ZRC=0x8402008F=-=SQLB_READERR& && && & "Non-critical I/O or verification error encountered during page read from disk."DATA #1 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes31DATA #2 : Pool ID, PD_TYPE_SQLB_POOL_ID, 2 bytes6DATA #3 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes31DATA #4 : Page ID, PD_TYPE_SQLB_PAGE_ID, 4 bytes16DATA #5 : Codepath, 8 bytes1:3:4:60:64-11.27.59. I& && && & LEVEL: ErrorPID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbDMSStartPool, probe:7107MESSAGE : ZRC=0x8402008F=-=SQLB_READERR& && && & "Non-critical I/O or verification error encountered during page read from disk."DATA #1 : Tablespace 6 (TSD_DATA_CZ)-11.27.59. E& && && & LEVEL: InfoPID& &&&: 12078& && && && && & TID&&: 072PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 15-61& && && && && & APPID: *N15.db2inst1.AUTHID&&: DB2INST1EDUID& &: 37& && && && && && & EDUNAME: db2agent (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:30MESSAGE : ADM6023I&&The table space "TSD_DATA_CZ" (ID "6") is in state 0x"0".&&& && && & The table space cannot be accessed.&&Refer to the documentation for & && && & SQLCODE -290.关注问题115回答数据库管理员
, IBM谢谢，学习了，表空间的第1,2个exent的确实是65534。 [ 0x0e& &[ 0x0e& &[ 0x00& &[0003]& &&&4 0x40* [0004]& &&&4 0x00*&&[0005]& &&...谢谢，学习了，表空间的第1,2个exent的确实是65534。 [ 0x0e& &[ 0x0e& &[ 0x00& &[0003]& &&&4 0x40* [0004]& &&&4 0x00*&&[0005]& &&&5 0x40*&&[0006]& &&&5 0x00*&&[0007]& &&&5 0x41*像这个故障，没有备份，是不是要将表空间的数据db2dart出来？如果该表空间里的表的表结构也没有备份，还有办法恢复吗？赞同软件开发工程师
, IBM嗯，tablespace header坏了，需要restore嗯，tablespace header坏了，需要restore赞同系统架构师
, 华为-12.26.28. E7& && &LEVEL: ErrorPID& &&&: 12078& && && && && & TID&&: 552PROC : db2sysc 15INSTANCE: db2inst...-12.26.28. E7& && &LEVEL: ErrorPID& &&&: 12078& && && && && & TID&&: 552PROC : db2sysc 15INSTANCE: db2inst1& && && && & NODE : 015& && && & DB& &: HEBDMAPPHDL&&: 998-209& && && && &&&APPID: *N998.db2inst1.AUTHID&&: JOB_ETL EDUID& &: 47& && && && && && & EDUNAME: db2agntp (HEBDM) 15FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:63MESSAGE : ADM6049E&&The database cannot be restarted because one or more table & && && & spaces cannot be brought online. To restart the database specify the & && && & "DROP PENDING TABLESPACES" option on the RESTART DATABASE command. & && && & Putting a table space into the drop pending state means that no & && && & further access to the table space will be allowed.&&The contents of & && && & the table space will be inaccessible throughout the remainder of the & && && & lif and the only operation that will be allowed & && && & on that table space is "DROP TABLE SPACE". There is no way in which & && && & it can be brought back.&&It is important that you consider the & && && & consequences of this action as data can be lost as a result. Before & && && & proceeding consult the DB2 documentation and contact IBM support if & && && & necessary.&&The table spaces to specify in the DROP PENDING & && && & TABLESPACES list are: "TSD_DATA_CZ TSD_DATA_SCP TSD_DATA_TS & && && & TSM_APP_BD TSM_APP_QHD TSM_APP_XT TSM_APP_ZJK TSM_TMP_LF DB2STMP32K & && && & ".这是最新的表错，但是现在连不上库，不能看tablespace 的状态。赞同系统架构师
, 华为回复 2# tongjixianing & & 这表空间没有备份，有其他办法解决这个问题么？回复
tongjixianing & & 这表空间没有备份，有其他办法解决这个问题么？赞同软件开发工程师
, IBM没备份的话，只能开pmr了。没备份的话，只能开pmr了。赞同软件开发工程师
, IBM你的tablespace的extent size是32吗？你的tablespace的extent size是32吗？赞同数据库管理员
, IBM嗯，tablespace header坏了，需要restoretongjixianing 发表于
问一下，怎么判断出是tablespace header坏的，是根据Obj: {pool:6;obj:65534;type:14}的type 14判断的吗？type 0表示数据页，type 1表示索引页，不知道这个type 14表示啥。表空间的extent size应该是page...嗯，tablespace header坏了，需要restoretongjixianing 发表于
问一下，怎么判断出是tablespace header坏的，是根据Obj: {pool:6;obj:65534;type:14}的type 14判断的吗？type 0表示数据页，type 1表示索引页，不知道这个type 14表示啥。表空间的extent size应该是pageSize:& && && && && && & 32768extentSize:& && && && && && &&&16bufferPoolID:& && && && && && & 2赞同软件开发工程师
, IBM回复 7# kkchat & & thx，之前没注意看，其实坏是header extent的最后一个页，page header坏掉了。& & obj type 65534 是 tablespace header和SMP共享的。回复
kkchat & & thx，之前没注意看，其实坏是header extent的最后一个页，page header坏掉了。& & obj type 65534 是 tablespace header和SMP共享的。赞同软件开发工程师
, IBM回复 9# kkchat & &需要patch，但是不保证一定可以恢复。db2dart可能都不好使，因为SMP坏了，除非可以修复那个坏页。回复
kkchat & &需要patch，但是不保证一定可以恢复。db2dart可能都不好使，因为SMP坏了，除非可以修复那个坏页。赞同数据库管理员
, IBM回复 10# tongjixianing Good.回复
tongjixianing Good.赞同
撰写回答系统架构师, 华为关注1发布765回答404请稍候...博客访问： 286769
博文数量： 96
博客积分： 980
博客等级： 准尉
技术积分： 1426
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： DB2/Informix
Load作为DB2导入大量数据的工具，在数据迁移的过程中有着很重要的应用。下面对load的操作进行测试。
I、测试load对数据的可恢复性的影响
由于load是直接对数据文件的操作，因此会记录很少的日志。这对数据的可恢复性提出了要求。为了保证数据的可恢复性，load提供了参数选项来保证数据库的可恢复性。
1、COPY NO
这个是默认选项，在此选项下，load操作完毕后，load数据的表所在的表空间将处于backup pending的状态，必须对表空间进行备份之后，表才能够使用。演示如下：
load前的表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 3
&Name & & & & & & & & & & & & & & & & = SYSTOOLSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 4
&Name & & & & & & & & & & & & & & & & = SYSTOOLSTMPSPACE
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = User Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
进行数据的load操作：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| replace into tpcd.part"
SQL3109N &The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W &The utility is beginning the "LOAD" phase at time "02/11/2014
03:18:41.601877".
SQL3519W &Begin Load Consistency Point. Input record count = "0".
SQL3520W &Load Consistency Point was successful.
SQL3110N &The utility has completed processing. &"200000" rows were read from
the input file.
SQL3519W &Begin Load Consistency Point. Input record count = "200000".
SQL3520W &Load Consistency Point was successful.
SQL3515W &The utility has finished the "LOAD" phase at time "02/11/2014
03:18:42.290729".
Number of rows read & & & & = 200000
Number of rows skipped & & &= 0
Number of rows loaded & & & = 200000
Number of rows rejected & & = 0
Number of rows deleted & & &= 0
Number of rows committed & &= 200000
db2inst1@baseDB2:/db2home/test_data> db2set -all
[i] DB2OPTIONS=+c
[g] DB2SYSTEM=baseDB2
[g] DB2INSTDEF=db2inst1
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I &The SQL command completed successfully.
load操作成功后，查看表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0020
& &Detailed explanation:
& & &Backup pending
&Tablespace ID & & & & & & & & & & & &= 3
&Name & & & & & & & & & & & & & & & & = SYSTOOLSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 4
&Name & & & & & & & & & & & & & & & & = SYSTOOLSTMPSPACE
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = User Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
可见表空间已经处于backup pending的状态。此时对此表空间的表的操作会：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &200000
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.SUPPLIER"
-----------
& & & & & 0
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tocd.part"
DB21034E &The command was processed as an SQL statement because it was not a
valid Command Line Processor command. &During SQL processing it returned:
SQL0204N &"TOCD.PART" is an undefined name. &SQLSTATE=42704
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.SUPPLIER"
DB21034E &The command was processed as an SQL statement because it was not a
valid Command Line Processor command. &During SQL processing it returned:
SQL0290N &Table space access is not allowed. &SQLSTATE=55039
db2inst1@baseDB2:/db2home/test_data>
可见，在表空间backup pending的状态下，表空间上的表只能被select，而不能进行更新操作；
由于默认的load操作具有以上的特性，因此，在生产系统的工作时间，进行load操作时，一定不能采用此种操作方式，否则，只能对表空间进行备份来解决表空间的backup pending，而在表空间的backup pending状态没有解决的期间，对表空间上的表的更新操作都会很失败。
解决表空间的backup pending：对此表空间进行备份：
db2inst1@baseDB2:/db2home/test_data> cat test_reslove_tablespace_bakcup_pending.sh
sh get_tablespaces_status.sh &
db2 "backup database tpcd tablespace USERSPACE1 online to /dev/null"
processno=` jobs -l | awk '{print $3}'`
kill -9 $processno
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> cat get_tablespaces_status.sh
db2 connect to tpcd > /dev/null
while true
db2 list tablespaces >> tablespace_status.txt
db2inst1@baseDB2:/db2home/test_data>
查看备份时的表空间的状态：
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0820
& &Detailed explanation:
& & &Backup pending
& & &Backup in progress
备份成功后：
db2inst1@baseDB2:/db2home/test_data> sh test_reslove_tablespace_bakcup_pending.sh
Backup successful. The timestamp for this backup image is : 21
test_reslove_tablespace_bakcup_pending.sh: line 4: kill: Running: arguments must be process or job IDs
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
此时再对表进行更新操作：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.SUPPLIER"
SQL0100W &No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. &SQLSTATE=02000
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
已经可以操作了，表空间的状态正常了。
2、COPY YES
使用此选项，会建立一个导入数据的备份介质，如果需要前滚，会用此介质进行前滚恢复，将load操作对数据库的修改重新放入数据库（因为load操作本身记录的日志比较少，如果没有此导入数据的备份介质，从以前的备份进行数据库恢复时，在使用日志进行前滚到日志末尾时，load操作所做的修改将不会出现在数据库，而这样的话，就相当于损失了数据）。演示如下：
确认导入前数据库中的表的条数：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &200000
& 1 record(s) selected.
对数据库进行在线备份：
db2inst1@baseDB2:/db2home/test_data> db2 backup db tpcd online
Backup successful. The timestamp for this backup image is : 52
db2inst1@baseDB2:/db2home/test_data>
删除一些数据：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.part where P_PARTKEY in (159,161,150162)"
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &199995
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
进行带COPY YES选项的load操作：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| insert into tpcd.part copy yes to /db2home/test_data"
SQL3109N &The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W &The utility is beginning the "LOAD" phase at time "02/11/2014
05:19:28.685060".
SQL3519W &Begin Load Consistency Point. Input record count = "0".
SQL3520W &Load Consistency Point was successful.
SQL3110N &The utility has completed processing. &"200000" rows were read from
the input file.
SQL3519W &Begin Load Consistency Point. Input record count = "200000".
SQL3520W &Load Consistency Point was successful.
SQL3515W &The utility has finished the "LOAD" phase at time "02/11/2014
05:19:29.348648".
Number of rows read & & & & = 200000
Number of rows skipped & & &= 0
Number of rows loaded & & & = 200000
Number of rows rejected & & = 0
Number of rows deleted & & &= 0
Number of rows committed & &= 200000
操作成功后，查看表空间的状态：
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
表空间状态正常
同时在db2 "load from part.tbl of del modified by coldel| replace into tpcd.part&copy yes to /db2home/test_data" 这个to的路径下生成一个备份介质：
-rw------- 1 db2inst1 db2iadm1 14-02-11 05:07 TPCD.0.db2inst1.NODE0000.CATN
-rw-r----- 1 db2inst1 db2iadm1 &4-02-11 05:14 TPCD.4.db2inst1.NODE0000.CATN
-rw-r----- 1 db2inst1 db2iadm1 &4-02-11 05:19 TPCD.4.db2inst1.NODE0000.CATN
其中的4表示是load操作的备份介质。
查看导入之后的数据大小：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &399995
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
再删除一些数据：
db2inst1@baseDB2:/db2home/test_data> db2 "delete from tpcd.part where P_PARTKEY in (159,161,150162)"
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &399990
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
进行restore操作：
db2inst1@baseDB2:/db2home/test_data> db2 deactivate db tpcd
DB20000I &The DEACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 52
SQL2539W &Warning! &Restoring to an existing database that is the same as the
backup image database. &The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I &The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
进行前滚：
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
& & & & & & & & & & & & & & & & &Rollforward Status
&Input database alias & & & & & & & & & = tpcd
&Number of nodes have returned status & = 1
&Node number & & & & & & & & & & & & & &= 0
&Rollforward status & & & & & & & & & & = not pending
&Next log file to be read & & & & & & & =
&Log files processed & & & & & & & & & &= S0000000.LOG - S0000003.LOG
&Last committed transaction & & & & & & = -21.21.18.000000 UTC
DB20000I &The ROLLFORWARD command completed successfully.
查看数据库中的数据条数：
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I &The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
& &Database Connection Information
&Database server & & & &= DB2/LINUXX.0
&SQL authorization ID & = DB2INST1
&Local database alias & = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &399990
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
和load、delete操作之后的总数据条数一致。说明load导入的数据在rollforward的过程中通过load的数据备份介质进行了导入。
但如果把这个load的备份介质删除，进行恢复前滚的时候，又会怎样呢？测试如下：
移走load的备份：
db2inst1@baseDB2:/db2home/db2inst1> cd /db2home/test_data
db2inst1@baseDB2:/db2home/test_data> mv TPCD.4.* /db2home/db2inst1
db2inst1@baseDB2:/db2home/test_data> ls -rtl
total 277875
-rw-r--r-- 1 db2inst1 db2iadm1 & 4-02-07 11:38 supplier.tbl
-rw-r--r-- 1 db2inst1 db2iadm1 &4-02-07 11:38 part.tbl
-rw-r--r-- 1 db2inst1 db2iadm1 14-02-07 11:39 partsupp.tbl
-rwxr-xr-x 1 db2inst1 db2iadm1 & & &-07 11:43 dss.ri
-rwxr-xr-x 1 db2inst1 db2iadm1 & & &-07 11:43 dss.ddl
drwxr-xr-x 2 root & & root & & & & & 912
13:36 tpds
drwxr-xr-x 2 db2inst1 db2iadm1 & & & 376
07:04 1Day
-rw-r--r-- 1 db2inst1 db2iadm1 & & &-11 03:46 DART.INF
-rw-r--r-- 1 db2inst1 db2iadm1 & & & 159
04:18 test_reslove_tablespace_bakcup_pending.sh
-rw-r--r-- 1 db2inst1 db2iadm1 & & & &97
04:20 get_tablespaces_status.sh
-rw------- 1 db2inst1 db2iadm1 14-02-11 05:07 TPCD.0.db2inst1.NODE0000.CATN
db2inst1@baseDB2:/db2home/test_data>
再次进行恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 52
SQL2539W &Warning! &Restoring to an existing database that is the same as the
backup image database. &The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I &The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL3799W &Load recovery for table "TPCD & &.PART" at time "5" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) c
不能继续前滚，因为load recovery的缘故，选择c，continue：
SQL3799W &Load recovery for table "TPCD & &.PART" at time "5" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
不行，选择t，abort
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
SQL1271W &Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
数据库可以连接了，但是表空间的状态不对：
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
& &Database Connection Information
&Database server & & & &= DB2/LINUXX.0
&SQL authorization ID & = DB2INST1
&Local database alias & = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0100
& &Detailed explanation:
& & &Restore pending
查询表中的数据：
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
SQL0290N &Table space access is not allowed. &SQLSTATE=55039
db2inst1@baseDB2:/db2home/test_data>
尴尬了……不知道怎么弄，只好再来一次恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 52
SQL2539W &Warning! &Restoring to an existing database that is the same as the
backup image database. &The database files will be deleted.
Do you want to continue ? (y/n)
Do you want to continue ? (y/n)
Do you want to continue ? (y/n) y
DB20000I &The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of log and stop
SQL0104N &An unexpected token "log" was found following "OF". &Expected tokens
may include: &"LOGS". &SQLSTATE=42601
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL3799W &Load recovery for table "TPCD & &.PART" at time "5" on
node "0" is pending due to warning "-2036" with additional information
"/db2home/test_data/TPCD.4.db2inst1".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) d
SQL3799W &Load recovery for table "TPCD & &.PART" at time "55" on
node "0" is pending due to warning "3798" with additional information
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
SQL1271W &Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
db2inst1@baseDB2:/db2home/test_data>
还是不行，选择参数d也是不行，用了t，又回到刚才的状态了……
尝试将load的备份文件拿回，然后强制设置数据库的状态为rollforward pending，再进行前滚，结果，还是不行……
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
SQL1271W &Database "TPCD" is recovered but one or more table spaces are
off-line on node(s) "0".
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd query status
& & & & & & & & & & & & & & & & &Rollforward Status
&Input database alias & & & & & & & & & = tpcd
&Number of nodes have returned status & = 1
&Node number & & & & & & & & & & & & & &= 0
&Rollforward status & & & & & & & & & & = not pending
&Next log file to be read & & & & & & & =
&Log files processed & & & & & & & & & &= &-
&Last committed transaction & & & & & & = -15.32.43.000000 UTC
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I &The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
& &Database Connection Information
&Database server & & & &= DB2/LINUXX.0
&SQL authorization ID & = DB2INST1
&Local database alias & = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0100
& &Detailed explanation:
& & &Restore pending
&Tablespace ID & & & & & & & & & & & &= 3
&Name & & & & & & & & & & & & & & & & = SYSTOOLSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 4
&Name & & & & & & & & & & & & & & & & = SYSTOOLSTMPSPACE
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = User Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
db2inst1@baseDB2:/db2home/test_data>
目前看来，只能重新恢复（并将load的备份放在指定位置）或者恢复到指定的时点了。
可见，在load过程中，copy yes参数设定的情况下，生成的load数据的备份文件对以后数据库的前滚至关重要，如果其丢失了，在做前滚时，将不能应用曾经导入的load的数据而造成数据的损失。
因此，虽然load过程中指定copy yes可以避免表空间的backup pending，但是，从保险的角度来说，做完load之后，还是进行一下表空间的备份为妙。
由以上两种参数的调查，得出的load的最稳妥的做法是，使用copy yes同时，在copy yes的load导入成功后，立即进行表空间的备份（或者全备）。
3、NONERECOVERABLE
如果在load过程中，指定了nonerecoverable参数，则表明被导入数据的表是不能进行前滚恢复的，如果进行前滚恢复，此表会置于unaviliable状态。验证如下：
db2inst1@baseDB2:/db2home/test_data> db2 "load from part.tbl of del modified by coldel| insert into tpcd.part nonrecoverable"
SQL3109N &The utility is beginning to load data from file
"/db2home/test_data/part.tbl".
SQL3500W &The utility is beginning the "LOAD" phase at time "02/12/2014
00:29:58.182231".
SQL3519W &Begin Load Consistency Point. Input record count = "0".
SQL3520W &Load Consistency Point was successful.
SQL3110N &The utility has completed processing. &"200000" rows were read from
the input file.
SQL3519W &Begin Load Consistency Point. Input record count = "200000".
SQL3520W &Load Consistency Point was successful.
SQL3515W &The utility has finished the "LOAD" phase at time "02/12/2014
00:29:58.822294".
Number of rows read & & & & = 200000
Number of rows skipped & & &= 0
Number of rows loaded & & & = 200000
Number of rows rejected & & = 0
Number of rows deleted & & &= 0
Number of rows committed & &= 200000
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 3
&Name & & & & & & & & & & & & & & & & = SYSTOOLSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 4
&Name & & & & & & & & & & & & & & & & = SYSTOOLSTMPSPACE
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = User Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
db2inst1@baseDB2:/db2home/test_data>
使用此参数完成导入后，表空间的状态也是正常的。
然后，对此表进行操作，删除一些数据：
db2inst1@baseDB2:/db2home/test_data> &db2 "delete from tpcd.part where P_PARTKEY in (159,161,150162)"
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 commit
DB20000I &The SQL command completed successfully.
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &199995
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
然后，进行恢复前滚：
db2inst1@baseDB2:/db2home/test_data> db2 restore db tpcd taken at 52
SQL2539W &Warning! &Restoring to an existing database that is the same as the
backup image database. &The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I &The RESTORE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to end of logs and stop
& & & & & & & & & & & & & & & & &Rollforward Status
&Input database alias & & & & & & & & & = tpcd
&Number of nodes have returned status & = 1
&Node number & & & & & & & & & & & & & &= 0
&Rollforward status & & & & & & & & & & = not pending
&Next log file to be read & & & & & & & =
&Log files processed & & & & & & & & & &= S0000000.LOG - S0000005.LOG
&Last committed transaction & & & & & & = -16.38.35.000000 UTC
DB20000I &The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 activate db tpcd
DB20000I &The ACTIVATE DATABASE command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
& &Database Connection Information
&Database server & & & &= DB2/LINUXX.0
&SQL authorization ID & = DB2INST1
&Local database alias & = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
SQL1477N &For table "TPCD.PART" an object "270" in table space "2" cannot be
accessed. &SQLSTATE=55019
db2inst1@baseDB2:/db2home/test_data> db2 list tablespaces
& & & & & &Tablespaces for Current Database
&Tablespace ID & & & & & & & & & & & &= 0
&Name & & & & & & & & & & & & & & & & = SYSCATSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Regular table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 1
&Name & & & & & & & & & & & & & & & & = TEMPSPACE1
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = System Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 2
&Name & & & & & & & & & & & & & & & & = USERSPACE1
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 3
&Name & & & & & & & & & & & & & & & & = SYSTOOLSPACE
&Type & & & & & & & & & & & & & & & & = Database managed space
&Contents & & & & & & & & & & & & & & = All permanent data. Large table space.
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
&Tablespace ID & & & & & & & & & & & &= 4
&Name & & & & & & & & & & & & & & & & = SYSTOOLSTMPSPACE
&Type & & & & & & & & & & & & & & & & = System managed space
&Contents & & & & & & & & & & & & & & = User Temporary data
&State & & & & & & & & & & & & & & & &= 0x0000
& &Detailed explanation:
& & &Normal
db2inst1@baseDB2:/db2home/test_data> db2 load query table tpcd.part
Tablestate:
& Unavailable
db2inst1@baseDB2:/db2home/test_data>
可以看到，恢复和前滚操作都显示成功了，但是被导入数据的表tpcd.part不可用，无法进行查询，同时显示表的状态是Unavailable。
这时，就只能通过以前的数据库备份进行重新恢复并前滚到指定的时间点，演示如下：
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd to -05.07.55.000000 &using local time
& & & & & & & & & & & & & & & & &Rollforward Status
&Input database alias & & & & & & & & & = tpcd
&Number of nodes have returned status & = 1
&Node number & & & & & & & & & & & & & &= 0
&Rollforward status & & & & & & & & & & = DB &working
&Next log file to be read & & & & & & & = S0000003.LOG
&Log files processed & & & & & & & & & &= S0000000.LOG - S0000002.LOG
&Last committed transaction & & & & & & = -05.07.55.000000 Local
DB20000I &The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 rollforward db tpcd stop
& & & & & & & & & & & & & & & & &Rollforward Status
&Input database alias & & & & & & & & & = tpcd
&Number of nodes have returned status & = 1
&Node number & & & & & & & & & & & & & &= 0
&Rollforward status & & & & & & & & & & = not pending
&Next log file to be read & & & & & & & =
&Log files processed & & & & & & & & & &= S0000000.LOG - S0000003.LOG
&Last committed transaction & & & & & & = -05.07.55.000000 Local
DB20000I &The ROLLFORWARD command completed successfully.
db2inst1@baseDB2:/db2home/test_data> db2 connect to tpcd
& &Database Connection Information
&Database server & & & &= DB2/LINUXX.0
&SQL authorization ID & = DB2INST1
&Local database alias & = TPCD
db2inst1@baseDB2:/db2home/test_data> db2 load query table tpcd.part
Tablestate:
db2inst1@baseDB2:/db2home/test_data>
db2inst1@baseDB2:/db2home/test_data> db2 "select count(*) from tpcd.part"
-----------
& & &200000
& 1 record(s) selected.
db2inst1@baseDB2:/db2home/test_data>
阅读(2020) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。