Data Federation With IBM DB2 Information Integrator V8.1

by Bruni, Paolo; Arnaudies, Francis; Bennett, Amanda; Englert, Susanne; Keplinger, Gerhard

• ISBN13: 9780738499161
• ISBN10: 0738499161
• Format: Paperback
• Copyright: 2003-10-01
• Publisher: Vervante

Table of Contents

Figures	xi
Tables	xv
Examples	xvii
Notices	xxi
Trademarks	xxii
Preface	xxiii
What this redbook is about	xxiii
The contents	xxiii
The audience	xxv
The team that wrote this redbook	xxvi
Become a published author	xxviii
Comments welcome	xxviii
Part 1. Introduction	1	(50)
Information integration concepts	3	(18)
The business need	4	(5)
Business challenges	4	(1)
Business on demand	5	(1)
From on demand to grid computing	6	(2)
From grid to federated data	8	(1)
IBM's information integration	9	(4)
Consolidation and federation	10	(1)
Transparency, transparency	11	(2)
The DB2 Information Integrator family of products	13	(8)
DB2 Information Integrator	14	(1)
DB2 Information Integrator for Content	15	(1)
Extension through partnership	16	(1)
DB2 Information Integrator and Life Sciences	17	(1)
The evolution	18	(3)
IBM DB2 Information Integrator V8.1	21	(30)
Overview	22	(5)
DB2 Information Integrator functions and objects	27	(24)
Database server	29	(1)
Federated database	29	(3)
Configuring the federated system	32	(13)
Replication	45	(2)
Wrapper development kit	47	(4)
Part 2. Configuring the federated data solution	51	(188)
The case study	53	(20)
The federated system	54	(4)
The base system	54	(1)
The DB2 Federated Data	55	(1)
Information Integrator for relational data sources	56	(1)
The final picture	57	(1)
Setting up the environment	58	(10)
The starting point	58	(1)
Connecting DB2 for z/OS and Informix data sources	59	(1)
Installing Information Integrator	60	(1)
Connecting Oracle data source	61	(1)
Connecting Microsoft SQL Server data source	62	(1)
Connecting XML data source	63	(1)
Connecting flat file data source	64	(1)
Connecting Microsoft Excel data source	65	(1)
System environment: Summary	66	(2)
The data and the application	68	(5)
Data model	68	(2)
Data distribution	70	(1)
Data access	71	(2)
Configuring DB2 Federated Data support	73	(48)
Background information	74	(1)
Setting up the DB2 UDB instance on AIX	74	(17)
Pre-installation requirements	75	(1)
32-bit or 64-bit DB2 ESE installation and instance	76	(1)
Installation instructions	76	(11)
DB2 database configuration for federation	87	(4)
Integrating DB2 UDB for z/OS	91	(14)
Cataloging DB2 for z/OS	92	(1)
Creating the DB2 for z/OS wrapper	93	(1)
Creating the DB2 for z/OS server	94	(4)
Altering DB2 for z/OS server definition and server options	98	(1)
Creating DB2 for z/OS user mappings	99	(2)
Altering DB2 for z/OS user mappings	101	(1)
Creating DB2 for z/OS nicknames	102	(3)
Altering DB2 for z/OS nicknames	105	(1)
Integrating Informix Dynamic Server	105	(13)
Informix client configuration	106	(1)
Informix wrapper libraries	106	(2)
Creating the Informix wrapper	108	(1)
Creating the Informix server	109	(3)
Altering Informix server definition and server options	112	(1)
Creating Informix user mappings	113	(2)
Altering Informix user mappings	115	(1)
Creating Informix nicknames	115	(3)
Altering Informix nicknames	118	(1)
Integrating DB2 UDB for iSeries	118	(3)
Installing and configuring DB2 Information Integrator	121	(118)
General prerequisites	122	(3)
Installing DB2 Information Integrator	125	(37)
Start the installer	126	(31)
Installation response file examples	157	(2)
Installation hints, tips, and techniques	159	(3)
Applying installed wrappers to instances	162	(6)
Integrating Oracle 9i	168	(13)
Configuration information for Oracle 9i wrapper	168	(3)
Creating the Oracle wrapper	171	(1)
Creating the Oracle server	172	(4)
Altering Oracle server definition and server options	176	(1)
Creating Oracle user mappings	176	(2)
Altering Oracle user mappings	178	(1)
Creating Oracle nicknames	179	(2)
Altering Oracle nicknames	181	(1)
Integrating Microsoft SQL Server 2000	181	(14)
Microsoft SQL Server client configuration	182	(3)
Creating the Microsoft SQL Server wrapper	185	(1)
Creating the Microsoft SQL Server server	186	(3)
Altering Microsoft SQL Server definition and server options	189	(1)
Creating Microsoft SQL Server user mappings	190	(2)
Altering Microsoft SQL Server user mappings	192	(1)
Creating Microsoft SQL Server nicknames	192	(3)
Altering Microsoft SQL Server nicknames	195	(1)
Integrating XML data source	195	(14)
Introduction	195	(2)
Configuration information for XML wrapper	197	(1)
Creating the XML wrapper	197	(1)
Creating the XML server	198	(1)
Creating XML nicknames	199	(10)
Altering XML nicknames	209	(1)
Integrating table-structured files	209	(9)
Introduction	209	(2)
Table-structured file configuration information	211	(1)
Creating the table-structured file wrapper	212	(1)
Creating the table-structured file server	212	(1)
Creating table-structured file nicknames	213	(4)
Altering table-structured file nicknames	217	(1)
Integrating Microsoft Excel	218	(14)
Introduction	218	(3)
Configuration information for ODBC wrapper	221	(1)
Setting Excel ODBC on Windows	222	(1)
Setting OpenLink client on AIX	223	(1)
Creating ODBC wrappers	223	(2)
Creating ODBC servers	225	(2)
Altering the ODBC server	227	(1)
Creating the ODBC nickname	228	(4)
Altering ODBC nicknames	232	(1)
Maintaining	232	(3)
Applying Fix Paks	232	(1)
Updating nickname statistics	233	(1)
Schema changes at data sources	234	(1)
Nicknames used in views and packages	234	(1)
Troubleshooting	235	(4)
Errors linking with data source clients	235	(2)
Errors when defining and using federated objects	237	(1)
Information to gather	238	(1)
Part 3. Performance concepts with DB2 Information Integrator	239	(52)
A performance tutorial	241	(42)
Federated query performance	242	(11)
Performance factors	242	(1)
The pushdown concept	243	(3)
Optimizing a federated query	246	(3)
Pushdown analysis and cost optimization	249	(1)
Importance of pushdown	250	(1)
Interpreting federated query execution plans	251	(2)
Tuning a query on a single data source	253	(11)
What is a good query?	253	(1)
Evaluating the execution plan with Explain	254	(6)
Federated server options for best performance	260	(3)
Analyzing performance of a single remote source query	263	(1)
Tuning a query on multiple data sources	264	(9)
Multiple source queries	264	(1)
A simple distributed two-source query	264	(1)
Execution plan for the simple distributed query	265	(1)
Performance of distributed queries	266	(5)
Federated queries with nicknames and partitioned tables	271	(1)
Summary of distributed query performance	272	(1)
Performance and availability with MQTs	273	(2)
Federated query performance checklist	275	(8)
Phase 1	276	(2)
Phase 2	278	(2)
Phase 3	280	(3)
Our performance tools	283	(8)
DB2 Explain	284	(4)
Overview	284	(1)
DB2 Explain facilities	285	(1)
Explain tables	286	(1)
Explaining the queries	287	(1)
db2batch	288	(1)
Get Statistics utility: get_stats	289	(2)
Part 4. Exploiting performance options	291	(82)
National language support	293	(12)
Introduction to code page settings	294	(5)
Remote relational data source	295	(1)
DB2 Information Integrator	296	(1)
DB2 Client	297	(2)
Supporting a non-default language	299	(1)
DB2 Information Integrator setting	299	(1)
DB2 Client setting	299	(1)
Conclusion	300	(1)
Adding a new language to an existing system	300	(5)
Remote data source code page definition	300	(1)
DB2 Information Integrator setting	301	(1)
DB2 Client setting	302	(1)
Conclusion	303	(2)
Nickname statistics	305	(12)
Overview	306	(2)
Nicknames with missing statistics	308	(3)
Check statistics for nicknames	308	(1)
Create the access plan	309	(1)
Perform the query	310	(1)
Nicknames with statistics	311	(2)
Option 1: Update stats and recreate nickname	311	(1)
Option 2: Run get_stats utility	312	(1)
Check statistics for nicknames again	312	(1)
Create the access plan	313	(1)
Perform the query	313	(1)
Conclusion	313	(4)
Comparing the access plans	313	(2)
Summary	315	(2)
Major server options with relational data sources	317	(16)
Server option db2_maximal_pushdown	318	(5)
Overview	318	(1)
DB2_Maximal_Pushdown set to N (default)	319	(1)
DB2_Maximal_Pushdown set to Y	320	(2)
Conclusion on DB2_maximal_pushdown	322	(1)
DB2_Maximal_Pushdown vs. Pushdown	323	(1)
Server option collating_sequence	323	(4)
Overview	323	(1)
Collating_Sequence set to N (default)	324	(1)
Collating_Sequence set to Y	325	(2)
Conclusion on collating_sequence	327	(1)
Oracle server option varchar_no_trailing_blanks	327	(6)
Trailing blanks with DB2, Informix, and SQL Server	328	(1)
Trailing blanks with Oracle	329	(1)
Varchar_No_Trailing_Blanks set to N	329	(1)
Varchar_No_Trailing_Blanks set to Y	330	(3)
Using data type mappings	333	(8)
Overview	334	(1)
Step 1: Explicit cast on nickname column	335	(2)
Step 2: Accommodate the default mapping	337	(1)
Step 3: Altering local column data type	338	(3)
Using function mappings	341	(6)
Overview	342	(1)
Mapping user defined functions	343	(4)
Major server options with non relational data sources	347	(8)
Overview	348	(1)
Pushdown with Excel data sources	349	(2)
Pushdown set to N (default)	349	(1)
Pushdown set to Y	350	(1)
Table-structured files parameter - Sorted	351	(4)
Nickname parameter Sorted set to N (default)	351	(1)
Nickname parameter Sorted set to Y	352	(1)
Conclusion	353	(2)
Using index specifications	355	(10)
Overview	356	(1)
Table acquires new index after nickname creation	357	(1)
Nicknames over remote views	357	(8)
What about utilizing an existing index?	361	(3)
Adding index statistics to nicknames for views	364	(1)
Using materialized query tables	365	(8)
Overview	366	(1)
Accessing data without MQT	367	(1)
Accessing data with MQT	368	(5)
Refreshing data in MQTs with nicknames	371	(2)
Part 5. Appendixes	373	(34)
Appendix A. Case study	375	(20)
A.1 DDL for DB2 for AIX	377	(5)
A.2 Script for building the case study	382	(6)
A.3 Data sources/wrapper/server/schema names	388	(1)
A.4 DB2 list db directory	388	(1)
A.5 DB2 list dcs directory	389	(1)
A.6 DB2 list Node Directory	390	(1)
A.7 /home/informix/ids92/etc/sqlhosts	391	(1)
A.8 /oracle9ir2/network/admin/tnsnames.ora	391	(1)
A.9 /home/db2fed32/.odbc.ini	391	(1)
A.10 /home/db2fed32/sqllib/cfg/db2dj.ini	392	(1)
A.11 Idb2set -all	393	(1)
A.12 env	393	(2)
Appendix B. Unicode tutorial	395	(12)
B.1 Unicode	396	(5)
B.2 Locale	401	(2)
B2.1 Understanding locale	403	(4)
Glossary	407	(2)
Abbreviations and acronyms	409	(4)
Related publications	413	(1)
IBM Redbooks	413	(1)
Other publications	413	(1)
Online resources	414	(1)
How to get IBM Redbooks	415	(1)
Help from IBM	415	(2)
Index	417