Figures 1-1 1-2 1-3 1-4 1-5 1-6 2-1 2-2 3-1 3-2 3-3 3-4 3-5 3-6 3-7 3-8 3-9 3-10 3-11 3-12 3-13 3-14 3-15 4-1 4-2 4-3 4-4 4-5 4-6 4-7 4-8 4-9 4-10 4-11 4-12 4-13 5-1 5-2 Data federation concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Overview of IBM products for information integration . . . . . . . . . . . . . . 10 Data federation technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Components of a federated system . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Basic steps in configuring a federated system. . . . . . . . . . . . . . . . . . . . 19 DB2 II topologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Performance management cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 A typical problem determination methodology . . . . . . . . . . . . . . . . . . . . 38 Federated query processing flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 SQL Compiler query analysis flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Query execution flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Performance considerations overview . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Federated query performance elements . . . . . . . . . . . . . . . . . . . . . . . . 59 Federated serer considerations topics . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Wrapper architecture - Fenced and trusted . . . . . . . . . . . . . . . . . . . . . . 63 Query optimization topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Nickname statistics collected by data source . . . . . . . . . . . . . . . . . . . . 72 MQT/AST look-aside concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Cache table concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Overflowed sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Non-overflowed piped sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Database manager snapshot showing sort monitor elements . . . . . . . . 97 Database snapshot showing sort monitor elements . . . . . . . . . . . . . . . 97 A typical DB2 II environment and hypotheses hierarchy . . . . . . . . . . . 117 DB2 II hypotheses hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Triggering event determines entry into DB2 II hypotheses hierarchy . 122 Statistics Update in DB2 Control Center . . . . . . . . . . . . . . . . . . . . . . . 153 DB2 snapshot monitor syntax and data collection . . . . . . . . . . . . . . . . 163 Federated test environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 TPCD tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 ping DB2 II federated server and Oracle data source server (azov) . . 172 vmstat command output from the federated server . . . . . . . . . . . . . . . 173 iostat command output from federated server . . . . . . . . . . . . . . . . . . . 174 lsps command output from the federated server . . . . . . . . . . . . . . . . . 174 ps -ef command output from the federated server . . . . . . . . . . . . . . . . 175 ps aux command from federated server . . . . . . . . . . . . . . . . . . . . . . . 175 Capacity planning procedure overview . . . . . . . . . . . . . . . . . . . . . . . . 382 Contents of FEDWH.FEDWH_SNAPSHOT_DYN_SQL table (1 of 3). 393 © Copyright IBM Corp. 2004. All rights reserved. vii