Front cover -- Contents -- Figures -- Tables -- Examples -- Notices -- Trademarks -- Preface -- What this redbook is about -- The contents -- The audience -- The team that wrote this redbook -- Become a published author -- Comments welcome -- Part 1 Introduction -- Chapter 1. Information integration concepts -- 1.1 The business need -- 1.1.1 Business challenges -- 1.1.2 Business on demand -- 1.1.3 From on demand to grid computing -- 1.1.4 From grid to federated data -- 1.2 IBM's information integration -- 1.2.1 Consolidation and federation -- 1.2.2 Transparency, transparency -- 1.3 The DB2 Information Integrator family of products -- 1.3.1 DB2 Information Integrator -- 1.3.2 DB2 Information Integrator for Content -- 1.3.3 Extension through partnership -- 1.3.4 DB2 Information Integrator and Life Sciences -- 1.3.5 The evolution -- Chapter 2. IBM DB2 Information Integrator V8.1 -- 2.1 Overview -- 2.2 DB2 Information Integrator functions and objects -- 2.2.1 Database server -- 2.2.2 Federated database -- 2.2.3 Configuring the federated system -- 2.2.4 Replication -- 2.2.5 Wrapper development kit -- Part 2 Configuring the federated data solution -- Chapter 3. The case study -- 3.1 The federated system -- 3.1.1 The base system -- 3.1.2 The DB2 Federated Data -- 3.1.3 Information Integrator for relational data sources -- 3.1.4 The final picture -- 3.2 Setting up the environment -- 3.2.1 The starting point -- 3.2.2 Connecting DB2 for z/OS and Informix data sources -- 3.2.3 Installing Information Integrator -- 3.2.4 Connecting Oracle data source -- 3.2.5 Connecting Microsoft SQL Server data source -- 3.2.6 Connecting XML data source -- 3.2.7 Connecting flat file data source -- 3.2.8 Connecting Microsoft Excel data source -- 3.2.9 System environment: Summary -- 3.3 The data and the application -- 3.3.1 Data model -- 3.3.2 Data distribution.

3.3.3 Data access -- Chapter 4. Configuring DB2 Federated Data support -- 4.1 Background information -- 4.2 Setting up the DB2 UDB instance on AIX -- 4.2.1 Pre-installation requirements -- 4.2.2 32-bit or 64-bit DB2 ESE installation and instance -- 4.2.3 Installation instructions -- 4.2.4 DB2 database configuration for federation -- 4.3 Integrating DB2 UDB for z/OS -- 4.3.1 Cataloging DB2 for z/OS -- 4.3.2 Creating the DB2 for z/OS wrapper -- 4.3.3 Creating the DB2 for z/OS server -- 4.3.4 Altering DB2 for z/OS server definition and server options -- 4.3.5 Creating DB2 for z/OS user mappings -- 4.3.6 Altering DB2 for z/OS user mappings -- 4.3.7 Creating DB2 for z/OS nicknames -- 4.3.8 Altering DB2 for z/OS nicknames -- 4.4 Integrating Informix Dynamic Server -- 4.4.1 Informix client configuration -- 4.4.2 Informix wrapper libraries -- 4.4.3 Creating the Informix wrapper -- 4.4.4 Creating the Informix server -- 4.4.5 Altering Informix server definition and server options -- 4.4.6 Creating Informix user mappings -- 4.4.7 Altering Informix user mappings -- 4.4.8 Creating Informix nicknames -- 4.4.9 Altering Informix nicknames -- 4.5 Integrating DB2 UDB for iSeries -- Chapter 5. Installing and configuring DB2 Information Integrator -- 5.1 General prerequisites -- 5.2 Installing DB2 Information Integrator -- 5.2.1 Start the installer -- 5.2.2 Installation response file examples -- 5.2.3 Installation hints, tips, and techniques -- 5.3 Applying installed wrappers to instances -- 5.4 Integrating Oracle 9i -- 5.4.1 Configuration information for Oracle 9i wrapper -- 5.4.2 Creating the Oracle wrapper -- 5.4.3 Creating the Oracle server -- 5.4.4 Altering Oracle server definition and server options -- 5.4.5 Creating Oracle user mappings -- 5.4.6 Altering Oracle user mappings -- 5.4.7 Creating Oracle nicknames -- 5.4.8 Altering Oracle nicknames.

5.5 Integrating Microsoft SQL Server 2000 -- 5.5.1 Microsoft SQL Server client configuration -- 5.5.2 Creating the Microsoft SQL Server wrapper -- 5.5.3 Creating the Microsoft SQL Server server -- 5.5.4 Altering Microsoft SQL Server definition and server options -- 5.5.5 Creating Microsoft SQL Server user mappings -- 5.5.6 Altering Microsoft SQL Server user mappings -- 5.5.7 Creating Microsoft SQL Server nicknames -- 5.5.8 Altering Microsoft SQL Server nicknames -- 5.6 Integrating XML data source -- 5.6.1 Introduction -- 5.6.2 Configuration information for XML wrapper -- 5.6.3 Creating the XML wrapper -- 5.6.4 Creating the XML server -- 5.6.5 Creating XML nicknames -- 5.6.6 Altering XML nicknames -- 5.7 Integrating table-structured files -- 5.7.1 Introduction -- 5.7.2 Table-structured file configuration information -- 5.7.3 Creating the table-structured file wrapper -- 5.7.4 Creating the table-structured file server -- 5.7.5 Creating table-structured file nicknames -- 5.7.6 Altering table-structured file nicknames -- 5.8 Integrating Microsoft Excel -- 5.8.1 Introduction -- 5.8.2 Configuration information for ODBC wrapper -- 5.8.3 Setting Excel ODBC on Windows -- 5.8.4 Setting OpenLink client on AIX -- 5.8.5 Creating ODBC wrappers -- 5.8.6 Creating ODBC servers -- 5.8.7 Altering the ODBC server -- 5.8.8 Creating the ODBC nickname -- 5.8.9 Altering ODBC nicknames -- 5.9 Maintaining -- 5.9.1 Applying FixPaks -- 5.9.2 Updating nickname statistics -- 5.9.3 Schema changes at data sources -- 5.9.4 Nicknames used in views and packages -- 5.10 Troubleshooting -- 5.10.1 Errors linking with data source clients -- 5.10.2 Errors when defining and using federated objects -- 5.10.3 Information to gather -- Part 3 Performance concepts with DB2 Information Integrator -- Chapter 6. A performance tutorial -- 6.1 Federated query performance -- 6.1.1 Performance factors.

6.1.2 The pushdown concept -- 6.1.3 Optimizing a federated query -- 6.1.4 Pushdown analysis and cost optimization -- 6.1.5 Importance of pushdown -- 6.1.6 Interpreting federated query execution plans -- 6.2 Tuning a query on a single data source -- 6.2.1 What is a good query? -- 6.2.2 Evaluating the execution plan with Explain -- 6.2.3 Federated server options for best performance -- 6.2.4 Analyzing performance of a single remote source query -- 6.3 Tuning a query on multiple data sources -- 6.3.1 Multiple source queries -- 6.3.2 A simple distributed two-source query -- 6.3.3 Execution plan for the simple distributed query -- 6.3.4 Performance of distributed queries -- 6.3.5 Federated queries with nicknames and partitioned tables -- 6.3.6 Summary of distributed query performance -- 6.4 Performance and availability with MQTs -- 6.5 Federated query performance checklist -- 6.5.1 Phase 1 -- 6.5.2 Phase 2 -- 6.5.3 Phase 3 -- Chapter 7. Our performance tools -- 7.1 DB2 Explain -- 7.1.1 Overview -- 7.1.2 DB2 Explain facilities -- 7.1.3 Explain tables -- 7.1.4 Explaining the queries -- 7.2 db2batch -- 7.3 Get Statistics utility: get_stats -- Part 4 Exploiting performance options -- Chapter 8. National language support -- 8.1 Introduction to code page settings -- 8.1.1 Remote relational data source -- 8.1.2 DB2 Information Integrator -- 8.1.3 DB2 Client -- 8.2 Supporting a non-default language -- 8.2.1 DB2 Information Integrator setting -- 8.2.2 DB2 Client setting -- 8.2.3 Conclusion -- 8.3 Adding a new language to an existing system -- 8.3.1 Remote data source code page definition -- 8.3.2 DB2 Information Integrator setting -- 8.3.3 DB2 Client setting -- 8.3.4 Conclusion -- Chapter 9. Nickname statistics -- 9.1 Overview -- 9.2 Nicknames with missing statistics -- 9.2.1 Check statistics for nicknames -- 9.2.2 Create the access plan -- 9.2.3 Perform the query.

9.3 Nicknames with statistics -- 9.3.1 Option 1: Update stats and recreate nickname -- 9.3.2 Option 2: Run get_stats utility -- 9.3.3 Check statistics for nicknames again -- 9.3.4 Create the access plan -- 9.3.5 Perform the query -- 9.4 Conclusion -- 9.4.1 Comparing the access plans -- 9.4.2 Summary -- Chapter 10. Major server options with relational data sources -- 10.1 Server option db2_maximal_pushdown -- 10.1.1 Overview -- 10.1.2 DB2_MAXIMAL _PUSHDOWN set to N (default) -- 10.1.3 DB2_MAXIMAL _PUSHDOWN set to Y -- 10.1.4 Conclusion on DB2_maximal_pushdown -- 10.1.5 DB2_MAXIMAL_PUSHDOWN vs. PUSHDOWN -- 10.2 Server option collating_sequence -- 10.2.1 Overview -- 10.2.2 COLLATING_SEQUENCE set to N (default) -- 10.2.3 COLLATING_SEQUENCE set to Y -- 10.2.4 Conclusion on collating_sequence -- 10.3 Oracle server option varchar_no_trailing_blanks -- 10.3.1 Trailing blanks with DB2, Informix, and SQL Server -- 10.3.2 Trailing blanks with Oracle -- 10.3.3 VARCHAR_NO_TRAILING_BLANKS set to N -- 10.3.4 VARCHAR_NO_TRAILING_BLANKS set to Y -- Chapter 11. Using data type mappings -- 11.1 Overview -- 11.2 Step 1: Explicit cast on nickname column -- 11.3 Step 2: Accommodate the default mapping -- 11.4 Step 3: Altering local column data type -- Chapter 12. Using function mappings -- 12.1 Overview -- 12.2 Mapping user defined functions -- Chapter 13. Major server options with non relational data sources -- 13.1 Overview -- 13.2 Pushdown with Excel data sources -- 13.2.1 PUSHDOWN set to N (default) -- 13.2.2 PUSHDOWN set to Y -- 13.3 Table-structured files parameter - Sorted -- 13.3.1 Nickname parameter SORTED set to N (default) -- 13.3.2 Nickname parameter SORTED set to Y -- 13.3.3 Conclusion -- Chapter 14. Using index specifications -- 14.1 Overview -- 14.2 Table acquires new index after nickname creation -- 14.3 Nicknames over remote views.

14.3.1 What about utilizing an existing index?.