因为16K的页，单分区最大只能放256G的表，所以最近迁移了一个220多G的表，命令记录如下：
CREATE BUFFERPOOL "BUFFER32K" SIZE 3000 PAGESIZE 32768 NOT EXTENDED STORAGE;
CREATE REGULAR TABLESPACE USAGESPACE2 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE
USING (FILE '/db2aix/eware/ts_table/usage/usage32k01.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k02.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k03.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k04.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k05.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k06.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k07.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k08.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k09.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k10.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k11.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k12.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k13.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k14.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k15.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k16.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k17.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k18.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k19.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k20.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k21.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k22.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k23.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k24.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k25.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k26.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k27.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k28.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k29.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k30.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k31.dat'300000,
FILE '/db2aix/eware/ts_table/usage/usage32k32.dat'300000
)
EXTENTSIZE 2
PREFETCHSIZE 8
BUFFERPOOL BUFFER32K
OVERHEAD 24.100000
TRANSFERRATE 0.900000
DROPPED TABLE RECOVERY ON;
create table userid.X_UWIP_PART_USAGE3 like userid.X_UWIP_PART_USAGE IN "USAGESPACE2" INDEX IN "INDEXSPACE" ;
CREATE INDEX "EWARE "."IX_HIST_ENTRY_TIM" ON "USERID "."X_UWIP_PART_USAGE3"
("HIST_ENTRY_TIME" DESC);
CREATE INDEX "EWARE "."IX_USAGE_PARTNAME2" ON "USERID "."X_UWIP_PART_USAGE3"
("PART_NAME" ASC);
CREATE INDEX "EWARE "."IX_USG_BARCODE2" ON "USERID "."X_UWIP_PART_USAGE3"
("BARCODE" ASC);
CREATE INDEX "EWARE "."PART_USAGE_PN_HC2" ON "USERID "."X_UWIP_PART_USAGE3"
("PART_NAME" ASC,
"HEADER_CODE" ASC);
ALTER TABLE "USERID "."X_UWIP_PART_USAGE3"
ADD CONSTRAINT "K_UWIP_PART_USAGE" PRIMARY KEY
("UWIP_KEY",
"MFG_LINE_ID",
"HIST_ENTRY_TIME",
"IBMSNAP_INTENTSEQ");
declare staffcursor cursor for select * from userid.X_UWIP_PART_USAGE with ur;
load from staffcursor of cursor insert into userid.X_UWIP_PART_USAGE3;
load from staffcursor of cursor restart into userid.X_UWIP_PART_USAGE3
BACKUP DATABASE eware TABLESPACE USAGESPACE2 ONLINE TO /dbbackup compress
db2 "select count(*) from userid.X_UWIP_PART_USAGE3 with ur"
db2 "select count(*) from userid.X_UWIP_PART_USAGE with ur"
db2 "select * from table(snapshot_lock('EWARE',-1)) as s"
db2 connect to eware
db2 "declare staffcursor cursor for select * from userid.X_UWIP_PART_USAGE with ur"
db2 load from staffcursor of cursor restart into userid.X_UWIP_PART_USAGE3
db2 connect to eware
db2 "BACKUP DATABASE eware TABLESPACE (INDEXSPACE,USAGESPACE2) ONLINE TO /dbbackup compress"
BACKUP DATABASE eware TABLESPACE USAGESPACE2 ONLINE TO /dbbackup compress
rew3:[/dbbackup]$db2 "select count(*) from userid.X_UWIP_PART_USAGE3 with ur"
1
-----------
526429426
1 record(s) selected.
rew3:[/db2aix/eware]$db2 "select count(*) from userid.X_UWIP_PART_USAGE with ur"
1
-----------
526429426
1 record(s) selected.
rew3:[/db2aix/eware]$
db2 list applications show detail | grep Executing
db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
db2 get snapshot for ALL BUFFERPOOLS global
alter BUFFERPOOL "TEMPBUFF" SIZE 50000
ALTER BUFFERPOOL "BUFFER16K" SIZE 10000
ALTER BUFFERPOOL "BUFFER32K" SIZE 10000
drop VIEW "USERID"."X_UWIP_QA_USAGE"
drop VIEW "DB2ADMIN"."DESC_PART_BARCODE"
drop TABLE "PABLO"."PART_USAGE"
rename userid.X_UWIP_PART_USAGE to X_UWIP_PART_USAGE2
rename userid.X_UWIP_PART_USAGE3 to X_UWIP_PART_USAGE
CREATE VIEW "USERID"."X_UWIP_QA_USAGE"
AS SELECT A.UWIP_KEY AS UWIP_KEY, C.MFG_LINE_ID , A.HIST_ENTRY_TIME, B.SERIAL_NUM
, A.OPERATION_ID, A.WORK_POSITION_ID, A.BARCODE, A.PART_NAME,
A.HEADER_CODE_TYPE, A.HEADER_CODE, SUBSTR(A.BARCODE,11,12) AS
PART_SERIAL_NUM, SUBSTR(A.BARCODE,17,6) AS HEADER_SERIAL_NUM,
A.REASON_CODE, ' ' AS REJECT_FLAG, ' ' AS DESCRIPTION, B.PRODUCT_ID, (
CASE
WHEN C.REVENUE_NUMBER IS NULL
OR RTRIM(C.REVENUE_NUMBER)=''
THEN B.PRODUCT_ID
ELSE C.REVENUE_NUMBER
END) AS C_PART
FROM USERID.X_UWIP_PART_USAGE A,USERID.X_UWIP B,USERID.X_MFG_ORDER C
WHERE A.UWIP_KEY=B.UWIP_KEY
AND B.MFG_ORDER_ID=C.MFG_ORDER_ID;
GRANT SELECT ON TABLE "USERID"."X_UWIP_QA_USAGE" TO USER "BRIOSYS";
CREATE VIEW "DB2ADMIN"."DESC_PART_BARCODE"
AS SELECT c.machine_type, c.machine_model, c.SERIAL_NUM, b.UWIP_KEY,
a.DESCRIPTION, a.PART_NAME, b.BARCODE, b.REASON_CODE, b.HIST_ENTRY_TIME,
c.mfg_order_id
FROM userid.x_uwip c,db2admin.part_name a, USERID.X_UWIP_PART_USAGE b
WHERE b.uwip_key=c.uwip_key
and b.mfg_line_id=c.mfg_line_id
and b.PART_NAME = a.Part_name
and rtrim(c.machine_type)<>'';
CREATE TABLE "PABLO"."PART_USAGE"
AS (SELECT MFG_LINE_ID, PART_NAME
FROM USERID.X_UWIP_PART_USAGE
GROUP BY MFG_LINE_ID, PART_NAME)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM
DATA CAPTURE NONE
IN "USERSPACE1";
ALTER TABLE "PABLO"."PART_USAGE"
LOCKSIZE ROW;
ALTER TABLE "PABLO"."PART_USAGE"
APPEND OFF;
ALTER TABLE "PABLO"."PART_USAGE"
NOT VOLATILE;
REFRESH TABLE "PABLO"."PART_USAGE" NOT INCREMENTAL;
db2 alter TABLESPACE USAGESPACE DROPPED TABLE RECOVERY Off
db2 alter TABLESPACE USAGESPACE2 DROPPED TABLE RECOVERY On
db2 alter TABLESPACE INDEXSPACE DROPPED TABLE RECOVERY Off
rew3:[/db2aix/eware]$db2 "call get_dbsize_info(?,?,?,-1)"
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2007-01-10-12.41.11.299572
Parameter Name : DATABASESIZE
Parameter Value : 759087517696
Parameter Name : DATABASECAPACITY
Parameter Value : 1254331563520
Return Status = 0
rew3:[/db2aix/eware]$
db2 drop table userid.X_UWIP_PART_USAGE2
db2 drop TABLESPACE USAGESPACE
db2 RENAME TABLESPACE USAGESPACE2 TO USAGESPACE
db2 alter TABLESPACE INDEXSPACE DROPPED TABLE RECOVERY On
体会：
1、此次是在load之前建的index，发现load的过程超长，中间强行中断一次，不过正好锻炼了一下如何解决此类问题。以后可以试试后建index，这样load本身的过程可以缩短。
2、load的两个参数NONRECOVERABLE和COPY YES，以后有机会试一下，这样可以防止表空间处于backup pending状态。
3、backup表空间的命令如果拆成两个，并同时进行，也许时间可以缩短，以后有机会验证。
4、整个操作过程中缺少了grant命令，这应该是唯一的疏忽。
5、以后再做长时间的操作的话，还是用nohup比较好。
| 网站地图 |