accelerate your business DB 2 Information Integrator Software

accelerate your business DB 2 Information Integrator Software Jaffa Sztejnbok IT Specialist, Information Management Global Technology Unit jaffas@il. ibm. com IBM Israel Software Symposium 2003 © 2002 IBM Corporation

accelerate your business Agenda § What is Enterprise Information Integration § Without Information Integrator § Data Challenges § Complementary Information Integration Approaches § IBM DB 2 Information Integration Products and Value § IBM’s Information Integrator 8. 1 § Demo IBM Israel Software Symposium 2003 2

accelerate your business What is Enterprise Information Integration ? § Provides access to diverse, distributed, and real-time data as if it were a single source, no matter where it resides. § Helps businesses : –Shorten application development time –Improve productivity and application efficiency –Leverage existing data assets for the benefit of the business IBM Israel Software Symposium 2003 3

accelerate your business Without Data Federation IBM Israel Software Symposium 2003 4

accelerate your business Data Challenges Variety, Velocity, and Volume n New composite applications need data from multiple sources ƒ Consumers expect holistic, personalized, and value-added content ƒ Relational, XML, packaged applications, content repositories, file systems all contain critical business information n Increasing emphasis on current data ƒ Real-time analytics ƒ Business activity monitoring n Petabytes will be the measure of available online data ƒ All client interactions are important ( e. g. instant messages, audio records, Web traffic, …) ƒ Internet and intranet content IBM Israel Software Symposium 2003 5

accelerate your business Complementary Information Integration Approaches § Consolidate data for local access – – – Data warehouses Operational data stores Production applications Creating additional reference copies Typically managed by ETL (Extract, Transform, Load) or replication technologies § Integrated access to distributed sources, Distributed Access – Real time data, e. g. , stock quotes , Extending a data warehouse with real-time data – Data changes rapidly – Wide heterogeneity in data to be accessed , relational and non-relational format – Data which is not practical or possible to copy and when movement of data is small IBM Israel Software Symposium 2003 6

accelerate your business Data Sources IBM DB 2 Information Integration Products DB 2 Information Integrator § SQL programming model § Leverage SQL skills and tools § Federated data server and replication server data warehouses DB 2 Information Integrator for Content § Content programming model § Leverage CM skills and tools § Federated data server, text mining, and workflow engine Content Sources spreadsheets relational databases @ office e-mail IBM Israel Software Symposium 2003 … reports fax content repositories Extende d Search Sources 7

accelerate your business DB 2 Information Integrator 8. 1 A Federated Data Server – Query distributed data as if it where a single source § Define integrated view across diverse and distributed data – Wide range of data and content sources – Extensible to virtually any data source § Query as if a single source – Use standard SQL query and SQL expressions – Include text semantics in the search – Surface specialized functions into SQL – Leverage query optimization and caching Integrated SQLView § Compose XML documents – Combine diverse sources – Validate against DTDs or schema § Publish results to a message queue – Familiar DB programming model § Single source, relational updates DB 2, Oracle, SQL Server, Sybase, Teradata, OLE DB, ODBC, Excel, XML, message queues, Web services, flat files, document repositories, content repositories, LDAP directories, WWW, email databases, and more. IBM Israel Software Symposium 2003 8

accelerate your business DB 2 Information Integrator 8. 1 A Replication Server – Manage consolidation for performance and availability § Distribute data among relational databases – DB 2, Informix, Microsoft, Oracle, Sybase, Teradata DB 2 § Support flexible topologies – – Distribution: One to many Consolidation: Many to one § Match data movement modes to usage requirements – Table-at-a-time for warehouse loading during batch window – Transaction-consistent for online data Microsoft § Choose latency characteristics – Scheduled, interval-based, continuous § Apply transformations in-line – Standard SQL expressions or stored procedure execution. IBM Israel Software Symposium 2003 9

accelerate your business DB 2 Information Integrator for Content n Define integrated views across diverse and distributed data ƒ IBM Content Manager portfolio and other content repositories e. g. File. NET, Lotus databases, ODBC and JDBC compliant relational databases, and IBM Lotus Extended Search sources (LDAP directories, WWW, email databases, …) n Search federated data ƒ Search application uses the IBM Content Manager API n Mine additional metadata from text documents ƒ Identify document language ƒ Extract entities like names or technical terms ƒ Categorize documents based on a taxonomy ƒ Group documents based on related content ƒ Create a document synopsis n Define workflows IBM Israel Software Symposium 2003 10

accelerate your business DB 2 Information Integrator Value § Extend current investments – Work within your existing infrastructure – Consolidate data or access distributed data as if it were a single data source – Combine existing data and content assets in new ways – Use familiar SQL programming model and existing tools – Build on a standards-based, strategic integration platform § Speed time to value for composite applications – Reduce hand-coding 40%-65% – Reduce skill requirements – Reduce development time by half § Control costs – Reduce payroll costs – Reduce need to rip and replace – Reduce need to manage redundant data IBM Israel Software Symposium 2003 11

accelerate your business Speeding Application Development effort to handle: üUnique interfaces Application Developer Other üSQL is on OPEN Standard üSQL is easily testable, independent of the application üJDBC, XML, Web. Services IBM Israel Software Symposium 2003 for each data type üJoining data from varied sources üAggregation and grouping üCorrelating data Special features: II handles: üSet processing üInterfaces for each üIn-built datadb type transformation üJoining data from functions varied sources üOptimisation üTransformation üAutomatic localdata üCorrelating caching üData driven triggers RDBMS Non-relational data Non-traditional data 12

accelerate your business Crystal Decisions Vision As a world-leading information infrastructure company, Crystal Decisions helps businesses make better decisions by bringing together their people and their information. Challenge Improve response time for complex queries over distributed heterogeneous data sources Solution Provides transparent, globally optimized access to heterogeneous, distributed data. Crystal Reports accesses the distributed data as if it were a single database. Response time improvement of up to 98% seen in house. Business Value "Users of Crystal Reports and Crystal Enterprise, with DB 2 Information Integrator, can … discover new ways to meet the information needs of their organization. " • Janet Wood, Vice President of Business Development, Crystal Decisions. Competitive Value “DB 2 Information Integrator provides Crystal Reports with exceptionally fast and efficient federated querying capability. “ • Trevor Smith, Program Manager, Business Development Group, Crystal Decisions IBM Israel Software Symposium 2003 13

accelerate your business Without Data Federation IBM Israel Software Symposium 2003 14

accelerate your business Federated Access to Diverse Data IBM Israel Software Symposium 2003 15

accelerate your business IBM DB 2 Information Integrator Software § Data federation –Extensible read/write access across diverse data and content sources –Database programming model (SQL) –Content programming model (OO API) Integrating diverse business information across and beyond the enterprise § Data placement –Caching and replication over heterogeneous information § Data transformation –SQL, XML, Web services –Advanced search and mining –Metadata management Information Integration § Part of a complete integration solution –XML publishing, consumption, and interchange –Web. Sphere business integration –Open platform based on industry standards IBM Israel Software Symposium 2003 16

accelerate your business Data Federation n Transparency: hides differences among sources ƒ Appears to be one source ƒ Supports a high level query language ƒ Functional compensation and passthru n Heterogeneity: integrates data from diverse sources ƒ Relational, XML, flat files, spreadsheet, messages, content repositories, Web, … n High Functions ƒ One query integrates data from multiple sources ƒ Capabilities of sources as well n Extensibility ƒ Access wide range of data sources ƒ Development wrapper toolkit n Autonomy ƒ Non-disruptive to data sources, existing applications, systems. IBM Israel Software Symposium 2003 17

accelerate your business Performance , Optimization of distributed queries n Federation leverages a full database engine ƒ Query Processor, Execution Engine, Catalog, Client Access, Security, Transactions n Query processing extended for Federated Data ƒ Pushdown Analysis ƒ Analyze how to decompose a user query ƒ Generate an optimal query execution plan using cost estimates including data source knowledge: database statistics, indexes, source functions, server and network capacities ƒ Allows function compensation IBM Israel Software Symposium 2003 18

accelerate your business Replication Architecture IBM Israel Software Symposium 2003 19

accelerate your business Heterogeneous Caching Feature n Improve query performance and availability n Administrator defines Materialized Query Table ƒ ƒ Precomputed or frequently used values Any data from the federated system Application indicates ability to use cache Implicit or explicit use n Developer enables cache use ƒ If enabled, reads are handled from the cache, writes passed through to the source ƒ If not, reads and writes passed through to source n Cache refresh managed: ƒ Manually ƒ By replication n Flexible caching topologies supported IBM Israel Software Symposium 2003 20

accelerate your business Wrappers Four important tasks: ƒ Data modeling – Map data model to relational data model (tables with rows and columns) – Map functions into SQL operations ƒ Query Planning – Represent data source capabilities – Push down as much work to data source as sensible – Detect missing function at source (so engine can compensate) – Supply cost and cardinality information ƒ Connection and transaction management ƒ Query Execution and data retrieval – Execute parts of a user’s query for a specific data source IBM Israel Software Symposium 2003 21

accelerate your business Configuration n Configuration steps: ƒ Wrapper: the wrapper code module itself ƒ Server: a specific data source, with associated attributes ƒ User mapping: information needed to connect to a specific server ƒ Nickname: a specific data set managed by a server, mapped to rows and columns in the federated server n Defined to system via DDL commands ƒ GUI administration generates DDL n Stored in the system catalog IBM Israel Software Symposium 2003 22

accelerate your business Administration Tools IBM Israel Software Symposium 2003 23

accelerate your business Discovery for Nicknames "Create Nicknames" window n Customized "Discover" GUI Launches customized GUI Returns Nickname defintions Wrappers which support discovery Sybase Oracle SQL Server DB 2 Informix ODBC Teradata HMMER Entrez XML Flat File Excel Extended Search IBM Israel Software Symposium 2003 24

accelerate your business Replication Administration n Definitions ƒ Manage control definitions for replication ƒ Customize names and sizes of objects n Operations ƒ Start Capture, Apply, Monitor, Analyzer, and Trace ƒ Issue commands such as STOP or STATUS n Monitoring ƒ Perform static and dynamic monitoring IBM Israel Software Symposium 2003 25

accelerate your business Application Development : Access DB 2 catalogs and DB 2 II federated sources Websphere Studio DB 2 Development Center Microsoft Visual Studio. NET IBM Israel Software Symposium 2003 26

accelerate your business Demo… Query Result SQL Server Excel IBM Israel Software Symposium 2003 DB 2 27

accelerate your business For more information http: //db 2 ii. dfw. ibm. com/demo/ IBM Israel Software Symposium 2003 28

accelerate your business Summary § Information integration is a foundation for companies to build an On Demand Operating Environment enabling them to align their IT infrastructure to business priorities § DB 2 Information Integrator provides access to diverse, distributed, and real-time data as if it were a single source, no matter where it resides. § DB 2 Information Integrator will help businesses – Shorten application development time – Improve productivity and application efficiency § Rely on IBM’s proven technology and support for open standards IBM Israel Software Symposium 2003 29

accelerate your business The whole is worth more then its constituent parts DB 2 Information Integrator helps businesses to leverage existing data assets into knowledge for the benefit of the business IBM Israel Software Symposium 2003 30

accelerate your business Don’t forget to give us feedback Presentation Code: A 4 IBM Israel Software Symposium 2003 31