Data federation with IBM DB2 Information Integrator V8.1 / [Paolo Bruni ... et al.].

Format:

Book

Contributor:

Bruni, Paolo.

International Business Machines Corporation. International Technical Support Organization.

Series:

IBM redbooks.

IBM redbooks

Language:

English

Subjects (All):

Relational databases.

Information resources management.

IBM Database 2.

Physical Description:

xxxviii, 424 p. : ill.

Edition:

1st ed.

Place of Publication:

[S.l.] : IBM, International Technical Support Organization, 2003.

Language Note:

English

Contents:

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?.

Notes:

"October 2003."

Includes bibliographical references and index.

OCLC:

137342253