?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
???? 3?? ??? DB2LOOK? ??? optimizer access plan ???? ???
????? ?????.
?? ? ??? ???? ?? ????, ??? db2look? ???? ??? ?????,
??? db2exfmt? ?? detail? ??, ???? ???? ??? ? ????.
???? db2look? ???? ?? -f? -fd, ??? db2fopt? ?? ????? ?????.

---

db2look? ??? optimizer access plan ??? I
??
DB2 UDB ?? ???? ??, Optimizer ?? Query planning??? ??? ???
?? ?? ???.
"??? ?? ??? ???? ????? ?? Query access plan? ??? ?? ?? ???."
? ??? ???? ??? ?? ????? ??????? ???? ???
?? ??, ?? ?? access plan?? ?? Query ????? ???? ?? ?? ????
???? ?? ??? ????? access plan? ????? ????.
??, ????? ??? ??? ???? ??? optimizer level? ????,
DB2 ???? ?? ?????.
?? ???? ???, ??? ????? ?? ?????? ???? ????.
?, ? ???? ???? ?? ????, OS LEVEL? ??, DB2 LEVEL? ??? ?????
????.
???, ? ???? ??? ?? ???? ????. ?, ????? ???? ??
????? ????? ??? ?????? ??? ?? ????.
?? ????? ?? detail? ???? ??? ? ????? DB2LOOK ?????
?? ??? ??? ? ????.

? ?? ? ??? ??? ??? DB2LOOK? ??? ?????.

db2look ???? ??
??? ?????? ??? ??? ???? ??????.
Listing 1. optimizer ??? recreate?? ???

db2look -d -l -o storage.out db2look -d -f -fd -o config.out db2look -d -e -a -m -o db2look.out db2look -d -e -a -m -t table1 table2 .... tableX -o table.ddl

?? ? ? ??? DB2LOOK ??? ??? ???.

???, ???????, database??? ?? ?? ??

db2look -d -l -o storage.out

?? DB2LOOK ????? ??? ??? ?? ??? ??? ????.

• -d : database? (??)
• -l : database layout? ??
• -o : ??? ???? output? redirect

????? ???? ??? -l ??? ?????. ?? ???? ??, ?? ???,
DB Partition group(multi-partition??), ???????(temp tablespace??)
??? ??? ????. ???, ?? ???? ?????, ????? ??? ??
large ???? ?????? ??? ? ??? DB2FOPT ???? ?????.
? ???? ???? ? section?? ??? ? ??? ?? ?????.
?, ??? ??? ?? ?? ???????? ??? ?? ??? ?? ????.
?? ?? ? ???? ??? device? ?? ?? ??, ??????? ?? device
???? ???? ?? ???? ??? ?? ?? ????.
??, ???????? ??????? ??? Path? ??? ?? ?? ????.
??? ?????? ???? ?? Path, Device, File?? ??? ??? ????. ??? ??? optimizer? ?? ???????? ??? ??? ?????.
??? ??? ??? ??? ???? ? ? ??? ???.
(???? ???? ?? ? ?? ?? ????)

PREFETCHSIZE 16 EXTENTSIZE 16 OVERHEAD 12.670000 TRANSFERRATE 0.180000

?? ???????? ?????? “managed by database” ?? ????? ??
??? ???. Note: ?? ???? MPP??, DB partition group??? partition ?? ????????
??? ???. ??? ???? ??? ?? ?? ??? ???.
??, ?? MPP????? ???? partition?? ? ???? ??? ???.

?? ????? ???? ??

db2look -d -f -fd -o config.out

??? ????? ?????.
-f : ?? ????? ?? ??? ??, ? ??? ???? -wrapper? server??? ?????.
-fd : opt_buffpage? opt_sortheap? ?? db2fopt? ??, ?? ?? ?????
????? ???? ??
Listing 2. db2look ? ??? Sample output

$ db2look -d sample -f -fd -- No userid was specified, db2look tries to use Environment variable USER -- USER is: SKAPOOR -- This CLP file was created using DB2LOOK Version 8.2 -- Timestamp: Sat Mar 26 00:13:36 EST 2005 -- Database Name: SAMPLE -- Database Manager Version: DB2/6000 Version 8.2.2 -- Database Codepage: 819 -- Database Collating Sequence is: UNIQUE CONNECT TO SAMPLE; -------------------------------------------------------- -- Database and Database Manager configuration parameters -------------------------------------------------------- UPDATE DBM CFG USING cpuspeed 6.523521e-07; UPDATE DBM CFG USING intra_parallel NO; UPDATE DBM CFG USING federated NO; UPDATE DBM CFG USING fed_noauth NO; !db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000; UPDATE DB CFG FOR SAMPLE USING locklist 1000; UPDATE DB CFG FOR SAMPLE USING dft_degree 1; UPDATE DB CFG FOR SAMPLE USING maxlocks 10; UPDATE DB CFG FOR SAMPLE USING avg_appls 1; UPDATE DB CFG FOR SAMPLE USING stmtheap 2048; UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; --------------------------------- -- Environment Variables settings --------------------------------- !db2set DB2_ANTIJOIN=yes; !db2set DB2_INLIST_TO_NLJN=yes; COMMIT WORK; CONNECT RESET; TERMINATE;

-f? -fd ??? ?? ????? ?? ??? ???? ?? ???? key ?????.
Optimizer? access plan? ?????. Listing 2?? ??? output? -fd ?????? ?????.

!db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000;

DB2fopt ???? ???? ?? ??? ????? ???? ??? ??
(db2exfmt??? ??? ???? ?? ??? ??? ???? ? ?? ? ???)
??? ???? ??? ?? ????? optimizer?? ?? ???.
?? ??, ??? ????? ??? ???? ? ???? ???? ?? ?, ??
???? ????? ?? ?, -fd??? ???? ??????? ?? ??? ???
??? ?? ???? ??? ?? ??? ???? ???? optimizer?? ????
db2fopt ???? ?????.
??? sort heap? ???? ?? ???? ?????.
??? db2fopt ???? ?? ?? ????.
Note) db2look? -fd??? ?? ????? ???? ??? ?? ?????? ?????
sortheap? ???? ?? syscat.bufferpools? ???? ?? back??? ?? ???
reset??? ??? ???.

C:>db2fopt Usage: db2fopt update [opt_buffpage ] [opt_sortheap f]   or db2fopt get [opt_buffpage] [opt_sortheap]]

?? opt_buffpage? opt_sortheap? ??? ???? ??? ?? ????.

db2fopt update opt_buffpage opt_sortheap

?? ??:

C:>db2fopt sample update opt_buffpage 50000 opt_sortheap 10000 Update succeeded

? ??? ? terminate? ?? connection? ?? ???.
??? ?? ???? ???? ??? ?? ????.

C:>db2fopt sample get opt_buffpage opt_sortheap

?? ?? ??? ??? ???? ? ?? ????? ???? ????.
??? ??? ??? ??? ?? syscat.bufferpools? back????.
??? SortHeap ???? ?? DB? ??????.

C:>db2fopt sample update opt_buffpage -1 opt_sortheap -1 Update succeeded

??? ??? DB??? terminate, reconnect ??? ???.
??? ?? ?? db2fopt? get??? ???? ?? ??? ?? ???.
C:>db2fopt sample get opt_buffpage opt_sortheap
opt_buffpage value is -1
opt_sortheap value is -1
DBA?, SQL access plan? ?? ?? DB2 SQL Explain Tool(db2exfmt)? ?????.
db2exfmt Tool? explain ???? ??? ?????. ?? db2exfmt? ???
access plan? ??? output? ?? plan? ? ???? ??? ?? ?????.
(Note: ???? ??? ??, ?? ????? DBHEAP? ???? db2look? -f,
-fd????? ? ? ????)
Listing 3. db2exfmt???? sample output

Database Context: ---------------- Parallelism: None CPU Speed: 6.523521e-07 Comm Speed: 100 Buffer Pool size: 50000 Sort Heap size: 10000 Database Heap size: 5120 Lock List size: 1000 Maximum Lock List: 10 Average Applications: 1 Locks Available: 7849 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 1 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1

db2exfmt output? ????, access plan?? ?????, Optimizer plan? ???
?? ?? ????? ?? ???? ? ? ????.
Note: ?? db2look -f? ?? ?? ??? ????? ??? ??? ?? ??
???? Missing? ??? ??? ??? ????. ????? ??? ???? ???
????? ??? ????, ????? close?? ?? ????.
Listing 4. access plan? ?? ?? ?? ????? ??

1) RETURN: (Return Result) Cumulative Total Cost: 57.6764 Cumulative CPU Cost: 191909 Cumulative I/O Cost: 2 Cumulative Re-Total Cost: 5.37264 Cumulative Re-CPU Cost: 134316 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 26.9726 Estimated Buffer pool Buffers: 2 Arguments: --------- BLDLEVEL: (Build level) DB2 v8.1.0.80 : s041221 ENVVAR : (Environment Variable) DB2_ANTIJOIN=yes DB2_INLIST_TO_NLJN = yes STMTHEAP: (Statement heap size) 2048

Data definition language (DDL) ??
??? db2look ???? ??? ????? ???? ?? DB ????? ????
?? DDL? ?????.

db2look -d -e -a -m -o db2look.out

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

• -a : ?? creator? ??? ??????. ?? ??? ????? -u ??? ?????.
• -e : DB? ???? ?? ??? DDL ??? ?????. ? ??? DDL?? ??? ????? ?????. ? ????? DB ????? ?? ???? ?? ?? DB?? ??? ? ????.
• -m : ?? ???? db2look ????? ?????. ? ??? SQL update?? ??? ????? ?????. ? SQL UPDATE?? ?? ??? ?????. ? ????? ?? DB? ???? ?? ?? DB??? ??? ? ????. -m ??? ????, -p, -g, -s ??? ?????.

Database subset? ?? ??? DDL ??
?? ???? ??? ????? ??? DDL ??? ?? ??? ???? ?????.

db2look -d -e -a -m -t .. -o table.ddl

???, ??? ??? ????? ?????.

• -t : ??? ???? ?? ??? ?????. ???? ???? 30? ??? ? ????.
• -z : ????. ?? -z? -a? ? ? ????, -z? ?????.

Note: -m ??? ?????. ? ??? system ?????? ?? ??? ?????.
? ??? ???????? ???????? ??? ?? ??????? ??????
??? ? ?? ????.

---

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