建库
db2 "CREATE DATABASE MDS AUTOMATIC STORAGE NO ON /db2 USING CODESET UTF-8 TERRITORY CN"
创建表空间:
db2 -tvf mds.sql
CONNECT TO MDS
Database Connection Information
Database server = DB2/AIX64 9.5.8
SQL authorization ID = DB2MDS
Local database alias = MDS
CREATE LARGE TABLESPACE "TBS_M_BP_GL_DAT" IN DATABASE PARTITION GROUP PDPG PAGESIZE 32768 MANAGED BY DATABASE USING (FILE '/db2/db2mds/db2data1/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (1) USING (FILE '/db2/db2mds/db2data2/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (2) USING (FILE '/db2/db2mds/db2data3/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (3) USING (FILE '/db2/db2mds/db2data4/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (4) USING (FILE '/db2/db2mds/db2data5/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (5) USING (FILE '/db2/db2mds/db2data6/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (6) USING (FILE '/db2/db2mds/db2data7/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (7) USING (FILE '/db2/db2mds/db2data8/TBS_M_BP_GL_DAT.DAT' 2G) ON DBPARTITIONNUMS (8) EXTENTSIZE 32 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_32K OVERHEAD 7.500000 TRANSFERRATE 0.060000 NO FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON
DB20000I The SQL command completed successfully.
修改归档方式:
db2 update db cfg for mds DBPARTITIONNUM 0 using NEWLOGPATH /db2/db2mds/db2log0/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc0/mds
db2 update db cfg for mds DBPARTITIONNUM 1 using NEWLOGPATH /db2/db2mds/db2log1/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc1/mds
db2 update db cfg for mds DBPARTITIONNUM 2 using NEWLOGPATH /db2/db2mds/db2log2/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc2/mds
db2 update db cfg for mds DBPARTITIONNUM 3 using NEWLOGPATH /db2/db2mds/db2log3/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc3/mds
db2 update db cfg for mds DBPARTITIONNUM 3 using NEWLOGPATH /db2/db2mds/db2log3/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc3/mds
db2 update db cfg for mds DBPARTITIONNUM 4 using NEWLOGPATH /db2/db2mds/db2log4/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc4/mds
db2 update db cfg for mds DBPARTITIONNUM 5 using NEWLOGPATH /db2/db2mds/db2log5/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc5/mds
db2 update db cfg for mds DBPARTITIONNUM 6 using NEWLOGPATH /db2/db2mds/db2log6/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc6/mds
db2 update db cfg for mds DBPARTITIONNUM 7 using NEWLOGPATH /db2/db2mds/db2log7/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc7/mds
db2 update db cfg for mds DBPARTITIONNUM 8 using NEWLOGPATH /db2/db2mds/db2log8/mds LOGARCHMETH1 DISK:/db2/db2mds/db2arc8/mds
进行归档切换备份
db2_all "<<+0< db2 BACKUP DB MDS to /dev/null"
db2_all "|<<-0< db2 BACKUP DB MDS to /dev/null"
生成测试数据:
CREATE TABLE TEMP (
ID INT NOT NULL PRIMARY KEY,
NAME CHAR(10),
SALARY DECIMAL(8,2))
DISTRIBUTE BY HASH ("ID")
IN "TBS_M_BP_GL_DAT";
INSERT INTO TEMP
WITH TEMP_CT (A,B) AS
(VALUES(INTEGER(1),RAND(1))
UNION ALL
SELECT A+1,RAND()
FROM TEMP_CT
WHERE A<1000000
)
SELECT A AS ID, 'EMP'||CHAR(A) AS NAME, DECIMAL((B*10000),8,2) AS SALARY
FROM TEMP_CT;
创建恢复备份:
db2_all "<<+0< db2 BACKUP DB MDS to /db2/db2mds/backup"
db2_all "|<<-0< db2 BACKUP DB MDS to /db2/db2mds/backup"
创建导入表:
CREATE TABLE TEMP1 (
ID INT NOT NULL PRIMARY KEY,
NAME CHAR(10),
SALARY DECIMAL(8,2))
DISTRIBUTE BY HASH ("ID")
IN "TBS_M_BP_GL_DAT";
导入数据:
db2 "load from temp.del of del replace into temp1 copy yes to /db2/db2mds/load"
使用备份进行恢复
db2_all "<<+0< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING"
nohup db2_all "<<+1< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+2< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+3< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+4< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+5< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+6< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+7< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
nohup db2_all "<<+8< db2 RESTORE DATABASE MDS FROM /db2/db2mds/backup INTO MDS REPLACE EXISTING" &
db2_all "db2set DB2LOADREC=/db2/db2mds/load/loadrec"
修改/db2/db2mds/load/loadrec文件
CASHMAPP2:/db2/db2mds/load$more loadrec
TIM 20120227141358
DBP 0
SCH DB2MDS
TAB TEMP1
DAT MDS
DB2 db2mds
BUF NULL
SES NULL
TYP L
LOCationnumber 8
entry /db2/db2mds/load/MDS.4.db2mds.NODE0008.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0007.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0006.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0005.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0004.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0003.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0002.CATN0000.20120227141358.001
/db2/db2mds/load/MDS.4.db2mds.NODE0001.CATN0000.20120227141358.001
查看数据库前滚状态:
db2 rollforward db mds query status
Rollforward Status
Input database alias = mds
Number of nodes have returned status = 9
Node number Rollforward Next log Log files processed Last committed transaction
status to be read
----------- -------------------------- ------------------- ------------------------- --------------------------
0 DB pending S0000001.LOG - 2012-02-27-06.12.43.000000 UTC
1 DB pending S0000005.LOG - 2012-02-27-06.13.00.000000 UTC
2 DB pending S0000005.LOG - 2012-02-27-06.13.01.000000 UTC
3 DB pending S0000005.LOG - 2012-02-27-06.13.01.000000 UTC
4 DB pending S0000005.LOG - 2012-02-27-06.13.01.000000 UTC
5 DB pending S0000005.LOG - 2012-02-27-06.13.04.000000 UTC
6 DB pending S0000005.LOG - 2012-02-27-06.13.04.000000 UTC
7 DB pending S0000005.LOG - 2012-02-27-06.13.04.000000 UTC
8 DB pending S0000005.LOG - 2012-02-27-06.13.04.000000 UTC
进行数据库前滚:
db2 "rollforward db mds to end of logs and complete "
CASHMAPP2:/db2/db2mds/backup$db2 "rollforward db mds to end of logs and complete "
SQL3799W Load recovery for table "DB2MDS .TEMP1" at time "20120227141358" on
node "5" is pending due to warning "-3784" with additional information "2".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t)
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t)
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t)
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t)
进行最后一步的前滚时,会一直让选择 "continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) "
大家谁见过类似的情况?怎么解决的?
收起