?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
?? ?????? ??????, XpertMon for DB2 UDB V2.0?
??????????(TTA) ??? GS??(Good Software) ? ???????.
GS??? ??? ?????? ??, ????? ???? ?? ?? ??? ??,
??? ?3? ????? ?????.
???? ???? ??? ?? ??????.
???? ??? ??????? V8? ??? ?? ? MDC(Multi-Dimensional Clustering)?
?? ????? ?????.
???? MDC? ?? ???? ??? ???, ??? ??????? V8.2.2? ???
MDC ??? ?? ?????? ?????.

---

MDC (Multi-Dimensional Clustering)
?? Clustering
????? ? ????? ??? ???? Clustering Index ? ????,
?? Index ??? ?? ??? ??? ???? ???? ??? ????????.
DB ???? ?? ??? ????, ?? ???? ???? ???? ??? ???,
???? I/O ? ?? ??? ??? ? ?? ??? ??????.
??? ? ??? ?? insert ???? ??? ???? ??? ???? ?? ????
?? row? ?? ?? ???? ????, REORG? ???? ???, I/O cost? ???
??? ??? ? ?? ??? ?????.

V8.1 ? ??? Clustering ??
MDC ??? ????, ??? ?? ?? ?? ???? ???? Clustering ? ? ????.
??? ?? ORGANIZE BY SQL ?? ????, ???? Clustering ?? ??? ???? ????.
?? ?? ??? ?? PRODUCT_SALES ???? region? type ??? ???? ?? clustering ??? ???.


??? ??
??? ???? ?????.

REGION	TYPE	DATE	PRODUCT_CODE
NORTH	SOFTWARE	01/02/03	3443
NORTH	SOFTWARE	03/03/03	5446
NORTH	HARDWARE	10/12/02	6654
NORTH	ACCESSORIES	21/03/03	5566
NORTH	ACCESSORIES	14/01/03	3443
EAST	HARDWARE	16/11/02	2344
EAST	ACCESSORIES	02/11/02	6543
EAST	ACCESSORIES	23/01/03	7567
EAST	ACCESSORIES	14/02/03	3345
SOUTH	HARDWARE	01/12/02	6632
SOUTH	SOFTWARE	31/01/03	3443
SOUTH	SOFTWARE	21/12/02	5674
SOUTH	SOFTWARE	25/12/02	8734
WEST	HARDWARE	01/01/03	2323
WEST	HARDWARE	14/02/03	8734

MDC ???? Extent ??? ??? Block ?? ?????.
? Block?? Cell??? ??? ?? clustering dimension row ??? ?????.
?? ??? ??? ??? ?? region ? product type ??? ?????.
??? region? product type ? unique ??? 8? ( ?, cell? 8?) ???,
? cell ?? ???? block ? ???? ??, ??? 8?? block ? ??? ????.
INSERT ??? ?? row? clustering dimension ?? ????, ??? ?? block??
???? ?????. ?? ?? block? ??? ??? ???, ??? block? ?????
??? ??? ??? ???? ??? ???? ?????.

MDC ??? ??
?? ??? optimizer ? ??? MDC ???? ???????
MDC ???? ????, ????? 2?? ??? ??? ???? ?????.
? ? ?????? ???? RID ???? ?????, index ? ???? point ?
?? row? ?? block? ????? ?? ????.
??? ??? “block index” ? ???? ????? ??? ? ????.
?? “composite block index “ ( ?? ?? ???, INDEXTYPE= BLOK ) ?
?? dimension ? ?? 1? ?????.
? index? insert ?? ?? row? ?? block? ??????? ????? ?????.
?? ??? dimension ?? “dimension block index “( INDEXTYPE=DIM) ? 1?? ?????.
? ???? ??? dimension ?? ??? ??, ?? dimension ?? ??? block?
???? ???? ??? ????.

??? ?? ???? ????? 3?? ???? ??? ????.
( 1?? “composite block index” ? 2?, ? REGION ? TYPE ? ?? ??? “dimension block index” ) ? ???? ???? ????, REGION ? TYPE? ?? ??? ???? ?????
clustering ? ? ?? ???.
??, ???? ???? ? ?? pointer? ??? row? ?? ? ?? ?? ???,
? ?? index pointer? block?? ?? ?? row?? ?? ? ????,
access ????? ?? ??????.

Explain ??

• Region? NORTH? ?? Row? PRODUCT_CODE? ???? ??? ?????.
  
• Explain ? ???? ?? ??? Access path? ?????.
  
  

?? Explain ???? ???? ???, REGION ? NORTH? ?? ??? ???? ??
REGION ??? ???? dimension index “SYSIBM.SQL050803104118420” ? ??????
? ? ????.
? ??? ????? ??? Access ??? ? ???? ???? ?? ?? ???? ????.
?? block index scan? ? ?, DB2 ? “block look-ahead prefetching” ???
??? ? ????.
??? prefetcher? ??? ?? ????? ???? ??? ???? prefetch ? ??
??? ? ??? ?? ID? ???? ??? ???? ?? Extent? ???? ?? ?? ?????.

Report ??? ??? ?? ?? ??
???? ???? ?? ????? MDC ?? ????? ?????? ?????.

???? ???? ???? ???? ??? MDC ? block index ? ???? ??? ?????.
??? ??? ?? ?????, ??? ??? MDC ??????.
?? ???? ??, ????? ???? ???? CPU time? ?????? ? ???
? ? ????.
??? index ? logical read ? ( 265: 9) ? MDC ? ???? ?? ?????.
???, MDC? ??, Index? ? ???? ??? ?????? ???? ? ? ??
??? ??? ??? ??? ? ??? ? ? ????.

??
?? ???? ???, MDC ? ???? ?? ??? ????.
MDC? ????? ?? ?? ???? ???? Clustering ???.
??? MDC? ???? block index ? ???? ????? ? ??? ?? ????
?? Business Intelligence? Data Warehousing Application ???? ??? ???
Query ??? ??? ???? ? ????.

---

XpertMon for DB2 UDB V2
DB ???
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr