V} 4 g{"vTB LOAD |n: load from load.del of del replace into table1 "RdP;v0k}]bVxZ0kYwZdD!mUd,G4+5XTBdv: SQL0289N ;\ZmUd“DMS4KT”PVdB3# SQLSTATE=57011 zmLr`M Zc SQL zk a{ ________________________________________________________________ LOAD 000 +00000000 Success. ________________________________________________________________ LOAD 001 -00000289 Error. May require RESTART. ________________________________________________________________ LOAD 002 +00000000 Success. ________________________________________________________________ LOAD 003 +00000000 Success. ________________________________________________________________ PARTITION 001 +00000000 Success. ________________________________________________________________ PRE_PARTITION 000 +00000000 Success. ________________________________________________________________ a{: I&jIK 4 v0kPD 3 v# ________________________________________________________________ VxzmLrD**: A!DP} =0 \xDP} =0 VxDP} =0 0kzmLrD**: A!DP} =0 x}DP} =0 0kDP} =0 \xDP} =0 >}DP} =0 Qd5DP} =0 dv8>0kYw5XKms SQL0289#}]bVx**8>}]bVx 1 D!Ud# g{T}]bVx 1 ODmUd]wmSK|`Ud,G4IT4gB==XBt/0 kYw: load from load.del of del restart into table1 *Vx}]b730kdC!n: Pm`IC4^DVx}]b73PD0kYwDdC!n# MODE X 8(0k`Vx}]b10kYwICD==#PARTITION_AND_LOAD G1!5#P '5*: v PARTITION_AND_LOAD#T}]xPV<(PI\T"P==xPV<),"R ,1Zwv`&}]bVxO0k}]# v PARTITION_ONLY#T}]xPV<(PI\T"P==xPV<),"+dv 4k?v0k}]bVxO8(;CPDD~#TZ CURSOR TbDD~`M, ?v}]bVxODdvD~{q=* filename.xxx,dP filename G LOAD | nP8(DdkD~{,xxx G 3 ;D}]bVxE#TZ CURSOR D~`M, 100 }]F/5CLr8OMN< ?v}]bVxODdvD~{I PART_FILE_LOCATION !n7(#kND PART_FILE_LOCATION !nTKbPXgN8(?v}]bVxDV }G)P#g{X(0k}]bVxOfZ}]bVx%},G4a+;uP XC}]bVxD/f4A0k{"D~#?v}]bVxDdkD~{q= &CG filename.xxx,dP filename G LOAD |nP8(DD~{,xxx G 3 ;D}]bVxE#kND PART_FILE_LOCATION !nTKbPXgN8(?v }]bVxDVD~{,r_Z LOAD_ONLY ==,K{FGTZ?v}]bVx*A!DD ~D;C#9C PARTITION_ONLY ==1,g{?jm|, LOB P,G4IT9 C8(Dy>{F44(`vD~# TZ CURSOR TbDD~`M,g{48(K!n,r+9C10?<4f"V< =D~# OUTPUT_DBPARTNUMS X X m>}]bVxEPm#}]bVxEm>*4P0kYwD}]bVx#}] bVxEXkG(eKCmD}]bVxDS/#1!ivB,!qKyP}]b Vx#Xk+KPm(Z2(EP,"RPmnXkI:EVt#Jm8(6' (}g,(0, 2 to 10, 15))# PARTITIONING_DBPARTNUMS X X m>V<}LP9CD}]bVxEPm#Xk+KPm(Z2(EP,"RP mnXkI:EVt#Jm8(6'(}g,(0, 2 to 10, 15))#TV<}L8( D } ] b V x I \ k * 0 k D } ] b V x ; , # g { 4 8 ( PARTITIONING_DBPARTNUMS,G4 LOAD 5CLra7(h*`Yv}]bVxT 0*9CDv}]bVx,TcqCnQT\# g{Z LOAD |nP48( anyorder D~`M^N{,G4Z0ka0P+;9 C;vVxzmLr#Kb,g{vT OUTPUT_DBPARTNUMS !n8(K;v}]b Vx,r_0kYwD-wLrVx;G OUTPUT_DBPARTNUMS D*X,G4aZV < } L P 9 C 0 k Y w D - w L r V x # q r , Z V < } L P 9 C OUTPUT_DBPARTNUMS PDZ;v}]bVx(;G-wLrVx)# g{8(K anyorder D~`M^N{,G44TB==7(V<}LP9CD} ]bVx}:(OUTPUT_DBPARTNUMS PDVx}/4+1)# MAX_NUM_PART_AGENTS X 8(0ka0P*9CDnsVxzmLr}#1!5* 25# ISOLATE_PART_ERRS X 8>0kYwgNTwv}]bVxO"zDmswv4}G,18(K LOAD |nD ALLOW READ ACCESS M COPY YES N}(ZKivB,1!5* NO_ISOLATION),qr1!5* LOAD_ERRS_ONLY#P'5*: v SETUP_ERRS_ONLY#hCZdZ}]bVxO"zDms(}g,CJ}]bV x1"zDJb,r_CJ}]bVxODmUdrm1"zDJb)+A!`Y}]?1"v(*#F?%;GWVZ (MB)#1!5* 100 MB# P'5G 1 = 4000 .dD{}# PORT_RANGE X X m>C44(Z?(EWSVD TCP KZD6'#1!6'G 6000 = 6063# g{ZwC1(eK DB2ATLD_PORTS "amd?D5,G4C5+f; PORT_RANGE 0kdC!nD5#TZ DB2ATLD_PORTS "amd?,&C9CTBq=4a)6 ': Z CLP P,q=*: ( lower-port-number, higher-port-number ) CHECK_TRUNCATION 8(Lr&CZdk/dv1li}]GV"3dDdvD~{#+Z"v LOAD |nD}]bVxO4(dvD~ (Y(4PVxYwD}]bVxi|,C}]bVx)#g{Z4NkVxD} ]bVx(I PARTITIONING_DBPARTNUMS (e)OwC LOAD |n,G4aZ9C PARTITIONING_DBPARTNUMS N}(eDZ;v}]bVxO4(dvD~#y0kdkD~P;V"=`&V" 3du?DP}#KE"ITozzj6}]+n,"R9ITozz7(Gq& C9C5CLrD ANALYZE ==4zImDBV"3d#g{48(K!n,G4 LOAD 5CLrD1!P*G;zI"48(5,G4+Zdv}]bVxPmPDZ;v}]bVxOU/3 FE"# INGEST 5CLr INGEST 5CLr(P1F*,x}]ekr CDI)G;v_YDM'K DB2 5CL r,|IT+}]TwDN=SD~M\@Pek= DB2 ?jmP#r* INGEST 5 CLrITF/s?51}]x;Xx(?jm,yTz;h*Z}]w(TMIC T.dxP!q# INGEST 5CLr1Sek$H&mD}]r(} ETL $_rd{VNdvDD~e k#|ITVxKP,rK|IT&mw-\@D}]w#+TO_DYHek} ],cTndVx}]b73PDsM}]b# INGEST |naZ%v=hP9COMH}1d4|B?jm#INGEST 5CLr9CP x(,rK|kkT`,mDd{C'n/De;nY# hzK5CLr,zIT9C`FZ SQL DSZ4Tm4P DML Yw,x;Xx( ?jm#b)ekYw'VBP SQL od:INSERT"UPDATE"MERGE"REPLACE M DELETE#INGEST 5CLr9'V9C SQL mo=4y]`v}]VN9(wv P5# INGEST 5CLrDd{X*&\gB: v 41drP}xPd5# IT9C commit_count ekdCN}T44kDP}47 (d55J,2IT9C1! commit_period ekdCN}T48(D1d47(d 55J# 104 }]F/5CLr8OMN< v 'V+Q\xDG<4F=D~rmP,2'VOzb)G<#zIT8( INGEST | ngN&m INGEST 5CLr((} DUMPFILE N})r DB2((} EXCEPTION TABLE N})\xDP# v 'VXBt/MV4#1!ivB,ITSns;vd5cXBt/yP INGEST | n#Kb,g{zhCK retry_count ekdCN},G4 INGEST 5CLra" TS3)msPV4# INGEST |n'VBPdk}]q=: v (gD> v ;CD>M~xF v wV3rMq=DP }K#fmMGFTb,INGEST |n9'VBPm`M: v `,/: (MDC) Mek1d/: (ITC) m v 46'VxDm v 6'/:m (RCT) v (e* MAINTAINED BY USER D_e/i/m(MQT),|(\am# v Y1m v I|BDS<(x`MS<}b) %v INGEST |n+-z}vw*WN: 1. +d +dLrS}]4PA!,"+G!Za)Kk9C INGEST 5CLr`XDw*hCMYwNqD_6Ev# hCekPd~ 1. v(KP INGEST 5CLrD;C zITZVPzwOrS@"DzwOKPekw5#PX|`E",k NDZ 106 3D:v(KP INGEST 5CLrD;C; 2. 20 INGEST 5CLr(DB2 }]~qwKP1M'z M DB2 }]~q wM'z D;?V)# }]F/5CLrMN< 105 g{v(ZBD@"zwO20 INGEST 5CLr,kKPJCZ DB2 M 'zD203q#PX|`E",kND620 IBM Data Server Client7 PD“20 IBM }]~qwM'z(Linux M UNIX)”# *"CZndmDxL 1. (gPX*)bvzk3Jb y]dk}]"DB2 M'zT0 DB2 ~qwGq9C`,Dzk3,ZK P INGEST |n.0I\h*4P;)C'Yw#PX|`E",kNDZ 120 3D:INGEST 5CLrDzk3"bBn;# 2. hCT&mQ'\D INGEST |nDXBt/ *9ekYwITXBt/,h*Z"v INGEST |n.04(XBt/U >m#PX|`E",kNDZ 107 3D:4(XBt/m;# 3. `4 INGEST |n "vxPXhN}(}gdk4M}]`M)D INGEST |nT0wVI! N}#PX|no(MC(Dj8hv0>},kNDZ 108 3D:ek }];M6Command Reference7PD“INGEST”# 4. hCT&mxPPDekw5w g{*aIwC$H+4D INGEST |n,k*C|n4(;vE>,"Z h*1xPwC#PX|`E",kNDZ 124 3D:=8:9C INGEST 5CLr&mD~w; 4PYwNq v (gPX*)bvQ'\D INGEST |n g{ekw5'\,G4zIT!qXBt/rU9C|n#PX|`E ",kNDZ 115 3D:XBt/'\D INGEST Yw;rZ 116 3D:U 9'\DekYw;# v `S INGEST |n PX|`E",kNDZ 117 3D:`SekYw;# (I!)E/T\ v 4i INGEST |nDIw{dCN}# v ^D INGEST |nT{O_T\*s# PX|`E",kNDZ 120 3D:ekYwDT\"bBn;# v(KP INGEST 5CLrD;C DB2 M'z20P+|( INGEST 5CLrbv?~#zITSM'zr~qwKP INGEST 5CLr# XZKNq CZKP INGEST 5CLrD;CP=v!n: Z}]Vb73PDVP~qwO ZK`MDhCPCZKPekw5D;CP=v!n: 106 }]F/5CLr8OMN< v Z DB2 -wLrVx(&CLr+,SA"R-wzmyZD}]bVx~ qw)O v ZVP ETL(i!,d;M0k)~qwO ZBD~qwO ZK`MDhCPCZKPekw5D;CP=v!n: v Z;KP INGEST 5CLrD~qwO v Z2w\d{(CZ INGEST 5CLrD DB2 -wLrVxD~qwO# P;)rXa0lzv(20 INGEST 5CLrD;C: v T\:+ INGEST 5CLr20Z|T:D~qwOITqC;ar9C INGEST 5C Lrxzzd{QC# v WZ\m 4(XBt/m 1!ivB,g{ INGEST |n'\,G4ISns;vd5cXBt/b)|n;+ G,zh*H4(XBt/m,Kmf"V4 INGEST |nyhDE"# XZKNq vh4(XBt/m;N,"RCm+I}]bPDyP INGEST |n9C# INGEST 5CLr+9CKm4f"Sns;vd5cV44jID INGEST |nyh DE"# ":XBt/m;|,dkPD1>,;|,;)F}wT8>D)PQd5# ^F v (iz+XBt/mk INGEST 5CLry|BD?jmECZ,;mUdP#g{ ^(ECZ,;mUdP,k7#|,XBt/mDmUdk|,?jmDmUd _P`,D6p#}g,g{*4-r0vdP;vmUd,G4Xk+m;vm Ud24-r0v=`,cN#g{b=vmUdD6p;,"RzKPx RESTART CONTINUE !nD INGEST |n,G4 INGEST 5CLrI\'\rek;}7D} ]# v g{VQV4_T|(4F INGEST YwD?jm,G49Xk4FXBt/mT9 dk?jm#V,=# }L *4(XBt/m,k4PTBYw: v g{}Z9C V10.1 ~qw,kwC SYSPROC.SYSINSTALLOBJECTS f"}L: db2 "CALL SYSPROC.SYSINSTALLOBJECTS(’INGEST’, ’C’, tablespace-name, NULL)" v g{}Z9C V9.5"V9.7 r V9.8 ~qw,k"vTB SQL od: CREATE TABLE SYSTOOLS.INGESTRESTART ( JOBID VARCHAR(256) NOT NULL, APPLICATIONID VARCHAR(256) NOT NULL, FLUSHERID INT NOT NULL, FLUSHERDISTID INT NOT NULL, }]F/5CLrMN< 107 TRANSPORTERID INT NOT NULL, BUFFERID BIGINT NOT NULL, BYTEPOS BIGINT NOT NULL, ROWSPROCESSED INT NOT NULL, PRIMARY KEY (JOBID, FLUSHERID, TRANSPORTERID, FLUSHERDISTID)) IN DISTRIBUTE BY (FLUSHERDISTID); GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE SYSTOOLS.INGESTRESTART TO PUBLIC; a{ VZ&Z8(mUdP4(XBt/m SYSTOOLS.INGESTRESTART,"RzVZI KPIXBt/D INGEST |n# >} DBA F.+yP INGEST |nw*IXBt/D|nKP,yTC DBA h*H4( XBt/m: 1. DBA ,SA}]b: db2 CONNECT TO sample 2. DBA wCf"}L: db2 "CALL SYSPROC.SYSINSTALLOBJECTS(’INGEST’, ’C’, NULL, NULL)" B;=v24 7#+^DCXBt/mDNNC'<_PJ1(^: v g{ INGEST |n8( RESTART NEW,G4C'Xk_PTXBt/mD SELECT"INSERT"UPDATE M DELETE X(# v g{ INGEST |n8( RESTART TERMINATE,G4C'Xk_PTXBt/mD SELECT M DELETE X(# ek}] IT9C INGEST 5CLr(} SQL }iek"|BM>}4+}],x"k= DB2 mP,1=4P;P}]*9# *<.0 ZwC INGEST 5CLr0,Xk,SA+}y>: INGEST FROM FILE my_file.txt FORMAT DELIMITED INSERT INTO my_table; (iz9Z INGEST |nP8(;vx RESTART NEW N}DV{.: INGEST FROM FILE my_file.txt FORMAT DELIMITED RESTART NEW ’CDIjob001’ INSERT INTO my_table; 8(DV{.IT`o 128 vVZ#r*CV{.a(;6p INGEST |n,Z10Q 8( RESTART NEW !n+P4jID}]bP,CV{.ZyP INGEST |nPXkG (;D# >} y>ek>} BP>}S(gD>D~Pek}]: INGEST FROM FILE my_file.txt FORMAT DELIMITED INSERT INTO my_table; BP>}S|,I:EVt(1!5)DVND(gD>D~Pek}]#D ~PDVNT&ZmP# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) INSERT INTO my_table VALUES($field1, $field2, $field3); (g{2G>} BP>}ek}]D==kO;v>}`F,+VNIz_Vt# INGEST FROM FILE my_file.txt FORMAT DELIMITED by ’|’ ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) INSERT INTO my_table VALUES($field1, $field2, $field3); !TVN(eM VALUES Pm>} ZBP>}P,+mvgB(e: CREATE TABLE my_table ( c1 VARCHAR(32), c2 INTEGER GENERATED BY DEFAULT AS IDENTITY, c3 INTEGER GENERATED ALWAYS AS (c2 + 1), ); C'"vBP INGEST |n: }]F/5CLrMN< 109 INGEST FROM FILE my_file.txt FORMAT DELIMITED INSERT INTO mytable; v 1!VN(ePm+G: ( $C1 CHARACTER(32), $C2 INTEGER EXTERNAL, $C3 INTEGER EXTERNAL ) v INSERT odOD1! VALUES PmG: VALUES($C1, $C2, DEFAULT) k"b,Z}v5G DEFAULT,r*Q+T&ZVN $C3 DP(e* GENERATED ALWAYS#Q!TZDv5,r*C5;PVN# CZFcP5DnbVN>} BP>}k(g{2G>}`,,+;P0=vVNT&Z0=vmP (PROD_ID M DESCRIPTION),xZ}vmP (TOTAL_PRICE) D5+S# `}vVNxPFc INGEST FROM FILE my_file.txt FORMAT DELIMITED BY ’|’ ( $prod_ID CHAR(8), $description CHAR(32), $price DECIMAL(5,2) EXTERNAL, $sales_tax DECIMAL(4,2) EXTERNAL, $shipping DECIMAL(3,2) EXTERNAL ) INSERT INTO my_table(prod_ID, description, total_price) VALUES($prod_id, $description, $price + $sales_tax + $shipping); nd{VN>} BP>}S|,I:EVt(1!5)DVND(gD>D~Pek}]#D ~PDVNT&ZmP,+ZT&ZP 2 M 3 DVNkT&ZP 3 M 4 D VN.dfZnbVN# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER, $field2 CHAR(8), $filler1 CHAR, $field3 CHAR(32), $filler2 CHAR, $field4 DATE ) INSERT INTO my_table VALUES($field1, $field2, $field3, $field4); q=^N{>} BP>}SICzk3 850 D(gD>D~Pek}]#UZVNIC@=q =,V{MVN|(ZHEZ# INGEST FROM FILE my_file.txt FORMAT DELIMITED INPUT CODEPAGE 850 ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, 110 }]F/5CLr8OMN< $field3 CHAR(32) ENCLOSED BY ’=’ ) INSERT INTO my_table VALUES($field1, $field2, $field3); ;C>} BP>}S|,;Z8(;CDVNDD~Pek}]#D~PDVNT&Z mP# INGEST FROM FILE my_file.txt FORMAT POSITIONAL ( $field1 POSITION(1:8) INTEGER EXTERNAL, $field2 POSITION(10:19) DATE ’yyyy-mm-dd’, $field3 POSITION(25:34) CHAR(10) ) INSERT INTO my_table VALUES($field1, $field2, $field3); DEFAULTIF >} K>}kO;v>}`F,+G1Z~vVNTUW*71,c!5CLra ek1!5: INGEST FROM FILE my_file.txt FORMAT POSITIONAL ( $field1 POSITION(1:8) INTEGER EXTERNAL, $field2 POSITION(10:19) DATE ’yyyy-mm-dd’ DEFAULTIF=’’, $field3 POSITION(25:34) CHAR(10) ) INSERT INTO my_table VALUES($field1, $field2, $field3); K>}kO;v>}`,,+1!8>{;Z}]P.sDPP: INGEST FROM FILE my_file.txt FORMAT POSITIONAL ( $field1 POSITION(1:8) INTEGER EXTERNAL, $field2 POSITION(10:19) DATE ’yyyy-mm-dd’ DEFAULTIF(35)=’’, $field3 POSITION(25:34) CHAR(10) ) INSERT INTO my_table VALUES($field1, $field2, $field3); `vdk4>} K>}S}v(gD>D~Pek}]: INGEST FROM FILE my_file.txt, my_file2.txt, my_file3.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) INSERT INTO my_table VALUES($field1, $field2, $field3); \@>} K>}S\@Pek}]: INGEST FROM PIPE my_pipe FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, }]F/5CLrMN< 111 $field3 CHAR(32) ) INSERT INTO my_table VALUES($field1, $field2, $field3); !n>} K>}S|,I:EVt(1!5)DVND(gD>D~Pek}]#D~ PDVNT&ZmP#C|n8(I DB2 \x(}g,IZ} K>}a"vxP8(ekw5j6D INGEST |n(1!ivBGIXBt/ D): INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART NEW ’ingestcommand001’ INSERT INTO my_table VALUES($field1, $field2, $field3); g{C|nZjI.0U9,G4zIT9CBP|n4XBt/C|n: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART CONTINUE ’ingestcommand001’ INSERT INTO my_table VALUES($field1, $field2, $field3); XBt/U9>} K>}a"vkOfD“XBt/>}”`,D INGEST |n: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART NEW ’ingestcommand001’ INSERT INTO my_table VALUES($field1, $field2, $field3); 112 }]F/5CLr8OMN< g{C|nZjI.0U9"RzF.;XBt/C|n,G4zIT9CB P|ne}XBt/G<# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART TERMINATE ’ingestcommand001’ INSERT INTO my_table VALUES($field1, $field2, $field3); "vK|n.s,z+;YXBt/xPw5j6“ingestcommand001”D INGEST |n,+\;ZB INGEST |nD RESTART NEW N}P4CCV{.# XBErP>} K>}S|,I:EVtDVND(gD>D~Pek}]#Cm|,}P, "Rdk}]PVND3rkmPD3r`4# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) INSERT INTO my_table VALUES($field3, $field2, $field1); y> UPDATE"MERGE M DELETE >} BP>}a|Bdw|kdkD~P`&VN`{DmP# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key1 INTEGER EXTERNAL, $key2 INTEGER EXTERNAL, $data1 CHAR(8), $data2 CHAR(32), $data3 DECIMAL(5,2) EXTERNAL ) UPDATE my_table SET (data1, data2, data3) = ($data1, $data2, $data3) WHERE (key1 = $key1) AND (key2 = $key2); r_ INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key1 INTEGER EXTERNAL, $key2 INTEGER EXTERNAL, $data1 CHAR(8), $data2 CHAR(32), $data3 DECIMAL(5,2) EXTERNAL ) UPDATE my_table SET data1 = $data1, data2 = $data2, data3 = $data3 WHERE (key1 = $key1) AND (key2 = $key2); K>}a+}]SdkD~O"=?jm#TZdw|VNkmP`{Ddk P,C>}a9CdkP4|BmP#TZd{dkP,C>}amSP=m P# }]F/5CLrMN< 113 INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key1 INTEGER EXTERNAL, $key2 INTEGER EXTERNAL, $data1 CHAR(8), $data2 CHAR(32), $data3 DECIMAL(5,2) EXTERNAL ) MERGE INTO my_table ON (key1 = $key1) AND (key2 = $key2) WHEN MATCHED THEN UPDATE SET (data1, data2, data3) = ($data1, $data2, $data3) WHEN NOT MATCHED THEN INSERT VALUES($key1, $key2, $data1, $data2, $data3); K>}a>}dw|kdkD~P`&w|VN`{DmP# INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key1 INTEGER EXTERNAL, $key2 INTEGER EXTERNAL ) DELETE FROM my_table WHERE (key1 = $key1) AND (key2 = $key2); 4= SQL >} },ZC>}P_P|, KEY"DATA M ACTION PDm#BP |na|Bdw|P (KEY) kdkD~P`&VN`{R ACTION PG“U” DmPD DATA P: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key_fld INTEGER EXTERNAL, $data_fld INTEGER EXTERNAL ) UPDATE my_table SET data = $data_fld WHERE (key = $key_fld) AND (action = ’U’); BP>}kO;v>}`,,+1|%d"R ACTION PG“D”1,C>}a +PSmP>}: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $key_fld INTEGER EXTERNAL, $data_fld INTEGER EXTERNAL ) MERGE INTO my_table ON (keyl = $key_fld) WHEN MATCHED AND (action = ’U’) THEN UPDATE SET data = $data_fld WHEN MATCHED AND (action = ’D’) THEN DELETE; B;=v24 g{ INGEST |nI&jI,G4zIT4C(} RESTART NEW N}8(DV{.# g{ INGEST |n'\"Rz*XBt/C|n,G4zXk9C-<|nP8(DV {.48( RESTART CONTINUE !n# 114 }]F/5CLr8OMN< g{;rcXBt/Q'\D INGEST |n"R*e}XBt/mPDu?,kZ8( RESTART TERMINATE !nDivBXBKP INGEST |n# XBt/'\D INGEST Yw: g{ INGEST |nZjI.0'\,"Rz*XBt/C|n,kXB"vx RESTART CONTINUE !nD INGEST |n#KZ~v INGEST |naSns;vd5ct/,"R 2GIXBt/D# *<.0 CZX B t / ' \ D INGEST | n D C ' j 6 X k T X B t / U > m _ P SELECT"INSERT"UPDATE M DELETE X(# XZKNq 13v|n=oD~r\@)21,INGEST 5CLraO*C|nQjI#ZNNd{ ivB,INGEST 5CLr} BP INGEST |n'\: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART NEW ’ingestjob001’ INSERT INTO my_table VALUES($field1, $field2, $field3); DBA a|}G<# *<.0 U9'\D INGEST |nDC'j6XkTXBt/U>m_P SELECT M DELETE X(# }L *U9'\D INGEST Yw,kXB"v INGEST |n# 8(_PJ1V{.D RESTART TERMINATE N}# 116 }]F/5CLr8OMN< a{ XBt/D INGEST |njI.s,ITTsfD INGEST |n4C RESTART NEW V {.# >} BP INGEST |n'\: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART NEW ’ingestjob001’ INSERT INTO my_table VALUES($field1, $field2, $field3); DBA ;#{XBt/ INGEST |n,yT{G9CTB|n(K|n|( RESTART TER- MINATE N})xPU9: INGEST FROM FILE my_file.txt FORMAT DELIMITED ( $field1 INTEGER EXTERNAL, $field2 DATE ’mm/dd/yyyy’, $field3 CHAR(32) ) RESTART TERMINATE ’ingestjob001’ INSERT INTO my_table VALUES($field1, $field2, $field3); `SekYw I9C INGEST LIST r INGEST GET STATS |n4`S INGEST |nDxH# *<.0 *"v INGEST LIST M INGEST GET STATS |n,zh*%@D CLP a0,+XkZ KP INGEST |nDzwOKPb)a0# }L `SekYwD==P\`V: v *q!PX10}ZKPDyP INGEST |nDy>E",k9C INGEST LIST | n# v *q!PXX( INGEST |nr10}ZKPDyP INGEST |nD|`j8E", k9C INGEST GET STATS |n# v 9I9CSZ(}g,MON_GET_CONNECTION m/})4i/TB`S*X: – client_acctng – client_applname – appl_name – client_userid – client_wrkstnname }]F/5CLrMN< 117 >} TB>}T>K INGEST LIST |nD$Zdv: INGEST LIST Ingest job ID = DB21000:20101116.123456.234567:34567:45678 Ingest temp job ID = 1 Database Name = MYDB Input type = FILE Target table = MY_SCHEMA.MY_TABLE Start Time = 04/10/2010 11:54:45.773215 Running Time = 01:02:03 Number of records processed = 30,000 TB>}T>K INGEST GET STATS |nD$Zdv: INGEST GET STATS FOR 4 Ingest job ID = DB21000:20101116.123456.234567:34567:4567 Database = MYDB Target table = MY_SCHEMA.MY_TABLE1 Records/sec Flushes/sec Records/sec Flushes/sec since start since start since last since last Total records ---------------- ----------------- --------------- ---------------- ---------------- 54321 65432 76543 87654 98765 TB>}T>KgN9C MON_GET_CONNECTION m/}4q!Q^DDP}Md 5N}: SELECT client_acctng AS "Job ID", SUM(rows_modified) AS "Total rows modified", SUM(total_app_commits) AS "Total commits" FROM TABLE(MON_GET_CONNECTION(NULL, NULL)) WHERE application_name = ’DB2_INGEST’ GROUP BY client_acctng ORDER BY 1 Job ID Total rows modified Total commits ---------------------------------------------- ------------------- ------------- DB21000:20101116.123456.234567:34567:45678 92 52 DB21000:20101116.987654.234567:34567:45678 172 132 2 record(s) selected. INGEST 5CLr^FMV^T 9C INGEST 5CLr1,fZE;)&C*@D^F# IXBt/T v g{dk}]4`MQ|D,G4 INGEST 5CLrI\^(lb=C| D,"R+zIk'\D-<|n;,DdvP# m'V v INGEST 5CLr;'VT DB2 for Linux, UNIX and Windows m4PY w# v INGEST 5CLr;'VkTBPZ]DYw: – Q4(rywD+VY1m – `Mm – x`MS< 118 }]F/5CLr8OMN< dk`M"q=MP`M v INGEST 5CLr;'VBPP`M: – sTs`M(LOB"BLOB"CLOB M DBCLOB) – XML – a9/`M – _PC'(eD}]`MDP,C}]`MyZH0P>DNN`M v mb,INGEST 5CLrTzIP_PBP^F: – INGEST 5CLr;\T(e* GENERATED ALWAYS DPxP35# g{ INGEST |nPD SQL odG INSERT r UPDATE,x?jm_ P GENERATED ALWAYS P,G4}Gz4PBPdP;vYw,q rekr|BYw+'\ (SQL0798N) "R INGEST |nax: - S*|BDPDPmPT%CP# - Z INSERT r UPDATE odP,+ DEFAULT 8(*3xCPD5# – INGEST 5CLr;\+1!5MX(5DiO3x(e* GENER- ATED BY DEFAULT AS IDENTITY DP#g{ INGEST |nPD SQL odG INSERT r UPDATE,x?jm_P GENERATED BY DEFAULT AS IDENTITY P,G4}Gz4PBPdP;vYw,qrekr|B Yw+'\ (SQL0407N) "R INGEST |n+\xCG<: - S*|BDPDPmPT%CP# - Z INSERT r UPDATE odP,+ DEFAULT 8(*3xCPD5# - +Fca{@6;a* NULL Dmo=8(*3xCPD5#}g,g {mo=G $field1,G4 $field1 ZdkGekYw# v SET UTIL_IMPACT_PRIORITY |n;0l INGEST |n v util_impact_lim }]b\mwdCN};0l INGEST |n v }K CURRENT SCHEMA"CURRENT TEMPORAL SYSTEM_TIME M CURRENT TEMPORAL BUSINESS_TIME Tb,INGEST 5CLravT T4P SQL odP0lDs`}(CDfwDhC# ;c INGEST 5CLr^F v g{zek_P`vy>mDS<,G41dPDNNy>m\2+_T# $1,Xk9C`,D2+_T4#$b)y>m#(zT;ITC3)y >m&Z4#$4,,+G)\#$Dy>mXk9C`,D2+_T#) GF'V v g{ INGEST |n8(r_Z1!ivB8( RESTART NEW r RESTART CONTINUE !n,"R?jmGGFr|BGFDI|BS < , k 7 # * | , C G F D ~ q w ( e + DB2_TWO_PHASE_COMMIT ~qw!nhC*“Y”# }]F/5CLrMN< 119 v Z"v INGEST |n.0,;\9C SET SERVER OPTION 4tC= WNd5,bGr* SET SERVER OPTION |n;T CLP ,Sp wC,x INGEST |na("T:D,S#XkZ?mP,G 4IZ|BC MQT h*1d,yTT\aTx5M# Ps! TZPs!O!Dm,kvs commit_count ekdCN}DhC;TZPs! OsDm,ku! commit_count ekdCN}DhC# d{$w:X g{*9Cd{$w:X44P INGEST 5CLr,kvs commit_count } ]bdCN}DhC"u! commit_count ekdCN}DhC# INGEST 5CLrDzk3"bBn 1 INGEST 5CLr&mdk}]1,+f0}vzk3:&CLr(M'z)zk 3"dk}]zk3M}]bzk3# zk3 8(== 1!5 &CLr(M'z)zk3, Z CLP |nD~P9C (}10oT737( (}10oT737( dk}]zk3 INGEST | n O D INPUT CODEPAGE &CLrzk3 }]bzk3 Z CREATE DATABASE |nO8 ( 1208(Unicode D UTF-8 ` k) g{dk}]zk3k&CLrzk3;,,G4 INGEST 5CLraY1+&CLr zk32G*dk}]zk3,Tc DB2 +}]1SSdk}]zk3*;*}]b zk3#Z3)u~B,INGEST 5CLr^(2G&CLrzk3#ZKivB, INGEST 5CLraH+4(e* FOR BIT DATA DV{}]*;*&CLrzk 3,;sY+]x DB2#ZyPivB,g{4+P(e* FOR BIT DATA,G4 DB2 a+}]*;*}]bzk3# CLP |nD~zk3 INGEST 5CLrY( INGEST |nDD>IC&CLrzk3,.yxF# 120 }]F/5CLr8OMN< ?}b#?1 INGEST 5CLrh*HO INGEST |nP8(DV{.(}g, + DEFAULTIF V{kdk}]PDV{xPHO)1,INGEST 5CLra 4PNNX*Dzk3*;,T7#yHODV{.Z`,Dzk3P# INGEST 5CLrM DB2 <;aT.yxF#?4PNN*;# dk}]zk3 g{VNM|yZDmP<(e* FOR BIT DATA,G4 INGEST 5CLr M DB2 <;a4PNNzk3*;#}g,Y( INGEST |n+VN $c1 8 (xP C1,"RCVNMCP<(e* CHAR FOR BIT DATA#g{dkV N|, X’E9’,G4^[dk}]zk3r}]bzk3G24,DB2 }{wKbViv: v dk}]zk3* 819# v &CLrzk3* 850# v }]bzk3* 1208 (UTF-8)# v dk}]*“é”(xPXt{D“e”),CV{Zzk3 819 P* X’E9’,Z zk3 850 P* X’82’,Z UTF-8 P* X’C3A9’# BmT>K}]Z~qwODa{,|!vVN0/rPGq(e* FOR BIT DATA T0 INGEST 5CLrGqIT2G&CLrzk3: m 16. 1VNMP(e<(e* FOR BIT DATA 1DI\a{ VN(e P(e dk}](z k3 819) } ] Z INGEST 5C Lr+d*; =&CLrz k3 850 . sDa{ 1 INGEST 5 CLrIT2 G&CLrz k31}]Z ~qwODa { 1 INGEST 5CLr^( 2G&CLr zk31}] Z~qwOD a{ CHAR CHAR X’E9’ X’82’ X’C3A9’ X’C3A9’ CHAR FOR BIT DATA CHAR FOR BIT DATA X’E9’ X’E9’ X’E9’ X’E9’ CHAR FOR BIT DATA CHAR X’E9’ X’E9’ X’C3A9’ X’C39A’ (″Ú″) CHAR CHAR FOR BIT DATA X’E9’ X’82’ X’E9’ X’82’ ZDPPD}]G INGEST 5CLrZdIT2G&CLrzk31"MA DB2 DZ]#ZDPPD}]G INGEST 5CLrZd^(2G&CLrz k31"MDZ]#k"b,1VN(eMP(eD FOR BIT DATA tT ;,1,a{I\;,,gOmy># zk3ms g{dkzk3"&CLrzkr}]bzk3;`,,G4 INGEST 5CL rM/r DB2 +4Pzk3*;#g{ DB2 ;'VBPNNivBDzk3 * ; , G 4 INGEST 5 C L r a " v m s " R | n a x # }]F/5CLrMN< 121 m 17. BPivBh**; ZKivB,STB zk3*; *;= *;4P_ INGEST |n|,h* *;=dk}]zk 3DV{.r SQL j 6# &CLrzk3 dk}]zk3 INGEST 5CLr 5CLrIT+&C Lrzk3f2G* dk}]zk3# dkzk3 }]bzk3 DB2 5CLr^(2G& CLrzk3# dkzk3 &CLrzk3 INGEST 5CLr 5CLr^(2G& CLrzk3# &CLrzk3 }]bzk3 DB2 DB2 pureScale73PDekYw Z DB2 pureScale73P9C INGEST 5CLr1,h*: max(1, ((number of logical CPUs)/2)/(number of partitions) ) 2IT+KN}hC* 0,bm>yPVx9C;veULr# ?veULr: 1. 7(?vV<|Gq}C_P;v`&VNr#?5#bZBPivB+* true: v TZ INSERT od,PPm|,?vV<|,"RTZ?vV<|,VALUES P mPD`&n?GVN{r#?# v TZ UPDATE r DELETE od,WHERE =JtZCq= 122 }]F/5CLr8OMN< (dist-key-col1 = value1) AND (dist-key-col2 = value2) AND ... (dist-key-coln = valuen) [AND any-other-conditions] dP dist-keycol1 = dist-key-coln GyPV<|,"R?v value GVN{r# ?# v TZ MERGE od,Qwu~_PH0* UPDATE M DELETE T>Dq=# 2. g{?vV"|}CP;vT&VNr#?5,G4 INGEST 5CLr9CCV< |47(VxE,;s+G<7IACVxDdP;veULr# ":ZBPivB,INGEST 5CLr;a7(G}P,1Y|P 2 A 8: UPDATE my_table SET data = $data WHERE (key1 = $key1) AND (key9 = $key9); v ;vV<|T&Z`vVNr5,gTB>}y>: UPDATE my_table SET data = $data WHERE key1 = $key11 OR key1 = $key12; v ;vV<|T&Z;vmo=,gTB>}y>: INGEST FROM FILE ... INSERT INTO my_table(dist_key, col1, col2) VALUES($field1 + $field2, $col1, $col2); v V"|PD`M* DB2SECURITYLABEL# v kV"|`T&DVN_P}V`M,+V"|P`MGm;}V`Mr__P ;,+Hr!};# y> INGEST 5CLrE> IT9C INGEST 5CLry>E>4ZPBD~*&m1T/`4BD INGEST | n# y>E> ingest_files.sh G;v shell E>,|T/liBD~"zI INGEST | n4&mb)D~#CE>43r4PBPNq: 1. li?+Kv# ":CE>Y(8(D?# kT73^DCE> IT9C ingest_files.sh E>w*zT:DE>Dy!#TBGzXkTCE>wv DX*^D: v +y>5(4,}]b{FMm{)f;*zT:D5 v +y> INGEST |nf;*zT:D|n }]F/5CLrMN< 123 v 4(CE>P8(D?< CE>&m|,}]DD~Tnd%vm#*nd`vm,ITT*ndD?vmX 4CzF,r_9CzF;c/T&m`vm# y>=8 D5P|,K;vy>=8,T5wgN9y>E>JOZzD}]Vb4T/zI BD INGEST |n# =8:9C INGEST 5CLr&mD~w BP=8T>KgN+}]VbdCIT/ekxPPD}]D~w# Jb:Z3)}]VbP,D~+l,CZT/liBD~"zIBD INGEST |n"KP C|n#ingest_files.sh GK`E>D;vy>#z9h*4( crontab u?E\8 ( Shell E>&CKPD5J# ZC'5VKzF(4,E>M chrontab u?)T&mD~w.0,C'h*zcBP Hvu~M@5X5: v Z?j}]bPQ4(K?jm v INGEST 5CLrQI)9C(4,QZM'zO20"hCKC5CLr) v Q(}9CbTD~4V/KP INGEST |n,8(C|n"TdxPKi$ v Q4( INGEST |nP}CDTs(}g,l#m) v QZKP INGEST 5CLrD53O4(K crontab D~ v C'_PCZ4(dkD~"+dF=E>y9CD4?: a. f;BPy>Ddk5,T43C'D5: v INPUT_FILES_DIRECTORY v DATABASE_NAME v SCHEMA_NAME v TABLE_NAME v SCRIPT_PATH b. f;y> INGEST |n c. +E>mf* populate_table1_script 2. C'mS;vu?= crontab D~,T8(E>*KPD5J#r*C'*CE>? VSKP;N"?l 24 !1KP"?jPD?;l# 124 }]F/5CLr8OMN< d{}]F/!n (}9C ADMIN_MOVE_TABLE }L4T*z==F/m I(}9C ADMIN_MOVE_TABLE }L4T*zrQz==F/m#g{`TZI >"Ud"F/T\MBq*z,z|XSICT,k9CmD*zF/x;GmD QzF/# *<.0 k7#Pc;DELUd4]ImMw}D1>"G(mT0=SU>u?# XZKNq I(}wCf"}L;Nr`N4T*z==F/m,C}L4PD?vYw#Z4FW NZd,a9C%"w46qT4mD|D(|B"ekr>})"+dEC=G( mP#14FWNjIs,aT0S1>XEG(mP6qD|D#.s,f"}L a8Y94mQz"+4m{FMw}{F8(x0S1>0dw}#;s,90S m*z,Sxf;4m#1!ivB,a>}4m,+IT9C KEEP !n4Tm;v {F#tC4m# TZ;xw}(XpG(;w})Dm,k;*4P*zF/#T;x(;w}Dm 4P*zF/I\} >} 1:Z;9 T1 QzDivB,+ T1 mS#= SVALENTI F= ACCOUNT- ING mUdP#8( DATA"INDEX M LONG mUdT+mF/=BDmUd# CALL SYSPROC.ADMIN_MOVE_TABLE( ’SVALENTI’, ’T1’, ’ACCOUNTING’, ’ACCOUNTING’, ’ACCOUNTING’, ’’, ’’, ’’, ’’, ’’, ’MOVE’) >} 2:Z;9 T1 QzDivB,+ T1 mS#= EBABANI F= ACCOUNTING mUdP,"RZF/s#t-#9C COPY_USE_LOAD M LOAD_MSGPATH !n4hC0k{"D~76#8( DATA"INDEX M LONG m UdT+mF/=BDmUd#-} 3:Z,;mUdPF/ T1 m#+ T1 PD C1 P(9C;FvD}]`M LONG VARCHAR)|D*9Cf]D}]`M# CALL SYSPROC.ADMIN_MOVE_TABLE( ’SVALENTI’, ’T1’, ’’, ’’, ’’, ’’, ’’, ’’, ’C1 VARCHAR(1000), C2 INT(5), C3 CHAR(5), C4 CLOB’, ’’, ’MOVE’) ":ZKYwZd;\|DP{# >} 4:z(}TBod4( T1 m: CREATE TABLE T1(C1 BIGINT,C2 BIGINT,C3 CHAR(20),C4 DEC(10,2),C5 TIMESTAMP,C6 BIGINT GENERATED ALWAYS AS (C1+c2),C7 GRAPHIC(10),C8 VARGRAPHIC(20),C9 XML Z,;mUdZF/Cm,">}P C5 M C6: CALL SYSPROC.ADMIN_MOVE_TABLE( ’SVALENTI’, ’T1’, ’’, ’’, ’’, ’’, ’’, ’’, ’c1 BIGINT,c2 BIGINT ,c3 CHAR(20),c4 DEC(10,2),c7 GRAPHIC(10), c8 VARGRAPHIC(20),c9 XML’, ’’, ’MOVE’) >} 5:zD6'Vxm_PmUd TS1 M TS2 P(eD=v6'#+CmFAmU d TS3,+#t TS1 PDZ;v6'# CREATE TABLE "EBABANI "."T1" ( "I1" INTEGER , "I2" INTEGER ) DISTRIBUTE BY HASH("I1") PARTITION BY RANGE("I1") (PART "PART0" STARTING(0) ENDING(100) IN "TS1", PART "PART1" STARTING(101) ENDING(MAXVALUE) IN "TS2"); + T1 mS#= EBABANI FA TS3 mUd#8(Vx(e# DB2 "CALL SYSPROC.ADMIN_MOVE_TABLE (’EBABANI’, ’T1’, ’TS3’, ’TS3’, ’TS3’, ’’, }]F/5CLrMN< 127 ’’, ’(I1) (STARTING 0 ENDING 100 IN TS1 INDEX IN TS1 LONG IN TS1, STARTING 101 ENDING MAXVALUE IN TS3 INDEX IN TS3 LONG IN TS3)’, ’’, ’’, ’MOVE’)" IBM 4F$_(4i~) IBM a)K=Vw*D4Fbv=8:Q 4FM SQL 4F# Q 4FDw*i~* Q Capture LrM Q Apply Lr#SQL 4FDw*i~* Cap- ture LrM Apply Lr#b=V`MD4F<2m“4F(/`Sw”$_#IT9C4 FPDM ASNCLP |nPLr4hC"\mb)4Fi~# TBPmr*EvKb)4Fi~: Q Capture Lr A! DB2 V4U>"iRT DB2 4mywD|D,;s+Qd5D4}]*;*I T XML q="Tq!TQ"aD4mrSD#e# }L v 9C ADMIN_COPY_SCHEMA }LZ,;}]bP4F%v#=# v + db2move 5CLrk -co COPY YwdO9C,Tc+4}]bPD;vr`v #=4F=?j}]b# 4#=PDs?V}]bTs;4F=B#=BD?j}] b# JOoOa> ADMIN_COPY_SCHEMA }LM db2move 5CLrD?vm<"v SET INTEG- RITY od,T9?vmlb=;\'VDTs`M#COPY YwT +I&;G#a+Cmw*#fmXB 4(# ;,D5} g{4}]bk?j}]b;Z,;5}P,G4XkT4}]bxP`?# SCHEMA_MAP !n 9C SCHEMA_MAP !n48(?j}]bODd{#={F1,4F#=Y w+vTTs(eod4Pn!LHDbv,Tc+-<#={Ff;*B# ={F#}g,Z SQL }LDZ]PvVD-<#=DNN5}y;af;* B#={F#rK,4F#=YwI\^(XB4(b)Ts#d{>}I\ |(G(m"a{mM_e/i/m#jI4FYws,I9CmsD~PD DDL 4V/XB4(b)'\DTs# Ts.dD`%@5T 4F#=Yw"TTzcb)Ts.dD`%@5TD3r4XB4(Ts# }g,g{m T1 |,D3;P}CKC'(eD/} U1,G4+HXB4( U1,;sYXB4( T1#+G,?