Federated Database Set Up Greg Magsamen ITK 478

Federated Database Set Up �Greg Magsamen �ITK 478 SIA

Proposed Deliverables �Federated Database and multiple heterogeneous databases and data sources (Excel, XML) accessed via the federated database. �Sample SQL Statements via some SQL Query Tool or Reporting Tool. �Possibly a program extracting data from data source (Net Beans, VS 2005 (. Net) �Reporting Tool (Crystal or Business Objects)

Agenda �Database Middleware �Federated Systems �DB 2 and WFS �Setup �Demo �Conclusions

Not Covered �Complex situations �Data Mappings, Function Mappings �Various types of data sources

Purpose �Discover the Architecture �What software do I need? �How do I set it up? �Is this easy, difficult? �How viable for a real world implementation?

Database Oriented Middleware �What is Database Oriented Middleware? �An interface to an application �The ability to convert the application language into something understandable to the target database �The ability to send a query to a database over the network �The ability to process a query on the target database �The ability to move a response set back over the network to the requesting application �The ability to convert a response set into a format understandable by the requesting application

Federated Systems

DB 2 / Web Sphere Federated DB �Why DB 2? �Other choices? Oracle? �http: //www. oracle. com/technology/products/middlewa re/index. html �SQL Server?

Web Sphere Federated Server

Setup of Federation Database

Special Interest Activity Federated Server Setup �Wrappers �User mappings �Nicknames

Special Interest Activity Setup �IBM DB 2 -C �IBM DB 2 EE (Extended Edition) �IBM Web Sphere Federated Server �Federated Object Setup

Final Architecture

Data Sources for the Special Interest Activity �SQL Server, Oracle, DB 2 �Excel �XML

Setting up Each Data Source �SQL Server �Oracle �DB 2 �Excel �XML

Wrappers

Outcome of Object Usage �Querying Tool - Command Center �SQL Statements - Command Center of DB 2 �Program to ITK 478 �Crystal Reporting �Uses Views

Demo �VPN over wireless may be slow �Review what’s been done �Add another data source

Demo �Program Source �Crystal Problem

Learning Curve �Product selection �Database access �File access �Focused on simple tasks �Viable system up and running �Simple data source �Many more issues and components would come into play for a ‘real-world’ system and were not addressed in this SIA

Review of Material �Database Middleware – what it is �Federated Systems – how they fit in �DB 2 and WFS – products and usage �Setup – problems and scenarios �Demo – simple set up �Conclusions – coming up

Not Covered �Complex situations �Data Mappings, Function Mappings �Various types of data sources

Conclusions �Ease of Use �Major Problems

Usage Scenarios �Data Mart Versus Federation �Viability found from the SIA

To Be Continued…. �Things not covered �Data Mappings �Mapping Functions �Web Services

Questions?