©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 155
LOAD時の考慮点（続き）
LOAD中はパーティションのデタッチ/アタッチ/追加はロック待機
SAVECOUNTオプションの指定不可
z 指定した場合無視され、SQL1128W、RC=3の警告
デタッチが未完了なパーティションが存在する場合、下記の条件下では、
パーティション表に対するREBUILDモード指定のLOAD不可
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 157
パーティション表へのLOAD
> db2 load from ./data1.txt of del replace into dp2 for exception exp1 SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/tukiv9/shell/data1.txt".
SQL3500W The utility is beginning the "LOAD" phase at time "2006-05-22 17:44:41.920210".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL0327N The row cannot be inserted into table "TUKIV9.DP2" because it is outside the bounds of the defined data partition ranges. SQLSTATE=22525
SQL3110N The utility has completed processing. "1100" rows were read from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "1100".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "2006-05-22 17:44:42.931265".
Number of rows read = 1100 Number of rows skipped = 0 Number of rows loaded = 1000 Number of rows rejected = 100 Number of rows deleted = 0 Number of rows committed = 1100
> echo $?
0
> db2 "select count(*) from exp1"
1
---100
1 record(s) selected.
例外表のチェック要
Rejectされた行は例外表へ 格納される。
Reject 行があってもRC=0
メッセージ出力は1回だけ
パーティション表へのLOAD
> db2 "create table part5 (a int, b char(20))"
DB20000I The SQL command completed successfully.
> db2 "alter table dp2 attach partition starting (1001) ending (1200) from part5"
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586
> db2 load from ./data1.txt of del replace into dp2 for exception exp1 SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file "/home/tukiv9/shell/data1.txt".
SQL3500W The utility is beginning the "LOAD" phase at time "2006-05-22 17:52:51.489839".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL0327N The row cannot be inserted into table "TUKIV9.DP2" because it is outside the bounds of the defined data partition ranges. SQLSTATE=22525
SQL3110N The utility has completed processing. "1100" rows were read from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "1100".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time
"2006-05-22 17:52:52.489112".
Number of rows read = 1100 Number of rows skipped = 0 Number of rows loaded = 1000 Number of rows rejected = 100 Number of rows deleted = 0 Number of rows committed = 1100
DBNAME PARTID TABNAME STATUS MODE -- -- ----PART0 0 DP2 F PART1 1 DP2 F PART2 2 DP2 F PART3 3 DP2 F PART4 4 DP2 F PART5 5 DP2 A N
Set Integrity未実行のパー ティションはLOAD対象外
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 159
パーティション表へのLOAD
> db2 set integrity for dp2 immediate checked DB20000I The SQL command completed successfully.
> db2 load from ./data1.txt of del replace into dp2 for exception exp1 SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/tukiv9/shell/data1.txt".
SQL3500W The utility is beginning the "LOAD" phase at time "2006-05-22 18:15:11.423612".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1100" rows were read from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "1100".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "2006-05-22 18:15:12.494593".
Number of rows read = 1100 Number of rows skipped = 0 Number of rows loaded = 1100 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 1100
DBNAME PARTID TABNAME STATUS MODE -- -- ----PART0 0 DP2 F PART1 1 DP2 F PART2 2 DP2 F PART3 3 DP2 F PART4 4 DP2 F PART5 5 DP2 F
Set Integrity実行後は、ア タッチされたパーティショ ンもLOAD対象
全データが格納された
パーティション表へのLOAD
> db2 "create table EXP2 (a int, b char(20)) partition by range(a) (starting 1 ending 1400)"
DB20000I The SQL command completed successfully.
> db2 load from ./data1.txt of del replace into dp2 for exception exp2 SQL3604N Exception table "EXP2" corresponding to table "DP2" in the SET INTEGRITY statement or LOAD utility does not have the proper structure, has been defined with unique indexes, constraints, generated columns, or triggers, or is in the Set Integrity Pending state, or is of invalid type. Reason code
"11".
11 The exception table is a data partitioned table 12 The exception table is a detached table
パーティション表を例外表 に指定したLOADは失敗
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 161
パーティション表へのLOAD
> db2 load from ./data1.txt of del savecount 500 replace into dp2 for exception exp1 SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
SQL1128W SAVECOUNT is ignored. Reason code = "3".
SQL3109N The utility is beginning to load data from file "/home/tukiv9/shell/data1.txt".
SQL3500W The utility is beginning the "LOAD" phase at time "2006-05-22 21:33:27.705480".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1000" rows were read from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "1000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "2006-05-2221:33:28.525231".
SQL3107W There is at least one warning message in the message file.
Number of rows read = 1000 Number of rows skipped = 0 Number of rows loaded = 1000 Number of rows rejected = 0 Number of rows deleted = 0 Number of rows committed = 1000
SAVECOUNTオプション は無視される。
パーティション表へのLOAD
SQL1128W SAVECOUNT is ignored. Reason code = "<reason-code>".
Explanation:
Possible reason codes are as follows:
1. Consistency points are not supported when loading into MDC tables.
2. The specified filetype does not allow consistency points.
3. Consistency points are not supported when loading into partitioned tables.
User Response:
No action is required.
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 163
Indexing mode rebuildが使用できないケース
> db2look -d smpdb -e -t dp1 CREATE TABLE "TUKIV9 "."DP1" (
"PKEY" INTEGER NOT NULL ,
"COL1" INTEGER ,
"COL2" CHAR(30) ) PARTITION BY RANGE("PKEY")
(PART "PART0" STARTING(MINVALUE) ENDING(200000) IN "TS0", PART "PART1" ENDING(400000) IN "TS1",
PART "PART2" ENDING(600000) IN "TS2", PART "PART3" ENDING(800000) IN "TS3", PART "PART4" ENDING(1000000) IN "TS4");
CREATE UNIQUE INDEX "TUKIV9 "."IDX0_DP1" ON "TUKIV9 "."DP1"
("PKEY" ASC)
NOT PARTITIONED IN ITS0 CLUSTER ALLOW REVERSE SCANS;
> db2 -tvf dp1.mqt.ddl
create summary table DP1_MQT as ( select * from dp1 where col1 < 100) data initially deferred refresh immediate maintained by system
DB20000I The SQL command completed successfully.
条件① ユニークインデック スが存在する。
条件②MQT表が存在する。
Indexing mode rebuildが使用できないケース（つづき）
> db2 set integrity for dp1_mqt immediate checked DB20000I The SQL command completed successfully.
> db2 "select substr(name,1,12) name,status,access_mode,const_checked from sysibm.systables where name like 'DP1%' with ur"
NAME STATUS ACCESS_MODE CONST_CHECKED
--- --- --- ---DP1 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_MQT N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
2 record(s) selected.
> db2 "select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid,
substr(TABNAME,1,12) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE,substr(b.tbspace,1,10) tbspace from syscat.datapartitions a,syscat.tablespaces b where tabname like 'DP1%' and
a.tbspaceid=b.tbspaceid"
DPNAME PARTID TABNAME STATUS ACCESS_MODE TBSPACE --- --- -- --- - ---PART1 1 DP1 F TS1 PART2 2 DP1 F TS2 PART3 3 DP1 F TS3 PART4 4 DP1 F TS4 PART0 0 DP1_MQT F TS0 PART0 0 DP1 F TS0
6 record(s) selected.
MQT表のSet Integrity 実施
基礎表、MQT表ともに フルアクセス可能
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 165
Indexing mode rebuildが使用できないケース（つづき）
> db2 alter table dp1 detach partition part2 into dp1_part2
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586
> db2 "select substr(name,1,12) name,status,access_mode,const_checked from sysibm.systables where name like 'DP1%' with ur"
NAME STATUS ACCESS_MODE CONST_CHECKED
--- --- --- ---DP1 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_MQT C N YYYYNYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_PART2 N N YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
> db2 "select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,12) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE as
mode ,substr(b.tbspace,1,10) tbspace from syscat.datapartitions a,syscat.tablespaces b where tabname like 'DP1%' and a.tbspaceid=b.tbspaceid"
DPNAME PARTID TABNAME STATUS MODE TBSPACE --- --- -- --- ---- ---PART1 1 DP1 F TS1 PART2 2 DP1 D N TS2 PART3 3 DP1 F TS3 PART4 4 DP1 F TS4 PART0 0 DP1_MQT N TS0 PART0 0 DP1_PART2 N TS2 PART0 0 DP1 F TS0
条件③Detachされたパー ティションが存在しており、
MQTのメンテナンスが未実施 PART2をデタッチ
Indexing mode rebuildが使用できないケース（つづき）
> db2 "load from ../tscont/dp1.10000.csv of del insert into dp1 indexing mode rebuild"
SQL27983N The LOAD utility is unable to rebuild the index.
> db2 "load from ../tscont/dp1.10000.csv of del insert into dp1 indexing mode incremental"
SQL3501W The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database.
＜中略＞
SQL3500W The utility is beginning the "BUILD" phase at time "2006-05-29 21:02:33.432653".
SQL3213I The indexing mode is "INCREMENTAL".
SQL3515W The utility has finished the "BUILD" phase at time "2006-05-29 21:02:37.422515".
Number of rows read = 9900 Number of rows skipped = 0 Number of rows loaded = 7920 Number of rows rejected = 1980 Number of rows deleted = 0 Number of rows committed = 9900
Indexing mode rebuildでは LOAD実行不可、incremental ではLOAD成功
SQL27983N The LOAD utility is unable to rebuild the index.
Explanation: The LOAD target table has detached data partitionsand has dependent materialized query tables or dependent staging tablesthat have not been incrementally refreshed with respect to the detached data partition. This condition prevents the LOAD utility running in insert mode or restart mode from rebuilding a unique index.
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 167