分区和集群指南


IBM DB2 10.1 for Linux, UNIX, and Windows VxM/:8O S151-1754-00  IBM DB2 10.1 for Linux, UNIX, and Windows VxM/:8O S151-1754-00  "b 9CKE"0d'VDz70,kHDAZ 407 3D=< E, :yw;BD#fE"# ^)fyw KD5|, IBM DyP(E"#|ZmI-iPa),R\f((D#$#>vfoP|,DE";|(TNNz7D #$,Ra)DNNod<;h*gKbM# zIZ_r(}1XD IBM zm&): IBM vfo# v *Z_):vfo,k*A IBM vfoPD,x7*:http://www.ibm.com/shop/publications/order v *iR1XD IBM zm&,k*A IBM +r*5K?<,x7*:http://www.ibm.com/planetwide/ *S@zrSCsD DB2 P!Mz[?): DB2 vfo,kBg 1-800-IBM-4YOU(426-4968)# z"ME"x IBM s,4Zh IBM G@<(^,IBM IT4|O*J1DNN==9CrV"zya)DNNE"x ^kTzP#NNpN# © Copyright IBM Corporation 2012. ?< XZ>i ..............vii >iJCTs ..............vii >iDa9 ..............vii ;vT><( ..............x Z 1 ?V f.MhF"bBn ....1 Z 1 B Vx}]bMm ........3 hCVx}]b73 ............3 g`v}]bVxT}]bxPVx......4 Vx}]bO$"bBn .........5 }]bVxi .............5 V"3d...............7 V<| ...............8 m"C ...............9 Vxf]T ..............9 Vxm ................10 mVx ...............11 }]VxM6' ............12 }]i/=8 .............13 DB2 M Informix }]bPD}]i/=8 ...16 mVx| ..............21 VxmD0k"bBn ..........23 4FM_e/i/m ..........25 }]bVxiPDmUd .........25 mVxM`,/:m ..........26 DB2 pureScale 73PDmVx .......29 Z 2 B 6'/:m .........31 T6'/:mD^F ...........32 Z 3 B `,/: (MDC) m ......33 `,/:m ..............33 #fmk MDC mDHO ..........33 !q MDC m, .............35 4( MDC r ITC m1D"bBn ......41 MDC M ITC mD0k"bBn ......45 MDC M ITC mDU>G<"bBn .....47 MDC M ITC mDiw}"bBn ......47 MDC mDiw} ............47 =8:`,/: (MDC) m .........49 MDC mDiw}Mi/T\.........52 Z INSERT YwZdT/,$/:.......55 MDC M ITC mDi3d ..........56 S MDC M ITC mP>} .........58 T MDC M ITC mD|B .........58 `,/:Mek1d/:)9}]i\m .....58 mVxM`,/:m ...........59 Z 4 B "P}]b53........63 "PT ................63 Vx}]b73 .............66 }]bVxM&mw73 ..........67 Z 2 ?V 20"bBn ......75 Z 5 B 20Hvu~ ........77 9C“DB2 20”r<420 DB2 }]b~qw (Windows) ...............77 *Vx DB2 ~qw<873(Windows) ...79 lY(E\mw(Windows)........81 DB2 }]b~qw20Ev(Linux M UNIX) ..81 DB2 20=( ............82 9C“DB2 20”r<420 DB2 ~qw(Linux M UNIX) .............84 Z 6 B 20.0 ..........89 d{Vx}]b73$20Nq(Linux M UNIX) 89 |BCZVx DB2 20D73hC(AIX) ..89 hC$w/OT+|nV"A`v AIX Zc ..91 i$ NFS GqZKP(Linux M UNIX) ...91 i$NkFczODICKZ6'(Linux M UNIX)...............92 *Vx}]b534(D~53 (Linux) ....93 *Vx}]b534( DB2 wD~53(AIX) 95 20 DB2 pureScale Feature 1yhDC' (Linux) 97 ZVx}]b73P*20 DB2 ~qw4(Xh C'(AIX) .............98 Z 7 B 20 DB2 ~qwz7 ....101 hCVx}]b73 ...........101 9Cl&D~ZNkDFczO20}]bVx~q w(Windows) .............103 9Cl&D~ZNkDFczO20}]bVx~q w(Linux M UNIX) ..........104 Z 8 B 20.s ..........107 i$20 ...............107 i$Vx}]b7320 (Windows) .....107 i$Vx}]b~qw20(Linux M UNIX) 107 Z 3 ?V 5)M,$ .......109 Z 9 B 4(}]b.0 .......111 hCVx}]b73 ...........111 4(ZcdCD~ ............112 DB2 ZcdCD~Dq= ........113 8(Vx}]b73PDzwPm......119 }%Vx}]b73ZzwPmPDX4u? . . 120 |BZcdCD~(Linux M UNIX) ....120 © Copyright IBM Corp. 2012 iii hC`_-Vx............122 dC`_-Vx............122 tCVxdDi/"PT..........123 Ti/tCVxZ"PT..........124 }]~qw]?D\m ..........124 lY(E\mw.............125 lY(E\mw(Windows) .......125 lY(E\mw(Linux M UNIX) .....125 9C FCM (E4tC}]bVx.dD(E 126 tC}]bVx~qw.dD(E(Linux M UNIX) ..............127 Z 10 B 4(M\mVx}]b73 129 \m}]bVx.............129 ZVx}]b73PmS}]bVx .....129 mS*z}]bVx ..........130 1T*z==$w4mS}]bVx1D^F . . 131 ZQz4,BmS}]bVx (Windows) . . . 131 mSQz}]bVx(Linux M UNIX) ...132 mS}]bVx1DmsV4 .......134 >}}]bVx............135 Z5}PP>}]bVx~qw (Windows). . . 136 +}]bVx~qwmSA5}(Windows) . . 136 |D}]bVx(Windows) .......137 r}]bVxOD SMS mUdmS]w ...139 S5}P>}}]bVx(Windows) ....139 =8:ZB}]bVxPXBV"}] .....140 ZVx}]b73P"v|n ........143 rah M db2_all |nEv ..........143 8( rah M db2_all |n ........144 T"P==KP|n(Linux M UNIX) ...145 )9 rah |nT9Cw_-(AIX M Solaris) 146 rah M db2_all |n ..........146 rah M db2_all |n0:3r .......146 XF rah |n ............148 8(k rah ;pKPD . D~(Linux M UNIX) ..............150 7( rah DJb(Linux M UNIX) .....150 `S rah xL(Linux M UNIX) .....152 Z Windows O* rah hC1!73E*D~ 152 Z 11 B 4(mMd{`XmTs ...155 Vx}]b73PDm ..........155 VxmPDsTsP* ..........156 4(Vxm ..............157 (eVxmD6' ...........157 }]Vx}]"w}M${M}]DEC ...160 +VPmMS<(F=Vxm .......161 +VPw}*;*Vxw} ........163 QVxD_e/i/m (MQT) P* ......164 4(6'/:m.............167 XZ9C6'/:mD}}]Vx .............198 =8:}*VxmPD}] .........199 =8:*kM*vVxm}] ........201 Z 14 B 0k ...........205 "PTM0k .............205 MDC M ITC "bBn ..........205 VxmD0k"bBn ..........206 Z 15 B ZVx}]b73P0k}] 209 load Ev - Vx}]b73 ........209 ZVx}]b73P0k}] - a>k} ....224 (FMf>f]T ............227 Z 16 B Vx}]bD(F73 ....229 (FVx}]b.............229 Z 17 B 9ClUMB~`Sw ....231 9ClU`Sw}]4`SVxmDXi ....231 Vx}]b53OD+VlU ........238 *Vx}]br* DB2 pureScale 73PD}]b4 (B~`Sw .............239 Z 18 B *"CD8]MV4_T ...241 @#V4 ...............241 SVx}]b73PDBqJOxPV4 ....242 S}]bVx~qwDJOV4 .......245 X(Vx}]b.............245 9C db2adutl 4V4}] .........246 9Vx}]b73PD1S,= .......259 Z 19 B JOoO .........261 iv VxM/:8O oOVx}]b73 ...........261 ZVx}]b73P"v|n .......261 Z 4 ?V T\Jb ........263 Z 20 B }]bhFPDT\Jb ...265 T\v?&\ .............265 mVxM`,/:m ..........265 VxmDE/_T ...........268 MDC mDE/_T ..........273 Z 21 B w} ...........277 VxmPDw}.............277 VxmODw}P* ..........277 VxmDGVxw}D/: ........281 Z 22 B hFKJLr .......285 9ChFKJLr+%;Vx}]b*;*`Vx} ]b ................285 Z 23 B \m"PT ........287 MDC mM ITC m0 RID w}(hDx(== . . 287 MDC iw}(hDx(== ........290 TVxmDx(P* ...........294 Z 24 B zmLr\m .......297 Vx}]bPDzmLr..........297 Z 25 B E/f!=8 .......299 w}CJM/:HJ ...........299 MDC M ITC mDmMw}\m ......299 VxZ"PTDE/_T..........301 ,S ................303 }]bVxiTi/E/D0l ......304 Vx}]bD,S_T .........304 CZVx}]bD,S=( ........305 Vx}]b73PD4FM_e/i/m ...311 ZVx}]b73PTmP4(d{w} ....313 B;=gNYw............315 Z 26 B }]XBV" .......317 xPU>GG< D;I0vV4XBV"DHO .......317 }]XBV"DHvu~..........319 T}]XBV"D^F ..........320 7(Gqh*XBV"}] .........321 (}9C REDISTRIBUTE DATABASE PARTITION GROUP |nZ}]bVx.dXBV"}] ...322 Z}]bVxiPXBV"}] .......324 }]XBV"DU>Ud*s ........324 XBV"B~U>D~ ..........325 9C STEPWISE_REDISTRIBUTE_DBPG }L4X BV"}]bVxi ...........325 Z 27 B dCTw{Zf.......329 Vx}]b73PDTw{Zf&\ ......329 ZVx}]b73P9CTw{Zf&\ ....330 Z 28 B DB2 dCN}Md? ....333 dCg`vVxD}]b..........333 Vx}]b73d? ...........334 Vx}]b73dCN}..........336 (E ...............336 "P&m ..............340 Z 5 ?V \m API, |nM SQL od ...............343 Z 29 B \m API.........345 sqleaddn - +}]bVxmSAVx}]b73 . . 345 sqlecran - Z}]bVx~qwO4(}]b ...346 sqledpan - >}}]bVx~qwOD}]b ...348 sqledrpn - li\q>}}]bVx~qw ....349 sqlugrpn - *Pq!}]bVx~qwE ....350 Z 30 B |n ...........355 REDISTRIBUTE DATABASE PARTITION GROUP 355 db2nchg - |D}]bVx~qwdC .....362 db2ncrt - +}]bVx~qwmSA5} ....363 db2ndrop - S5}P>}}]bVx~qw ...364 Z 31 B SQL oT*X .......367 }]`M ...............367 k}]bVxf]D}]`M .......367 (CDfw ..............368 CURRENT MEMBER ..........368 Z 32 B SQL /} .........371 DATAPARTITIONNUM ..........371 DBPARTITIONNUM ...........372 Z 33 B SQL od .........375 ALTER DATABASE PARTITION GROUP ....375 CREATE DATABASE PARTITION GROUP . . . 378 Z 34 B \'VD SQL \m}LMS < ................381 ADMIN_CMD f"}LMX*D\m SQL }L 381 9C ADMIN_CMD }LD GET STMM TUNING |n ............381 9C ADMIN_CMD }LD UPDATE STMM TUNING |n ............382 dC\m SQL }LMS< .........383 DB_PARTITIONS ...........383 4=hXBV"\m SQL }L .......384 STEPWISE_REDISTRIBUTE_DBPG }L - XB V"?V}]bVxi .........384 Z 6 ?V =< ..........387 =< A. w*G root C'20 ....389 ?< v w*G root C'20 DB2 }]b~qw ....389 =< B. 9C8] ..........391 8]}] ...............391 =< C. Vx}]b73? SQL 4,oz ......401 CJ;,f>D DB2 E"PD .......401 |B20ZFczrZ?x~qwOD DB2 E"P D .................402 V/|B20ZFczrZ?x~qwOD DB2 E "PD................403 DB2 LL...............405 DB2 JOoOE"............405 E"PDunMu~ ...........405 =< E. yw ............407 w} ...............411 vi VxM/:8O XZ>i DB2® X5}]b\m53D&\Z\sLHO\VxM/:&\0l,b=V&\J m\m1M53Yw1P'Xv?}]bT\"+m`}]bTsVi|, DB2 }]bbPD;vPi /Dwb/,|NI;va;DE"4,;XcV[f."hF"5)"9CT0} ]bVx"mVx"m/:"m6'/:"`,/:mM"PTD,$# >iJCTs >iw*frh*hF"5Vr,$+I>XM6LM'zCJDVxr/:}]b D}]b\m1"53\m1"2+\m1M53Yw1#&CLr*"_Md{C '2I(}>iqCa;E"4,"SPKb DB2 X5}]b\m53DVx"/: M"PT&\D\mMYw==#TZG)X"RGK&V[DNNryPw*&\ D+45)DK45,>iGQCDNiDa9 DB2 bPDKwb/a);va;DE"4,|;XcV[ DB2 Vx"/:M"PT &\#*K9z\;=cP'X9C>i,|V*yvw*?V,0ev?Vm>\ m1"53Yw1M&CLr*"_yXDDw*\mwb#>iw*?VPDwb I3dAm> DB2 bPm;>iDZ]Dwb,Sx9z\;=cX;f}C|`; cE",r*|f0s?d{ DB2 &\MTs#}g,ZDAZ 4 ?VZ 20 BPX Z`,/:mDE/_TgNDFT\Dwbs,zI\#{(}DACX(>}w b3dAD6w{}]bT\7;i,4li#fmOIdCDd{;cT\v?& \#ZBfDm 1 P,z+"V>iP3dAd{i.Dw*?V,(}NDb)i .,zITKbXZ`Fwb|,Dd{ DB2 TsM&\D|`E"# m 1. >iw?VA DB2 bPDd{i.D3d 6VxM/:8O7PDw?V 3dA DB2 bPDi. Z 1 ?V f.MhF"bBn }]b\mEnMdCN< }]b2+T8O Z 2 ?V 20"bBn }]b\mEnMdCN< 20 DB2 ~qw © Copyright IBM Corp. 2012 vii m 1. >iw?VA DB2 bPDd{i.D3d (x) 6VxM/:8O7PDw?V 3dA DB2 bPDi. Z 3 ?V 5)M,$ Data Movement Utilities Guide and Reference }]V40_ICT8OkN< }]b\mEnMdCN< }6= DB2 V10.1 }]b`S8OMN< Visual Explain LL XQuery N< Z 4 ?V T\Jb }]b\mEnMdCN< JOoOMw{}]bT\ Visual Explain LL Z 5 ?V \m API"|nM SQL o d Administrative API Reference Administrative Routines and Views Command Reference *" ADO.NET M OLE DB &CLr *"6k= SQL &CLr Developing Java Applications Developing Perl, PHP, Python, and Ruby on Rails Appli- cations *"C'(eD}L(SQL Mb?}L) }]b&CLr*"kE SQL Reference Volume 1 SQL Reference Volume 2 Z 6 ?V =< }]V40_ICT8OkN< 20 DB2 ~qw SQL Reference Volume 1 >iDwBZPV[KBPw*wblr: Z 1 ?V f.MhF"bBn BPyPBZ<|,kf.MhF}]b/m`XDEnTE",+Tb)} ]b/mxPVxr/:,r_+|GCZ"P}]b53P# v Z 1 B:Vx}]bMm;}kKXZT}]bMmxPVxD&\MC& D`XEn# viii VxM/:8O v Z 2 B:6'/:m;a)KXZ9C6'/:mD&\MEcD;cEn TE"# v Z 3 B:`,/: (MDC) m;hvK9C`,/:w*TmP}]xP/ :D+Q=(# v Z 4 B:"P}]b53;hvKgN{C"PT+sXa_T\# Z 2 ?V 20"bBn BPBZa)KXZZ<8}]bVx1h*4PD$20M20NqDE "# v Z 5 B:20Hvu~;hvKk<8Vx}]b73Pf0D DB2 ~q w`XDHvu~M^F# v Z 6 B:20.0;V[K UNIX M Linux Yw53PDd{$20Nq M"bBn# v Z 7 B:20 DB2 ~qwz7;hvKgN20}]bVx~qwMhC Vx}]b73# v Z 8 B:20.s;hvKgNi$ Windows"UNIX M Linux Yw53 OD20# Z 3 ?V 5)M,$ jIf."hFM20=hs,BPBZV[KgN5)M,$H0<8$w zID&\M/rTs# v Z 9 B:4(}]b.0;hvKZ4(}]b.0&i ix Z 4 ?V T\Jb BPBZ|,Jmza_VxM/r/:73DT\D`XE"# v Z 20 B:}]bhFPDT\Jb;hvKmVxM`,/:DT\v?& \,|(mVxM`,/:DE/_T# v Z 21 B:w};a)PzZKbVxmw}DEnTE"# v Z 22 B:hFKJLr;hvKgN9ChFKJLr4q!XZS%;V x(FA`Vx}]bDE",T0XZV"}]M4(Bw}"_e/i /m0`,/:mD(i# v Z 23 B:\m"PT;a)KXZx(==DE"# v Z 24 B:zmLr\m;hvKgNE/CZ&m&CLrksD}]bz mLr# v Z 25 B:E/f!=8;hvKgNDFf!=8T0E/wgN9C(} wV(hqCDE"4E/}]CJ_T,"R|(XZ,S_TDE", yPb)KkVx}]b73M"PT& \`XDN}Md?# Z 5 ?V \m API"|nM SQL od BPBZ+kVx}]b73`XD\m API"|nM SQL *XDE"+?O "Z;p# v Z 29 B:\m API;a)KvkVx}]b73`XD API DE"# v Z 30 B:|n;a)KvkVx}]b73`XD|nDE"# v Z 31 B:SQL oT*X;a)Kk}]bVxf]D}]`MM(CDf w# v Z 32 B:SQL /};hvKvkVx}]b73`XD SQL /}# v Z 33 B:SQL od;hvKvkVx}]b73`XD SQL od# v Z 34 B:\'VD\m SQL }LMS<;hvKvkVx}]b73`X D SQL }LMS<# Z 6 ?V =< v =< A:w*G root C'20;hvKw*G root C'Z UNIX M Linux Yw53O20 DB2 }]bz7D}L# v =< B:9C8];hvKgN9C BACKUP DATABASE |n# v =< C:Vx}]b73?KVx}]b73DXb?<( >iP9CKBP;vT><(# Ve 8>|n"X|VMd{I53$(e{FDn# x VxM/:8O 1e 8>BPdP;Viv: v XkIC'a)D{Fr5(d?) v ;c?w v }kBuo v }Cd{E"4 HmVe 8>BPdP;Viv: v D~M?< v 8>z&Z|na>{&r0ZPdkDE" v X(}]5D>} v `FZ53I\T>DD>DD>>} v 53{">} v Lrzk>} XZ>i xi xii VxM/:8O Z 1 ?V f.MhF"bBn © Copyright IBM Corp. 2012 1 2 VxM/:8O Z 1 B Vx}]bMm hCVx}]b73 XkZ4(}]b.0v(4(`Vx}]b#Zwv}]bhFv(1,Xk7( Gq&{C}]bVxITa)DT\a_# XZKNq ZVx}]b73P,T;9C CREATE DATABASE |nr sqlecrea() /}44(}]b# ^[9CDV=(,DNNVx4"vks# db2nodes.cfg D~G}]bVx~qwdCD~# }KZ Windows Yw5373O.b,IT9CNN`-w4i4M|B}]bVx ~qwdCD~ (db2nodes.cfg) DZ]#Z Windows Yw5373O,k9C db2ncrt M db2nchg |n44(M|D}]bVx~qwdCD~ Z4(`Vx}]b.0,Xk!q+w*}]bD?X}]bVxXw},Tca)_T\D>X}]CJ# ZVx}]bP,V<|CZ+m}]V<=;i}]bVxP#2aVdw}}] 0d`&Dm,"+|G>Xf"Z?v}]bVxP# Z9C}]bVxf"}].0,XkT}]b\mw(eb)}]bVx#Z{* db2nodes.cfg DD~P(e}]bVx# Z CREATE TABLE odr ALTER TABLE odP*Vx}]bVxiPDmUd PDm8(V<|#g{;P*m8(V<|,G41!ivB+y]w|DZ;P 4(V<|#g{4(ew|,G41!V<|GCmP(eDZ;vG$Mr LOB } ]`MDP#Vx}]bPDmXkAYP;PHG$M2G LOB }]`M#;PZ T=8(s,%;Vx}]bVxiPDmUdPDmE_PV<|# 4gBy>+PECZ}]bVxP: 1. TyPV<|P&C"Pc((}]bVx/}),b+zIV"3dw}5# 2. V"3dPCw}5&D}]bVxEj6+f"PD}]bVx# }]b\mw'V?VVx,bb6EIT+mV<=53PD}]bVxS/O (4,}]bVxi)#;X+mVK;v_Pev}]bVxD}]bD>}# v }]bVxi 1 |,};v}]bVxbDyPd{Vx# v }]bVxi 2 |,;v}]bVx# v }]bVxi 3 |,=v}]bVx# v i 2 PD}]bVxki 1 2m("RX~)# v i 3 PD%v}]bVxki 1 2m("RX~)# 4(}]b1,9+4({* db2nodes.cfg D}]bVxdCD~P8(DyP}] bVx#ITVp9C ADD DBPARTITIONNUM r DROP DBPARTITIONNUM VERIFY |n4 mSr}%d{}]bVx#}]+V}}]bVx)#ZmSr>}}]bVx.s,k9C REDISTRIBUTE DATABASE PARTITION GROUP |n4Z}]bVxiPD;iB}]bV x.dXBV"}]# }]bVxihF"bBn }G*k;v|sDm"C,qrk+!mEZ%;Vx}]bVxiP#"CG+ ;,mP|,`X}]DPECZ,;v}]bVxP#"CDmPzZ}]b\m w9C|_'D,S_T#byDmITfZZ%;Vx}]bVxiP#g{m; Z`Vx}]bVxiP"ZV<|P_P`,}?DP"RT&PD}]`Mf ],G4b)m+S*Q"C#Z"CDmP_P`,V<|5DP;ECZ,;v }]bVxO#b)mIT;Z`,}]bVxiPD%@mUdP,RT;S*G "CD# k\b+PHs!Dm)9=+`}]bVxO#}g,100 MB DmZ_P 16 vV xD}]bVxiPDT\I\_ZZ_P 32 vVxD}]bVxiPDT\# IT9C}]bVxi+*zBq&m (OLTP) mkv_'V (DSS) mV*#by+ PzZ7#;aT OLTP BqDT\zz:f0l# 6 VxM/:8O g{z}Z9C`Vx}]bVxi,kgN+_PV<|5 (c1, c2, c3) DP3dA`E 2,;s}C}] bVx n5# Z 1 B Vx}]bMm 7 V"3dITinXXF+}]f"Z`Vx}]bPDDv;C#g{Xk|D} ]bPw}]bVxOD}]V<,G4IT9C}]XBV"5CLr#K5CL rJmXB=brw{}]V#g{Lx9C sqlugtpi API 4q!V"E",G4K API I\5Xms{" SQL2768N,r*|;\lw= |, 4096 vu?DV"3d# V<| V<|G;P(r;iP),CZ7(f"X(}]PD}]bVx# V<|G9C CREATE TABLE odZmO(eD#g{;P*V }V<|#vITdmUdk%;Vx}]bVxi`XDmDdV<|# !qCDV<|\X*#kkV<|Ds!GI}HD#V<|; \,} 16 P,xRP=Y,T\=C#kpZV<|P|(;X*DP# (eV<|1,k}]`MxPHO,"I+|Gyw*Vxf]#Vxf] D}]`M_PgBtT:_P`,5+;,`MD=vd?a4`,DVxc(3 dA,;v`E# Z 1 B Vx}]bMm 9 Vxf]T_PBPXw: v y>}]`Mkm;v`,Dy>}]`Mf]# v Z?q=CZ DATE"TIME M TIMESTAMP }]`M#|GKK<;f],Rk V{r}]`MCZVvVxf]T# v TV<|P5`,D!}D&mGj+`,D,49|GD!};M+H;,2G gK# v V{.P(CHAR"VARCHAR GRAPHIC r VARGRAPHIC)D2?Uqa;"P c(vT# v BIGINT"SMALLINT M INTEGER Gf]D}]`M# v 9CxVoT73DyZ UCA D{m1,CHAR"VARCHAR"GRAPHIC M VARGRAPHIC Gf]D}]`M#9Cm;{m1,;,$HD CHAR M VARCHAR Gf]`M,x GRAPHIC M VARGRAPHIC Gf]`M,+ CHAR M VARCHAR k GRAPHIC M VARGRAPHIC ;f]# v Vxf]T;JCZ LONG VARCHAR"LONG VARGRAPHIC"CLOB"DBCLOB M BLOB }]`M,r*;'V|Gw*V<|# Vxm Vxm9CK}]i/=8,ZC}]i/=8P,y]CmP;vr`vmVx| PPD5+m}]V<=`vf"Ts(F*}]Vxr6')P# }]Vxr6'G;vmD;?V,||,CmD;?VP,"Rkd{P/V*f "#y] CREATE TABLE odD PARTITION BY SdP8(DZ],x(mD} ];.V=`v}]Vxr6'P#b)}]Vxr6'I;Z;,mUdM/r,; mUdP#g{;vmG9C PARTITION BY Sd4(D,G4CmGVxm# 8(DyPmUd# mVxDEc g{BPNNivJCZzMzDi/,k}w}M4(*zw}1DxT\# v \;kT?vmw}.dDNNmUdXw9C;,D5(}g,*K7# |CDUd{CJ,T?vw}9C;,D3s!I\|OJ)# v uY I/O yC,bPzZ|_'XTmw}}]xP"PCJ# v >}wvw}1,Ud"4T53IC,;h*w}Xi# v g{z!q4Pw}Xi,ITXi%vw}# DMS M SMS mUd<'VZ;,ZmDm;v;C9Cw}# a_KL5G\y=i/DT\ v?Ki/&m&\,\;y]i/=JT/{}3)}]Vx#Ki/&m F*}]Vx{},I*m`v_'Vi/x4C&# BP>}4(K;v{* CUSTOMER Dm,dP|, l_shipdate >= ’01/01/2006’ M l_shipdate <= ’03/31/2006’ DPf"ZmUd TS1 P,|, l_shipdate >= ’04/01/ 2006’ M l_shipdate <= ’06/30/2006’ DPf"ZmUd TS2 P,HH# CREATE TABLE customer (l_shipdate DATE, l_name CHAR(30)) IN ts1, ts2, ts3, ts4, ts5 PARTITION BY RANGE(l_shipdate) (STARTING FROM (’01/01/2006’) ENDING AT (’12/31/2006’) EVERY (3 MONTHS)) }]VxM6' Vxm9CK}]i/=8,ZC}]i/=8P,y]CmP;vr`vmVx| PPD5+m}]V<=`vf"Ts(F*}]Vxr6')P#ITT/zIT ?v}]Vx8(D6',2ITZ4(m1V/zIb)6'# }]VxZ{v DB2 bPT`V==;}C#BmP>Kn#{D}C==: v DATAPARTITIONNAME G4(x(Dm1T}]Vx8(D@C{F#KP5f" Z SYSCAT.DATAPARTITIONS ?X(}]Vx6'`TZmPd{}]Vx6'D3r,dP,pk} ]VxEPZyPIS}]VxM,S}]Vxsf# 12 VxM/:8O }]i/=8 }kmVx.s,DB2 }]ba)K;V}6}]i/=8#CREATE TABLE odP P}vSd,b)Sd|,8>*gNi/}]Dc(# BP}vSd]>KITNbiOZ;p9CD}]i/6p: v DISTRIBUTE BY CZ+}]yHXVmVxi/=8,dPm>Bz[}]Dm.V=`v}]VxP#Cm9g==v mUd(ts1 M ts2)# Z 1 B Vx}]bMm 13 `,/: (MDC) kmVx.dDTxxpG`,M%,#MDC JOZ"=e(4, _P`,Dm),x1P;v,G}]bhFDPD1(}g,DATE P),mVxM \\CXpwC#1,1{Ob=vu~1,MDC MmVx%9#bZZ 15 3D< 5 PxPK]># dbgroup1() ( )dbpart1 ( )dbpart2 ( )bpart3 SALES  ( )ts1 ( )ts1 (ts1 ( )ts2 ( )ts2 ( )s2 Jan Mar May Jul Sep Nov Jan Mar May Jul Sep Nov Jan Mar May Jul Sep Nov Feb Apr Jun Aug Oct Dec Feb Apr Jun Aug Oct Dec Feb Apr Jun Aug Oct Dec  = ) < 4. ]>}]bVxkmVx`%9dDi/=8#m>Bz[}]Dm.V=`v}]Vx P"Rg=vmUd(ts1 M ts2),xb=vmUdVVDNN=8dO9C#K=8G ORGA- NIZE BY KEY SEQUENCE#|CZ+?vGiI#}]bVxITG_-romD# mVx ;V}]i/=8,4,m}]y]CmP;vr`vVxPPD5V<=` v}]VxP#y] CREATE TABLE odD PARTITION BY SdP8(D Z],x(mD}];.V=`vf"TsP#b)f"TsITZ;,Dm UdP# ( )dbgroup1 ()dbpart1 ()dbpart1 ()dbpart1 SALES  ( )ts ( )tsl ( )ts ( )ts2 ( )ts2 ( )ts2 Jan Mar May Jul Sep Nov Jan Mar May Jul Sep Nov Jan Mar May Jul Sep Nov Feb Apr Jun Aug Oct Dec Feb Apr Jun Aug Oct Dec Feb Apr Jun Aug Oct Dec  East 97 East 99 North 98 South 99 West 00  = < 5. }]bVx"mVxM`,i/=8Dm>(,dP SALES mPD}];vVKwV}]i/=8IT gNozzzcb)*s# m 2. +mVxk}]bVx&\dO9C Jb (iD=8 5w }]*v mVx 9Cpk4*vs?}]," R;vVnYPO# "Pi/4P(i/T\) }]bVx&\ a)i/"PTTDFi/T \ }]Vx{}(i/T\) mVx a)}]Vx{}TDFi/ T\ qCnQi/T\ =_ ;p9C1ITqCnQi/ T\:i/"PTM}]Vx {}%9 \m1D$w:XOX }]bVx&\ *?v}]bVx4Pm`N q m 3. +mVxk MDC mdO9C Jb (iD=8 5w *vZdD}]ICT mVx 9C DETACH PARTITION S d4*vs?}],"R;v VnYPO# i/T\ =_ MDC nJOC4i/`v,# mVx(}}]Vx{}a_ T\# nYXi MDC MDC ,$/:,SxuYxP XiDX*T# ":TZ UNION ALL S<,?0(i9CmVx# DB2 M Informix }]bPD}]i/=8 mVx&\G;V}]i/=8,4,m}]y];vr`vmPPD5V<=`v f"Ts(F*}]Vxr6')P#?v}]Vx} BP>}j85wKZ DB2 }]bPgN5VkNN9Cmo=D Informix VN=8 H,Da{# >} 1:Bfbvy>D Create Table odT>K Informix VNT0H,D DB2 } ]b53mVxo(: Informix o(: CREATE TABLE demo(a INT) FRAGMENT BY EXPRESSION a=1INdb1, a=2INdb2, a=3INdb3; Z 1 B Vx}]bMm 17 DB2 o(: CREATE TABLE demo(a INT) PARTITION BY RANGE(a) (STARTING(1) IN db1, STARTING(2) IN db2, STARTING(3) ENDING(3) IN db3); Informix XPS 'VF* hybrid D=cVN=8,ZK=8P,9C;vmo=4Z- w~qw.dV"}],"9CZ~vmo=4Z-w~qwZV"}]#b9yP -w~qw<\;Nki/(4,ZyP-w~qwO} 2:TB>}T>KiOSdDo(: Informix o( CREATE TABLE demo(a INT, b INT) FRAGMENT BY HYBRID HASH(a) EXPRESSIONb=1INdbsl1, b=2INdbsl2; DB2 o( CREATE TABLE demo(a INT, b INT) IN dbsl1, dbsl2 DISTRIBUTE BY HASH(a), PARTITION BY RANGE(b) (STARTING 1 ENDING 2 EVERY 1); Kb,IT9C`,/:4x;=i/}]: CREATE TABLE demo(a INT, b INT, c INT) IN dbsl1, dbsl2 DISTRIBUTE BY HASH(a), PARTITION BY RANGE(b) (STARTING 1 ENDING 2 EVERY 1) ORGANIZE BY DIMENSIONS(c); by,P a 5`,DyPP} >} 1:TB>}T>T/zIDo(q=: CREATE TABLE orders ( l_orderkey DECIMAL(10,0) NOT NULL, l_partkey INTEGER, l_suppkey INTEGER, 18 VxM/:8O l_linenumber INTEGER, l_quantity DECIMAL(12,2), l_extendedprice DECIMAL(12,2), l_discount DECIMAL(12,2), l_tax DECIMAL(12,2), l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44)) PARTITION BY RANGE(l_shipdate) (STARTING ’1/1/1992’ ENDING ’12/31/1993’ EVERY 1 MONTH); b+4( 24 v6',4T 1992-1993 D?vB4(;v6'#"Tek l_shipdate , vC6'DP+} 2:+O;>}kTB Informix o(wHO: create table orders ( l_orderkey decimal(10,0) not null, l_partkey integer, l_suppkey integer, l_linenumber integer, l_quantity decimal(12,2), l_extendedprice decimal(12,2), l_discount decimal(12,2), l_tax decimal(12,2), l_returnflag char(1), l_linestatus char(1), l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(25), l_shipmode char(10), l_comment varchar(44) ) fragment by expression l_shipdate < ’1992-02-01’ in ldbs1, l_shipdate >= ’1992-02-01’ and l_shipdate < ’1992-03-01’ in ldbs2, l_shipdate >= ’1992-03-01’ and l_shipdate < ’1992-04-01’ in ldbs3, l_shipdate >= ’1992-04-01’ and l_shipdate < ’1992-05-01’ in ldbs4, l_shipdate >= ’1992-05-01’ and l_shipdate < ’1992-06-01’ in ldbs5, l_shipdate >= ’1992-06-01’ and l_shipdate < ’1992-07-01’ in ldbs6, l_shipdate >= ’1992-07-01’ and l_shipdate < ’1992-08-01’ in ldbs7, l_shipdate >= ’1992-08-01’ and l_shipdate < ’1992-09-01’ in ldbs8, l_shipdate >= ’1992-09-01’ and l_shipdate < ’1992-10-01’ in ldbs9, l_shipdate >= ’1992-10-01’ and l_shipdate < ’1992-11-01’ in ldbs10, l_shipdate >= ’1992-11-01’ and l_shipdate < ’1992-12-01’ in ldbs11, l_shipdate >= ’1992-12-01’ and l_shipdate < ’1993-01-01’ in ldbs12, l_shipdate >= ’1993-01-01’ and l_shipdate < ’1993-02-01’ in ldbs13, l_shipdate >= ’1993-02-01’ and l_shipdate < ’1993-03-01’ in ldbs14, l_shipdate >= ’1993-03-01’ and l_shipdate < ’1993-04-01’ in ldbs15, l_shipdate >= ’1993-04-01’ and l_shipdate < ’1993-05-01’ in ldbs16, l_shipdate >= ’1993-05-01’ and l_shipdate < ’1993-06-01’ in ldbs17, l_shipdate >= ’1993-06-01’ and l_shipdate < ’1993-07-01’ in ldbs18, l_shipdate >= ’1993-07-01’ and l_shipdate < ’1993-08-01’ in ldbs19, l_shipdate >= ’1993-08-01’ and l_shipdate < ’1993-09-01’ in ldbs20, l_shipdate >= ’1993-09-01’ and l_shipdate < ’1993-10-01’ in ldbs21, l_shipdate >= ’1993-10-01’ and l_shipdate < ’1993-11-01’ in ldbs22, l_shipdate >= ’1993-11-01’ and l_shipdate < ’1993-12-01’ in ldbs23, l_shipdate >= ’1993-12-01’ and l_shipdate < ’1994-01-01’ in ldbs24, l_shipdate >= ’1994-01-01’ in ldbs25; Z 1 B Vx}]bMm 19 "b,Informix o(a)KOB;b%D6'T6q$Z6'bDUZ#(}mS9C MINVALUE M MAXVALUE D6',IT+ DB2 o(^D*k Informix o(%d# >} 3:TB>}+>} 1 ^D*k Informix o(%d: CREATE TABLE orders ( l_orderkey DECIMAL(10,0) NOT NULL, l_partkey INTEGER, l_suppkey INTEGER, l_linenumber INTEGER, l_quantity DECIMAL(12,2), l_extendedprice DECIMAL(12,2), l_discount DECIMAL(12,2), l_tax DECIMAL(12,2), l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44) ) PARTITION BY RANGE(l_shipdate) (STARTING MINVALUE, STARTING ’1/1/1992’ ENDING ’12/31/1993’ EVERY 1 MONTH, ENDING MAXVALUE); bV} >} 1:TB>}9C Informix o(,ZKivBJO9CzIP#Z>>}P,xP Vx1yyZDPfEKSCsD!MXr#IZ!Pm;sI\|D,rKzIP mo=2;sI\|D# CREATE TABLE customer ( cust_id INT, cust_prov CHAR(2)) FRAGMENT BY EXPRESSION cust_prov = "AB" IN dbspace_ab cust_prov = "BC" IN dbspace_bc cust_prov = "MB" IN dbspace_mb ... cust_prov = "YT" IN dbspace_yt REMAINDER IN dbspace_remainder; >} 2:Z>>}P,9CzIPT DB2 mxPVx: 20 VxM/:8O CREATE TABLE customer ( cust_id INT, cust_prov CHAR(2), cust_prov_gen GENERATED ALWAYS AS (CASE WHEN cust_prov = ’AB’ THEN 1 WHEN cust_prov = ’BC’ THEN 2 WHEN cust_prov = ’MB’ THEN 3 ... WHEN cust_prov = ’YT’ THEN 13 ELSE 14 END)) IN tbspace_ab, tbspace_bc, tbspace_mb, .... tbspace_remainder PARTITION BY RANGE (cust_prov_gen) (STARTING 1 ENDING 14 EVERY 1); bo,CASE odPDmo=k FRAGMENT BY EXPRESSION SdPD`&mo= %d#CASE od+?v->} PG cust_prov_gen)P#KPGf"ZELODf5P,rK,m>}9CLo(q=#rK, XkZ CREATE TABLE odD IN SdPP>C4EC}]VxDmUd#g{9 C$o(q=,G4?v}]Vx H# v +6'(eIk}]*ks!`%d#n#{DivGy]UZr1dPT}]x PVx# v y]PfZ{}VxDPxPVx# 'VD}]`M m 5 T>K'VCwmVx|PD}]`M(|(,eJ): m 5. 'VD}]`M }]`MP 1 }]`MP 2 SMALLINT INTEGER INT BIGINT FLOAT REAL DOUBLE DECIMAL DEC DECFLOAT NUMERIC NUM CHARACTER CHAR VARCHAR DATE Z 1 B Vx}]bMm 21 m 5. 'VD}]`M (x) }]`MP 1 }]`MP 2 TIME GRAPHIC VARGRAPHIC CHARACTER VARYING TIMESTAMP CHAR VARYING CHARACTER FOR BIT DATA CHAR FOR BIT DATA VARCHAR FOR BIT DATA CHARACTER VARYING FOR BIT DATA CHAR VARYING FOR BIT DATA C'(eD`M(%5) ;'VD}]`M VxmIT|,BP}]`M,+;'V+|GCwmVx|P: v C'(eD`M(a9/) v LONG VARCHAR v LONG VARCHAR FOR BIT DATA v BLOB v BINARY LARGE OBJECT v CLOB v CHARACTER LARGE OBJECT v DBCLOB v LONG VARGRAPHIC v REF v C d$V{. v Pascal d$V{. v XML g{z!q9C CREATE TABLE odD EVERY Sd4T/zI}]Vx,G4; \+;PCwmVx|#g{z!q(}Z CREATE TABLE odD PARTITION BY SdP8(?v6'4V/zI}]Vx,G4IT+`vPCwmVx|,gTB >}y>: CREATE TABLE sales (year INT, month INT) PARTITION BY RANGE(year, month) (STARTING FROM (2001, 1) ENDING (2001,3) IN tbsp1, ENDING (2001,6) IN tbsp2, ENDING (2001,9) IN tbsp3, ENDING (2001,12) IN tbsp4, ENDING (2002,3) IN tbsp5, ENDING (2002,6) IN tbsp6, ENDING (2002,9) IN tbsp7, ENDING (2002,12) IN tbsp8) b+zI 8 v}]Vx,4 2001 jM 2002 jD?v>HP;v}]Vx# ": 1. 1+`vPCwmVx|1,+Qb)PS*iO|(`FZw}PDiO|), dP,sfDP@5Z0fDP#8(D?vp<5rax5(yPP;p);\ ,v 512 vV{#K^Fk SYSCAT.DATAPARTITIONS ?}4(|, 12 v}]VxDm,4?vB;v}] Vx#TZNNj],;B]DyPP<+;E=Z;v}]VxP,~B]DP+ ;E=Z~v}]VxP,@K`F# >} 1 CREATE TABLE monthly_sales (sales_date date, sales_month int GENERATED ALWAYS AS (month(sales_date))) PARTITION BY RANGE (sales_month) (STARTING FROM 1 ENDING AT 12 EVERY 1); ": 1. TZmVx|P9CDzIP,;\Ddr>}dmo=#;JmTmVx|P9 CDPmSzIPmo=#TZmVx|P9CDP,g{"TmS">}rDd CPDzIPmo=,Ma},kNDZ 41 3D:4( MDC r ITC m1D"bBn;# VxmD0k"bBn T?jmxPVx1,+'VyPVP0k&\,+fZTB#f^F: v 1VxzmLr}sZ 1 1,;'V;Bc# v ;'V+}]0k=}]VxS/PD,1#Vd`}]Vxj+*z# v 0kYw9CDl#m;\Vx# v g{?jm|, XML P,G4;\8(l#m# v 1 LOAD 5CLrTek==rXBt/KP"R0k?jm_PNNQpkDS t1,G4;\X((;w}# v k0k MDC m`,,0kVxm1+;a#tdk}]G3)?jm}]Vx&Z`, rpk4,#kTT&Z?jmD`X?v4,+a|(Z\xDG"V6'%},+;aT?;vG<4G<{"#}K?jmPD yPP.b,l#m9|(CZhvX(P"zD`M%}DP#|,^'} ]DP(|(;\VxD}])+4A*"D~# r*l#mekI>\_,yTITXFek=l#mPD4P'+r*6'^Fr(;TVxmKPK0kY w# U90kYw U90kf;Yw+j+XOyPIS}]Vx,xU90kekYwa+y PIS}]VxXOA0k0D$H#g{ ALLOW READ ACCESS LOAD Y wZ0k4FWN'\,G4ZU9CYwZd,w}adC^'#ZU9f 0w}D ALLOW NO ACCESS LOAD Yw1,w}2adC^',bGr *X(w}==r_v?,$ZdekK|x9Cw}&Z;;B4,#+} ]0k=`v?jP;a0l0kV4Yw,++^(S0kWNZdq!D ;BcXBt/0kYw#ZKivB,g{T?jmxPVx,G4+vT SAVECOUNT 0k!n#KP*k+}]0k= MDC ?jmPDP*;B# zIP g{zIPZNNVx",rV<|P,G4avT generatedoverride D~ `M^N{"R LOAD 5CLrazI5,Mq8(K generatedignore D ~`M^N{;y#ZKivB,0kmsDzIP5I\G<,b)U>GUd?# v 1m,1xPKVxMV<1,Vx}]b0kI\;a0lyP}]bV x#;Pdv}]bVxODTsEa|D# v Z0kYwZd,VxmDZf{Dafm}DvSxvS#"b,\vS ?;G_TD,r*v\Zf*sD;!?Vk}]Vx}I}H# 4FM_e/i/m _e/i/mIi/(e,Ci/2CZ7(mPD}]#_e/i/mIC4Dx i/DT\#g{}]b\mw7(i/D;?VI9C_e/i/m4bv,G4 IX4Ci/T9C_e/i/m# ZVx}]b73P,IT4FM_e/i/m"9C|G4DFi/T\#4FM _e/i/myZby;vm:I\Q-Z%;Vx}]bVxiP4(Cm,+G zkZm;v}]bVxiPDyP}]bVx.d4FCm#*4(4FM_e/ i/m,k9Cx REPLICATED !nD CREATE TABLE od# (}9C4FM_e/i/m,I+4dM"CDm"C#TZsB5mM!,mD ,S,4FM_e/i/mXpPC#*+yhDnbf"wT0Xk|B?v1> yx4D0l5An!,*4FDm&O!,R|B;51# ":9&I(}"CqCDT\ 'f4V{# (}ZCZ(e4FmD subselect SdP8(J1D=J,IT4F!qDPM/r! qDP# ;'VT4FD_e/i/m4P|,G7(TYwD DELETE r UPDATE od# }]bVxiPDmUd (}+mUdEZ`Vx}]bVxiP,M+CmUdZDyPm.VrVx=C }]bVxiD?v}]bVxP# Z 1 B Vx}]bMm 25 IKCmUd;4(=K;v}]bVxiP#;);Z3v}]bVxiP,Cm UdMXk#tZC&;x;\|DAm;}]bVxi#CREATE TABLESPACE o dCZ+mUdk}]bVxiX*# mVxM`,/:m Z,1G`,/:mM}]VxmDmP,IT,1ZmVxD6'Vxf6M`, /: (MDC) |P9CP#k;%@9C`,/:rVx&\`H,,1G`,/:m MVxmDmITq!Oj8D}]VxMi{}# Zm`&CP+ MDC |P8(*;,ZTmxPVxDP\PC#&C"bDG,m VxG`PD,x MDC G`,D# ww DB2 }]VbDXw BP(iw*kT DB2 V9.1 PBvDdMwwVb#Y(BPXw: v }]bZ`(zwr`v AIX® _-VxOKP# v 9CVx}]b73(9C DISTRIBUTE BY HASH Sd44(m)# v P 4 = 50 v}]Vx# v H*v=Z}]# v m9Cs6'Di/`M#+G,`TZ OLTP $w:X45,C$w:X(#G_ PBPXwDVvi/: – OsDa{/,n`P 2 YrP – s`}r+?i/<|PS<,x;Gy>m v SQL Sd46'(BETWEEN Sd)"PmPDnH!q}]# ww DB2 V9.1 }]VbB5mDXw ;vdMVbB5mI\ICTBhF: v Z Month PP4(}]Vx# v **vD?v1dN(}g,1 vBM 3 vB)(e}]Vx# v Z Day M 1 = 4 vd{,Dy!O4( MDC ,#dMD,P:zz_Mxr# v yP}]VxM MDC /:i/PD1Zhs"y]H07(DX w7((iDi/=8# 26 VxM/:8O m 6. +mVxk MDC mdO9C Jb (iD=8 (i *vZdD}]ICT mVx 9C DETACH PARTITION S d4*vs?}],"R;v VnYPO# i/T\ mVxM MDC MDC nJOC4i/`v,# mVx(}}]Vx{}a_ T\# nYXi MDC MDC ,$/:,SxuYxP XiDX*T# Z+3Qz0ZP*v;vB r|$1dD}] mVx }]VxITj+bvKh s#MDC ;pNNwC,"R v MDC ";JO# ZL1dQz0Z(!Z 1 V S)Zd*v;vBr|$1 dD}] mVx }]VxITj+bvKh s#MDC ;pNNwC,"R v MDC ";JO# *v;vBr|$1dD} ],",1Z;p'NN~q DivB9mTZa;i/D s5C'j+IC# MDC MDC ;\bv;?VKhs# IZm&ZQz4,D1dN +L,mVx";JO# ?l0k}](ALLOW READ ACCESS r ALLOW NO ACCESS) mVxM MDC K1 MDC _P\`C&#m Vx_Pv?DC&# “,x”0k}](ALLOW READ ACCESS) mVxM MDC K1 MDC _P\`C&#m Vx_Pv?DC&# “+3 BI”i/Di/4PT\ mVxM MDC MDC XpJOC4i/"= e/`v,#mVx(}Vx{ }a_T\# (}{}xPXiDX*Tr uY4PNqyzzD;<0 l,9Xiyx4D;<0l 5=nM# MDC MDC ,$/:,SxuYxP X i D X * T # g { 9 C MDC,G4}]Vx;a)v ?C&#+G,g{;9C MDC,G4mVx(}ZVx 6p,$;)V#H/:aP zZuYXiDX*T# >} 1: # CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (Province); Z 1 B Vx}]bMm 27 >} 2: (}+ YearAndMonth mSA ORGANIZE BY Sd,ITqCO_Dj8LH,gZ 29 3D< 7 Py># CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (YearAndMonth, Province); =1  1 MDC Province( )  () YearandMonth 9901- 9902 9903- 9904 AB QBBC ON 1 5 316204 2 34 4524 30 12 14 31 50 54 56 18 32 33 36 11 6 7 10 13 22 26 15 38 5125 8 17 44 53 43 9 39 19 41 42 < 6. 4 YearAndMonth Vx"4 Province i/Dm 28 VxM/:8O g{Vxm`H,MDC mMVxm }Yw.sG<|Y# v h*D:eXZf|Y#;h*nbDZf4f"(za9(}g,B+ ww})# +* RCT $VdUd,"R+#tKUd)Cm9C,49CmPP;PG<12G gK#rK,6'/:m;h*ICUdXFG< (FSCR)#4(m1,mP;PG<; ;x,+$Vd{v36'#$VdGy]G}YwZd4P}],$(}g,X i)Mw},$YwD*z#MDC w*CZ}]V"MsM}]b73,+2ICZ *zBq&m(OLTP)73P# #fmk MDC mDHO #fmDw}GyZG<4("D#w}DNN/:}#Zb)>}D3) >}P,Y(fZ;v MDC m t1,_P c1"c2 M c3 ,#Zd{>}P,Y(fZ ;v MDC m mdctable,_P color M nation ,# >} 1: SELECT .... FROM t1 WHERE c3 < 5000 Ki/f0=%v,D6'=J,rKITZZ?X4T9C c3 D,iw}4CJC m#+(hw}TiR|5!Z 5000 Dij6 (BID),"RTiDa{/&Cn!X 5(hTlw5JG<# >} 2: SELECT .... FROM t1 WHERE c2 IN (1,2037) Ki/f0=%v,D IN =J,"RIT%"yZiw}D(h#ITZ?X4Ki /T9C c2 D,iw}4CJCm#+(hw}TiR_P|5 1 M 2037 D BID, "RTiDa{/&Cn!X5(hTlw5JG<# >} 3: SELECT * FROM MDCTABLE WHERE COLOR=’BLUE’ AND NATION=’USA’ Z 3 B `,/: (MDC) m 35 *4PKi/ks,jIBP=h(T>Z< 9 P): v jI,iw}iR:T Blue ,4P;N,T USA ,4P;N# v 4Pi_- AND YwT7(=v,D;/#4,_- AND Yw;7(Zb=v, P} 4: SELECT ... FROM t1 WHERE c2 > 100 AND c1 = ’16/03/1999’ AND c3 > 1000 AND c3 < 5000 Ki/f0= c2 M c3 D6'=JM c1 DH[=J,"R9*4P_- AND Yw# ITZ?X4Ki/TCJ?v,iw}ODm: v (h c2 iw}TiR_PsZ 100 D|5D BID v (h c3 iw}TiR|5Z 1000 = 5000 .dD BID v (h c1 iw}TiR|5*“16/03/1999”D BID# ;s,TS?vi(hqCD BID 4P_- AND YwTiR|GD;/,"TiD a{/&Cn!X5(hTiR5JG<# >} 5: SELECT * FROM MDCTABLE WHERE COLOR=’BLUE’ OR NATION=’USA’ “ ”!"!Colour “ ”!"!Nation Blue #$ 4,0 12,0 12,0 12,0 76,0 48,0 92,0 52,0 76,0 76,0 100,0 100,0 100,0 112,0 216,0 216,0 276,0 216,0 ()AND %&'*+!!/:( );BID < 9. T=viw}9C_- AND YwDi/ks# 36 VxM/:8O *4PKi/ks,jIBP=h: v jI,iw}iR:T?v,<4P;N# v 4P_- OR YwTiR=v,D"/# v TmPqCDi4Pn!X5(h# >} 6: SELECT .... FROM t1 WHERE c1 < 5000 OR c2 IN (1,2,3) Ki/f0= c1 ,D6'=J"c2 ,D IN =JT0_- OR Yw#ITZ?X4 Ki/TCJ,iw} c1 M c2 ODm#(h c1 ,iw}TiR!Z 5000 D5, "T c2 ,iw}4Pm;v(hTiR5 1"2 M 3#TS?viw}(hqCD BID 4P_- OR Yw,;sTiDa{/&Cn!X5(hTiR5JG<# >} 7: SELECT .... FROM t1 WHERE c1 = 15 AND c4 < 12 Ki/f0= c1 ,DH[=JM;G,D;PDm;v6'=JT0_- AND Yw# ITZ?X4Ci/TCJ c1 D,iw},Tq! c1 5* 15 DmD,DiPm# g{ c4 _P RID w},G4ITxPw}(hTlw c4 !Z 12 DGD RID# g{ c4 ;P RID w},G4IT(hiw}TiROqiDPm,"RZ?viD n!X5(hZd,ITT"VD?vG<&C=J c4<12# >} 8: Y(by;v=8,fZ color"year M nation ,T0?~EDPj6(RID)w}, G4ITxPTBi/# SELECT * FROM MDCTABLE WHERE COLOR=’BLUE’ AND PARTNO < 1000 Z 3 B `,/: (MDC) m 37 *4PKi/ks,jIBP=h(T>Z< 10 P): v jI,iw}iRM RID w}iR# v TiM RID 9C_- AND Yw47(,kzc=Ju~DG)PD;/# v qCDa{v*9tZzcu~DiDG) RID# >} 9: SELECT * FROM MDCTABLE WHERE COLOR=’BLUE’ OR PARTNO < 1000 “ ”!"!Colour Partno RID RID! "!/:( ) %&'<>!/: Blue 6,4 4,0 8,12 6,4 12,0 50,1 50,1 48,0 77,3 52,0 76,0 107,0 77,3 100,0 115,0 216,0 219,5 219,5 276,9 ()AND < 10. Tiw}MPj6(RID)w}9C_- AND YwDi/ks 38 VxM/:8O *4PKi/ks,jIBP=h(T>Z< 11 P): v jI,iw}iRM RID w}iR# v TiM RID 9C_- OR Yw47(,kzc=Ju~DG)PD"/# v qCDa{Gzcu~DiPDyPPT0zc=Ju~+Zzcu~Dib?D d{ RID#T?vi4Pn!X5(hTlw|GDG<"pvlwb)i.bDd {G<# >} 10: SELECT ... FROM t1 WHERE c1<5ORc4=100 Ki/f0= c1 ,D6'=J"G,P c4 DH[=JT0_- OR Yw#g{ c4 PO_P RID w},G4ITZ?X4Ki/T9C c1 OD,iw}M c4 OD RID w}44P_- OR Yw#g{ c4 O;Pw},G4IT**!qm(h,r*Xk liyPG<#_- OR Yw+T c1 9Ciw}(hTR=!Z 4 D5,"T c4 9 C RID w}(hTR=5 100#T?vzcu~Di4Pn!X5(h,r*b)i PDyPG<} 11: SELECT .... FROM t1,d1,d2,d3 WHERE t1.c1 = d1.c1 and d1.region = ’NY’ AND t2.c2 = d2.c3 and d2.year=’1994’ AND t3.c3 = d3.c3 and d3.product=’basketball’ “ ”!"!Colour ! "!/: ( )Partno RID RID %&'<>!? RID Blue 6,4 4,0 4,0 8,12 12,0 8,12 12,0 50,1 48,0 107,0 48,0 77,3 52,0 52,0 76,0 107,0 76,0 115,0 100,0 115,0 100,0 216,0 219,5 216.0 276,9 276,9 , ()OR < 11. 9C_- OR YwDiw}MPj6D$w== Z 3 B `,/: (MDC) m 39 Ki/f0GM,S#Z>>}P,t1 GB5m"R|_Pb| c1"c2 M c3(kw | d1"d2 M d3 `T&)T0,m#,m;;(G MDC m#Region"year M prod- uct GIT9C#fw}riw}("w}D,mDP(g{,mG MDC m)#1y ] c1"c2 M c3 5CJB5m1,ITTb)PD,iw}xPiw}(h,;s9 CqCD BID 44P_- AND Yw#1fZiPm1,ITT?vixPn!X5 (hTq!G<# %*D\H !qJ1D,M)9}]is!TZ MDC hF45G#X*#b)rX+7(mDZ {%*\H#|G.yTX*Gr*T?vVP%*}Yw,r*a|lXeUMndi#k}?OYD /:}]DOsiO`HO,|9a}PD}]bxP/:,++ Day , f;* YearAndMonth ,#b+* YearAndMonth"Region M Product ,xhy } 60(12 vBKT 5 j)"12 M 5,I\D%*}* 3600#ZG,?v%* +_P|s6'D5,"R5MK;|,\YG}-}mDyPStTs, 2. X|{m, 3. 9CB(e44(m, 4. XB4(mDyPStTs, 5. +mPDVP}]d;*BmPyh*D}]#4,SImP!q}],;s+C }]0kBmP,ITZBmP9CP/}4+ID}]`Md;*BD}]` M# +}]SVPmFA ITC m *5MT}],$YwDhs,IT+}]S#fmFAek1d/: (ITC) m#*+ }]SVPmFA ITC m,k9C*zmF/f"}L# ExampleBank =8T>KgN+VPmPD}]FA ITC m#K=895w9C ITC m1XUUdDc{LH#PX|`E",kND“`XEn”4S# DB2 hFKJLrOD MDC KJLr&\?~ DB2 hFKJLr (db2advis) _P MDC &\?~#K&\?~(iCZ MDC m PD/:,(|(y>PDVZH)Tca_$w:XT\#VZHbvuom>C 4u!/:,y}(%5D}?)D;v}'mo=#VZHD#{>}GVZHI *UZ"UZyZDGZ"UZyZDB]r;jPD>H# 9C“DB2 hFKJLr”D MDC &\?~*s}]bPAYfZ8v)9}]iD} ]#“DB2 hFKJLr”9C}]4T}]\HMy}("#M# g{}]bDmP;P}],G4“DB2 hFKJLr”;a(i9C MDC,49C} ]b|,Um,+P;iiYD3FE"4m>|G;vQndD}]b# (i9j6KC4(e,DVZHD1ZzIP#(iP;|(I\Dis!#Z* MDC ma)(i1,9CmUdD)9}]is!#Y(+ZVPmyZDmUdP 4((iD MDC m,rKCm_P`,D)9}]is!#T MDC ,D(i+y] mUdD)9}]is!Dd/xd/,bGr*)9}]is!a0lITnd= ir%*PDG<}#K)9}]is!+1S0l%*D\H# !\IT*m(i%v,r`v,,+k;}YwD0l#Tm4Pb)Yw1I\a j>4KP MDC &\?~#9C “C”(i`M48>`,/:m#(i`M*:“I”m>w}"“M”m>_e/i/m" “C”m> MDC,x“P”m>Vx}]b73#(i`MIT`%iO9C# ":“DB2 hFKJLr”;a&mds!;= 12 v)9}]iDm# 1av(i1,KJLr+,1Vv MQT M#fy>m# MDC &\?~Ddv|(: v ?vmDzIPmo=(CZT MDC bv=8PvVD,hCVZH)# v *?vm(iD ORGANIZE BY Sd# Tj}D MDC m4U+V} 1: CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE) ORGANIZE BY DIMENSIONS (c1, c3, c4) Z 3 B `,/: (MDC) m 43 >} 1 PDmy]NI_-"=e(4,_P}v,)D}vPPD5/:#VZIT Zi/&mZdy];vr`v,TmxP_-V,,Tcf0DX5Kc{v&m `&D,r%*PDi#iDs!(3})GmD)9}]is!# _PyZ`PD,D MDC m ?v,ITI;Pr`PiI#w*;v>},IT4(;vy]|,=PD;v, 4/:Dm# >} 2: CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE) ORGANIZE BY DIMENSIONS (c1, (c3, c4)) Z>} 2 P,my]=v, c1 M (c3, c4) 4xP/:#by,Zi/&mZd,m ITy] c1 ,riO (c3, c4) ,_-V,#Cmk>} 1 PDm_P`,}?Di, +GY;v,iw}#Z>} 1 P,P}v,iw},P c1"c3 M c4 w;v#Z> } 2 P,P=v,iw},;vkTP c1,xm;vkT c3 M c4#b=v=(Dw *npZZ,Z>} 1 P,f0 c4 Di/IT9C c4 D,iw}4lY1SXCJ `X}]i#Z>} 2 P,c4 G,iw}PD(z|?V,rKf0 c4 Di/f0 |`D&m#+G,Z>} 2 P,+Y,$Mf";viw}# “DB2 hFKJLr”+;T|,`PD,a)(i# +Pmo=w*,D MDC m Pmo=2ICZ/:,#y]Pmo=/:D&\TZ+,OvA|MDj8LH G#PC,}g,+X7Ov*Xm;Crxr,r_+UZOv*GZ"B]r j#*TK==5V,DOv,IT9CzIP#K`MDP(eJm9CITm> ,Dmo=44(P#Z>} 3 P,Cod4(y];vy>PM=vPmo=xP/ :Dm# >} 3: CREATE TABLE T1(c1 DATE, c2 INT, c3 INT, c4 DOUBLE, c5 DOUBLE GENERATED ALWAYS AS (c3 + c4), c6 INT GENERATED ALWAYS AS (MONTH(C1))) ORGANIZE BY DIMENSIONS (c2, c5, c6) Z>} 3 P,c5 PGyZ c3 M c4 PDmo=,x c6 Pa01+ c1 POvA| MDj8LH#Kody] c2"c5 M c6 PPD54/:Cm# TzIP,D6'i/ TzIP,D6'i/h*%wP/}#mo=XkG%wDE\*zIPD,Iz 6'=J#g{TzIP4(,,G4Ty>PDi/\;{CzIPDiw}4a _T\(P;Viv}b)#*9y>P(}g,UZ)D6'i/T,iw}9C 6'(h,C4Z CREATE TABLE odPzIPDmo=XkG%wD#!\Pmo =IT|(NNP'mo=(|(C'(eD/}(UDF)),+Gg{mo=;G %wD,G41H[=Jr IN =JPO1,|GE\;9Ciw}4zci /# 44 VxM/:8O w*;v>},Y(9CzIP month D,44( MDC m,dP month = INTEGER (date)/100#TZC,(month)Di/,IT4Piw}(h#TZy>P(date) Di/,2IT4Piw}(h4u!*(hDiD6',;s;+UZD=J&C Zb)iPDP# `kw+zI*Ziw}(hP9CDd{=J#}g,TZTBi/: SELECT * FROM MDCTABLE WHERE DATE > "1999-03-03" AND DATE < "2000-01-15" `kw+zITB=J::month >= 199903; M:month <= 200001;,|GITC w,iw}(hD=J#1TqCDixP(h1,a+-<=J&CZb)iPD P# G%wmo=JmTC,&CH[=J#G%w/}D;vOCD>}G MONTH( ), g>} 3 PD c6 PD(ey>#g{ c1 PGUZ"1dAGrUZr1dAGDP 'V{.m>(,G4/}+5X6'G 1 = 12 D{}5#!\/}DdvG7( D,+G5JO|zIDdvkW>/}(4,-7#=)`F: MONTH(date(’01/05/1999’)) = 1 MONTH(date(’02/08/1999’)) = 2 MONTH(date(’03/24/1999’)) = 3 MONTH(date(’04/30/1999’)) = 4 ... MONTH(date(’12/09/1999’)) = 12 MONTH(date(’01/18/2000’)) = 1 MONTH(date(’02/24/2000’)) = 2 ... !\K>}PDUZG,xvSD,+G MONTH(date) ;avS#|_exT,?1 date1 sZ date2,";\#$ MONTH(date1) sZrHZ MONTH(date2)#bG%w Ty*sD#KG%wTGJmD,+G|^FK,,y>PD6'=J;\zI, D6'=J#+G,mo=D6'=JGITD,}g,where month(c1) between 4 and 6#bITIC#f==9C,Dw},p<|* 4 x#9|* 6# *9K/}%w,k+j|(*B]D_;?V#fZT INTEGER ZC/}D)9T ozy]UZ(e%wmo=#INTEGER(date) 5XUZD{}m>(,ITV*iR jMB]D{}m>(#}g,INTEGER(date(’2000/05/24’)) 5X 20000524,rK INTEGER(date(’2000/05/24’))/100 = 200005#/} INTEGER(date)/100 G%wD# `FD,ZC/} DECIMAL M BIGINT 2_P)9,yTITIz%w/}#DECI- MAL(timestamp) 5X1dAGD.xFm>(,ITZ%wmo=P9C|4IzB ]"l"!1rVSHHDvSD5#BIGINT(date) 5XUZDs{}m>(,`FZ INTEGER(date)# ;*I\,}]b\mw+Z*m4(zIPr_y],SdPDmo=4(,17 (mo=D%wT#X(/};6p*#t%wT,}g,DATENUM( )"DAYS( ) M YEAR( )#"R,PM#?DwVcumo=,}g,}("K(rS(G#t%wT D#1 DB2 7(mo=;#t%wT1,r_g{|;\7(b;c,G4C,v' VTdy>P9CH[=J# MDC M ITC mD0k"bBn g{(Z+}]0k=}]VbP,G49C`,/: (MDC) ma\Poz#Z MDC mP,0kaH4CmPH0eUDi,;sE)9Cm"mSBiTc0k`BD }]# Z 3 B `,/: (MDC) m 45 Z>}K;i}].s(}g,;vBD}]),IT9C LOAD 5CLr40kB ;vBD}],"R|IT4CZ(Qd5)>}.sQ-eUDi#9IT!q+ MDC *v&\kSYe}dO9C#Zd5*v(|2G;n>}Yw).s,wv i"4;ME,P;\4Cb)i#+wCs(xLT,$yZGG<#*K7#T\}WN>}BG<# v g{4P0kYw0Cm4|,(;|,"R+(;|MX4G<<0kCm,G 4+;0kdP;vxP(;|DG<,"RZ>}WN>}d{G<# ":;P7PD}DG<# 0k+Si_g*<,yTnC+|CZtZB%*D}]"CZmDuG<"bBn k9C RID w}1Div`H,9C,"rKx9Ciw}1+uYw},$MU> G<$w?# v1>}K{viPDns;vG<1,}]b\mwEaSiw}P}% BID#K1 9aGG<$w?`TO!#T +G}Yw#TZ*v>},;aG<;>}DG<# xG,+(}XBq=/3D3)?V9|,b)Gm# MDC M ITC mDiw}"bBn 1(e MDC mD,1,+4(,iw}#Kb,g{(eK`v,,G49I4(i Oiw}#g{;* MDC m(eK;v,,r_mGek1d/: (ITC),G4v4 (;viw},|+,1d1,iw}MiOiw}#TZ}]Vx MDC r ITC m, 53aTmD MDC r ITC iw}xPVx# ,y,g{4(D MDC m_PkTP A MkTP A MP B D,,G4TP A 4 (,iw},"TP A MP B 4(,iw}#r*iOiw}GmPDyP,Diw },yTP A MP B D,iw}2+d1iOiw}# TZ MDC m,Zi/&mPiOiw}2CZCJ_PX(,5DmPD}]#iO iw}PD|?VD3rI\a0l|TZi/&mD9CrJCT#|D|?VD 3rI4( MDC m19CD{v ORGANIZE BY DIMENSIONS SdPDPD3r 7(#}g,g{mG9CBPod4(D: CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int) ORGANIZE BY DIMENSIONS (c1, c4, (c3,c1), c2) G4aTP (c4, c3, c1, c2) 4(iOiw}#!\ c1 Z,SdP8(K=N,+G |vZw*iOiw}D|?V19CK;N,"RGTZ;N"V|D3r9C D#iOiw}PD|?VD3rTZek&m;P0l,+TZi/&mI\GP 0lD#rK,g{|#{iOiw}_PP3r (c1,c2,c3,c4),G4&9CTBod4 4(m: CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int) ORGANIZE BY DIMENSIONS (c1, c2, (c3,c1), c4) MDC mDiw} >wb5wgN9Ciw}i/ MDC mPDG<# Z 48 3D< 12 PT>D MDC mGTom==i/D,byMIT+_P`, “Region”M“Year”5DGG!vZ|D|?VD3r#|?V3rGIbvw ZTZ CREATE TABLE odD ORGANIZE BY SdP8(D,xPbv1v=D P3r47(D#PX|`E",kND Z 47 3D:MDC M ITC mDiw}"bB n;# iw}k#fw}Za9OG`,,;;}|G8rDGix;GG<#iw}H# fw}!D6}*?3=yG<}KTis!yC=DK}#;viPD3}HZm UdD)9}]is!,|D6'G 2 = 256 3#3s!IT* 4KB"8KB"16 KB r 32 KB# g< 13 Py>,k?;P_P%vu?`H,Ziw}P,?vi,"Ry]mPVdD)9}]iD_-3rxP`E#< mPDxqm>b)iD_-}]bVx,"R?v}=Nm>;v_-%*#xq PDPMPm>X(,D,#}g,“Region”PP|,“South-central”5DyPG<< ITZIxqPD“South-central”P(eD,P|,DiPR=#5JO,K,PD? vi2;|,“Region”VNP_P“South-central”DG<#by,1Rv1;vi|, Z“Region”VNP_P“South-central”DG<1,CiE|,ZK,rxqDPP# *KcZ7(D)iiI,,r_D)i|,_PX(,|5DyPG<,Z4(m 1+T/*?v,4(,iw}# ZZ 51 3D< 15 P,;v,iw}Gy]“YearAndMonth”,4(D,m;v,iw} Gy]“Region”,4(D#?v,iw}Da9==k+3 RID w}Da9==`,, +Z66,b)|8rij6 (BID) x;GGi#r*iPDyP3ZomOGSC3*<,xD," RRG*@CiDs!,yTIT9CK BID 4R=CiPDyPG<# ,,r|,xPZ,P_PX(|5DG# =1  1 Region YearAndMonth 9901 9903 9904 9902 Northwest NortheastSouthwest South central- 1 5 316204 2 34 4524 30 12 14 31 50 54 56 18 32 33 36 11 6 7 10 13 22 26 15 38 5125 8 17 44 53 43 9 39 19 41 42 < 14. _P“Region”M“YearAndMonth”,DF* Sales D`,m 50 VxM/:8O < 16 T>“Region”D,iw}PD|gNvV#|I|5(4“South-central”)M BID PmiI#?v BID |,;vi;C#Z< 16 P,P>DiEkZ Sales mDxq P"VD“South-central”,`,(kNDZ 50 3D< 14)# `FD,*R=|,“YearAndMonth”,*“9902”DyPG# =1  1 Region YearAndMonth 9901 9903 9904 9902 Northwest NortheastSouthwest South central- 1 5 316204 2 34 4524 30 12 14 31 50 54 56 18 32 33 36 11 6 7 10 13 22 26 15 38 5125 8 17 44 53 43 9 39 19 41 42 @B! B! YearAndMonth < 15. T>,iw}R_P“Region”M“YearAndMonth”,D Sales m South-central 9 1618192224253036394142 C BID ; /:( )BID < 16. “Region”D,iw}PD| Z 3 B `,/: (MDC) m 51 MDC mDiw}Mi/T\ (h MDC mDNNiw}+a)/:}]CJ,bGr*?vij6 (BID) # v mPD}]/:Z,x3O,byME/K I/O "+a{/(;=mD!qD?V# v g{ZyZiD:eXDis!HZ)9}]is!DivB9CC:eX,G4 +Q MDC iSELOD,x3$!=ZfD,x3P,byMx;=v?K/:T T\ypD}+wC# v ?viPDG<}ICJmPDd{i,x;h*kKi/D}]/ ;%# g{ Sales my]}v,xP/:,G49IT9Cvp,iw}4iR|,zcTm D y P , D S / D i / D G < D ; i i # g { m _ P “YearAndMonth”"“Region”M“Product”,,G4ITO*|G_-"=e,gZ 53 3 D< 18 Py># 9902 2 5 7 8 14 15 17 18 31 32 33 43 C BID ; /:( )BID < 17. “YearAndMonth”D,iw}PD| 52 VxM/:8O +T< 18 PT>D MDC m4(Dviw},*“YearAndMonth”"“Region”M“Product” ,?v<4(;viw};xm;viw}+yPb),Pw*|D|#*lw “Product”HZ“ProductA”"R“Region”HZ“Northeast”DyPG<,}]b\mw+W HS“Product”,iw}PQw ProductA |#(kND< 19#);s,}]b\mw( }Z“Region”,iw}PiR“Northeast”|47(|,“Region”HZ“Northeast”DyP G}*},*iR|,_Pb=v,5DyPG}5wgNTiw}9C_- OR Yw4zc_Pf0==v,D=JDi/# < 21 Y(;v MDC m_P=v,:“Colour”M“Nation”#?jGlw MDC mPz cTBu~DyPG<:“Colour”*“blue”r_“Nation”{F*“USA”# ><5wKgNiO=v%@Diw}(hDa{T7(zc=J^FD5D6'# (}?8>G}5wKITgN+iOiw}CZi/&m#g{k*iRZ 55 3D< 22 D mP“Region”*“Northwest”R“YearAndMonth”*“9903”DyPG<,G4}]b\m w+ZiOiw}PiR|5 9903, Northwest,g< 23 Py>#|I|5(4“9903, Northwest”)M BID PmiI#IT4=P>D BID vP 3 M 10,"R5JO Sales mP;P=vi|,_Pb=vX(5DG<# *K5wZekZdgN9CiOiw},RGTek_P,5 9903 M Northwest D m;uG<*}#}]b\mw+ZiOiw}PiRK|5,"iRZ 3 vMZ 10 viD BID#b)i|,_Pb),|5DvP+?G<#g{_PICUd,G4} ]b\mw+QBG>} P,m10;P9CZ 48 vi#}]b\mw+G_PmP?viD;,u?Di3d}i#R_T>K}ZgN9 CmD?v)9}]i:3))9}]iGICD,xs`})9}]i}&Z9C 4,,"RvZi3dPjG*}Z9CDiPR=KG<#*Kr%p{,Z

=v,iw}DdP;v# ": 1. iw}PfZv8ri3dPjG* IN USE DG)iD8k# 2. Z;viG#ti#Ki|,mD53G<# (}(hi3dPD FREE i(4,4hCNN;DG)i),\]WR=ICiTc Z%*P9C# m(h99Ci3d4vCJ10|,}]D)9}]i#y>;h*Zm(hP| (NN4Z9CD)9}]i#*KcZ5w,K>}(< 25)PDm(h+x}Z ;v#tD)9}]iMfsDU)9}]iSmPDZ}v)9}]i()9}] i 2)*<,(hmPDi 2"3 M 4,x}B;v)9}]i(;af0C)9}] iD}]3),;sSGoLx(h# KL PQ--TUVXZ[\ ^_`Q!-- f jkmq!  !wx  00 X X 11 F F 22 U 33 U 44 U U 55 F 66 U North, 1996 North, 1997 South, 1999 East, 1996 Year { < 25. i3dD$w== Z 3 B `,/: (MDC) m 57 S MDC M ITC mP>} Z MDC r ITC mP>}G<1,g{CG<;GiPDns;uG<,G4}]b \mw;>}CG<"SZCmO(eDNNyZG}Yw}%iPDns;uG<1,}]b\mw+MECi#+(}|D IN_USE 4,;"SyPiw}P}%CiD BID 4MECi#g{9fZyZG};P1}10G<.sekQ|DDG<,r*CG<+|D|ytZD _-%*#g{>}10G<}ns;uG<1Eh*|Biw}#r K,kiw}`X*DCZ,$MU>GGD)i M)9}]iO|,}]T0D)iM)9}]iO;P|,}]#_P}]Dij G*“}Z9C”#?1T MDC r ITC m4P>}rT MDC m4P*v1,_Pi 3dDiu?;YjG*“}Z9C”,xG;MET)Cm4C# 58 VxM/:8O +G,mUdPDd{Ts^(9Cb)iM)9}]i#IT(}Xim4SmP MEb)IC}])9}]i#I9Cx RECLAIM EXTENTS N}D REORG TABLE |n, T9XUUd1CmTC'ICR&Z*z4,#S MDC r ITC mME)9}]i v\ DMS mUdPDm'V# REORG TABLE |n9C RECLAIM EXTENTS N}4ME MDC r ITC m(CD)9} ]i,"R9CUdI)mUdZd{}]bTs9C# C!n9JmzXFZME)9}]iZdT MDC r ITC mD"PCJ#4CJ( *1!CJ(,9I!qACJ(M^CJ(4XF"PCJ# g{ MDC r ITC m9G6'Vxmr}]bVxm,G41!ivB,aZyP} ]r}]bVxOME)9}]i#(}8(Vx{F(TZ}]Vx)rVxE (TZ}]bVx),ITKPC|nTvZX(VxOME)9}]i# REORG TABLE |nM db2Reorg API yICZME)9}]i# T/'VICZ}]bDT/,$n/DME)9}]i?V#*tCXiTME MDC r ITC mPD)9}]i,auto_maint"auto_tbl_maint M auto_reorg }]bdC N}Xk+?_P5 ON#IT(}9C|nP4dCb)}]bdCN}#ZtCK} ]bVx&\D DB2 5}O,XkZ?H*v=Z}]# Z 3 B `,/: (MDC) m 59 v m9Cs6'Di/`M#+G,`TZ OLTP $w:X45,C$w:X(#G_ PBPXwDVvi/: – OsDa{/,n`P 2 YrP – s`}r+?i/<|PS<,x;Gy>m v SQL Sd46'(BETWEEN Sd)"PmPDnH!q}]# ww DB2 V9.1 }]VbB5mDXw ;vdMVbB5mI\ICTBhF: v Z Month PP4(}]Vx# v **vD?v1dN(}g,1 vBM 3 vB)(e}]Vx# v Z Day M 1 = 4 vd{,Dy!O4( MDC ,#dMD,P:zz_Mxr# v yP}]VxM MDC /:i/PD1Zhs"y]H07(DX w7((iDi/=8# m 7. +mVxk MDC mdO9C Jb (iD=8 (i *vZdD}]ICT mVx 9C DETACH PARTITION S d4*vs?}],"R;v VnYPO# i/T\ mVxM MDC MDC nJOC4i/`v,# mVx(}}]Vx{}a_ T\# nYXi MDC MDC ,$/:,SxuYxP XiDX*T# Z+3Qz0ZP*v;vB r|$1dD}] mVx }]VxITj+bvKh s#MDC ;pNNwC,"R v MDC ";JO# ZL1dQz0Z(!Z 1 V S)Zd*v;vBr|$1 dD}] mVx }]VxITj+bvKh s#MDC ;pNNwC,"R v MDC ";JO# *v;vBr|$1dD} ],",1Z;p'NN~q DivB9mTZa;i/D s5C'j+IC# MDC MDC ;\bv;?VKhs# IZm&ZQz4,D1dN +L,mVx";JO# ?l0k}](ALLOW READ ACCESS r ALLOW NO ACCESS) mVxM MDC K1 MDC _P\`C&#m Vx_Pv?DC&# “,x”0k}](ALLOW READ ACCESS) mVxM MDC K1 MDC _P\`C&#m Vx_Pv?DC&# “+3 BI”i/Di/4PT\ mVxM MDC MDC XpJOC4i/"= e/`v,#mVx(}Vx{ }a_T\# 60 VxM/:8O m 7. +mVxk MDC mdO9C (x) Jb (iD=8 (i (}{}xPXiDX*Tr uY4PNqyzzD;<0 l,9Xiyx4D;<0l 5=nM# MDC MDC ,$/:,SxuYxP X i D X * T # g { 9 C MDC,G4}]Vx;a)v ?C&#+G,g{;9C MDC,G4mVx(}ZVx 6p,$;)V#H/:aP zZuYXiDX*T# >} 1: # CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (Province); >} 2: =1  1 MDC Province( )  () YearandMonth 9901- 9902 9903- 9904 AB QBBC ON 1 5 316204 2 34 4524 30 12 14 31 50 54 56 18 32 33 36 11 6 7 10 13 22 26 15 38 5125 8 17 44 53 43 9 39 19 41 42 < 26. 4 YearAndMonth Vx"4 Province i/Dm Z 3 B `,/: (MDC) m 61 (}+ YearAndMonth mSA ORGANIZE BY Sd,ITqCO_Dj8LH,gZ 29 3D< 7 Py># CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (YearAndMonth, Province); g{Vxm`H,MDC mMVxmK;vi/,|;V*I"PKPD}v?V,5Xa{DYHH 4.P==KPCi/DYHl#b8?V%*1>#*9CVxZ"PT,XkJ 1XdC}]b#zIT!q"PH,rI53*z!q#"PHm>;vi/P" PKPD?V}# © Copyright IBM Corp. 2012 63 Vxd"PT Vxd"PTG8+;vi/V*`v?V"+b8?VCZ;vVx}]bD`v Vx(;Z;(r`(zwO)OD\&#Ci/T"P==KP#3) DB2 5CL r24PK`MD"PT# Vxd"PT+(#O*G%v}]bYw(}g,4(w}"0k}]br SQL i /)DYw8VI`v?V,dPDs?Vr+?YwITZ;(r`(zwOD; vVx}]bD`vVxPT"P==KP# Z 65 3D< 29 T>K;vi/,|;V*I"PKPD}v?V,5Xa{DYHH Z%v}]bVxO4.P==KPCi/DYHl# "PHZ\sLHO!vZz4(D}]bVx}Mz(e}]bVxiD==# SELECT FROM... ... |} < 28. VxZ"PT 64 VxM/:8O ,19CVxZMVxd"PT IT,19CVxZ"PTMVxd"PT#KiOa)K=V"PT,b29&m i/DYHTxSl# SELECT... ...FROM |} < 29. Vxd"PT SELECT... FROM...SELECT... FROM... SELECT... FROM...SELECT... FROM... |} < 30. ,19CVxd"PTMVxZ"PT Z 4 B "P}]b53 65 5CLr"PT DB2 5CLrIT{CVxZ"PT#|G9IT{CVxd"PT;0aGfZ`v }]bVx,x5CLrZ?v}]bVxZT"P==KP# LOAD 5CLrIT{CVxZ"PTM I/O "PT#0k}]G;vs?9C CPU DNq#LOAD 5CLr{C`v&mw44PgbvMq=/}]b`Nq#|2I 9C"P I/O ~qw4T"P==+}]4k]wP# ZVx}]b73P,LOAD |n(}ZmyZD?v}]bVxOxP"PwC4{C VxZ"VxdM I/O "PT# Z4(w}Zd,I"P4P}]D(hMsxEr#Z4(w}1,DB2 53H{C I/O "PTV{CVxZ"PT#bPzZZ"v CREATE INDEX od1"XBt/ Zd(t;vw}jG*^')0}]XiZdSlw}4(DYH# 8]M4-}]NqG1XXf0 I/O DNq#Z4P8]M4-Yw1,DB2 53 H{C I/O "PTV{CVxZ"PT#8]Yw(}T"P==A!`vmUd]w "T"P==l=4k`8]iJ,4{C I/O "PT# Vx}]b73 Vx}]b73G'V+}]V<=w}]bVxOD}]b20# v }]bVxG}]bD;?V,|IdT:D}]"w}"dCD~MBqU>i I#Vx}]b73G'V+}]V<=w}]bVxOD}]b20# v %;Vx}]bG;P;v}]bVxD}]b#C}]bPDyP}]Xw },bITa_>X}]DCJT\# ":";GXk+yPmv` ,DYwXwMl&1dD\&# >ZTBP2~73a)K;vEv: v %&mwOD%v}]bVx(%&mw) v _P`&mwD%v}]bVx (SMP) v `v}]bVxdC – _P%&mwD}]bVx (MPP) – _P`&mwD}]bVx(SMP /:) – _-}]bVx BfV[?V73D]?MIluT# %&mwOD%v}]bVx K73IZfMELiI,+v|,;v CPU(kNDZ 68 3D< 31)#K73PD }]bIzc;v?Er!l+RDh*,dPD}]M53J4(|(;v%&m wr CPU)I%}]b\mw4\m# Z 4 B "P}]b53 67 ]?MIluT ZK73PITmS|`DEL#g{C?vEL5P;vr`v I/O ~qw,G4` v I/O YwI,14P# %&mw53\&mwI&mDELUd]?D^F#^[ITmSDd{i~(g ZfrEL)gN,fE$w:XDvS,%v CPU I\^(|lX&mC'ks# g{Qo=ns]?rnsIluT,G4I CPU mƒ„…†‡ ˆ‰ < 31. %&mwOD%v}]bVx 68 VxM/:8O ]?MIluT IT(}vSEL}4vSk&mwX*D}]bVxD I/O ]?#IT(" I/O ~ qwT(E&m I/O ks#g{C?vEL5P;vr`v I/O ~qw,G4`v I/O YwI,14P# g{Qo=ns]?rnsIluT,G4IZhvBP}]bVxdC: v _P%&mwD53OD}]bVx v _P`&mwD53OD}]bVx v _-}]bVx _P%&mwD}]bVx ZK73P,Pm`}]bVx#?v}]bVx<;Z|T:DzwO,xR|P T:D&mw"ZfMEL(Z 70 3D< 33)#yPzw(}(E$_,SZ;p# I9Cm`;,D{F4FtK73,|(:/:"%&mw/:"sf#"P&m (MPP) 73M“;2m”dC#ns;v{F<7X43KK73PDJ42E#k SMP 73;,,MPP 73;P2mDZfrEL#MPP 73\bKI2mZfMELx4 D^F# ;vVx}]b73Jm;v}]b#VZ_-OD{eT,+|5JOV CPU CPU CPU CPU < 32. %;Vx}]bTF`&mw73 Z 4 B "P}]b53 69 ]?MIluT ZK73PITrzDdCmS|`D}]bVx#Z3)=(O,ns}?G 512 v }]bVx#+G,Z\m\`zwM5}1,I\fZ5JD^F# g{Qo=ns]?rnsIluT,G4I;vi/ITZg`&mwD%v }]bVxP4P#`4;vi/ITC"P==Z`v}]bVxP4P# ˆ‰ ˆ‰ˆ‰ mƒ„…†‡ mƒ„…†‡mƒ„…†‡ ... Œ‘ ‚> ‚>‚> CPU CPUCPU < 33. sf#"P&m (MPP) 73 70 VxM/:8O ]?MIluT ZK73P,ITmS|`D}]bVx,"ITrVP}]bVxmS|`D&m w# _-}]bVx _-}]bVxkomVxD;,.&ZZ_-Vx4;ZhT{(zwDXF(# d;zwQ2mJ4,+G}]bVx;2mJ4#&mwG2mD,+ELMZf 4;2m# _-}]bVxa)KIluT#Z`_-VxOKPD`v}]b\mwITH% v}]b\mw|\dV{CICDJ4#Z 72 3D< 355wK(}mS|`D}] bVxITZ;( SMP zwOqC|sDIluT;TZG)_Pm`&mwDzw xT|GgK#(}Vd}]b,ITVpT?v}]bVxxP\mMV4# CPU CPU CPU CPU ‚> CPU CPU CPU CPU ‚> Œ‘ SMP †‡ SMP †‡ ˆ‰ ˆ‰ < 34. /:PD8vTF`&mw (SMP) 73 Z 4 B "P}]b53 71 Z 73 3D< 36 Y}5wIT)s< 35 PT>DdCTv?&m\&# ˆ‰ ˆ‰ ’“ †‡SMP 1 2 ‚> ‚> CPU CPU CPU CPU Œ‘ < 35. xPTF`&mw73DVx}]b 72 VxM/:8O ":=vTOD}]bVx,1fZZ,;(zwOD\&(; ‚>‚> ‚> CPU CPUCPU CPU CPU CPUCPU CPU Œ‘ Œ‘ < 36. xP/PZ;pDTF`&mw73DVx}]b Z 4 B "P}]b53 73 74 VxM/:8O Z 2 ?V 20"bBn © Copyright IBM Corp. 2012 75 76 VxM/:8O Z 5 B 20Hvu~ 9C“DB2 20”r<420 DB2 }]b~qw (Windows) KNqhvgNZ Windows Ot/“DB2 20”r<#9C“DB2 20”r<4(e20 T0Z53O20 DB2 }]bz7# *<.0 Zt/“DB2 20”r<.0: v g{zrchCVx}]b73,kND:hCVx}]b73;# v 7#53zc20"ZfMEL*s# v g{zF.9C LDAP Z Windows Yw53 Active Directory P"a DB2 ~q w,kZ20.0)9?<#=,qrXkV/"aZc"`?}]b#PX|` E",kND“* LDAP ?<~q)9 Active Directory #= (Windows)”wb# v XkP_8(iDC'(^D>X\m1C'J'E\4P20#Z LocalSystem I TCw DAS M DB2 5}C'Rz49C}]bVx&\D DB2 }]b~qwP, _Pa}X(DG\m1C'IT4PK20# ":g{rc9CG\m1C'J'xPz720,G4Z"T20 DB2 }]bz 7.0,Xk20 VS2010 KP1b#Yw53Oh*P VS2010 KP1b,;s E\20 DB2 }]bz7#IS Microsoft KP1bBX Web >cqC VS2010 KP1b#P=v!n:TZ 32 ;53,!q vcredist_x86.exe;TZ 64 ;5 3,!q vcredist_x64.exe# v (izXUyPDLr(d;;G?F),by20LrIT|BFczODNb D~x;h*XB}<# v Sib}/wr43dDxg}/w(}g Windows J4\mwP \\hostname\ sharename)20 DB2 z7;\'V#Z"T20 DB2 z7.0,Xk+xg}/ w3dA Windows L{(}g Z:)# ^F v (}NNC'J'<^(KP`v“DB2 20”r<5}# v DB2 1>{FM5}{;\T}V5*<# DB2 1>{Fn$I* 64 v"oV {,b)V{ITG A-Z"a-z M 0-9# v DB2 1>{FM5}{ZyPD DB2 1>PXk(;# v ;\Tv_P;v}]bVxD}]b9C XML &\# v g{Q20BPdP;n,G4d{ DB2 }]bz7y;\20Z,;76P: – IBM® }]~qwKP1M'z – IBM Data Server Driver Package – DB2 E"PD v “DB2 20”rDYw53OtC)92+ T,G4C'XktZ DB2ADMNS r DB2USERS iE\KP>X DB2 |nM& © Copyright IBM Corp. 2012 77 CLr,bGr*P;vnbD2+T&\(C'CJXF)Z1!ivBa^F >X\m1y_PDX(#g{C';tZdPNN;vi,G4{G+;PT> X DB2 dCr&CLr}]DACJ(# }L Zt/“DB2 20”r<: 1. 9CQ* DB2 20(eD>X>X\m1J'4G<53# 2. g{z_P DB2 }]bz7 DVD,G4+dek}/wP#g{tCKT/KP &\,G4|+T/t/ DB2 20t/e#g{T/KP&\;\$w,k9C Windows J4\mw4/@ DB2 }]bz7 DVD,;s+w20I)20Dz7# g{FczOP420NN DB2 }]bz7,G4(}%w20Bz74t/2 0#q-“DB2 20”rp=jI20# g{FczOAYQ-20K;v DB2 }]bz7,G4IT: v %w20Bz7T4(BD DB2 1># v %w9CVPz7T|BVP DB2 1>"+&\mS=VP DB2 1>"}6V P DB2 V9.5 M V9.7 1>r20=Sz7# 6. “DB2 20r<”+7(53oT,"t/CoTD20Lr#*zozI8fe+8 Program Files ?}?]v# z9IT8(T:D DB2 }]bz72076# B;=v24 v i$20# v 4PXhD20sNq# PX20Zdv=DmsDE",ki4;Z My Documents\DB2LOG\ ?D~#CU>D~9CTBq=:DB2-ProductAbrrev-DateTime.log,}g,DB2- ESE-Tue Apr 04 17_04_45 2012.log# 78 VxM/:8O g{bG 64 ; Vista OB20D DB2 z7,xz9C 32 ; OLE DB a)Lr, G4XkV/"a IBMDADB2 DLL#*"aK DLL,kKPTB|n: c:\windows\SysWOW64\regsvr32 /s c:\Program_Files\IBM\SQLLIB\bin\ibmdadb2.dll dP Program_Files m> Program Files ?XFczrxgPDm;FczOD DB2 D5, G4Xk20 DB2 E"PD#DB2 E"PD|, DB2 }]b53M DB2 `Xz7 DD5#g{;PZ>X20 DB2 E"PD,G41!ivB+S Web CJ DB2 E "# IT(}KP“DB2 20”r<420 IBM Data Studio# DB2 Express®fM DB2 $wi~qwfDZf^F g{*20 DB2 W]f,G4JmCZ5}DnsZf* 4GB# g{*20 DB2 $wi~qwf,G4JmCZ5}DnsZf* 64 GB# Vdx5}DZf?I INSTANCE_MEMORY }]b\mwdCN}7(# S V9.5 r V9.7 xP}61DX*5w: v Tw{Zf\mw;a+5}Zf\?^Fvs=,vmI$^ F# *Vx DB2 ~qw<873(Windows) >wbhvKZ<8 Windows 73TcT DB2 }]bz7xPVx201h*4P D=h# *<.0 ?(NkFczwFczDwz{#g{bTI&,z+SU=`FgBD dv: Pinging ServerA.ibm.com [9.21.27.230] with 32 bytes of data: Reply from 9.21.27.230: bytes=32 time<10ms TTL=128 Reply from 9.21.27.230: bytes=32 time<10ms TTL=128 Reply from 9.21.27.230: bytes=32 time<10ms TTL=128 X44Pb)=h,1=7(yPNkFcz<\9C TCP/IP `%xP(E* 9#?(FczXk_P2, IP X7# g{}rc9C`vxgJdw,IT8(*9CDvJdwZ}]bVx~ qw.dxP(E#20jIs,9C db2nchg |nZ db2nodes.cfg D~P8 (“xg{”VN# 5. 20Zd,53+*sza) DB2 \m~qwC'J'# bG+I DB2 \m~ qw(DAS)9CD>XrrC'J'#DAS GCZ'V GUI $_M-zjI\ mNqD\m~q#VZIT(e;vC',2ITC“DB2 20”r<4(;vC '#g{k*9C“DB2 20”r<4(BDrC',C44P20DJ'Xk_P 4(rC'D(^# 6. Z+205}5PDVxDwFczO,Xk_PtZ>X Administrators iDrC 'J'# 20 DB2 }]bz71,z+TKC'm]G<#Xk+,;C'J' mSA?(NkFczOD>X Administrators i#KC'Xk_PTYw53== YwC'(^# 7. 7#5}PDyPFcz<_P,;v>X}/wL{OD}]b?<# zIT( }KP GET DATABASE CONFIGURATION |n"i$ dftdbpath DBM dCN}54 liKiv# 8. 20Zd,a*sza)k DB2 5}`X*DrC'J'# ?v DB2 5}<8 (K;vC'#t/5}1,DB2 }]b53+9CKC'{G<#VZIT(e ;vC',2ITC“DB2 20”r<4(BDrC'# 1mSBZc=Vx73P1,DB2 1>{FXkZyPDFczO`,# g{k*9C“DB2 20”r<4(BDrC',C44P20DJ'Xk_P4( rC'D(^#5}C'rJ'ZyPNkFczOXktZ>X Administrators i,"R+;ZhBPC'(^: v TYw53==Yw v 4(jGTs v x(ZfPD3 v w*~qG< v vS^n v f;xL6pjG g{!qK)92+T,G4CJ'9XkG DB2ADMNS iDI1#DB2ADMNS iQ-_Pb)X(,rK,Q-TCJ'T=XmSKb)X(# 80 VxM/:8O lY(E\mw(Windows) Z`I173P,?vI1P;T FCM X$Lr,CZ'VI1dkzmLrks` XD(E#;vX$LrCZ"M(E,m;vX$LrCZSU(E#b)X$L rM'Vy!a9GZ5}t/1$nD#FCM (E2CZZ,;I1Z$wDzmL r;K`MD(EVF*I1Z(E# IT9C fcm_num_buffers }]b\mwdCN}48( FCM {":ex}#IT 9C fcm_num_channels }]b\mwdCN}48( FCM (@}#1!ivB, fcm_num_buffers M fcm_num_channels }]b\mwdCN}QhC* AUTOMATIC# g{hC* AUTOMATIC(bG(iDhC),G4 FCM a`SJ49Civ"w{J 4Tzc$w:Xhs# DB2 }]b~qw20Ev(Linux M UNIX) KwbEvKZ AIX"HP-UX"Linux M Solaris O20 DB2 ~qwz7D=h# }L *20 DB2 ~qwz7: 1. i4 DB2 z7Hvu~# 2. g{a)K DB2 }6E",ki4KE"# 3. Z HP-UX"Linux M Solaris O^DZKN}#Z} Linux on x86_32 .bDyP =(O,zXk20 64 ;ZK,;sEITLxxP20,qr20+'\# 4. <820iJ: z7 DVD g{ DB2 z7 DVD 4T/20,G420 DB2 z7 DVD# 203q g{QBX203q,G4+CD~b9u# 5. 9CdP;VIC=(420 DB2 z7: v “DB2 20”r< v 9Cl&D~xP2,20 v P'Z]D~?p TZ DB2 ~qw,zIT9C“DB2 20”r<44P20MdCNq,}g: v !q DB2 20`M(dM"+rr(F)# v !q DB2 z720;C# v 20zTsIT8(DoTw*z7gfM{"D1!oT# v 20r}6 IBM Tivoli® System Automation for Multiplatforms(Linux M AIX)# v hC DB2 5}# v hC DB2 \m~qw(|( DAS C'hC)# v hC DB2 Text Search ~qw# v hC\m*5KMKP4v`Sw(*# v hCMdC5}(|(5}C'hC)# v hC Informix }]4'V# Z 5 B 20Hvu~ 81 v <8 DB2 $_?<# v 8( DB2 E"PDKZ# v 4(l&D~# 6. g{9C}“DB2 20”r<.bDd{=(20 DB2 ~qw,G4h*4P20 sdC=h# DB2 20=( 20 DB2 }]bz7D=(P`V#?V20=(Yw53ICD20=(# m 9. Yw53ICD20=( 20=( Windows Linux r UNIX “DB2 20”r< GG l&D~20 GG db2_install |n q G P'Z]D~?p q G *c:;Fv9C db2_install |n,+4D"PfPI\a}%K|n#kD*9C db2setup |nrl&D~20=(# TBPmhvK DB2 20=(# “DB2 20”r< “DB2 20”r“DB2 20”r<# l&D~20 l&D~G;v|,hCMdC5DD>D~#“DB2 20”Lr+A!CD~, "y]Q8(D544P20# l&D~202F*2,20# l&D~Dm;vEcG:|Ga)KTG);\9C“DB2 20”r#KP*I INTERACTIVE l&D~X|VXF# ITICBP=(44(l&D~: 9Cl&D~zIw IT9Cl&D~zIw44(;vCZ4FVP20Dl&D~# 82 VxM/:8O }g,IT20 IBM }]~qwM'z"j{dCCM'z,;sz Il&D~T+CM'zD20MdC4F=d{Fcz# 9C“DB2 20”r< “DB2 20”rl&D~ }K9Cl&D~zIwr“DB2 20”r<44(l&D~Tb,9I TV/^Dy>l&D~#DB2 }]bz7 DVD Oa)Ky>l& D~#y>l&D~a)KXZ?vz7DyPP'X|VDj8E "# db2_install |n(vJCZ Linux M UNIX Yw53) db2_install |n+(}"ogf'V420z8(D DB2 }]bz7DyP i~#(}9C -L N}MIT!q*'VDd{oT#z;\!qre}i ~# !\ db2_install |na20z8(D DB2 }]bz7DyPi~,+|; a4(C'Mi,;a4(5},2;a4PdC#Z20.s4PdC1, K20=(I\GW!#*Z20 DB2 }]bz71dCKz7,k# K20=(*sZ?pz7D~.sxPV/dC# PG:;Fv9C db2_install |n,+4D"PfPI\a}%K|n# P'Z]D~?p(vJCZ Linux M UNIX) K=(G;V_620=(,TZs`}C',;Fv9CK=(#|*sC 'Tom==20P'Z]D~#P'Z]D~G;v9uD tarball,||,I 20Di~DyPD~M*}]# K20=(*sZ?pz7D~.sxPV/dC# ":DB2 }]bz720;YG Linux M UNIX ODYw53Lr|#rK,IT; Y9CYw53|n4xP20#Xk|DzC4k DB2 }]bz720xP;%T 0i/ DB2 }]bz720DNNVPE># Z 5 B 20Hvu~ 83 9C“DB2 20”r<420 DB2 ~qw(Linux M UNIX) KNqhvgNZ Linux M UNIX Yw53Ot/“DB2 20”r<#“DB2 20”r< C4(e20W!nT0Z53O20 DB2 }]bz7# *<.0 Zt/“DB2 20”r<.0: v g{zrchCVx}]b73,kND20 DB2 ~qwPD:hCVx}]b7 3; v 7#53zc20"ZfMEL*s# v 7#20K\'VD/@w# v IT9C root C'(^r_G root C'(^420 DB2 }]b~qw#PXG root C'20D|`E",kND20 DB2 ~qwPD:G root C'20Ev(Linux M UNIX);# v DB2 }]bz73qXkIC#I(}:r5e DB2 }]bz7 DVD r_S Pass- port Advantage BX203q4q! DB2 203q# v g{*20G"Df>D DB2 }]bz7,G4Xk_PJ1D>XoTLr|# v “DB2 20”rZ]#}g,export DISPLAY=9.26.163.144:0. v g{*ZzyZ73P9C2+Tm~,G4Zt/“DB2 20”r<.0XkV/4 (XhD DB2 C'# ^F v (}NNC'J'<^(KP`v“DB2 20”r<5}# v ;\T9Czk/ UTF-8 (eD"R;P;v}]bVxD}]b9C XML &\# v “DB2 20”r DB2 }]bz7 DVD D20c# 2. g{BXK DB2 }]bz73q,G4XkTz7D~xPb9u# a. b9uz7D~: gzip -d product.tar.gz dP product GBXDz7D{F# b. b9z7D~: 84 VxM/:8O Z Linux Yw53O tar -xvf product.tar Z AIX"HP-UX M Solaris Yw53O gnutar -xvf product.tar dP product GBXDz7D{F# c. |D?<: cd ./product dP product GBXDz7D{F# ":g{BXK>XoTLr|,G4+db9uA,;v?# 3. (}S}]bz73qyZ?I)20Dz7# (}%w“20Bz7”t/20#q-“DB2 20”rp=jI20# 6. 9C“DB2 20”r<1Dy>fe+8G root C'Dw?<# TZ root C'20,DB2 }]bz7Z1!ivB20ZBPdP;v?}?]v# z9IT8(T:D DB2 }]bz72076# DB2 2076_PBPfr: v IT|,!4V8(a–z)"s4V8(A–Z)MB._V{( _ ) v ;\,} 128 vV{ v ;\|,Uq v ;\|,G"DV{ 20U>D~G: Z 5 B 20Hvu~ 85 v DB2 20U>D~#KD~+6q|(msZZDyP DB2 20E"# – TZ root C'20,DB2 20U>D~{G db2setup.log# – TZG root C'20,DB2 20U>D~{G db2setup_username.log,dP username GCZ4P20DG root C'j6# v DB2 msU>D~#KD~a6qI Java 5XDNNmsdv(}g,l#M]e E")# – TZ root C'20,DB2 msU>D~{G db2setup.err# – TZG root C'20,DB2 msU>D~{G db2setup_username.err,dP username GCZ4P20DG root C'j6# 1!ivB,b)U>D~Z /tmp ?D~D;C# ;YfZ db2setup.his D~#+G,DB2 20Lra+ DB2 20U>D~D1># fZ DB2_DIR/install/logs/ ?}?# ?v201>,G4K 2076BDz7GXoT|yZD?XoT|# Z Linux x86 O,g{k* DB2 }]bz7\;CJ>XFczrxgODm;Fc zOD DB2 D5,G4Xk20 DB2 E"PD#DB2 E"PD|, DB2 }]b5 3M DB2 `Xz7DD5# DB2 W]fM DB2 $wi~qwfDZf^F g{*20 DB2 W]f,G4JmCZ5}DnsZf* 4GB# g{*20 DB2 $wi~qwf,G4JmCZ5}DnsZf* 64 GB# Vdx5}DZf?I INSTANCE_MEMORY }]b\mwdCN}7(# S V9.5 r V9.7 xP}61DX*5w: v g{ V9.5 r V9.7 DB2 }]bz7DZfdC,}JmD^F, G4Z}6=10f>.s,DB2 }]bz7I\^(t/# v Tw{Zf\mw;a+\D5}ZfO^vs=,vmI$^ F# lY(E\mw(Linux M UNIX) lY(E\mw (FCM) a)TVx}]b73D(E'V# 86 VxM/:8O Z`I173P,?vI1P;T FCM X$Lr,CZ'VI1dkzmLrks` XD(E#;vX$LrCZ"M(E,m;vX$LrCZSU(E#b)X$L rM'Vy!a9GZ5}t/1$nD#FCM (E2CZZ,;I1Z$wDzmL r;K`MD(EVF*I1Z(E# FCM X$Lr+U/PX(En/DE"#IT(}9C}]b53`Sw4qCPX FCM (EDE"#g{I1.dD(E'\,r_|GXB("(E,G4 FCM X $Lra9CKE"4|B`S*X#FCM X$Lr9a*KB~%"J1DYw#} g,Xv\=0lDBq#IT9C}]b53`Sw4ozzhC FCM dCN}# IT9C fcm_num_buffers }]b\mwdCN}48( FCM {":ex}#IT 9C fcm_num_channels }]b\mwdCN}48( FCM (@}#1!ivB, fcm_num_buffers M fcm_num_channels }]b\mwdCN}QhC* AUTOMATIC# g{hC* AUTOMATIC(bG(iDhC),G4 FCM a`SJ49Civ"w{J 4Tzc$w:Xhs# Z 5 B 20Hvu~ 87 88 VxM/:8O Z 6 B 20.0 d{Vx}]b73$20Nq(Linux M UNIX) |BCZVx DB2 20D73hC(AIX) KNqhvK+NkVx}]b53D?(FczOh*|BD73hC# }L *|B AIX 73hC: 1. Tx root C'(^DC'm]GO,+ TCP/IP xgN}hC*BP5#b)5 yPxg`XN}D10hC,dkTB|n: no -a | more *hCN},dkTB|n: no -o parameter_name=value dP: v parameter_name m>k*hCDN}# v value m>k*TKN}hCD5# }g,*+ tcp_sendspace N}hC* 221184,dkTB|n: no -o tcp_sendspace=221184 4. g{*9C_Y%,,G4Xk+ css0 D spoolsize M rpoolsize hC*TB 5: spoolsize 16777216 rpoolsize 16777216 *P>b)N}D10hC,dkTB|n: © Copyright IBM Corp. 2012 89 lsattr -l css0 -E *hCb)N},dkBP|n: /usr/lpp/ssp/css/chgcss -l css0 -a spoolsize=16777216 /usr/lpp/ssp/css/chgcss -l css0 -a rpoolsize=16777216 g{49C /tftpboot/tuning.cst D~4w{53,G420sI9C DB2DIR/ misc/rc.local.sample y>E>D~(dP DB2DIR G DB2 }]bz7D207 6)4|BxgD`XN}#*Z20.s9Cy>E>D~4|Bxg`XN },4PBP=h: a. (}dkBP|n,+KE>D~4F= /etc ?<,"9|II root C'4 P: cp /usr/opt/db2_09_01/misc/rc.local.sample /etc/rc.local chown root:sys /etc/rc.local chmod 744 /etc/rc.local b. i4 /etc/rc.local D~"TdxP|B(g{PX*D0)# c. r /etc/inittab D~mS;vu?,Tc?1zwXB}<14P /etc/ rc.local E># I9C mkitab |n4r /etc/inittab D~mS;vu?# *mSKu?,dkTB|n: mkitab "rclocal:2:wait:/etc/rc.local > /dev/console 2>&1" d. (}dkTB|n,7# /etc/inittab D~|( /etc/rc.nfs u?: lsitab rcnfs e. (}dkTB|n,Z;XB}<53DivB|BxgN}: /etc/rc.local 5. 7#Pc;w3Ud) DB2 Enterprise Server Edition DVx20KP# g{;P c;Dw3Ud,G4Yw53+#9}Z9Cs?VibZf(bI\GdP; v DB2 xL)DxL# *liICDw3Ud,dkTB|n: lsps -a K|n+5X`FZTBDdv: Page Space Physical Volume Volume Group Size %Used Active Auto Type paging00 hdisk1 rootvg 60MB 19 yes yes lv hd6 hdisk0 rootvg 60MB 21 yes yes lv hd6 hdisk2 rootvg 64MB 21 yes yes lv ICw3Ud&GZFczO20DomZf?D=6# 6. g{}Z4(!M=PMDVx}]b53,G45P5}DFczODxgD~ 53X$Lr(NFSD)D}?&CS|Z: ?(FczOD biod } × 5}PDFcz} mkivB,&CZ?(FczOKP 10 v biod xL#y]TO+=,ZID( FcziID53O(?(FczOP 10 v biod xL),&C9C 40 v NFSD# g{}Z20sM53,G4FczOn`IT20 120 v NFSD# PX NFS Dd{E",ND NFS D5# 90 VxM/:8O hC$w/OT+|nV"A`v AIX Zc Z AIX ODVx}]b73P,IThC$w/OT+|nV"ANkVx}]b5 3D;i System p® SP $w>#9C dsh |nIT+|nV"A$w># *<.0 Z AIX O20r\mVx}]b531bI\a\PC,|9z\;T73PDyP FczlYX4P`,|n"RuYKvmDI\T# Xk*@*|(Z$w/OPD?(FczDwz{# zXkTxP root C'(^DC'm]G# XkP;vD~,CD~P>+NkVx}]b53DyP$w>Dwz{# }L *hC$w/OT+|nV"A$w>Pm,k4PTBYw: 1. 4(F* nodelist.txt DD~,CD~+P>+Nk$w/ODyP$w>Dwz {# }g,Y(zkhz{* workstation1 M workstation2 D=v$w>44($w /O#nodelist.txt DZ]&*: workstation1 workstation2 2. |B$w/O73d?# *|BKPm,dkTB|n: export DSH_NODE_LIST=path/nodelist.txt dP path G4( nodelist.txt D;C,x nodelist.txt G4(DD~D{F, CD~P>$w/OPD$w># 3. (}dkTB|n,i$$w/OPD{FGq75Gk*D$w>: dsh -q z+SU=kBPZ]`FDdv: Working collective file /nodelist.txt: workstation1 workstation2 Fanout: 64 i$ NFS GqZKP(Linux M UNIX) ZhC}]bVx73.0,&i$xgD~53 (NFS) Z+NkVx}]b53D? (FczOGq}ZKP# *<.0 NFS XkZ?(FczO<}ZKP# }L *i$ NFS GqZ?(FczO<}ZKP: v AIX Yw53: Z 6 B 20.0 91 Z?(FczOdkTB|n: lssrc -g nfs NFS xLD4,VN&C8>n/#i$K NFS Gq}Z?v53OKPs,&l i DB2 }]bz7yhDX( NFS xL#XhDxLP: rpc.lockd rpc.statd v HP-UX M Solaris Yw53: Z?(FczOdkTB|n: showmount -e hostname dk;x hostname N}D showmount |n4li>X53#g{ NFS ;n/,G 4z+SU=`FgBD;u{": showmount: ServerA: RPC: Program not registered i$K NFS Gq}Z?v53OKPs,&li DB2 }]bz7yhDX( NFS xL: rpc.lockd rpc.statd IT9CBP|n4lib)xL: ps -ef | grep rpc.lockd ps -ef | grep rpc.statd v Linux Yw53: Z?(FczOdkTB|n: showmount -e hostname dk;x hostname N}D showmount |n4li>X53# g{ NFS ;n/,G4z+SU=`FgBD;u{": showmount: ServerA: RPC: Program not registered i$K NFS Gq}Z?v53OKPs,&li DB2 }]bz7yhDX( NFS xL#XhDxLG rpc.statd# IT9C ps -ef | grep rpc.statd |n4liKxL# g{b)xL;PKP,G4NDzDYw53D5# i$NkFczODICKZ6'(Linux M UNIX) KNqhvKi$NkFczODICKZ6'1h*4PD=h#KZ6'IlY (E\mw (FCM) 9C#FCM G DB2 C4&m}]bVx~qw.dD(ED&\ ?~# *<.0 &CZ205P5}D}]bVx~qw.s,"Z20NNNkD}]bVx~q w.0i$NkFczODICKZ6'# 92 VxM/:8O 1ZwFczO205P5}D}]bVx~qw1,DB2 y]8(DNkVx}]b 73D_-}]bVx~qw}?#tKZ6'#1!6'*DvKZ#TZ?vN kVx}]b73D~qw,zXk* FCM KZV/dC /etc/services D~#FCM KZ6'!vZ*ZNkDFczO9CD_-Vx}?#AYh*=vu?: DB2_instance M DB2_instance_END#ZNkDFczO8(D FCM KZDd{*s: v p}: DB2_db2inst1 60000/tcp DB2_db2inst1_1 60001/tcp DB2_db2inst1_2 60002/tcp DB2_db2inst1_END 60003/tcp DB2 +#t 60000 .sD0DvICKZ# 3. Z?(NkFczO,r* services D~,"i$ZwFczD services D~P * DB2 FCM #tDKZGq49C# 4. g{NkD3(Fcz}Z9Ch*DKZ,G4&7(yPFczDICKZ6 ',"|B?v services D~(|(wFczOD services D~)# B;=v24 ZwFczO205P5}D}]bVx~qw.s,XkZNk}]bVx~qw O20 DB2 }]bz7#IT9C*Vx~qwzIDl&D~(1!{F* db2ese_addpart.rsp),zh** FCM KZV/dC /etc/services D~#FCM K Z6'!vZ*Z10zwO9CD_-Vx}?#AYh*P DB2_ M DB2__END b =v_P,xICKZEDu?#Z?(NkzwO9CD FCM KZEyXk_P` ,DpX==20CD~5 3# PX|`|nE",kND Linux V"D5# }L *4(T NFS ==X==20zUU4(DD~53,"r /etc/fstab D~mS;vu?,Tc 53?NXB}<1<20KD~53# }g: /dev/hda1 /db2home ext3 defaults 1 2 4. *Z Linux OZ}<1T/,18(20c!n1,7#CD~5 3GIA4D"GZ}<1220D,||(K bg(s()!n,R setuid Lr IT}#KP# fusion-en:/db2home /db2home nfs rw,timeo=7, hard,intr,bg,suid,lock dP fusion-en m>zw{F# 6. T NFS ==Z/:PD`B?(zwO20zw{F# K showmount |n&P>Szw fusion-en D~53D~53}P,20c* /db2home# g. +Z53XBt/1T/20VNhC*G# `BVNI#t1!hC# h. %w7(# 2. D{F# 9C:E (,) w*?v{F.dD(g{#}g,ServerA, ServerB, ServerC#g{GZ9C_Y%,,RG(izZKVNP9*8(? v$w>D_Y%,{F#`BVNI#t1!hC# f. %w7(# 3. "z# 4. S?(NkFcz20 DB2 wD~53# G},9C /db2home# e. Z6L?},&dkzZ20cD76{(76)VNPdkD5# f. Z6L?<+^(CJK DB2 w?<# g. +"420,+u?mSA /etc/filesystems 9G+?VNhC*+?# h. + /etc/filesystems u?+Z53XBt/120?Fcz+;a^^ZX"TT6L==20C?<#220m>zw +;#X"T20C?<#baZ53@#1zzJb#(iz+KVNhC* 220# `BVNI#t1!hC# 96 VxM/:8O k. 7#KD~53GZGqJmZKD~53P4P SUID M sgid LrVNhC *GDivB20D# bG1!hC# l. %w7(# m. "z# 20 DB2 pureScale Feature 1yhDC' (Linux) Z Linux Yw53OKP DB2 }]b73h*=vC'Mi# *<.0 v Xk_P root C'(^E\4(C'Mi# v g{9C2+Tm~4\mC'Mi,G4Z(e DB2 C'Mi1I\9h*4P d{=h# XZKNq h*=vC'E\4( DB2 pureScale 5}: v ;vC'w*5}yP_ v ;vC'w*\@$DC' &C+=v;,DC'k=v;,DidO9C#=vC'wT&CZyPwzO_ P`,D UID"GID"i{Mw?<#g{*9CDNNC'ZNNwzPfZ,k7 #{G_P%dDtT#;XZ*<20.04(b)yhDC'#ITZ4P“DB2 2 0”rD*s# BP8>E"P9CDC'{Mi{G1!5,"ZBmPxPK5w#IT8(z T:DC'{Mi{,+G|GXkq-53|{frM DB2 |{fr# m 10. 1!C'Mi XhDC' C'{ i{ 5}yP_ db2sdin1 db2iadm1 \@$DC' db2sdfe1 db2fadm1 Bm5wZBP8nP9CDC'{Mi{#IT8(zT:DC'{Mi{,+G |GXkq-53|{frM DB2 |{fr# g{F.9C“DB2 20”r<420 DB2 }]bz7,G4“DB2 20”r<+4(b )C'# ^F z4(DC'{Xk,1{OYw53|{frM DB2 }]b53|{fr# z+Z;,wzO4(D`,C'{Xk_P`,Dw?<#+G,CC'{;\Q ZNNwzOfZ#g{9CVPC'{,G4b)C'{XkZyPwzOz|D?vC'D\k# 7. "z# 8. Z+Nk}]b73D?(FczO4(j+`,DC'MiJ'# ZVx}]b73P*20 DB2 ~qw4(XhC'(AIX) Z AIX Yw53ODVx}]b73PKP DB2 }]bh*}vC'Mi# *<.0 v Xk_P root C'(^E\4(C'Mi# v g{9C2+Tm~4\mC'Mi,G4Z(e DB2 C'Mi1I\9h*4P d{=h# XZKNq Bm5wZBP8nP9CDC'{Mi{#IT8(zT:DC'{Mi{,+G |GXkq-53|{frM DB2 |{fr# g{F.9C“DB2 20”r<420 DB2 }]bz7,G4“DB2 20”r<+4(b )C'# m 11. XhDC'Mi XhDC' C'{ i{ 5}yP_ db2inst1 db2iadm1 \@$DC' db2fenc1 db2fadm1 DB2 \m~qwC' dasusr1 dasadm1 g{ DB2 \m~qwC'GVPC',G4KC'XkfZZyPNkFczO,; sE\20#g{9C“DB2 20”rz|D?vC'D\k# 7. "z# 8. Z+NkVx}]b73D?(FczO4(j+`,DC'MiJ'# Z 6 B 20.0 99 100 VxM/:8O Z 7 B 20 DB2 ~qwz7 hCVx}]b73 >wbhvgNhCVx}]b73#z+9C“DB2 20”r<4205P5}D}] b~qw"4(l&D~,;sY9Cb)l&D~44(NkD}]b~qw# *<.0 ":Vx}]b73ZG root C'20P;\'V# v 7#z_P+h*4F=yPNkFczPD InfoSphere® Vb$n CD mI$\ ?# v +*NkVx}]b73D?(FczO`,}?D,xKZXkUP#}g,g {Vx}]b73+ID(FcziI,G4b)FczO`,DDv,xKZy XkUP#Z4(5}Zd,+ZBPD~P#tKZ,xRd}?k10~qw OD_-Vx}?`H:Z Linux M UNIX O,+#tZ /etc/services P;xZ Windows O,+#tZ %SystemRoot%\system32\drivers\etc\services P#b)K Z+I“lY(E\mw”9C##tKZ+9CTBq=: DB2_InstanceName DB2_InstanceName_1 DB2_InstanceName_2 DB2_InstanceName_END ( ; X n D u ? G p < K Z ( DB2_InstanceName) M a x K Z (DB2_InstanceName_END)#d{u?<#tZ services D~P,T9d{&CL r;9Cb)KZ# v *K\'V`vNk DB2 }]b~qw,*20 DB2 DFczXktZ;vIC Jr#+G,IT+>XVxmSACFcz,49CFcz;tZ3vr# v Z Linux M UNIX 53O,Vx}]b53h*6L shell 5CLr#DB2 }]b 53'VTB6L shell 5CLr: – rsh – ssh 1!ivB,T6L DB2 Zc4P|n1(}g,t/6L DB2 }]bVx1), DB2 }]b53a9C rsh#*9C DB2 1!5,Xk20 rsh-server Lr|#P X|`E",kND6}]b2+T8O7PD:20M9C DB2 }]b\mw1 D2+T"bBn;# g{!q9C6L shell 5CLr rsh,G49Xk20"KP inetd(r xinetd)# g{!q9C ssh 6L shell 5CLr,G4h*Z DB2 20jIs"4hC DB2RSHCMD "amd?#g{;hCK"amd?,G49C rsh 5CLr# v Z Linux M UNIX Yw53O,7# etc ?iI# Vx}]bGP=vr|`vVxD}]b# }L *hCVx}]b73: 1. 9C“DB2 20”r<4205P5}D}]b~qw#PXj8D8>E",kN DJCZzD=(D:20 DB2 ~qw;wb# v Z!q20M/rl&D~4(0ZP,7#!q+20hC#fZl&D~P !n#jI20.s,Bf=vD~+;4F=Z“DB2 20”rODVx}]b73,kt/“DB2 6L|n~q ”2+T&\?~T#$}]MJ4# *K7#.V2+,kt/*/NDFcz(g{~q}Z LocalSystem J'DO BDBKP)rC'(g{~q}ZC'DG \m$_ > Active Directory C'MFcz# b. ZR0ZfeP,R|%w*t/DFczrC',;s!qtT c. %w#f!n("!PENFczw*/I4!r#TZC'hC,k%wJ '!n("ZJ'!niP!PENJ'T/N4!r#7#4!PJ'\t P,;\xP/Nr# d. %w7(Tt/*/NDFczrC'# Th*t/D?vFczrC'X4Ov=h#XkXBt/FczE\92+T |Dz'# 9Cl&D~ZNkDFczO20}]bVx~qw(Windows) ZKNqP,z+9C(}“DB2 20”r<4(Dl&D~ZNkDFczO20}] bVx~qw# *<.0 v Q-ZwFczO9C“DB2 20”r<20K DB2 1># v zQ-4(l&D~T20ZNkDFczM+d4F=NkDFcz# v zZNkDFczOXk_P\m(^# }L *9Cl&D~20d{}]bVx~qw: 1. 9CQ* DB2 20(eD>X\m1J'4G<+NkVx}]b73DFcz# 2. P;A|, DB2 }]bz7 DVD D?<# }g: cd c:\db2dvd dP db2dvd m>|, DB2 }]bz7 DVD D?{&dk setup |n,gBy>: setup -u responsefile_directory\response_file_name ZBP>}P,l&D~ Addpart.file IZ c:\responsefile ? }D|n+G: setup -u c:\reponsefile\Addpart.file 4. jI20.s,liU>D~PD{"#zITZ My Documents\DB2LOG\ ?D~#z&CZU>D~D)24=kBPE"`FDdv: === Logging stopped: 5/9/2007 10:41:32 === MSI (c) (C0:A8) [10:41:32:984]: Product: DB2 Enterprise Server Edition - DB2COPY1 -- Installation operation completed successfully. Z 7 B 20 DB2 ~qwz7 103 5. 1ZwFczO205P5}D}]bVx~qw1,DB2 }]bz7y]8(D NkVx}]b73D_-}]bVx~qw}?#tKZ6'#1!6'*Dv KZ#TZ?vNkVx}]b73D~qw,zXk* FCM KZV/dC /etc/ services D~#FCM KZ6'!vZ*ZNkDFczO9CD_-Vx}?#A Yh*=vu?:DB2_instance M DB2_instance_END#ZNkDFczO8(D FCM KZDd{*s: v pXFczrxgPDm;FczOD DB2 D5, G4Xk20 DB2 E"PD#DB2 E"PD|, DB2 }]b53M DB2 `Xz7 DD5# 9Cl&D~ZNkDFczO20}]bVx~qw(Linux M UNIX) ZKNqP,z+9C(}“DB2 20”r<4(Dl&D~ZNkDFczO20}] bVx~qw# *<.0 v Q-ZwFczO9C“DB2 20”r<20K DB2 }]bz7,"ZNkDFcz O4(KCZ20Dl&D~# v zZNkDFczOXk_P root C'(^# }L *9Cl&D~20d{}]bVx~qw: 1. w* root C'G<+NkVx}]b73DFcz# 2. P;AZdP4F DB2 }]bz7 DVD Z]D?<# }g: cd /db2home/db2dvd 3. dkTB db2setup |n: ./db2setup -r /responsefile_directory/response_file_name Z>>}P,Q+l&D~ AddPartitionResponse.file #f= /db2home ?D~PD{"# a{ XkGXFczrxgPDm;FczOD DB2 }] bD5,G4Xk20 DB2 E"PD#DB2 E"PD|, DB2 }]b53M DB2 } ]b`Xz7DD5# Z 7 B 20 DB2 ~qwz7 105 106 VxM/:8O Z 8 B 20.s i$20 i$Vx}]b7320 (Windows) *i$GqI&20K DB2 }]b~qw,4(y>}]b"KP SQL |n4lw y>}]"i$}]GqQV"AyPNk}]bVx~qw# *<.0 QjIyP20=h# }L *4( SAMPLE }]b: 1. w*_P SYSADM (^DC'G{1,C}LjI# 4( SAMPLE }]b1,C}]bT/T}]bp{ SAMPLE xP`?# 3. dk db2start |n4t/}]b\mw# 4. Z DB2 |n0ZPdkBP DB2 |n4,SA SAMPLE }]b,"lwZ?E 20 $wDyP01DPm: db2 connect to sample db2 "select * from staff where dept = 20" 5. *i$GqQ+}]V"A}]bVx~qw,Z DB2 |n0ZPdkBP|n: db2 "select distinct dbpartitionnum(empno) from employee" dv+P> employee m9CD}]bVx#X(dv+!vZ}]bPD}]bV x}T04( employee mDmUdy9CD}]bVxiPD}]bVx}# B;=v24 Zi$20s,I}% SAMPLE }]bTMEELUd#+G,g{rc9Cy>&C Lr,G4#ty>}]b\PC# dk db2 drop database sample |nT>} SAMPLE }]b# i$Vx}]b~qw20(Linux M UNIX) 9C db2val $_4i$20D~"5}"}]b4(iv"kC}]bD,ST0V x}]b73D4,,Ti$ DB2 1>DKD&\# © Copyright IBM Corp. 2012 107 PXj8E",kND“i$ DB2 1>”#v1AYP 2 vZc1,Eai$Vx}] b73D4,#Kb,*Ki$GqQI&20 DB2 }]b~qw,z+4(y>} ]b"KP SQL |n4lwy>}]T0i$K}]GqQV"AyPNk}]bV x~qw# *<.0 Z4Pb)=h.0,7#QjIyP20=h# }L *4( SAMPLE }]b: 1. w*5P5}DC'G},db2inst1 G5P5} DC'# 2. dk db2sampl |n44( SAMPLE }]b# 1!ivB,+Z5}yP_Dw? }]b#ZK>}P,/db2home/db2inst1/ G5}yP_Dw?<# 5}yP_Dw?{1,C}LjI# 4( SAMPLE }]b1,C}]bT/T}]bp{ SAMPLE xP`?# 3. dk db2start |n4t/}]b\mw# 4. Z DB2 |n0ZPdkBP DB2 |n4,SA SAMPLE }]b,"lwZ?E 20 $wDyP01DPm: db2 connect to sample db2 "select * from staff where dept = 20" 5. *i$GqQ+}]V"A}]bVx~qw,Z DB2 |n0ZPdkBP|n: db2 "select distinct dbpartitionnum(empno) from employee" dv+P> employee m9CD}]bVx#X(dv+!vZ: v }]bPD}]bVx}? v 4( employee mDmUdy9CD}]bVxiPD}]bVx} B;=v24 Zi$20s,I}% SAMPLE }]bTMEELUd#dk db2 drop database sample |nT>} SAMPLE }]b# 108 VxM/:8O Z 3 ?V 5)M,$ © Copyright IBM Corp. 2012 109 110 VxM/:8O Z 9 B 4(}]b.0 hCVx}]b73 XkZ4(}]b.0v(4(`Vx}]b#Zwv}]bhFv(1,Xk7( Gq&{C}]bVxITa)DT\a_# XZKNq ZVx}]b73P,T;9C CREATE DATABASE |nr sqlecrea() /}44(}]b# ^[9CDV=(,DNNVx4"vks# db2nodes.cfg D~G}]bVx~qwdCD~# }KZ Windows Yw5373O.b,IT9CNN`-w4i4M|B}]bVx ~qwdCD~ (db2nodes.cfg) DZ]#Z Windows Yw5373O,k9C db2ncrt M db2nchg |n44(M|D}]bVx~qwdCD~ Z4(`Vx}]b.0,Xk!q+w*}]bD?}}]bVx~qw#I 9C db2nchg |n4^D}]bVx~qwdC,|(+}]bVx~qwS;(Fc zFAm;(Fcz;|D TCP/IP wz{;r!qm;_-KZrxg{# ":;&CZ;G}]b\mw4(D sqllib S?} 5}1*'}]#+P=v}biv#g{53'Vf"}L,G4+Cf"}L& CLrEk sqllib S?};v}]bVx,G4|D}]bVxEITYNCZmSDNNB }]bVx# }]bVxECZZ}]b?PD DB2NODE 73d?r sqlesetc() API 42GKP*# netname KN}GI!D,"RCZ'VP`vn/ TCP/IP SZDwz,?vSZPd T:Dwz{# TB>}T>K;v53DI\ZcdCD~,ZC53O,SP2EN1 P`v TCP/IP S ZM=v_-Vx,"R9C SP2SW1 w* DB2 }]bSZ#K>}9T>KS 1 * <(x;GS 0 *<)D}]bVxET0 dbpartitionnum rPPDd6: m 12. }]bVxE>}m# dbpartitionnum hostname logical-port netname 1 SP2EN1.mach1.xxx.com 0 SP2SW1 2 SP2EN1.mach1.xxx.com 1 SP2SW1 4 SP2EN2.mach1.xxx.com 0 5 SP2EN3.mach1.xxx.com IT9C!qD`-w|B db2nodes.cfg D~#(}biv:;&Z Windows O9 C`-w)#+G,Xk!D#$KD~PDE"Dj{T,bGr*}]bVx& \*sz"v START DBM 1+ZcdCD~x(,xZ"v STOP DBM ax}]b\ mw.s+dbx#+KD~x(.s,START DBM |nMITZX*1TdxP|B# }g,zIT"v START DBM "8( RESTART !nr ADD DBPARTITIONNUM !n# ":g{ STOP DBM |n4I&"R4+ZcdCD~bx,k"v STOP DBM FORCE +dbx# DB2 ZcdCD~Dq= db2nodes.cfg D~C4(eNk DB2 5}D}]bVx~qw#g{k*+_Y%, CZ}]bVx~qw(E,G49IT9C db2nodes.cfg D~48(_Y%,D IP X7rwz{# Linux M UNIX Yw53OD db2nodes.cfg D~Dq=gB: dbpartitionnum hostname logicalport netname resourcesetname Z 9 B 4(}]b.0 113 ZB;ZP(eK dbpartitionnum"hostname"logicalport"netname M resourcesetname# Windows Yw53OD db2nodes.cfg D~Dq=gB: dbpartitionnum hostname computername logicalport netname resourcesetname Z Windows Yw53O,db2ncrt r START DBM ADD DBPARTITIONNUM |n a+b)u?mSA db2nodes.cfg#9IT(} db2nchg |n4^Db)u?#z; &C1SmSb)Pr`-KD~# dbpartitionnum bG;v(;Ek,d6'G 0 = 999,C4j6Vx}]b53PD}]b Vx~qw# *)dVx}]b53,T?v}]bVx~qw,r db2nodes.cfg D~mS ;vu?#*d{}]bVx~qw!qD dbpartitionnum 5Xk4}rEr, +GKrPPD5.dITfZdt#g{F.mS_-Vx~qw,"R# {9ZcZKD~P#V4_-Vi,G4IT!q9 dbpartitionnum 5.d #V;(dt# Ku?GXhD# hostname ) FCM 9CD}]bVx~qwD TCP/IP wz{#Ku?GXhD#?R (i9Cf6wz{# g{ db2nodes.cfg D~a)DGwz{x;G IP X7,G4}]b\mw+ T/,=="Tbvwz{#bvI\G>Xbvr(}ZQ"ar{~qw (DNS)Oi/4xPbv,bIzwODYw53hC7(# S DB2 f> 9.1 *<'V TCP/IPv4 M TCP/IPv6 -i#CZbvwz{D =(Q|D# g{Z db2nodes.cfg D~P(eKL{F,G4f> 9.1 .0D"Pf+4 db2nodes.cfg D~PD(ebvV{.,xf> 9.1 r|_f>+"Tbvj O8(X(}]bVx~qw# 114 VxM/:8O Z201,DB2 +Z /etc/services D~P#t;(6'ZDKZ(}g, 60000 = 60003 .d)CZVxd(E#db2nodes.cfg PD logicalport VN 8(z*+C6'PDDvKZVdxX(D_-Vx~qw# g{KVNP^NNu?,G41!5* 0#+G,g{T netname VNmS ;vu?,G4XkT logicalport VNdk;v}V# g{}Z9C_-}]bVx,G48(D logicalport 5XkS 0 *<,"4 }r@N]v(}g,0"1"2)# Kb,g{*;v}]bVx~qw8( logicalport u?,G4Xk* db2nodes.cfg D~PP>D?v}]bVx~qw8( logicalport# v149C_-}]bVxr_Y%,1,KVNEGI!D# netname 8(CZ FCM (ED_Y%,Dwz{r IP X7# g{*KVN8(K;vu?,G4}]bVx~qw.dDyP(E(}K IZ db2start"db2stop M db2_all |nxxPD(E.b)}dC 9CBfD>}dC47(73D`&dC# ;(Fcz,Dv}]bVx~qw g{49C/:73,Rk*Z;({* ServerA Dom$w>O_PDv} ]bVx~qw,G4&T db2nodes.cfg D~wgB|B: 0 ServerA 0 1 ServerA 1 2 ServerA 2 3 ServerA 3 Z 9 B 4(}]b.0 115 =(Fcz,?(Fcz;v}]bVx~qw g{k*CVx}]b53|,=vom$w>:ServerA M ServerB,G4& T db2nodes.cfg D~wgB|B: 0 ServerA 0 1 ServerB 0 =(Fcz,;(FczOP}v}]bVx~qw g{k*Vx}]b53|,=vom$w>:ServerA M ServerB,"R ServerA *KP 3 v}]bVx~qw,G4&T db2nodes.cfg D~wgB |B: 4 ServerA 0 6 ServerA 1 8 ServerA 2 9 ServerB 0 =(Fcz,xP_Y;;zD}v}]bVx~qw g{k*Vx}]b53|,=(Fcz:ServerA M ServerB(RServerB K P=v}]bVx~qw),"R9C{* switch1 M switch2 D_Y%,, G4&T db2nodes.cfg D~wgB|B: 0 ServerA 0 switch1 1 ServerB 0 switch2 2 ServerB 1 switch2 9C resourcesetname D>} b)^FJCZTB>}: v >>}5wZdCP;P_Y%,1 resourcesetname D9C# v netname GZDP,ZCP;P;;z{xz4k*9C resourcesetname DivB, z9ITZCPO8( hostname#ZevN}G resourcesetname(g{Q(e)#K J4if6;IT>* db2nodes.cfg D~PDZeP#bb6E,*8(J4i, 9XkdkZDP#ZDPrcCZ_Y;;z# v g{;P_Y;;zr_;k9C|,G4Xkdk hostname(kZ~P`,)#; d05,DB2 }]b\m53;'V db2nodes.cfg D~PfZPdt(rP; ;)#K^FT0JCZ0}P,VZ|JCZyPeP# AIX >} K&GgN* AIX Yw53hCJ4/D>}# ZK>}P,P;v_P 32 v&mwM 8 v_-}]bVx(MLN)DomZc# K>}5wgN*?v MLN a)xL`FT# 1. Z /etc/rset P(eJ4/: DB2/MLN1: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00000,sys/cpu.00001,sys/cpu.00002,sys/cpu.00003 DB2/MLN2: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00004,sys/cpu.00005,sys/cpu.00006,sys/cpu.00007 116 VxM/:8O DB2/MLN3: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00008,sys/cpu.00009,sys/cpu.00010,sys/cpu.00011 DB2/MLN4: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00012,sys/cpu.00013,sys/cpu.00014,sys/cpu.00015 DB2/MLN5: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00016,sys/cpu.00017,sys/cpu.00018,sys/cpu.00019 DB2/MLN6: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00020,sys/cpu.00021,sys/cpu.00022,sys/cpu.00023 DB2/MLN7: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00024,sys/cpu.00025,sys/cpu.00026,sys/cpu.00027 DB2/MLN8: owner = db2inst1 group = system perm = rwr-r- resources = sys/cpu.00028,sys/cpu.00029,sys/cpu.00030,sys/cpu.00031 2. (}dkTB|n4tCZf`FT: vmo -p -o memory_affinity=1 3. Jm5}9CJ4/: chuser capabilities= CAP_BYPASS_RAC_VMM,CAP_PROPAGATE,CAP_NUMA_ATTACH db2inst1 4. +J4/{Fw*ZePmS= db2nodes.cfg: 1 regatta 0 regatta DB2/MLN1 2 regatta 1 regatta DB2/MLN2 3 regatta 2 regatta DB2/MLN3 4 regatta 3 regatta DB2/MLN4 5 regatta 4 regatta DB2/MLN5 6 regatta 5 regatta DB2/MLN6 7 regatta 6 regatta DB2/MLN7 8 regatta 7 regatta DB2/MLN8 HP-UX >} K>}5wgN9C PRM iZ9C 4 v CPU M 4 v MLN DzwOVm CPU J 4,*?v MLN hC 24% D CPU ]n,*d{&CLrtB 4%#DB2 5}{* db2inst1# 1. `- /etc/prmconf D GROUP N: Z 9 B 4(}]b.0 117 OTHERS:1:4:: db2prm1:50:24:: db2prm2:51:24:: db2prm3:52:24:: db2prm4:53:24:: 2. r /etc/prmconf mS5}yP_u?: db2inst1::::OTHERS,db2prm1,db2prm2,db2prm3,db2prm4 3. (}dkTB|n4u} Z Linux Yw53O,resourcesetname P(ek53O“G;BTZfCJ”(NUMA) Zc`T&D;v}V#w*T'V NUMA _TD 2.6 ZKD9d,9Xk_P numactl 535CLr#PX Linux Yw53OD NUMA 'VD|`E",kND numactl D*zoz3# >>}5wgNhC;v_PDv NUMA ZcDFcz,"9?v_-ZckTBZ]`FDdv: available: 4 nodes (0-3) node 0 size: 1901 MB node 0 free: 1457 MB node 1 size: 1910 MB node 1 free: 1841 MB node 2 size: 1910 MB node 2 free: 1851 MB node 3 size: 1905 MB node 3 free: 1796 MB 3. ZK>}P,53OPDv NUMA Zc#4gBy>`- db2nodes.cfg D~,T 9?v MLN } K&GgN* Solaris V9 hCn?D>}# ZK>}P,P;vxP 8 v&mwDomZc:dP 1 v CPU +CZ1!n?, 3 v CPU I&CLr~qw9C,4 v CPU CZ DB2#5}{* db2inst1# 118 VxM/:8O 1. 9C`-w4(J4XdCD~#TZK>},CD~+;F* pool.db2#dZ] gB: create system hostname create pset pset_default (uint pset.min = 1) create pset db0_pset (uint pset.min = 1; uint pset.max = 1) create pset db1_pset (uint pset.min = 1; uint pset.max = 1) create pset db2_pset (uint pset.min = 1; uint pset.max = 1) create pset db3_pset (uint pset.min = 1; uint pset.max = 1) create pset appsrv_pset (uint pset.min = 3; uint pset.max = 3) create pool pool_default (string pool.scheduler="TS"; boolean pool.default = true) create pool db0_pool (string pool.scheduler="TS") create pool db1_pool (string pool.scheduler="TS") create pool db2_pool (string pool.scheduler="TS") create pool db3_pool (string pool.scheduler="TS") create pool appsrv_pool (string pool.scheduler="TS") associate pool pool_default (pset pset_default) associate pool db0_pool (pset db0_pset) associate pool db1_pool (pset db1_pset) associate pool db2_pool (pset db2_pset) associate pool db3_pool (pset db3_pset) associate pool appsrv_pool (pset appsrv_pset) 2. `- /etc/project D~TmS DB2 n?M appsrv n?,gBy>: system:0:::: user.root:1:::: noproject:2:::: default:3:::: group.staff:10:::: appsrv:4000:App Serv project:root::project.pool=appsrv_pool db2proj0:5000:DB2 Node 0 project:db2inst1,root::project.pool=db0_pool db2proj1:5001:DB2 Node 1 project:db2inst1,root::project.pool=db1_pool db2proj2:5002:DB2 Node 2 project:db2inst1,root::project.pool=db2_pool db2proj3:5003:DB2 Node 3 project:db2inst1,root::project.pool=db3_pool 3. 4(J4X:# poolcfg -f pool.db2# 4. $nJ4X:# pooladm -c 5. +n?{w*ZePmS= db2nodes.cfg D~: 0 hostname 0 hostname db2proj0 1 hostname 1 hostname db2proj1 2 hostname 2 hostname db2proj2 3 hostname 3 hostname db2proj3 8(Vx}]b73PDzwPm 1!ivB,S}]bVxdCD~ db2nodes.cfg Pq!FczPm# XZKNq ":Z Windows O,*\b+;;B}kC}]bVxdCD~,;*TV/==T dxP`-#*q!5}PDFczPm,I9C db2nlist |n# }L *2G db2nodes.cfg PDFczPm,k4PBPYw: v (}D?v_-}]bVxy4P;N#9C rah |n48(F cz#9C db2_all |n48(_-}]bVx# ":Z Linux M UNIX Yw53O,g{8(Fcz,G4 rah +a}#XSFcz PmP>}X4Dn,}bivG:g{8(_-}]bVx,G4 db2_all a+BP 35=S=zD|n.0: export DB2NODE=nnn(TZ Korn shell Lro() dP nnn G db2nodes.cfg D~D`&PPD}]bVxE,Tc+|n7IAy#{ D}]bVx~qw# 18(_-}]bVx1,IT9C <<-nnn< M <<+nnn< 0:rP4^FPm|(} 3v_-}]bVxbDyP_-}]bVx,r;8(;v_-}]bVx#g{ k*WHKPT}]bVxxP`?D|n,"ZC|njI1,ZyPd{}]b Vx~qwOKP,;|n(I\T"P==),G4I\*4PKYw#1KP RESTART DATABASE |n1,|##GXhD#h**@?}# +G,g{8( ” 0:,G4;>}X4n,r*O*9C ” 0:a~,"MA?v }]bVx~qw,x;G"MA?(Fcz# |BZcdCD~(Linux M UNIX) KNqa)K;)=h,T+ db2nodes.cfg D~|B*|(NkFczDu?# *<.0 v XkZyPNkDFczO20 DB2 }]bz7# v DB2 5}XkfZZwFczO# v zXkG_P SYSADM (^DC'# v g{vVTBN;iv,ki4 DB2 ZcdCD~Dq=wbPa)DdC>}M D~q=E": – zF.9C_Y;;zZ}]bVx~qw.dxP(E – zDVxdC+_P`v_-Vx XZKNq ZcdCD~ (db2nodes.cfg) ;Z5}yP_Dw?: 0 ServerA 0 ^F Z“}L”?VD=hP9CDwz{XkGj: 0 ServerA 0 1 ServerB 0 2 ServerC 0 3 ServerD 0 5. |Bj db2nodes.cfg D~1,dk INSTHOME/sqllib/adm/db2start |n,dP INSTHOME G5}yP_Dw?<# }g,g{5}w?<* /db2home/ db2inst1,G4dkTB|n: /db2home/db2inst1/sqllib/adm/db2start 6. "z# Z 9 B 4(}]b.0 121 hC`_-Vx Z8VivB,Z,;(FczOKP}v}]bVx~qw\PC&# bb6EdC|,D}]bVx}IT`ZFcz}#Zb)ivB,g{b)_- VxNk,;5},RGcFCFcz}ZKP`_-Vx#g{|GNk;,D5 },G4KFcz;w\`_-Vx# hz`_-Vx'V,zITS}V`MDdCPxP!q: v j539CE/w7( D"PH;qr,9CC'8(D5# dft_degree a) DEGREE s(!nM CURRENT DEGREE (CDfwD1!5D}]b dCN}#1!5* 1#5 ANY m>539CE/w7(D"PH# DEGREE 2, SQL D$`krs(!n# CURRENT DEGREE /, SQL D(CDfw# }]~qw]?D\m g{}]~qwD]?;\zc?0r+4D*s,G4I(}vsELUdM4( d{]wr(}vSZf4)dd]?#g{b)r%k);\vSyhD]?,9 IT?Vsfy>#FCM +9C 8(DKZ4(E#g{QZ,;wzO(eK`v}]bVx,G4Xk(e;v KZ6',g>?Vsfy># Z"T*lY(E\mw(FCM)V/dCZf.0, (iS FCM :ex} (fcm_num_buffers) M FCM E@} (fcm_num_channels)DT/hC*<(1! hC)#9C FCM n/D53`Sw}]4P(KhCGqOJ# Windows "bBn TCP/IP KZ6'+ITBLrT/mS=~qD~: v 20Lr,Z4(5}rmSBD}]bVx1 v db2icrt 5CLr,Z4(B5}1 v db2ncrt 5CLr,ZFczOmSZ;v}]bVx1 ~qu?Do(gBy>: DB2_instance port/tcp #comment DB2_instance instance D5G}]b\mw5}D{F#C{FDyPV{Xk*!4#Y( 5}{* DB2PUSER,G4&8( DB2_db2puser# port/tcp **C}]bVx#tD TCP/IP KZ# #comment k*kCu?X*DNN"M#"M.0XkS # {E# g{ etc ?`,,+|P;v=S "bBn:Xk7#* DB2 }]b5}(eDu?Z etc ?}ZVdDKZ6'# Z;P8(Z;vKZ,xZ~P8>KZiD)2#ZBP>}P,*5} SALES V dKevKZ#bb6EC5}P;aP&mw_P`ZevD}]bVx#}g: DB2_sales 9000/tcp DB2_sales_END 9004/tcp ":;\Cs4V848( END#9Xk7#|(K=vB._ (_) V{# tC}]bVx~qw.dD(E(Linux M UNIX) KNqhvKgNtCNkVx}]b53D}]bVx~qw.dD(E#}]b Vx~qw.dD(EIlY(E\mw (FCM) &m#*tC FCM,XkZVx}] b53PD?(FczOD /etc/services D~P#t;vKZrKZ6'# *<.0 zXkPx root C'(^DC'j6# zXkZNk5}DyPFczO4PKNq# XZKNq * FCM #tDKZ}?HZ5}PINNFczw\rI\w\D}]bVxDns }?# ZTB>}P,db2nodes.cfg D~|,TBu?: 0 server1 0 1 server1 1 2 server2 0 3 server2 1 4 server2 2 5 server3 0 6 server3 1 7 server3 2 8 server3 3 Yh FCM KZS 60000 *<`E#ZKivB: v server1 T|D=v}]bVx9C=vKZ(60000 M 60001) v server2 T|D}v}]bVx9C}vKZ(60000"60001 M 60002) v server3 T|DDv}]bVx9CDvKZ(60000"60001"60002 M 60003) yPFczyXk#t 60000"60001"60002 M 60003,r*bG5}PDNNFcz yhDnsKZ6'# g{9Cng Tivoli System Automation r IBM PowerHA® SystemMirror for AIX . `D_ICTbv=84+}]bVxS;(FczJO*F=m;(Fcz,G4 XkdCb,FCM KZ &`FgBy>: db2c_db2inst1 50000/tcp #Add FCM port information DB2_db2inst1 60000/tcp DB2_db2inst1_1 60001/tcp DB2_db2inst1_2 60002/tcp DB2_db2inst1_END 60003/tcp 1!ivB,Z;vKZ(50000)#tx,Sks9C,x 60000 TOD0DvI CKZ#tx FCM (E9C#;vKZCZ5P5}D}]bVx~qw,}vK ZCZ_-}]bVx~qw,ITZjI20s!q+dmSAFcz# KZ6'Xk|(p}VxrzYVx# *<.0 *9C}]bVx,h*,SA5}D(^#_P SECADM r ACCESSCTRL (^D NNK}X(}]bVx,k9CxP DROP DBPARTITIONNUM N}D STOP DATABASE MANAGER |n# Z9C DROP DBPARTITIONNUM N}.0,kKP DROP DBPARTITIONNUM VERIFY |nT7#K}]bVxO;PC '}]# v *zY}]bVxODn/,k9C IBM 'Vz98(D!n# "b:v1 IBM 'Vz9r;P4(#Xk9C ALTER TABLESPACE od4+Y1mUd]wmSA? v}]b.s,E\9C}]b# © Copyright IBM Corp. 2012 129 ":1mSB}]bVx1,;a6pNN4`?D}]b#ZB}]bVxO+; fZ4`?D}]b#"TkB}]bVxOD}]bxP,Sa5Xms{" SQL1013N# ZDd;vr`v}]bVxiT|(B}]bVx.0,;\9CB}]bVxO D}]b4|,}]# ;\(}T53mS}]bVx4+%Vx}]b|D*`Vx}]b#bGr*g }]bVxXBV"}]h*?v\0lDmODV<|#V<|GZ`Vx}]b P4(m1T/zID#Z%Vx}]bP,I9C CREATE TABLE r ALTER TABLE SQL odT=X4(V<|# ":g{53P;P(eNN}]bRz}Z UNIX Yw53OKPs5~qwf,G 4`- db2nodes.cfg D~TmSBD}]bVx(e;;*9CyvDNN;v}L, r*|G;PZ}]bfZ1EJC# Windows "bBn:g{Z Windows Yw53O9Cs5~qwf,"R5}P; PNN}]b,G4k9C db2ncrt |n4w{}]b53#+G,g{QP}]b, G4k9C START DBM ADD DBPARTITIONNUM |n,T7#Zw{531*?vVP} ]b<4(;v}]bVx#Z Windows Yw53O,kpV/`-}]bVxdC D~ (db2nodes.cfg),r*bIT9D~zz;;B# mS*z}]bVx IZVx}]b73KPT0&CLr,SA}]b1+*zDB}]bVxmSA KVx}]b73# }L *9C|nP+*z}]bVxmSA}ZKPD}]b\mw,k4PTBYw: 1. ZNNVP}]bVxO,KP START DBM |n# ZyP=(O,* DBPARTITIONNUM"ADD DBPARTITIONNUM"HOSTNAME"PORT M NETNAME N}8(B}]bVx5#Z Windows =(O,9&C8( COMPUTER" USER M PASSWORD N}# z9I*XkZC}]bP4(DNN“Y1mUd]w(e”8(4#g{4a) mUdE",G4S?v}]bD?X4(;v}]bVx#+B}]bVxD}]b N}hC*1!5,"RZ+}]F/AdP.0,?v}]bVx#V*U#| B}]bdCN}5,Tckd{}]bVxOD5`%d# 3. KP START DBM |nTt/}]b53# "b,Z20B~qwZd,}]bVx dCD~QI}]b\mw|B*|(B~qw# 4. 4gBy>|BB}]bVxODdCD~: a. ZNNVP}]bVxO,KP START DBM |n# Z 10 B 4(M\mVx}]b73 131 * DBPARTITIONNUM"ADD DBPARTITIONNUM"HOSTNAME"PORT M NETNAME N}T 0 COMPUTER"USER M PASSWORD N}8(B}]bVx5# z9I*h*ZC}]bP4(DNN“Y1mUd]w(e”8(4#g{4 a)mUdE",G4S?v}]bD?}} ]b# ":I\XkTyP}]bVx~qw"v START DBM |n=N,E\CJBD db2nodes.cfg D~# 6. I!: Dd}]bVxiTO"B}]bVx# +}]XBV"AB}]bVx1 2I!qKYw# 7. I!: +}]XBV"AB}]bVx# g{*{CB}]bVx,G4;\f} X!qKYw#9I+Dd}]bVxi!nw*XBV"YwD;?V4|(# qr,Z+}]XBV"AB}]bVx.0,Xk+Dd}]bVxiTO"B }]bVxw*%@DYw4jI# 8. I!: ZB}]bVxO8]yP}]b# d;I!,+TZB}]bVxT0d {}]bVx,XpGZBI}]bVx.dX1>49I4PD~I)CJ# 132 VxM/:8O v 9Yw53D~kVP&mwODYw53D~,=# v 7# sqllib ?X4(;v}]bVx#+B}]bVxD}] bN}hC*1!5,"RZ+}]F/AdP.0,?v}]bVx#V* U#|B}]bdCN}5,Tckd{}]bVxOD5`%d# 3. KP START DBM |nTt/}]b53# "b,B~qw20Zd,}]bVx dCD~ (db2nodes.cfg) QI}]b\mw|B*|(B~qw# 4. 4gBy>|BB}]bVxODdCD~: a. ZNNVP}]bVxO,KP START DBM |n# * DBPARTITIONNUM"ADD DBPARTITIONNUM"HOSTNAME"PORT M NETNAME N} T0 COMPUTER"USER M PASSWORD N}8(B}]bVx5# z9I*XkZC}]bP4(DNN“Y1mUd]w(e”8(4#g{ 4a)mUdE",G4S?v}]bD?} }]b# ":I\XkTyP}]bVx~qw"v START DBM |n=N,E\CJBD db2nodes.cfg D~# 6. I!: Dd}]bVxiTO"B}]bVx# +}]XBV"AB}]bVx 12I!qKYw# 7. I!: +}]XBV"AB}]bVx# g{*{CB}]bVx,G4;\f }X!qKYw#9I+Dd}]bVxi!nw*XBV"YwD;?V4| (#qr,Z+}]XBV"AB}]bVx.0,Xk+Dd}]bVxiT O"B}]bVxw*%@DYw4jI# 8. I!: ZB}]bVxO8]yP}]b# d;I!,+TZB}]bVxT0 d{}]bVx,XpGZBI}]bVx.d: 1. `- db2nodes.cfg D~,"+B}]bVxmSACD~# 2. "vTB|n4t/B}]bVx:START DBM DBPARTITIONNUM partitionnum +zVdxB}]bVxD`E8(* partitionnum D5# 3. g{*9B~qwI*_-Vx(4,|;G}]bVx 0),k9C db2set | n4|B DBPARTITIONNUM "amd?# 8(*mSD}]bVxDEk# 4. ZB}]bVxOKP ADD DBPARTITIONNUM |n# K|n9*QfZZ53PD?v}]bZ>X4(;v}]bVx#+B}] bVxD}]bN}hC*1!5,"RZ+}]F/AdP.0,?v}]b Vx#V*U#|B}]bdCN}5,Tckd{}]bVxOD5`%d# 5. 1 ADD DBPARTITIONNUM |njI1,k"v START DBM |n4t/53PDd {}]bVx# ZI&t/yP}]bVx.0,;*4PNN536'ZDn/,g4(r> }}]b# mS}]bVx1DmsV4 r*}]b\mwa4(53:eXTTyP:eX3s!a)1!T/'V,yT mS}]bVx;ar;fZD:eXx'\# +G,r*b)53:eXG#!,yTg{9CdP;v53:eX,G4I\a OX0lT\#g{9C53:eX,\m(*G}: 1. 9C START DBM |n4+}]bVxmS=10D`Vx}]b: START DBM DBPARTITIONNUM 2 ADD DBPARTITIONNUM HOSTNAME newhost PORT 2 134 VxM/:8O 2. Z9CBD}]bVxhvV/|B db2nodes.cfg D~.s,9C ADD DBPARTITIONNUM |n# @9b)Jb"zD;V=(GZ ADD DBPARTITIONNUM r START DBM |nO8( WITHOUT TABLESPACES Sd#Ks,k9C CREATE BUFFERPOOL od44 (9CJ1 SIZE M PAGESIZE 5D:eX,"9C ALTER TABLESPACE od +53Y1mUdk:eX`X*# v +}]bVxmS=VPD}]bVxi,C}]bVxi_k1!3s! 4KB ; ,D3s!D;vr`vmUd#"zKivD-rGP4TmUd$nZB}] bVxO4(DG1!3s!:eX# ":ZH0f>P,K|n9CK NODEGROUP X|V,x;G DATABASE PAR- TITION GROUP X|V# k}: – IT9C ALTER DATABASE PARTITION GROUP od4r}]bVximS }]bVx,gBy>: START DBM CONNECT TO mpp1 ALTER DATABASE PARTITION GROUP ng1 ADD DBPARTITIONNUM (2) @9KJb"zD;V=(GZ"v ALTER DATABASE PARTITION GROUP o d.0,*?v3s!4(:eX,;sXB,SA}]b: START DBM CONNECT TO mpp1 CREATE BUFFERPOOL bp1 SIZE 1000 PAGESIZE 8192 CONNECT RESET CONNECT TO mpp1 ALTER DATABASE PARTITION GROUP ng1 ADD DBPARTITIONNUM (2) ":g{}]bVxi_P3s!*1!5DmUd,G45X{" SQL1759W# >}}]bVx zIT>}NN}]b<4Z9CD}]bVx,"*d{DC'UvFcz# *<.0 (}"v DROP DBPARTITIONNUM VERIFY |nr sqledrpn API,i$Gq49CK}] bVx# v g{SU={" SQL6034W(4ZNN}]bP9C}]bVx),G4IT>}C }]bVx# v g{SU={" SQL6035W(}]b}Z9C}]bVx),G49C REDISTRIB- UTE DATABASE PARTITION GROUP |n,T+*>}D}]bVxPD}]XBV"= }]bp{;,Dd{}]bVx# 9&7#yPBq(K}]bVxGd-wLr)QI&d5rXv#bI\h*Z d{~qwO4P@#V4#}g,g{z>}-wLrVx,xNkBqDm;v }]bVxZ>}-wLrVx.0@#,G4C@#D}]bVx+^(i/-w LrVx4q!NN;7(BqDdv# Z 10 B 4(M\mVx}]b73 135 }L *9C|nP4>}}]bVx: "vx DROP DBPARTITIONNUM N}D STOP DBM |n4>}C}]bVx# ZI&jI|ns,#953#;s,9C START DBM |n4t/}]b\mw# Z5}PP>}]bVx~qw (Windows) Z Windows O,9C db2nlist |n4q!Nk5}D}]bVx~qwDPm# XZKNq 4gBy>9CC|n: db2nlist 14TOy>9CK|n1,1!5}G105}(I DB2INSTANCE 73d?hC)# *8(X(D5},I9CTB|n8(C5}: db2nlist /i:instName dP instName Gk*DX(5}{# (I!)2I9CTB|nks?v}]bVx~qwD4,: db2nlist /s ?v}]bVx~qwD4,I\G:}Zt/"}ZKP"}Z#9rQ#9# +}]bVx~qwmSA5}(Windows) Z Windows O,9C db2ncrt |n4+}]bVx~qwmSA5}# XZKNq ":g{K5}Q|,}]b,G4;*9C db2ncrt |n#kD*9C START DBM ADD DBPARTITIONNUM |n#b7#I}7X+C}]bmSABD}]bVx~qw# ;*`- db2nodes.cfg D~,r*|DD~I\Xwz{)#g{ Z6LFczOmS}]bVx~qw,G4KN}GXhD# /i:instance_name 5}{;1!5*105}# /m:computer_name C}]bVxyZD Windows $w>DFcz{F;1!{FG>XFczD Fcz{F# /o:instance_owning_computer w*5P5}DFczDFcz{F;1!5G>XFczDFcz{F#1 ZNNG5P5}DFczOwC db2ncrt |n1,KN}GXhD# }g,g{*r5P5}DFcz MYMACHIN OD5} TESTMPP mSBD}]b Vx~qw(TcKP`_-}]bVx),Rzk*CbvB}]bVxI*9C _-KZ 1 D}]bVx 2,G4dk: db2ncrt /n:2 /p:1 /u:my_id,my_pword /i:TESTMPP /M:TEST /o:MYMACHIN |D}]bVx(Windows) Z Windows O,I9C db2nchg |n4|D}]bVx# XZKNq v +}]bVxS;(FczFAm;(Fcz# v |DFczD TCP/IP wz{# g{rc9C`vxgJdw,G4Xk9CK|n* db2nodes.cfg D~PD “netname”VN8( TCP/IP X7# v 9Cm;_-KZE# v T}]bVx~qw9Cm;{F# C|n_PTBXhN}: Z 10 B 4(M\mVx}]b73 137 db2nchg /n:node_number N} /n: G*|DD}]bVx~qwD`E#KN}GXhD# I!N}|(: /i:instance_name 8(K}]bVx~qwyNkD5}#g{48(KN},G41!5G105 }# /u:username,password |D DB2 }]b~qDG*: db2set DB2REMOTEPREG=remote_workstation dP remote_workstation G6L$w>{F# ": v Xk!DhCK!n,r*yP DB2 }]b5}E*D~M5}Pm<+;Z8( D6LFcz{FO# v g{zD73|(rPDC',G47#k DB2 5}~q`X*DG} TB>}T>Z UNIX Yw53O,gN+TmUd“plans”y9CD}]bVxiD 3 E}]bVxmSB]w: ALTER TABLESPACE plans ADD (’/dev/rhdisk0’) ON DBPARTITIONNUM (3) S5}P>}}]bVx(Windows) Z Windows O,9C db2ndrop |nS;P}]bD5}P>}}]bVx~qw# g{>};v}]bVx~qw,G4|D}]bVxEITYNCZBD}]bV x~qw# XZKNq 1S5}P>}}]bVx~qw1qX!D#g{SC5}P>}5P5}D}] bVx~qw 0,G4C5}+dC;IC#g{*>}C5},k9C db2idrop | n# ":g{K5}|,}]b,G4;*9C db2ndrop |n#kD*9C STOP DBM DROP DBPARTITIONNUM |n#b7#I}7XS}]bVxP}%C}]b#;*`- db2nodes.cfg D~,r*|DD~I\}VdK_-KZ 0 D}]bVx,G 4Z>}VdK_-KZ 0 D}]bVx.0,Xk>}Vdxd{_-KZDyPd {}]bVx#?v}]bVx~qw5P5}DFcz# Z 10 B 4(M\mVx}]b73 139 /i:instance_name 5}{F(instance_name)#bG;vI!N}#t;xv,r1!5G10 5}(I DB2INSTANCE "amd?hC)# =8:ZB}]bVxPXBV"}] K=85wgN+BD}]bVxmSA}]b"Z}]bVx.dXBV"}]# REDISTRIBUTE DATABASE PARTITION GROUP |nGZT>gNZ}]bVxiPD;, m/OXBV"}]1]>D# XZKNq =8: }]b DBPG1 P=v8(* (0, 1) D}]bVxM;v}]bVxi(e (0, 1)# Z}]bVxi DBPG_1 O(eKBPmUd: v mUd TS1 - KmUdP=vm T1 M T2 v mUd TS2 - KmUd(eK}vm T3"T4 M T5 S V9.7 *<,zITZ}]bKP}LPT0+&CLr,SA}]b1mS }]bVx#;x,(}+ DB2_FORCE_OFFLINE_ADD_PARTITION "amd?D 1!5|D* TRUE,ITZKivBQz4PKYw# }L *Z DBPG1 PD}]bVx.dXBV"}]: 1. 7(ZXBV".0Xk{Cr}%DTs# a. 4F MQT:XBV"Yw;'VK`MD MQT#XkZKPXBV".0+ |G>},"Z.sXB4(# SELECT tabschema, tabname FROM syscat.tables WHERE partition_mode = ’R’ b. “4km”B~`Sw:g{NNT/$nD“4km”B~`Sw_P|,Z* XBV"D}]bVxiPDm,k{Cb)B~`Sw# SELECT distinct evmonname FROM syscat.eventtables E JOIN syscat.tables T on T.tabname = E.tabname AND T.tabschema = E.tabschema JOIN syscat.tablespaces S on S.tbspace = T.tbspace AND S.ngname = ’DBPG_1’ c. 5wm:(iZ%;Vx}]bVxiP4(5wm#;x,g{Zh*XB V"D}]bVxiP(eK5wm,"R?0*9yzID}];h*xP ,$,k}b)5wm#;)jIKXBV",MIXB(eb)5w m# d. mCJ==M4,:7#*XBV"D}]bVxiPyPm<&Zj+CJ =="R&Z}#Dm4,# SELECT DISTINCT TRIM(T.OWNER) || \’.\’ || TRIM(T.TABNAME) AS NAME, T.ACCESS_MODE, A.LOAD_STATUS FROM SYSCAT.TABLES T, SYSCAT.DBPARTITIONGROUPS N, SYSIBMADM.ADMINTABINFO A WHERE T.PMAP_ID = N.PMAP_ID AND A.TABSCHEMA = T.OWNER 140 VxM/:8O AND A.TABNAME = T.TABNAME AND N.DBPGNAME = ’DBPG_1’ AND (T.ACCESS_MODE <> ’F’ OR A.LOAD_STATUS IS NOT NULL) e. 3FE"E*D~:g{*m(eK3FE"E*D~,G4w*XBV"} LD;?V,ITTm3FE"xP|B#9C REDISTRIBUTE 5CLr4| BmD3FE"1,+uY I/O,bGr*Z4PXBV"1+(hyP}], xZ4P RUNSTATS 1,+;h*YT}]xP(h# RUNSTATS on table schema.table USE PROFILE runstats_profile SET PROFILE ONLY 2. i4}]bdC# util_heap_sz TZ}]bVx.dD}]F/&m\X| - Z XBV"DVx1dZ,* util_heap_sz !I\`XVdZf#g{ZXBV" ZdX(Kw},G4h*c;D sortheap#4h*vs util_heap_sz M sortheap,Ta_XBV"T\# 3. lw*CZB}]bVxD}]bdChC# 1mS}]bVx1,a9C1!} ]bdC#rK,PGZ"v REDISTRIBUTE DATABASE PARTITION GROUP |n.0 TB}]bVxD}]bdCxP|B#B~DK3r+7#dC&Z=b4 ,# SELECT name, CASE WHEN deferred_value_flags = ’AUTOMATIC’ THEN deferred_value_flags ELSE substr(deferred_value,1,20) END AS deferred_value FROM sysibmadm.dbcfg WHERE dbpartitionnum = existing-node AND deferred_value != ’’ AND name NOT IN (’hadr_local_host’,’hadr_local_svc’,’hadr_peer_window’, ’hadr_remote_host’,’hadr_remote_inst’,’hadr_remote_svc’, ’hadr_syncmode’,’hadr_timeout’,’backup_pending’,’codepage’, ’codeset’,’collate_info’,’country’,’database_consistent’, ’database_level’,’hadr_db_role’,’log_retain_status’, ’loghead’,’logpath’,’multipage_alloc’,’numsegs’,’pagesize’, ’release’,’restore_pending’,’restrict_access’, ’rollfwd_pending’,’territory’,’user_exit_status’, ’number_compat’,’varchar2_compat’,’database_memory’) 4. Zt/XBV"}L.0T}]b(r`X}]bVxiPDmUd)xP8 ]# KYw+7#V4cGnBD# 5. +}vB}]bVxmS=}]b# "vBP|n: START DBM DBPARTITIONNUM 3 ADD DBPARTITIONNUM HOSTNAME HOSTNAME3 PORT PORT3 WITHOUT TABLESPACES; START DBM DBPARTITIONNUM 4 ADD DBPARTITIONNUM HOSTNAME HOSTNAME4 PORT PORT4 WITHOUT TABLESPACES; START DBM DBPARTITIONNUM 5 ADD DBPARTITIONNUM HOSTNAME HOSTNAME5 PORT PORT5 WITHOUT TABLESPACES; g{ DB2_FORCE_OFFLINE_ADD_PARTITION QhC* TRUE,G4ZXU"XBt/ 5}.s,B}]bVxETC5}IS#}g: STOP DBM;START DBM; 6. ZB(eD}]bVxO(e53Y1mUd]w# ALTER TABLESPACE tablespace_name ADD container_information ON dbpartitionnums (3 to 5) Z 10 B 4(M\mVx}]b73 141 7. +B}]bVxmS=}]bVxi# TB|n+ DBPG_1 (eS (0, 1) |D * (0, 1, 3, 4, 5): ALTER DATABASE PARTITION GROUP DBPG_1 ADD dbpartitionnums (3 to 5) WITHOUT TABLESPACES 8. ZB(eD}]bVxO(e@C}]mUd]w# ALTER TABLESPACE tablespace_name ADD container_information ON dbpartitionnums (3 to 5) 9. +}]bdChC&CZB}]bVx(rTyP}]bVx"v%v UPDATE DB CFG |n)# 10. 6q*XBV"D}]bVxiPfZDNN4FM MQT D(e,;s+b) MQT >}# db2look -d DBPG1 -e -z schema -t replicated_MQT_table_names -o repMQTs.clp 11. {C*XBV"D}]bVxiPfZDNN“4km”B~`Sw# SET EVENT MONITOR monitor_name STATE 0 12. KP REDISTRIBUTE 5CLrTZyP}]bVx.dyHXXBV"# REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE UNIFORM STOP AT 2006-03-10-07.00.00.000000; Y(C|nTm T1"T2 M T3 I&KP,;sIZ8(K STOP AT !nx#9# *lP9}]bVxiD}]XBV""9-Tm T1"T2 M T3 ywD|D,k "vTB|n: REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE ABORT; Z"zmsrvVPO,"Rz;#{Lx4PXBV"Yw1,zITP9} ]XBV"#TZK=8,Y(K|nKPI&"Rm T1 M T2 Q9-*|GD -<4,# *Z;P 5000 v 4K 3fw* DATA BUFFER DivBXBV" T5 M T4: REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE UNIFORM TABLE (T5, T4) ONLY DATA BUFFER 5000; g{C|nKPI&,G4QI&XXBV"m T4 M T5 PD}]# *48(3rjIm T1"T2 M T3 P}]DXBV",k"v: REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE CONTINUE TABLE (T1) FIRST; 8( TABLE (T1) FIRST 1,+?F}]b\mwWH&mm T1,TcCmIT Zd{m.05X=*z(;A)4,#yPd{m4}]b\mw7(D3r &m# ": v w*Z=h 7 M 8 P4P ALTER DATABASE PARTITION GROUP M ALTER TABLESPACE odDfz=(,ITZ REDISTRIBUTE DATABASE PARTITION 142 VxM/:8O GROUP |nP8( ADD DBPARTITIONNUM N}#9CK|nN}mS}]bVx 1,mUdD]w+yZ}]bVxiP`En!DVPVxO`&mUdD ]w# v K>}PD REDISTRIBUTE DATABASE PARTITION GROUP |n+;I0vV4# v jI REDISTRIBUTE DATABASE PARTITION GROUP |n.s,|yCJDyPm Ud<+#V BACKUP PENDING 4,#XkH8]b`mUd,|G|,D mEI)4YwCJ# PX|`E",kND“REDISTRIBUTE DATABASE PARTITION GROUP |n”# 9&C: REDISTRIBUTE DATABASE PARTITION GROUP DBPG_1 NOT ROLLFORWARD RECOVERABLE uniform TABLE (t1, t2,...) FIRST; ZVx}]b73P"v|n ZVx}]b73P,zI\k*"v+Z5}PDFczrZ`v}]bVx~q wOKPD|n#*K,zIT9C rah |nr db2_all |n#rah |nJmz"v +Z5}PDFczOKPD|n# g{k*|nZ5}PD`v}]bVx~qwOKP,G4KP db2_all |n#>B ZEvKb)|n#TBE"vJCZVx}]b73# Z Windows O,*KP rah |nr db2_all |n,zXk9C Administrators iI 1DC'J'4G<# Z Linux M UNIX Yw53O,zDG< shell LrITG Korn shell LrrNNd {D shell;+G,;, shell &m|,XbV{D|nyCD==;,# mb,Z Linux M UNIX Yw53O,rah 9C DB2RSHCMD "amd?8(D6L shell Lr#ITZ=v6L shell Lr.d!q:ssh(CZ|_D2+T*s)r rsh (TZ HP-UX,r* remsh)#g{;PhC DB2RSHCMD,G4a9C rsh(TZ HP-UX,r* remsh)#ssh 6L shell LrC4@9Z UNIX Yw5373PTwD N=+d\k# g{|nZ;v}]bVx~qwOKP,xzkCC|nZ|GyPOfKP,k 9C db2_all#db2trc |n}b,C|nZFczDyP_-}]bVx~qwOK P#g{*ZyPFczDyP_-}]bVx~qwOKP db2trc,k9C rah# ":db2_all |n;'Vh*;%=C'dkD|n# rah M db2_all |nEv IT@NZwv}]bVx~qwO43rKP|n,2ITC"PD==KP| n# Z 10 B 4(M\mVx}]b73 143 Z Linux M UNIX Yw53O,g{T"P==KPb)|n,G4IT!q+dv "MA:ex"U/CdvTcT>(1!P*),r_IZ"vC|nDFczO T>Cdv#Z Windows O,g{T"P==KPb)|n,G4Z"vC|nDF czOT>dv# *9C rah |n,kdk: rah command *9C db2_all |n,kdk: db2_all command *q!XZ rah o(Doz,kdk: rah "?" C|n8uITGZ;%=a>{BdkDNNZ],}g,*43rKPD`v| n#Z Linux M UNIX Yw53O,9CVE (;) +`v|nV*#Z Windows O, 9C & {E+`v|nVt*#;*Zns;v|n.s9CVt{# TB>}T>gN9C db2_all |n4|DZ}]bVxdCD~P8(DyP}]b VxOD}]bdC#r* ; V{;Z+}EZ,yTks+,1KP# db2_all ";DB2 UPDATE DB CFG FOR sample USING LOGFILSIZ 100" ":db2_all |n;'Vh*;%=C'dkD|n# 8( rah M db2_all |n ITZ|nPP+ rah |n8(*N},r_Z48(NNN}1,*l&a>x8( C|n# g{|n|,BPXbV{,G4k9Ca>=(: |&;<>() {}[]unsubstituted $ tZ|nPO8(|nw*N},xRt||,UEP>DNNXbV{,XkT+ }E+|n(p4# ":Z Linux M UNIX Yw53O,+C|nmSA|nz7G {&dk|;y# IT}#dk|nPDyPXbV{(} \ b,;XT+}E(p4)#g{h*Z| nP9C;v (\),G4Xkdk=v41\ (\\)# ":Z Linux M UNIX Yw53O,g{49C Korn shell Lr,G4IT}#dk |nPDyPXbV{(} ″" \ ";If;D $ M%}E (’) b,d{V{<;h *C}E}p4)#g{h*Z|nP9Cb)V{.;,G4XkZV{0S}v 41\ (\\\)#}g,g{h*Z|nP9C;v (\),G4XkdkDv41\ (\\\\\)# g{h*Z|nP9C+}E (″),G4XkZ+}E0S}v41\,}g,(\\\″)# ": 1. Z Linux M UNIX Yw53O,}G|n shell a)KZ;(O%}EDV{.P dk%}ED3V=(,qrz;\Z|nP|(%}E (’)# 144 VxM/:8O 2. Z Windows O,}G|n0Za)KZ;(O%}EDV{.Pdk%}ED3V =(,qrz;\Z|nP|(%}E (’)# 1KPNN korn-shell LrD shell E>,xCE>|,Ss(PDj: shell_script ZPDE";JCZ Linux M UNIX Yw53# KxLV=v?Vlwdv: 1. Z6L|njIs# 2. Z rshell U9s,g{3)xLTZKP,G4 rshell I\a};N1dEU9# 1!ivB,:exD~D{F* /tmp/$USER/rahout,+IT(}73d? $RAHBUFDIR r $RAHBUFNAME 8(C{F# 18(k*|n,1KP1,1!ivB,KE>+=S|nw*0:S="MAy PwzD|nO,Tli $RAHBUFDIR M $RAHBUFNAME GqICZ:exD~#ba4 ( $RAHBUFDIR#*\bK`iv,wb|(T rah M db2_all |nDhv# |n hv rah ZyPFczOKPC|n# db2_all Z8(DyP}]bVx~qwOKPG;%=|n#db2_all |n;'Vh* ;%=C'dkD|n# db2_kill ;;#9}Z`v}]b~qwOKPDyPxL,"e}yP}]bVx~ qwODyPJ4#K|n9}]bdC;;B#}GZ IBM m~'Vz9D 8SVx]exPV4# db2_call_stack Z Linux M UNIX Yw53O,9ZyP}]bVx~qwOKPDyPxL +wCX]4k syslog# Z Linux M UNIX Yw53O,b)|n4PxX(~=hCD rah,}g: v T"P==ZyPFczOKP v +|ndvVp:f= /tmp/$USER/db2_kill M/tmp/$USER/ db2_call_stack P# |n db2_call_stack Z Windows O;IC#kD*9C db2pd -stack |n# rah M db2_all |n0:3r 0:rPG;vr`vXbV{# Z|nV{0dk;vr`v0:rPx;ekNNUq#tk8(`vrP,IT NN3rdk|G,+NN`V{rPPDdkV{Xk43rdk#g{zdkN N0:rP,G4zXk+{v|n(|(C0:rP)CZ+}EZ,gB}y >: v Z Linux M UNIX Yw53O: rah "};ps -F pid,ppid,etime,args -u $USER" db2_all "};ps -F pid,ppid,etime,args -u $USER" v Z Windows Yw53O: 146 VxM/:8O rah "||db2 get db cfg for sample" db2_all "||db2 get db cfg for sample" 0:rPP: rP C> | Zs(43rKP|n# |& Zs(43rKPb)|n,"ZyP6L|njI.s,U9C|n,49 P;)xLTZKP#}g,g{SxL(Z Linux M UNIX Yw53O)r s(xL(Z Windows Yw53O)TZKP,G4I\alISY#ZKi vB,C|nt/@"Ds(xL4lw|nU9.szIDNN6Ldv, "+Cdv4XA4Fcz# ":Z Linux M UNIX Yw53O,8( & a5MT\,r*h*KP|` D rsh |n# || Zs(T"P==KP|n# ||& Zs(T"P==KP|n"ZyP6L|njI.sU9C|n,gH0T |& ivDhv;y# ":Z Linux M UNIX Yw53O,8( & a5MT\,r*h*KP|` D rsh |n# ; k ||& `,#bG;vOLDfzq=# ":Z Linux M UNIX Yw53O,8( ; a5MT\(`TZ || xT), r*h*KP|`D rsh |n# ] Z4P|n.0$H]RC'E*D~Dc4P# ":vZ Linux M UNIX Yw53OIC# } Z4P|n.0$H]RZ $RAHENV P8(DD~Dc4P(I\G .kshrc)# ":vZ Linux M UNIX Yw53OIC# ]} Z4P|n.0,$H]RC'E*D~Dc4P,;s4PZ $RAHENV P8 (DD~(I\G .kshrc)# ":vZ Linux M UNIX Yw53OIC# ) #94PC'E*D~M $RAHENV P8(DD~# ":vZ Linux M UNIX Yw53OIC# ’ +|nwCX+AFcz# < "MA}KFczbDyPFcz# <<-nnn< "MA}}]bVx~qw nnn bDyP}]bVx~qw(db2nodes.cfg P }}]bVxE* nnn .bDyP}]bVx~qw,kND>mPns;v 0:rPsDZ;N)# nnn G 1 ;"2 ;r 3 ;D}]bVxE,CVxEk db2nodes.cfg D~ PD nodenum 5T&# Z 10 B 4(M\mVx}]b73 147 <<-nnn< vJCZ db2_all# <<+nnn< v"MA}]bVx~qw nnn(db2nodes.cfg P}]bVxE* nnn D} ]bVx~qw,kND>mPns;v0:rPsDZ;N)# nnn G 1 ;"2 ;r 3 ;D}]bVxE,CVxEk db2nodes.cfg D~ PD nodenum 5T&# <<+nnn< vJCZ db2_all# (UWV{) Zs(KP6L|n,stdin"stdout M stderr +?XU#K!nv1Zs( KP|n1EP',4vZ9|( \ r ; D0:rPPP'#|Jm|n!l jI(6L|n;t/MjI)#g{Z rah |nPO8(K0:rP,G4 +C|nC%}E(p4,rC+}E(pC|n"Z0:V{.0S \#} g, rah ’; mydaemon’ r_ rah "; \ mydaemon" 1w*s(xLKP1,rah |nS;aH}NN*5XDdv# > CFcz{Ff;R=D ># ″ CFczw}f;R=D (),C}]bVxEf;R=D ### v Fczw}Gk}]b53PDFczX*DEk#g{;PZKP`_- Vx,G4FczDFczw}T&Z}]bVxdCD~PCFczD} ]bVxE#*Z`_-Vx}]b73Pq!FczDFczw},;* FcG)KP`_-VxDFczDX4u?#}g,g{ MACH1 }ZK P=v_-Vx,MACH2 2}ZKP=v_-Vx,G4}]bVxdCD ~P MACH3 D}]bVxE* 5#+G,MACH3 DFczw}&G 3# – Z Windows Yw53O,;*`-}]bVxdCD~#*q!Fcz w},I9C db2nlist |n# v 18(K " 1,;aSFczPmP>}X4n# 9C5w v 0:rP;O*G|nD;?V#t8(0:rPw*|nD;?V,Xk+{v |n,|(0:rP,(Z+}EZ# XF rah |n >wbP>CZXF rah |nD73d?# m 13. XF rah |nD73d? {F ,e 1!5 $RAHBUFDIR ":vZ Linux M UNIX Yw53OI C# :ex?< /tmp/$USER 148 VxM/:8O m 13. XF rah |nD73d? (x) {F ,e 1!5 $RAHBUFNAME ":vZ Linux M UNIX Yw53OI C# :exD~{ rahout $RAHOSTFILE(Z Linux M UNIX Yw 53O);RAHOSTFILE (Z Windows Yw5 3O) |,wzPmDD~ db2nodes.cfg $RAHOSTLIST(Z Linux M UNIX Yw 53O);RAHOSTLIST (Z Windows V{.N=DwzPm i!T $RAHOSTFILE $RAHCHECKBUF ":vZ Linux M UNIX Yw53OI C# thC*“no”,rF}li 4hC $RAHSLEEPTIME(Z Linux M UNIX Yw 53O); RAHSLEEPTIME(Z Windows Yw53O) TkFD1d,KE>+ZK1dZH}4T T"PN=KPD|nDu ... {"D1ddt(TkF)# TZyPYw53,8(NN}{}#_P0 wbP>Z48(0:rP1KPD . D~# ":>ZPDE";JCZ Linux M UNIX Yw53# P .profile E Z $RAHENV P8(DD~(I\G .kshrc) K k E `, PE .profile szZ $RAHENV P8(DD~(I\G .kshrc) B k PE `, N ^ ":g{G< shell Lr;G Korn shell Lr,G4+Z Korn shell LrxLP4P z8(*4PDNNcD~,rK,XkqX Korn shell Lro(#}g,g{G< shell LrG C shell,*T rah 4PD|nhC .cshrc 73,&C4(H'Z .cshrc D Korn shell Lr INSTHOME/.profile,"Z INSTHOME/.cshrc P8(: setenv RAHDOTFILES P r_&C4(H[Z .cshrc D Korn shell Lr INSTHOME/.kshrc,"Z INSTHOME/ .cshrc P8(: setenv RAHDOTFILES E setenv RAHENV INSTHOME/.kshrc Kb,g{;P tty(I rsh wC1),G4 .cshrc ;C4kjwba)K;)(i,f_zgN&mZKP rah 1I\v=D3)Jb# ":>ZPDE";JCZ Linux M UNIX Yw53# 1. rah Rp(rKP\$D1d) KJbI\GIBP-r}pD: v rah Q7(|h*:edv,xz4{# KP rah Dj6;PZd .rhosts D~P}7(edP;(Fcz,r_g{Q+ DB2RSHCMD "amd?dC*9C ssh,G4I\G4}7dC?(FczOD ssh M'zM~qw# 4. d; rah S shell |nPKP1KPiv DxL4U9`SxL#;*8(EE`E#xG*9C1!5(15)# 5. 1Z,; $RAHUSER B"v`v rah |n1,rah Ddv4}7T>,r rah m sX(f $RAHBUFNAME ;fZ# bGr*,"P4P`v rah }Z"T9C,;:exD~(}g,$RAHBUFDIR r $RAHBUFNAME)TdvxP:e#*\bKJb,+;,D $RAHBUFNAME CZ?v" P rah |n,}g,ZBP ksh P: export RAHBUFNAME=rahout rah ";$command_1" & export RAHBUFNAME=rah2out rah ";$command_2" & r9C9 shell T/!q(;{FD=(,g: RAHBUFNAME=rahout.$$ db2_all "....." g{ELUdP^,G4^[9CDV=(,,}G75h*I & a)D&\,qr, 9C | zf |&,"9C || zf ||& r ;#8( & h*|`6L shell |n,rK a5MT\# `S rah xL(Linux M UNIX) 1NN6L|nTZKPrTZ[S:edv1,I rah t/DxL+`Sn/T+8 >D)n/P4KPD{"4kUK"lw:edv# XZKNq ":>ZPDE";JCZ Linux M UNIX Yw53# 473d? RAHWAITTIME yXFD1ddt4vN<{"#kNDozE"Tq!PX gN8(KhCDj8E"#(}yPN<{"# w`S}LG;v|n,d|n{(I ps |nT>)* rahwaitfor#Z;uN<{" f_zKxLD pid(xLj6)#yPd{`SxL+mV*KP rah E>(r{E 4SD{F)D ksh |n#t8b,I(}TB|n#9yP`SxL: kill pid dP pid Gw`SxLDxLj6#;*8(EE`E#C|*1!5 15#by>; a0l6L|n,+ah9T/T>:edv#"b,Z4P%v rah 1,I\P=i r|`i;,`SxLZ;,1d4P#+G,g{NN1r#910i`SxL, G4;aYt/d{`SxL# g{}fG< shell Lr;G Korn shell Lr(}g,/bin/ksh),G4IT9C rah, +gNdk|,BPXbV{D|nDfrT"P)nl: " unsubstituted $ ’ PX|`E",kdk rah "?"#xR,Z Linux r UNIX Yw53P,g{4P6 L|nDj6DG< shell Lr;G Korn shell Lr,G4TCj64P rah DG< shell Lr2;CG Korn shell Lr#(rah y]>Xj64v(6Lj6D shell G q* Korn shell Lr)#C shell ;CT%}EZDV{.4PNNf;rXb&m# XkU-y#V# Z Windows O* rah hC1!73E*D~ ** rah |nhC1!73E*D~,I9CD~ db2rah.env,CD~&Z5}?< P4(# XZKNq ":>ZPDE";JCZ Windows# CD~&CPTBq=: 152 VxM/:8O ; This is a comment line DB2INSTANCE=instancename DB2DBDFT=database ; End of file IT8(* rah xP73u ( ) IN INDEX IN LONG IN DISTRIBUTE BY HASH () TBG;v>}: CREATE TABLE MIXREC (MIX_CNTL INTEGER NOT NULL, MIX_DESC CHAR(20) NOT NULL, MIX_CHR CHAR(9) NOT NULL, MIX_INT INTEGER NOT NULL, MIX_INTS SMALLINT NOT NULL, © Copyright IBM Corp. 2012 155 MIX_DEC DECIMAL NOT NULL, MIX_FLT FLOAT NOT NULL, MIX_DATE DATE NOT NULL, MIX_TIME TIME NOT NULL, MIX_TMSTMP TIMESTAMP NOT NULL) IN MIXTS12 DISTRIBUTE BY HASH (MIX_INT) ZO;v>}P,mUdG MIXTS12,xV<|G MIX_INT#g{4T=8(V< |,G4|G MIX_CNTL#(g{48(w|R4(eV<|,G4V<|GCPmP DZ;vG$MP#) mD;PMPXCPDyPE"X#^[G9C1!P*9G9C LONG IN Sd42G1!P*,+yPHZ8(5D5<|(Z|,K_gD}]VxP# v EXCLUSIVE m>yPHZ8(5D5<;|(Z|,K_gD}]VxP# v CREATE TABLE odD NULL Sd8(:Z*} 1 M 2 ]>gN9C CREATE TABLE od4T/(eMzIT?v}]Vx 8(D6'# >} 1: "v(eKBP6'D CREATE TABLE od: CREATE TABLE lineitem ( l_orderkey DECIMAL(10,0) NOT NULL, l_quantity DECIMAL(12,2), l_shipdate DATE, l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate))) PARTITION BY RANGE(l_shipdate) (STARTING (’1/1/1992’) ENDING (’12/31/1992’) EVERY 1 MONTH); KodzI 12 v}]Vx,?v}]Vx|, 1 v|5: (l_shipdate) >= (’1/1/ 1992’), (l_shipdate) < (’3/1/1992’), (l_shipdate) < (’4/1/1992’), (l_shipdate) < (’5/1/ 1992’), ..., (l_shipdate) < (’12/1/1992’), (l_shipdate) < (’12/31/1992’)# IZ{ep} 2: "v(eKBP6'D CREATE TABLE od: CREATE TABLE t(a INT, b INT) PARTITION BY RANGE(b) (STARTING FROM (1) EXCLUSIVE ENDING AT (1000) EVERY (100)) KodzI 10 v}]Vx,?v}]Vx|, 100 v|5: (11andb<=101)D p<5;|(K5#,y,IZ{eaxg^(1000)|(K5,yTns;v}] Vx(b > 901 b <= 1000)Daxg^|(K5#d` STARTING 5<;|(K5, "Rd` ENDING 5+<|(K5#?v}]VxD?v6'4(;vB}]Vx#bVo (q=a_K(e6'1DinT,SxvSK}]M LOB } 3 M 4 ] >gN9C CREATE TABLE od4TV/==(eMzIT}]Vx8(D6'# >} 3: KodT=vUZPxPVx,b=vUZP} 4: 4(;vm,"RZ5 101 k 200 .dfZdt# Z 11 B 4(mMd{`XmTs 159 CREATE TABLE foo(a INT) PARTITION BY RANGE(a) (STARTING FROM (1) ENDING AT (100), STARTING FROM (201) ENDING AT (300)) 9CJmmSr}%}]VxD ALTER TABLE od9a P# SQLSTATE=22525 ^F v m6p^F: – 9CT/zIDo(q=(|, EVERY Sd)4(DmZmVx|P;\9C} VrUZ1d`M# v od6^F: – ZT/zIDo(q=P,;'V MINVALUE M MAXVALUE# – 6'4}rEP# – ZT/zIDo(q=P,;\8(;P# – EVERY SdPDv?XksZc# – ENDING 5XksZrHZ STARTING 5# }]Vx}]"w}M${M}]DEC S>JO5,4(VxmJmzf;mDwv?VT0X(mUdPDX*mTs# 4(m1,I8(+f;DvmUdPDj{m}]MX*mTs#r_,If;X (mUdPDmw}"$}]rs}]rmVx#yPmUdKf;X(mUdPDm}]MX*mTs D&\# CREATE TABLE table_name IN table_space_name1 INDEX IN table_space_name2 LONG IN table_space_name3 PARTITIONED BY ... PARTITION partition_name | boundary specification | IN table_space_name4 INDEX IN table_space_name5 LONG IN table_space_name6 IZ;,mUdPf;CVxmD?vVx# 9I9C CREATE INDEX ... IN table_space_name1 od4TVxmOC'4(DG Vxw}8(mUd,CmUdI\k CREATE TABLE ... INDEX IN table_space_name2 odP8(Dw}mUd;,#CREATE INDEX odD IN SdvCZVxm#g{ 4Z CREATE TABLE r CREATE INDEX odO8( INDEX IN Sd,G4w} +EZCmDZ;vISr,SVxyZDmUdO# 53zIDGVxw}(}g XML P76w})EZ CREATE TABLE odD INDEX IN SdP8(DmUdO# 160 VxM/:8O ZxP XML }]DVxmO,XML xrw}km}]},;sXB4(Cm# g{{OBPN;u~,G4PD SYSCAT.COLUMNS IMPLICITVALUE VNPI_ PGU5: v IMPLICITVALUE VNGZ,SYwZdS4m+%x4D# v IMPLICITVALUE VNGZpkYwZdS4mLPx4D# v IMPLICITVALUE VNGS V8 (F= V9 ZdhCD,|;7(*QmSPrI \*QmSP#QmSPGr*4P ALTER TABLE...ADD COLUMN odx4( DP# mP D}]DivBjI*;#*;s,XkXB4(GVxw}MStS }ib}]VxT\b6'X~Jb# }L 1. +#fm(FAVxm# 9C LOAD FROM CURSOR |n4\bNNPd=h#TB >}5wgN+m T1 (FA SALES_DP m# Z 11 B 4(mMd{`XmTs 161 a. 4("nd#fm T1# CREATE TABLE t1 (c1 int, c2 int);INSERT INTO t1 VALUES (0,1), (4, 2), (6, 3); b. 4(UVxm# CREATE TABLE sales_dp (c1 int, c2 int) PARTITION BY RANGE (c1) (STARTING FROM 0 ENDING AT 10 EVERY 2); c. 9C LOAD FROM CURSOR |n+ SQL i/PD}]1S-kABVxm# SELECT * FROM t1; DECLARE c1 CURSOR FOR SELECT * FROM t1; LOAD FROM c1 of CURSOR INSERT INTO sales_dp;SELECT * FROM sales_dp; 2. + UNION ALL S}5wgN+{* ALL_SALES D UNION ALL S<*;* SALES_DP m# a. 4( UNION ALL S<# CREATE VIEW all_sales AS ( SELECT * FROM sales_0198 WHERE sales_date BETWEEN ’01-01-1998’ AND ’01-31-1998’ UNION ALL SELECT * FROM sales_0298 WHERE sales_date BETWEEN ’02-01-1998’ AND ’02-28-1998’ UNION ALL ... UNION ALL SELECT * FROM sales_1200 WHERE sales_date BETWEEN ’12-01-2000’ AND ’12-31-2000’ ); b. 4(xP%;ibVxDVxm#!q6'T9|;ak*,SDZ;v}] VxX~# CREATE TABLE sales_dp ( sales_date DATE NOT NULL, prod_id INTEGER, city_id INTEGER, channel_id INTEGER, revenue DECIMAL(20,2)) PARTITION BY RANGE (sales_date) (PART dummy STARTING FROM ’01-01-1900’ ENDING AT ’01-01-1900’); c. ,SZ;vm# ALTER TABLE sales_dp ATTACH PARTITION STARTING FROM ’01-01-1998’ ENDING AT ’01-31-1998’ FROM sales_0198; d. >}FVx# ALTER TABLE sales_dp DETACH PARTITION dummy INTO dummy; DROP TABLE dummy; e. ,S`BVx# ALTER TABLE sales_dp ATTACH PARTITION STARTING FROM ’02-01-1998’ ENDING AT ’02-28-1998’ FROM sales_0298; ... ALTER TABLE sales_dp ATTACH PARTITION STARTING FROM ’12-01-2000’ ENDING AT ’12-31-2000’ FROM sales_1200; f. "v SET INTEGRITY odT9i/ITCJB,SDVxPD}]# SET INTEGRITY FOR sales_dp IMMEDIATE CHECKED FOR EXCEPTION IN sales_dp USE sales_ex; 162 VxM/:8O a>:g{Z4P,SYw.0IT(}@"Z}]~qwD&CLr_-4 P}]j{Tli(|(6'i$Md{}PCZ?F4P(; }T&DGVxw}"Zh*1X|{BDVxw}# a{ BfD>}]>gN+VPGVxw}*;*Vxw}# >} TBG+z4(DGVxw}*;*Vxw}D>}: UPDATE COMMAND OPTIONS USING C OFF; CREATE INDEX data_part ON sales(sale_date) PARTITIONED; DROP INDEX dateidx; RENAME INDEX data_part TO dateidx; COMMIT; TBG+}]b\mw4(DGVxw}*;*Vxw}D>}#KivB,Z>} -<}P,company1.parts mP region M color * MDC |P;`&Diw} *GVxiw}# CALL SYSPROC.ADMIN_MOVE_TABLE( ’COMPANY1’, --Table schema ’PARTS’, --Table name ’ ’, --null; No change to columns definition ’ ’, --null; No additional options ’MOVE’); --Move the table in one step QzF/Vx MDC mT9CVxiw} *Kn!/}]F/,ITZmQz1+}]S_PGVxiw}D MDC mF=_P Vxiw}D MDC m#K}L9CBP=h: 1. 4(;vk**;Dm_P`,(eDB%Vx MDC m#*Vx8(6'1,k 9CZ**;D MDC mD6'.bD6'# B%Vx MDC mDiw}QVx#ZTs=hP+pk8(6'1y4(DVx# 2. pk MDC mD?vVx#?vVx+I*@"D MDC m# 1pkVx1,Vx}]+,SAB?jm,;h*F/VxPD}]# ":;\pk MDC mDns;vVx#|G_PGVxiw}D%Vx MDC m# 3. TZIpk MDC mVx4(D?v@"m,T0_PGVxiw}D%Vx MDC m,+m,SAZ=h 1 P4(DBVx MDC m# Z,Sm1,m}]+,SABVx MDC m,^hF/}],xiw}+4(* Vxiw}# 4. Z,SZ;v@" MDC m.s,IT,S4(B MDC m1y4(DUVx# 5. TBVx MDC m"v SET INTEGRITY od# B;=v24 QVxD_e/i/m (MQT) P* Vxm'VyP`MD_e/i/m (MQT)#9CQVxD MQT 1,I9C;)}?jm#ZTyPQpkDStmKP SET INTEGRITY o 164 VxM/:8O ds,}]VxZ_-O+S4mpk,l=VxpkYw+C}]VxS4mp k=?jmP#1=l=VxpkYwjI,?jm+;IC# v *lbQpkmGqT;ICJ,ki/ SYSCAT.TABDETACHEDDEP ?} 1# >} 1:+QVxD MQT *;*;vU(m d;;1S'VTQVxD MQT 4P ATTACH Yw,+IT(}BPYwqC`, D'{:+QVxD MQT *;*;vU(m,Tm}]4PZ{D*kM*v,;s +Cm*;X MQT#BP CREATE TABLE M ALTER TABLE od]>KC'{: CREATE TABLE lineitem ( l_orderkey DECIMAL(10,0) NOT NULL, l_quantity DECIMAL(12,2), l_shipdate DATE, l_year_month INT GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate))) PARTITION BY RANGE(l_shipdate) (STARTING (’1/1/1992’) ENDING (’12/31/1993’) EVERY 1 MONTH); CREATE TABLE lineitem_ex ( l_orderkey DECIMAL(10,0) NOT NULL, l_quantity DECIMAL(12,2), l_shipdate DATE, l_year_month INT, ts TIMESTAMP, msg CLOB(32K)); CREATE TABLE quan_by_month ( q_year_month, q_count) AS (SELECT l_year_month AS q_year_month, COUNT(*) AS q_count FROM lineitem GROUP BY l_year_month) DATA INITIALLY DEFERRED REFRESH IMMEDIATE PARTITION BY RANGE(q_year_month) (STARTING (199201) ENDING (199212) EVERY (1), STARTING (199301) ENDING (199312) EVERY (1)); CREATE TABLE quan_by_month_ex( q_year_month INT, q_count INT NOT NULL, ts TIMESTAMP, msg CLOB(32K)); SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED; CREATE INDEX qbmx ON quan_by_month(q_year_month); ALTER TABLE quan_by_month DROP MATERIALIZED QUERY; Z 11 B 4(mMd{`XmTs 165 ALTER TABLE lineitem DETACH PARTITION part0 INTO li_reuse; ALTER TABLE quan_by_month DETACH PARTITION part0 INTO qm_reuse; SET INTEGRITY FOR li_reuse OFF; ALTER TABLE li_reuse ALTER l_year_month SET GENERATED ALWAYS AS (YEAR(l_shipdate)*100 + MONTH(l_shipdate)); LOAD FROM part_mqt_rotate.del OF DEL MODIFIED BY GENERATEDIGNORE MESSAGES load.msg REPLACE INTO li_reuse; DECLARE load_cursor CURSOR FOR SELECT l_year_month, COUNT(*) FROM li_reuse GROUP BY l_year_month; LOAD FROM load_cursor OF CURSOR MESSAGES load.msg REPLACE INTO qm_reuse; ALTER TABLE lineitem ATTACH PARTITION STARTING ’1/1/1994’ ENDING ’1/31/1994’ FROM li_reuse; SET INTEGRITY FOR lineitem ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN lineitem USE lineitem_ex; ALTER TABLE quan_by_month ATTACH PARTITION STARTING 199401 ENDING 199401 FROM qm_reuse; SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED FOR EXCEPTION IN quan_by_month USE quan_by_month_ex; ALTER TABLE quan_by_month ADD MATERIALIZED QUERY (SELECT l_year_month AS q_year_month, COUNT(*) AS q_count FROM lineitem GROUP BY l_year_month) DATA INITIALLY DEFERRED REFRESH IMMEDIATE; SET INTEGRITY FOR QUAN_BY_MONTH ALL IMMEDIATE UNCHECKED; 9CxP IMMEDIATE CHECKED !nD SET INTEGRITY od,TliQ,SD }]VxGqfZj{T%}#Z+m|DX MQT .0,h*4PK=h#9CxP IMMEDIATE UNCHECKED !nD SET INTEGRITY od4F}T MQT xPXh Dj+"B#*qCnQT\,MQT Dw}GXhD#(izZJ1D1r+l#mk SET INTEGRITY oddO9C# (#,Z,yxPVxKDsMB5mO4(QVxD MQT#g{ZsMB5mO*k r*vm}],G4XkV/w{QVxD MQT,g>} 2 Py># >} 2:V/w{QVxD MQT Dd MQT(quan_by_month)T+|*;*;vU(DVxm: ALTER TABLE quan_by_month DROP MATERIALIZED QUERY; SB5m(lineitem)M MQT Ppk**vD}],"9C**kDB}]XB0kG (m li_reuse: ALTER TABLE lineitem DETACH PARTITION part0 INTO li_reuse; LOAD FROM part_mqt_rotate.del OF DEL MESSAGES load.msg REPLACE INTO li_reuse; ALTER TABLE quan_by_month DETACH PARTITION part0 INTO qm_reuse; xPek.0^t qm_reuse#b+ZekSi/}].0>}QpkD}]#bG(} f;0k MQT 45VD,dP0kD}]D~GSi/DZ]# 166 VxM/:8O db2 load from datafile.del of del replace into qm_reuse IT9C INSERT INTO ... (SELECT ...) 4V/"Bm#bvTB}]45GXhD, rK&CZ,S.0"vCod: INSERT INTO qm_reuse (SELECT COUNT(*) AS q_count, l_year_month AS q_year_month FROM li_reuse GROUP BY l_year_month); VZ,IT*kB5mDB}]: ALTER TABLE lineitem ATTACH PARTITION STARTING ’1/1/1994’ ENDING ’1/31/1994’ FROM TABLE li_reuse; SET INTEGRITY FOR lineitem ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN li_reuse USE li_reuse_ex; SE,*k MQT D}]: ALTER TABLE quan_by_month ATTACH PARTITION STARTING 199401 ENDING 199401 FROM TABLE qm_reuse; SET INTEGRITY FOR quan_by_month IMMEDIATE CHECKED; Z,S}]Vx.s,Xki$B}]T7#|Z6'Z# ALTER TABLE quan_by_month ADD MATERIALIZED QUERY (SELECT COUNT(*) AS q_count, l_year_month AS q_year_month FROM lineitem GROUP BY l_year_month) DATA INITIALLY DEFERRED REFRESH IMMEDIATE; SET INTEGRITY FOR QUAN_BY_MONTH ALL IMMEDIATE UNCHECKED; 1= SET INTEGRITY odi$Kb)}].s,E\T|GxPCJ#d;'V REFRESH TABLE Yw,+Kiv]>K(} ATTACH PARTITION M DETACH PARTITION YwTQVxD MQT xPV/,$#C'(} SET INTEGRITY od D IMMEDIATE UNCHECKED Sd+}]jG*Qi$# 4(6'/:m XZ9C6'/:mDwbP>K&m6'/:m (RCT) 1*q-D;)Z|,;)=8,b)=85wKgNE\4(byDm# Z 11 B 4(mMd{`XmTs 167 =8 1:4(6'/:m(Jmgv) TB>}T>K;v6'/:m,IT9CCm4lwX('zDE"#?v'zG <<|,BPE": v '#j6 v Lrj6 v 'z`E v 'zj6 v 'zD{ v 'zDU v 'zD'j=yI((GPA) CREATE TABLE students ( school_id INT NOT NULL, program_id INT NOT NULL, student_num INT NOT NULL, student_id INT NOT NULL, first_name CHAR(30), last_name CHAR(30), gpa FLOAT ) ORGANIZE BY KEY SEQUENCE (student_id STARTING FROM 1 ENDING AT 1000000) ALLOW OVERFLOW ; ZK>}P,9CK STUDENT_ID P(|d1m|)4mS"|Br>}'zG<# ?uG}P,?uG}P,L}V\m 200 y'#,?y'#P 20 dLR,?dLRI]I 35 v'z#KL}Vn`ITPU 140,000 {'z# CREATE TABLE students ( school_id INT NOT NULL, class_id INT NOT NULL, student_num INT NOT NULL, student_id INT NOT NULL, first_name CHAR(30), last_name CHAR(30), gpa FLOAT ) ORGANIZE BY KEY SEQUENCE (school_id STARTING FROM 1 ENDING AT 200, class_id STARTING FROM 1 ENDING AT 20, student_num STARTING FROM 1 ENDING AT 35) DISALLOW OVERFLOW ; 168 VxM/:8O ZK>}P,SCHOOL_ID"CLASS_ID M STUDENT_NUM P2,d1m|,+9C Cm|4mS"|Br>}'zG<# ;Jmgv,bGr*L}V_Ta^F?dLRPD'z},"RKL}V\mD '#MLRD}?GL(D#TZ3)O!D'#(knsD'#`HLR}|YD '#),KmP+fZI\@6;a9CD$VdUd# 4( MDC r ITC m1D"bBn 4( MDC r ITC m1*}-}mDyPStTs, 2. X|{m, 3. 9CB(e44(m, 4. XB4(mDyPStTs, 5. +mPDVP}]d;*BmPyh*D}]#4,SImP!q}],;s+C }]0kBmP,ITZBmP9CP/}4+ID}]`Md;*BD}]` M# +}]SVPmFA ITC m *5MT}],$YwDhs,IT+}]S#fmFAek1d/: (ITC) m#*+ }]SVPmFA ITC m,k9C*zmF/f"}L# ExampleBank =8T>KgN+VPmPD}]FA ITC m#K=895w9C ITC m1XUUdDc{LH#PX|`E",kND“`XEn”4S# DB2 hFKJLrOD MDC KJLr&\?~ DB2 hFKJLr (db2advis) _P MDC &\?~#K&\?~(iCZ MDC m PD/:,(|(y>PDVZH)Tca_$w:XT\#VZHbvuom>C 4u!/:,y}(%5D}?)D;v}'mo=#VZHD#{>}GVZHI *UZ"UZyZDGZ"UZyZDB]r;jPD>H# Z 11 B 4(mMd{`XmTs 169 9C“DB2 hFKJLr”D MDC &\?~*s}]bPAYfZ8v)9}]iD} ]#“DB2 hFKJLr”9C}]4T}]\HMy}("#M# g{}]bDmP;P}],G4“DB2 hFKJLr”;a(i9C MDC,49C} ]b|,Um,+P;iiYD3FE"4m>|G;vQndD}]b# (i9j6KC4(e,DVZHD1ZzIP#(iP;|(I\Dis!#Z* MDC ma)(i1,9CmUdD)9}]is!#Y(+ZVPmyZDmUdP 4((iD MDC m,rKCm_P`,D)9}]is!#T MDC ,D(i+y] mUdD)9}]is!Dd/xd/,bGr*)9}]is!a0lITnd= ir%*PDG<}#K)9}]is!+1S0l%*D\H# !\IT*m(i%v,r`v,,+k;}YwD0l#Tm4Pb)Yw1I\a j>4KP MDC &\?~#9C “C”(i`M48>`,/:m#(i`M*:“I”m>w}"“M”m>_e/i/m" “C”m> MDC,x“P”m>Vx}]b73#(i`MIT`%iO9C# ":“DB2 hFKJLr”;a&mds!;= 12 v)9}]iDm# 1av(i1,KJLr+,1Vv MQT M#fy>m# MDC &\?~Ddv|(: v ?vmDzIPmo=(CZT MDC bv=8PvVD,hCVZH)# v *?vm(iD ORGANIZE BY Sd# Tj}D MDC m4U+V} 1: CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE) ORGANIZE BY DIMENSIONS (c1, c3, c4) >} 1 PDmy]NI_-"=e(4,_P}v,)D}vPPD5/:#VZIT Zi/&mZdy];vr`v,TmxP_-V,,Tcf0DX5Kc{v&m `&D,r%*PDi#iDs!(3})GmD)9}]is!# _PyZ`PD,D MDC m ?v,ITI;Pr`PiI#w*;v>},IT4(;vy]|,=PD;v, 4/:Dm# >} 2: CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE) ORGANIZE BY DIMENSIONS (c1, (c3, c4)) Z>} 2 P,my]=v, c1 M (c3, c4) 4xP/:#by,Zi/&mZd,m ITy] c1 ,riO (c3, c4) ,_-V,#Cmk>} 1 PDm_P`,}?Di, +GY;v,iw}#Z>} 1 P,P}v,iw},P c1"c3 M c4 w;v#Z> } 2 P,P=v,iw},;vkTP c1,xm;vkT c3 M c4#b=v=(Dw *npZZ,Z>} 1 P,f0 c4 Di/IT9C c4 D,iw}4lY1SXCJ `X}]i#Z>} 2 P,c4 G,iw}PD(z|?V,rKf0 c4 Di/f0 |`D&m#+G,Z>} 2 P,+Y,$Mf";viw}# “DB2 hFKJLr”+;T|,`PD,a)(i# +Pmo=w*,D MDC m Pmo=2ICZ/:,#y]Pmo=/:D&\TZ+,OvA|MDj8LH G#PC,}g,+X7Ov*Xm;Crxr,r_+UZOv*GZ"B]r j#*TK==5V,DOv,IT9CzIP#K`MDP(eJm9CITm> ,Dmo=44(P#Z>} 3 P,Cod4(y];vy>PM=vPmo=xP/ :Dm# >} 3: Z 11 B 4(mMd{`XmTs 171 CREATE TABLE T1(c1 DATE, c2 INT, c3 INT, c4 DOUBLE, c5 DOUBLE GENERATED ALWAYS AS (c3 + c4), c6 INT GENERATED ALWAYS AS (MONTH(C1))) ORGANIZE BY DIMENSIONS (c2, c5, c6) Z>} 3 P,c5 PGyZ c3 M c4 PDmo=,x c6 Pa01+ c1 POvA| MDj8LH#Kody] c2"c5 M c6 PPD54/:Cm# TzIP,D6'i/ TzIP,D6'i/h*%wP/}#mo=XkG%wDE\*zIPD,Iz 6'=J#g{TzIP4(,,G4Ty>PDi/\;{CzIPDiw}4a _T\(P;Viv}b)#*9y>P(}g,UZ)D6'i/T,iw}9C 6'(h,C4Z CREATE TABLE odPzIPDmo=XkG%wD#!\Pmo =IT|(NNP'mo=(|(C'(eD/}(UDF)),+Gg{mo=;G %wD,G41H[=Jr IN =JPO1,|GE\;9Ciw}4zci /# w*;v>},Y(9CzIP month D,44( MDC m,dP month = INTEGER (date)/100#TZC,(month)Di/,IT4Piw}(h#TZy>P(date) Di/,2IT4Piw}(h4u!*(hDiD6',;s;+UZD=J&C Zb)iPDP# `kw+zI*Ziw}(hP9CDd{=J#}g,TZTBi/: SELECT * FROM MDCTABLE WHERE DATE > "1999-03-03" AND DATE < "2000-01-15" `kw+zITB=J::month >= 199903; M:month <= 200001;,|GITC w,iw}(hD=J#1TqCDixP(h1,a+-<=J&CZb)iPD P# G%wmo=JmTC,&CH[=J#G%w/}D;vOCD>}G MONTH( ), g>} 3 PD c6 PD(ey>#g{ c1 PGUZ"1dAGrUZr1dAGDP 'V{.m>(,G4/}+5X6'G 1 = 12 D{}5#!\/}DdvG7( D,+G5JO|zIDdvkW>/}(4,-7#=)`F: MONTH(date(’01/05/1999’)) = 1 MONTH(date(’02/08/1999’)) = 2 MONTH(date(’03/24/1999’)) = 3 MONTH(date(’04/30/1999’)) = 4 ... MONTH(date(’12/09/1999’)) = 12 MONTH(date(’01/18/2000’)) = 1 MONTH(date(’02/24/2000’)) = 2 ... !\K>}PDUZG,xvSD,+G MONTH(date) ;avS#|_exT,?1 date1 sZ date2,";\#$ MONTH(date1) sZrHZ MONTH(date2)#bG%w Ty*sD#KG%wTGJmD,+G|^FK,,y>PD6'=J;\zI, D6'=J#+G,mo=D6'=JGITD,}g,where month(c1) between 4 and 6#bITIC#f==9C,Dw},p<|* 4 x#9|* 6# *9K/}%w,k+j|(*B]D_;?V#fZT INTEGER ZC/}D)9T ozy]UZ(e%wmo=#INTEGER(date) 5XUZD{}m>(,ITV*iR jMB]D{}m>(#}g,INTEGER(date(’2000/05/24’)) 5X 20000524,rK INTEGER(date(’2000/05/24’))/100 = 200005#/} INTEGER(date)/100 G%wD# 172 VxM/:8O `FD,ZC/} DECIMAL M BIGINT 2_P)9,yTITIz%w/}#DECI- MAL(timestamp) 5X1dAGD.xFm>(,ITZ%wmo=P9C|4IzB ]"l"!1rVSHHDvSD5#BIGINT(date) 5XUZDs{}m>(,`FZ INTEGER(date)# ;*I\,}]b\mw+Z*m4(zIPr_y],SdPDmo=4(,17 (mo=D%wT#X(/};6p*#t%wT,}g,DATENUM( )"DAYS( ) M YEAR( )#"R,PM#?DwVcumo=,}g,}("K(rS(G#t%wT D#1 DB2 7(mo=;#t%wT1,r_g{|;\7(b;c,G4C,v' VTdy>P9CH[=J# Z 11 B 4(mMd{`XmTs 173 174 VxM/:8O Z 12 B Dd}]b Dd5} |D`v}]bVxPD}]bdC 1}]bVwbj6KZfZQ,SMQpkD}]VxDivBn#CDDdmYwM"b Bn# SYSCAT.DATAPARTITIONS ?VxISR&Z}#4,# v 1 STATUS *“A”1,m>Vx*B,SDVx,Xk"v SET INTEGRITY od T+,SDVx**}#4,# v 1 STATUS *“D”"“L”r“I”1,m>}Z,SVx,+G,SYwP4jI# – TZ&Z“D”4,DVx,XkTyPQpkDStm"v SET INTEGRITY o d,T+Vx*;*Z_-OQpk4,# – TZ&Z“L”4,DVx,CVx*Z_-OQpkDVxRl=VxpkNq }Z* DB2 V9.7 FP1 0|_"PfjICVxDpk# – TZ&Z“I”4,DVx,l=VxpkNqQjI,l=w}e}}Z|BCV xO(eDGVxw}# mSrDd}rX|{xP&Z“L”r“D”4,DVxDP1,Yw+'\"+5Xm s#TZ&Z“L”4,DVx,Yw+5X SQL20285N(SQLSTATE 55057)# TZ&Z“D”4,DVx,Yw+5X SQL0270N(SQLSTATE 42997)# mSzIP 1+zIPmS=xPQ,SrQpk}]VxDVxm1,Xkq-NNd {P`MDmSfr# mSr^DGVxw} TxPQ,S}]VxDm4("XB4(rXiw}1,Cw};|(Q, S}]VxPD}],bGr* SET INTEGRITY od,$yPQ,S}]V xDyPw}#TxPQpk}]VxDm4("XB4(rXiw}1,} GQpk}]VxxPh*y]C}]Vx(CVx&Z“D”4,)xP]v "BDQpkStmrG(m,qrw};|(Qpk}]VxPD}]#Z sfbVivB,w}+|(KQpk}]VxD}]# 178 VxM/:8O mSr^DVxw} 1ZPQ,S}]VxDivB4(Vxw}1,+4(?vQ,S}]Vx Dw}Vx#1=KPK SET INTEGRITY odT9,SD}]Vx*z,y ,S}]VxOw}VxDw}u?EIS#"b,r*4(w}f0Q,S }]Vx,yT4((;Vxw}1IR=Q,S}]VxPw*X4|5D P,Sx9w}4('\#*K\bKJb,(iC'ZPQ,SVxDiv B;*"T4(Vxw}# g{m_PNNQpkStm,G4;'VZxPQpkStmDVxmO4 (Vxw}#ZKivB,xP4(Vxw}DNN"T<+ 7(#g{;P* ADD PAR- TITION Sd8(NN INDEX IN

,G4Vxw}TsDmUd+k1! ivBT&}]Vx9CDmUd`,# VxmOI53zID XML xrw}}4mODw}#bJCZ_-Mom XML w}#Z ATTACH YwZd,+}%|GZ53?}4mOD XML P76w}"R+}%| GD? Z;69C.s,;ar*P1*?jm4(KGVxw}x#9$w# *DdmT,S}]Vx,odZ(j65PDX(XkAY|(T4mDBPdP ;n(^rX(: v TmD SELECT X(T0TmD#=D DROPIN X( v TmD CONTROL X( v DATAACCESS (^ XZKNq ,S}]Vx1,+9CVPm(4m)"+dw*B}]Vx,SA?jm#hz DB2 V10.1 M|_"Pf,Z9Cx ATTACH PARTITION SdD ALTER TABLE od+}]Vx,SAVxm1,?jVxmT;&Z*z4,,Z RS"CS r UR t k6pBKPDkTCmD/,i/+LxKP# ^FMC(4+F}TzIDPDli# SET INTEGRITY FOR table-name ALLOW WRITE ACCESS IMMEDIATE CHECKED FOR EXCEPTION IN table-name USE table-name KodT,SD}]Vx4Pj{Tli,+G;lizIP# v 49Z?jPGj6Px4P;Gj6P1,2Jm,S}]Vx#od SET INTEGRITY IMMEDIATE CHECKED ;*,SDPzIj65#od SET INTEG- RITY FOR T GENERATE IDENTITY ALLOW WRITE ACCESS IMMEDIATE CHECKED n4y,SDPDj65#kj6P%dDPZ`MMIUT=fXk% d#TKPD1!5;P*s#(iD=(GZG(mPn4}7Dj65#Z G,IZQZ4mP#$Kj65,yT;h*Z4P ATTACH s9C GENER- ATE IDENTITY !n# v TZ}]V}D(4,;\TdhC RESTRICT DROP)# v g{8(}]Vx{F,G4|;CfZZ?jmP# v g{?jmG`,/: (MDC) m,G44m2XkG MDC m# v Z9CGVxmDivB,4mD}]mUdZ`M(4 DMS r SMS)"3s!" )9}]is!M}]bVxi=fXkk?jmD}]mUd%d#g{$!s !;%d,G4a5X/f#4mDw}mUdZ`M"}]bVxi"3s!M )9}]is!=fXkk?jmDVxw}9CDw}mUd%d#4mDsM mUdZ`M"}]bVxiM3s!=fXkk?jmDsMmUd%d#Z9 CVxmDivB,4mD}]mUdZ`M"3s!")9}]is!M}]b Vxi=fXkk?jmD}]mUd%d# v 1TxPNNa9/P"XML Pr LOB PDVxm"v ALTER TABLE ATTACH od1,4mONNa9/P"XML Pr LOB PD INLINE LENGTH Xkk? jmOT&a9/P"XML Pr LOB PD INLINE LENGTH %d# v 1+ REQUIRE MATCHING INDEXES Sdk ATTACH PARTITION SddO9 C1,g{?jmOvVNNZ4mO;P%dnDVxw},G4+5X SQL20307N# v g{4mTZ?jmO?vVx(;w};P%dDw},G4kC4mxP,S +D XML GDG# 2. 9C TRUNCATE od4SCmP>}yPP"MEQVdxCmDf"w# 3. 9C LOAD |n4+}]mS=CmP# ZQ|BCmD XML GUd#g{4mODw}4}7 <8,G4}]b\mw+h**z,$b)w}#*K7#*kYw;a,ITZ,SVxYwO8( REQUIRE MATCHING INDEXES#8( REQUIRE MATCHING INDEXES +7#Z4m;Pk?jOVxw }%dDw}DivB,SYw'\#;s,I9C|}Yw"XB"v,SYw# Kb,ZKP,SYw.0>}4mONN``Dw}#``Dw}G4mOzcT Bu~Dw}:Z?jmO;P%dnrk?jmOGVxw}%d#ZKP,SY w.0>}``Dw}a9CYwDKPYH|l# }g,Y(P;vF* ORDERS DVxm,|_P 12 v}]Vx(TZjHD?v B]Ud,CUdkB,SVxPD}]?"?vGVxw}D|s!T0GVxw} D}?IH}# +*B}]VxOD?vVxw}a)9C4mPDmUdj6MTsj6D SYSINDEXPARTITIONS ?5"RZlb=wba)K;)Du~,M a5Xms SQL20408N r SQL20307N# BPwZhvKI\vVDwV`MD;%div,"a)K;)(i=h49=v m%d: (5)9uSd(SYSCAT.TABLES D COMPRESSION P);%d#(SQL20307N -rk 2) *959u;B,k9CBPdP;vod: ALTER TABLE... ACTIVATE VALUE COMPRESSION r_ ALTER TABLE... DEACTIVATE VALUE COMPRESSION *9P9u5%d,k9CBPdP;vod: ALTER TABLE... COMPRESS YES r_ ALTER TABLE... COMPRESS NO mD APPEND ==;%d#(SQL20307N -rk 3) *97S==%d,k9CBPdP;vod: ALTER TABLE ... APPEND ON r_ ALTER TABLE ... APPEND OFF 4mk?jmDzk3;%d#(SQL20307N -rk 4) 4(;vBD4m 4mG|,`v}]Vxr|,Q,SrQpk}]VxDVxm#(SQL20307N - rk 5) 9CTBod4S4mPpk}]Vx,1=;#B;vIS}]Vx*9: ALTER TABLE ... DETACH PARTITION QpkDVx#Vpk4,,1=jITByP=h: 1. 4PNNX*D SET INTEGRITY odTv?"BQpkDStn# Z 13 B DdmMd{`XmTs 185 2. Z V9.7.1 0|_f>P,kH}pkTl===jI#*a_K}LDYH,k7 #pkYw.0TCmxPDyPCJ}4mODGVxw}# g{k*"44P,SYw,IT!q4(B4m# 4mG53m"S<"`Mm"4|rPi/Dm"Q4(DY1mrQywDY1 m#(SQL20307N -rk 6) 4(;vBD4m# ?jmk4mG,;vm#(SQL20307N -rk 7) ;\+m,S=|Tm#7(}7DmTCw4mr?jm# T4mr?jm8(K NOT LOGGED INITIALLY Sd,+4T=vm<8(KSd# (SQL20307N -rk 8) (}"v COMMIT od,Tnu4xPU>GG<,r_(}dkT Bod4+xPKU>GG<: ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY T4mr?jm8(K DATA CAPTURE CHANGES Sd,+4T=vm<8(K Sd#(SQL20307N -rk 9) *T4r*}]6q|D&\DmtC}]6q|D&\,kKPTBod: ALTER TABLE ... DATA CAPTURE CHANGES *TQr*}]6q|D&\Dm{C}]6q|D&\,kKPTBod: ALTER TABLE ... DATA CAPTURE NONE mDV"Sd;%d#4mk?jmDV<|Xk`,#(SQL20307N -rk 10) (iz4(;vBD4m#TZg`v}]bVxDm,;\|DdV<|#*T% ;Vx}]bPDm|DV<|,kKPBPod: ALTER TABLE ... DROP DISTRIBUTION; ALTER TABLE ... ADD DISTRIBUTION(key-specification) ,SYwZd1Yw}1a5Xms(SQL20307N -rk 18) ,SYwa~=XT4m9(k?jmOVxw}T&Dy1Yw}#~=4(b) y1Yw}75*C;)1d4jI#*za)K;v!n,TZ,SYwv=NN 1Yw}DJb14(msu~#C!nF* ERROR ON MISSING INDEXES,G, SYw!n.;#1"zKiv15XDms* SQL20307N(SQLSTATE 428GE,- rk 18)#PX;%dw}DE"EZ\mU>P# ,SYwa>}4mOk?jmODVxw};%dDw}#6pM>}b);%d w}*C;)1d4jI#Z"T,SYw.0,&C>}b)w}# Z,SYwZd,1?jmO;%dw}G(;w},r_ XML w}G9C REJECT INVALID VALUES Sd(eD1,a5Xms(SQL20307N -rk 17) 186 VxM/:8O 1;_P4mONN%dw}D?jmOfZVxw}"R49C ERROR ON MISS- ING INDEXES 1,I$FBPa{: 1. g{?jmO;%dw}G(;w},r_ XML w}G9C REJECT INVALID VALUES S d ( e D , G 4 , S Y w + ' \ , " 5 X m s {" SQL20307N(SQLSTATE 428GE,-rk 17)# 2. g{?jmO;%dw};zc0;cDu~,G4Z,SYwZd,4mOw} Ts;jG*^'#,SYwI&jI,+G,B}]VxOw}Ts;jG*^ '#SET INTEGRITY YwC4X(B,SDVxODw}Ts#(#,bGZ,S }]Vx.s+4PDB;Yw#XB4(b)w}*C;)1d# \mU>+|,PX4mk?jmOw}.dNN;%dnDj8E"# vTdP;vm8(K ORGANIZE BY DIMENSIONS Sd,r_i/,;,# (SQL20307N -rk 11) 4(;vBD4m# PD}]`M (TYPENAME) ;%d#(SQL20408N -rk 1) *|}}]`MD;%d,I"vTBod: ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE... PDIUT (NULLS) ;%d#(SQL20408N -rk 2) *DddP;vmP;%dDPDIUT,"vBPod: ALTER TABLE... ALTER COLUMN... DROP NOT NULL r_ ALTER TABLE... ALTER COLUMN... SET NOT NULL PD~=1!5 (SYSCAT.COLUMNS IMPLICITVALUE) ;f]#(SQL20408N - rk 3) 4(;vBD4m#g{?jmPM4mP<_P~=1!5(R IMPLICITVALUE ; * NULL),G4b=v~=1!5Xkj+%d# g{TZ?jmPD;P,IMPLICITVALUE ;G NULL,"RTZ4mD`&P, IMPLICITVALUE ;G NULL,G4aZTm4P-MZ V9.1 DmxP(Fr_Sf>MZ V9.1 Dm,S} ]Vx.s,IMPLICITVALUE ;G NULL,r*53;*@Z4P-< CREATE TABLE od.sGqmSKP#g{}]b^(7(CPGqGmSDP,G4+dS *gK#mSDPMGw* ALTER TABLE ...ADD COLUMN odDa{4(DP# ZKivB,;Jm4PCod,r*g{JmLxxP,S,CPD5I\a;F 5#Xk+4mPD}]4F=BmP(KPD IMPLICITVALUE * NULL),"+ BmCw,SYwD4m# PDzk3 (COMPOSITE_CODEPAGE) ;%d#(SQL20408N -rk 4) 4(;vBD4m# Z 13 B DdmMd{`XmTs 187 539u1!Sd (COMPRESS) ;%d#(SQL20408N -rk 5) *DdPD539u,"vBfDdP;vod4|};%dDiv: ALTER TABLE ... ALTER COLUMN ... COMPRESS SYSTEM DEFAULT r_ ALTER TABLE ... ALTER COLUMN ... COMPRESS OFF Z ATTACH PARTITION Zd4mw}k?jmVxw}%dDu~ ?jmOVxw}DyPw}|P}%@Dm(byMF5 Z 13 B DdmMd{`XmTs 189 K}]VxPD}]);ITT|xPi5r_TpD==+|w*%@Dm9C; +|,S=m;vVxm(}gz7m);2ITT|xPY]"emMd;T0+ |XB,S=-}P,yPodZ,;$w%* (UOW) ZKPTx("P|B: // Change the RI constraint to informational: ALTER TABLE child ALTER FOREIGN KEY fk NOT ENFORCED; ALTER TABLE parent DETACH PARTITION p0 INTO TABLE pdet; SET INTEGRITY FOR child OFF; // Change the RI constraint back to enforced: ALTER TABLE child ALTER FOREIGN KEY fk ENFORCED; SET INTEGRITY FOR child ALL IMMEDIATE UNCHECKED; // Assuming that the CHILD table does not have any dependencies on partition P0, 190 VxM/:8O // and that no updates on the CHILD table are permitted until this UOW is complete, // no RI violation is possible during this UOW. COMMIT WORK; v g{PNNh*y]Qpk}]VxxPv?,$DStm(b)StmF*Qp kDStm),G4h*TQpkDStmKP SET INTEGRITY od,TcTb )mxPv?,$#{C DB2 V9.7 FP1 r|_"Pf,ZTyPQpkDStmK P SET INTEGRITY ods,l=VxpkNq+9}]Vxxk@"D?jm# 1=l=VxpkYwjI,?jm+;IC# }L 1. *DdVxmT0SCmPpk}]Vx,k"vx DETACH PARTITION SdD ALTER TABLE od# 2. I!: g{#{BpkD@"mO_P`,G<(SYSCAT.COLUMNS ?