Integrated Data Management Vision and Roadmap

© 2007 IBM Corporation

IBM Information Management

Integrated Data Management
Vision and Roadmap

Curt Cotner

IBM Fellow

Vice President and CTO for IBM Database Servers

cotner@us.ibm.com

IBM Information Management

© 2007 IBM Corporation

2

What do Businesses Have?

A Collection of Disparate, Single
-
Purpose Products


CA ERwin

IBM InfoSphere Data Architect

Embarcadero ER/Studio

Sybase PowerDesigner

Design

IBM DB2 tools

BMC Patrol



Quest Central

Oracle Diagnostic

Pack

Operate

Oracle Tuning Pack

Solix EDMS

IBM Optim Data Growth Solution

Optimize

Quest Spotlight

Quest TOAD

IBM Data Studio Developer

Oracle JDeveloper

Develop

Embarcadero

Rapid SQL

IBM Comparison

Tool for DB2 z/OS

Embarcadero

Change Manager

Data Studio

Administrator

Deploy

Oracle Change

Management Pack

Quest

InTrust

Guardium

IBM Optim

Govern

Oracle

Vault

IBM Information Management

© 2007 IBM Corporation

The gaps create risk …


Loss of customers

Average customer churn rate up
2.5
%
after a breach



Loss of revenue

$
197
USD per customer record leaked

Average cost was ~ $
6.3
million / breach
in this study

Average cost for financial services
organizations was
17
% higher than
average



Fines, penalties or inability to
conduct business based on non
-
compliance

PCI

Sarbanes
-
Oxley (SOX)


HIPAA

Data Breach Disclosure Laws

Gramm
-
Leach
-
Bliley Act

Basel II





Source: “2007 Annual Study: Cost of a Data Breach” , The Ponemon Institute

IBM Information Management

© 2007 IBM Corporation

4

Driven by the increasing
numbers of physical
systems, system
management

has become the main
component of IT costs
and is growing rapidly

Many Servers, Much Capacity, Low Utilization =

$140B unutilized server assets



IBM Information Management

© 2007 IBM Corporation

5

What do Businesses Need?

An integrated environment to span today’s flexible roles


Manage data throughout its lifecycle

From design to sunset


Manage data across complex IT environments

Multiple interrelated databases, applications

Involving databases and platforms from multiple vendors


Facilitate cross
-
functional collaboration

Within IT

Among Line of Business, Compliance functions

Across disparate skill sets


Optimize business value

Respond quickly to emerging opportunities

Improve quality of service

Reduce cost of ownership

Mitigate risk

IBM Information Management

© 2007 IBM Corporation

6











Integrated Data Management
–

What’s Different?

AppDev


Produce enterprise
-
ready applications faster


Improve data access, speed iterative testing and empower collaboration across the lifecycle


Consistently achieve service level targets

Automate and simplify operations with contextual intelligence across the solution stack


Support business growth

Accommodate new initiatives without expanding infrastructure

Simplify application upgrades, consolidation and retirement


Facilitate alignment, consistency and governance

Upfront business policies and standards; share, extend, and apply throughout the lifecycle

Data Architect

DBAs

Application Manager

Testers

IBM Information Management

© 2007 IBM Corporation

7

InfoSphere Data Architect

Optim Development Studio

Optim Test Data Management

Optim Data Growth Solutions

Optim Data Privacy Solutions

The broadest range of capabilities for

managing the value of your data throughout its
lifetime

DB2 Performance Expert and
Extended Insight Feature

Optim pureQuery Runtime

DB2 Audit Management Expert

Database Encryption Expert

Optim Database Administrator

Develop

Design

Deploy

Optimize

Operate

Policies

Models

Metadata

DB
2

Optim Query Tuner

(a.k.a. Optimization Expert)

IBM Information Management

© 2007 IBM Corporation

8

InfoSphere Data Architect

InfoSphere Data Architect is a collaborative, data design solution
to discover, model, relate, and standardize diverse data assets.

Key Features


Create logical and physical data models


Discover, explore, and visualize the
structure of data sources


Discover or identify relationships
between disparate data sources


Compare and synchronize the structure
of two data sources


Analyze and enforce compliance to
enterprise standards


Support across heterogeneous
databases


Integration with the Rational Software
Delivery Platform, Optim, IBM
Information Server, and IBM Industry
Models

IBM Information Management

© 2007 IBM Corporation

9

Visualization

Customizable Data Viewing and Reporting


Diagramming

Graphically visualize logical and
physical models using the
customizable data diagramming
capabilities


Reporting

Out
-
of
-
the box reports as well as fully
customizable reports with flexible
formats; HTLM, PDF and BIRT


Data Browsing

Browse and sample data using the
data browse and sample content
features


Data Editing

Edit and change data using the single
table editor for all supported DBMS
platforms

IBM Information Management

©
2007
IBM Corporation

10

Automate Data Design via Model
-
driven Transformation

Rational Software Architect

InfoSphere Data Architect

Data Studio Administrator

WebSphere Business Modeler

XSD

INTEGRATION

DEVELOPER

DATA
ARCHITECT

SOLUTION

ARCHITECT


Built
-
in
transformation


Compare and sync
facilitates merge

DATABASE
ADMINISTRATOR

UML

PDM

IBM Information Management

© 2007 IBM Corporation

11

InfoSphere Foundation Tools Integration

Industry Data Models

import

Data Architect

import

(business terms)

import

(physical models)

link

(terms to columns)

Information Analyzer

Business Glossary

FastTrack

Model, Link,

Generate Tables

Profile, Define

Data Rules,

Monitor

Define Terms,

Expose to any

Application,

Assign Stewards

view

(profiling results)

Define Source

to Target Business

Specifications

link

(terms to business rules)

Metadata Workbench

View IS Lineage,

Relationships

& Perform Impact

Analysis

Databases, files, etc.

on all platforms

IBM Information Management

© 2007 IBM Corporation

12

Archiving: Key Requirements

1.
Archive Complete Business Object

Apply Functional Condition Checks

Accommodate Business Requirements

Archive associated File Attachments

Audit
-
Ready Snapshot in Time

2.
Ensure “Full Lifecycle Archiving”

Support data retention policies as per ILM business requirements

Multiple formats
–

DBMS, File

Storage options
–

hardware targets, tiers

Automate enforcement of retention & disposal policies

3.
Universal Access to Archived Data

Native application access

Application independent access

Combined reporting of Active and In
-
Active Data

IBM Information Management

© 2007 IBM Corporation

13

1
. Identify and Archive Complete Business Object

Several techniques available:


--

Import from database catalog or InfoSphere Data Architect


--

Deduce from SQL activity (Data Relationship Analyzer)


--

Detect patterns in the data itself (Exeros acquisition)


--

Pre
-
built application aware models (Sieble, Oracle Financials, PeopleSoft, SAP, etc.)

IBM Information Management

© 2007 IBM Corporation

14

Oracle e
-
Business Suite General Ledger

Archive Details / Complete Business Object

IBM Information Management

© 2007 IBM Corporation

15

Siebel Basic Activities Archive Detail

IBM Information Management

© 2007 IBM Corporation

16

Customization of Access Definitions

Optim Application Solution

Business Object Delivered

“out of the box”

Customer’s

Custom Tables

Site Specific Archive

Template Customization


IBM Information Management

© 2007 IBM Corporation

17

Non DBMS

Retention Platform

ATA File Server

IBM RS550

EMC Centera

HDS

2
. Information Lifecycle Management Support

Current

Data


1
-
2 years

Active

Historical


3
-
4 years

Offline

Retention Platform


CD

Tape

Optical


Production

Database

Archive

Database

Archive

Online

Archive


5
-
6 years

Offline

Archive


7
+ years

Restore

IBM Information Management

© 2007 IBM Corporation

18

Non DBMS

Retention Platform

ATA File Server

IBM RS550

EMC Centera

HDS

3. Universal Access to Data

Current

Data


1
-
2
years

Active

Historical


3
-
4 years

Offline

Retention Platform


CD

Tape

Optical


Production

Database

Archive

Database

Archive

Online

Archive


5
-
6 years

Offline

Archive


7+ years

Restore

Report Writer

XML

ODBC / JDBC

Native
Application

Universal Access to Application Data

Application Independent Access

IBM Information Management

©
2007
IBM Corporation

19

Key Capability
-

Federated Data Support

Siebel

Other apps / any
DBMS

Custom Application

Complete Business
Object Captures
End to End
Business Process

IBM Information Management

© 2007 IBM Corporation

20

Sample Detailed Viewing Screen (Siebel)

IBM Information Management

© 2007 IBM Corporation

21

Optim Test Data Management Solution


Accelerate time to market

Create “right sized” test databases

Extract referentially intact subsets

Compare baseline data against test results to
pinpoint and resolve application defects faster

Edit test data to create error and boundary
conditions

Easily refresh, reset and maintain test
environments


Cut storage costs

Reduce storage requirements by using
smaller subsets for testing


Enable compliance

De
-
identify or mask data


Optim

Production
or Clone

Dev

QA

Test

Extract

Streamline building test databases, improve application
quality, cut IT costs and accelerate solution delivery


IBM Information Management

© 2007 IBM Corporation

22

Enterprise Challenge: Data Privacy

Optim Data Privacy Solution

A comprehensive set of data masking techniques to transform or de
-
identify data,
including:


String literal values


Character substrings


Random or sequential numbers


Arithmetic expressions


Concatenated expressions


Date aging


Lookup values


Intelligence


Application
-
aware masking capabilities ensure data is realistic but fictional


Prepackaged data masking routines make it easy to de
-
identify elements

E.g. credit card numbers & email addresses

Data is masked with
realistic but fictional
information

IBM Information Management

© 2007 IBM Corporation

23

Aligning Around Data Privacy


Define policies once and reuse


Flow definitions to team members


Drive consistent practices around privacy

Data Architect

Tester

Developer

Define privacy policies

Analyse use of sensitive data

Provision fictionalized test data

IBM Information Management

© 2007 IBM Corporation

24

Deploy without Disruption

Optim Database Administrator

Develop

Design

Deploy

Optimize

Operate

Models

Policies

Metadata

Deploy

Install,
configure,
change,
promote

Database

Administrator


Enhance DBA productivity and accelerate
complex changes while ensuring data and
process integrity


Automatically manages dependent objects

Saves and restores data for extended alters

Generates needed maintenance commands


Reduce errors and downtime

Provides impact analysis visualization

Factors in impacts and side effects automatically

Automatically generates commands to undo
changes


Foster teamwork and enhance auditability

Integrated into Rational Software Delivery
Platform

Document changes for collaboration and audit

IBM Information Management

© 2007 IBM Corporation

25

Optim Database Administrator


Models, automates and deploys
complex schema changes


Identifies dependencies and
analyzes impact to mitigate
deployment risk


Preserves data, dependent objects,
privileges, and application binding


Synchronizes, copies, clones, or
merges database schema definitions
from the source to the target


Documents changes for collaboration
and audit


Enables undo or restart
--

if needed


Manages common database
maintenance tasks

Improves DBA productivity and reduces application outages by
automating and simplifying complex DB2 structural changes
including change
-
in
-
place as well as database migration scenarios.


IBM Information Management

© 2007 IBM Corporation

26

26

High Performance Unload

What is it?

A utility for unloading data at very high speed (minimum wall clock time). Also can extract
individual tables from DB2 backups. While unloading, it can repartition the data for even faster,
parallel reloading on a different system which has a different partitioning layout from the one
being unloaded from.

What’s its value to customers?

Reduced costs by speeding up operations which require the unloading of large amounts of DB2
data.

Been used in a number of disaster recovery situations by extracting individual tables from DB2
backups.

Speeding up the process of migrating a DB2 server to new hardware.

New features and functions:

System migration performed entirely by HPU. The unloading and repartitioning of the data,
sending of it across the network and loading using DB2 LOAD command all handled by HPU.

•
Today, you have to build complicated scripts to do this process

Improved autonomics. One memory tuning parameter instead of several. Tell HPU how much
memory it can use, and HPU will figure out the best way to use it.

Simplification of syntax by eliminating some keywords for specifying certain HPU functions
through the use of “templates” to define the output file names.

•
Existing syntax also supported for backward compatibility


Modified 12/07/2006

© 2007 IBM Corporation

IBM Information Management

Support for Oracle in

Optim Development Studio

IBM Information Management

© 2007 IBM Corporation

28

Design, Develop, and Deploy for Oracle

Unified solution across DB2, Informix, and Oracle


Design
–

InfoSphere Data Architect

Logical models, physical models, privilege
models, privacy models

Platform
-
specific for physical objects


Develop
–

Optim Development Studio

Generate data access layers

Develop and debug procedures, SQL,
functions including PL/SQL


Test
-

Optim Test Data Manager and Data
Privacy solutions

Invoke Optim TDM from Developer

Use relationship and privacy definitions
from InfoSphere Data Architect

Create right
-
sized, fictionalized,
production
-
like test databases


Deploy
–

Optim Development Studio and
Optim Database Administrator

Create/manage database objects

Copy/paste objects between DB2 and
Oracle

Load Oracle
catalog

Edit database objects

View SQL and
execution results

Take context
-
sensitive actions

IBM Information Management

© 2007 IBM Corporation

29

Advanced heterogeneous support

IBM

ORACLE

Physical Data Modeling

Logical Data Modeling


Oracle

Support

•

Visualize

•

Design Privileges

•

Storage and Data Partition

•

Advanced Code Generation

•

Analyze Impact

•

Validate


IBM Information Management

© 2007 IBM Corporation

30

Visualize Oracle Data Sources


High fidelity display of the Catalog Information


Load on Demand technology

Instantaneous connection

Fast retrievals


Enable Physical Data Model transformation


IBM Information Management

© 2007 IBM Corporation

31

Managing Oracle Tables

Tree
-
Based
Representation

Object
Properties
Editor

SQL and
Results of the
Execution

Context
-
Sensitive
Actions

IBM Information Management

© 2007 IBM Corporation

32

Oracle Privileges Support


Physical Model enables Design capability

Grant appropriate privileges and roles to users


More detailed display allows finer
-
grained control



IBM Information Management

© 2007 IBM Corporation

33

Oracle Storage


Storage properties display


Ability to design Table Spaces

IBM Information Management

© 2007 IBM Corporation

34

Oracle Data Partitions


Table and Materialized View support

Range partition




List partition

Hash partition

Composite partition

IBM Information Management

© 2007 IBM Corporation

35

PL/SQL Development


Integrated Query Editor support

Content Assist

Parser support (2009) with Error reporting



IBM Information Management

© 2007 IBM Corporation

36

Data & Object Movement


Value Proposition
–


•
Provide for the copying of database
objects and data between
homogeneous
and

heterogeneous
databases within Data Studio


Key Features

•
Copy objects at various levels
–

complete databases to a fixed
number of rows from a single table

•
Action performed in Data Source
Explorer
–

Copy/Paste and
Drag/Drop

•
Can automatically copy rows from
related tables using:

•
RI in database

•
Data Architect model

•
Optim application models

•
Data Relationship Analyzer

•
Can optionally annonymize the rows
using Optim Test Database Manager

© 2007 IBM Corporation

IBM Information Management

Optim Query Tuner

and

Option Workload Tuner for z/OS



IBM Information Management

© 2007 IBM Corporation

38



Optim
Query Tuner Key Features at a Glance

Access Path

Graph

Visual Plan

Hint

Workload

Control Center

Query

Report

Index

Advisor

Query

Advisor

Workload

Statistics

Advisor

Workload

Index


Advisor

Workload

Query


Advisor


Query Tuner User Interface

Eclipse

Statistics

Advisor

Access Path

Advisor

Formatter

Annotation

Query Based Tools and Advisors

Workload Advisors

Query Advisors

Query Tools



Database

Capture Query

Environ.

Profile

Monitor

Workload


Report

Capture

Workload

Environ

IBM Information Management

© 2007 IBM Corporation

39


IBM Optim Query Tuner key functions

Functions

Query Tuner (single Query)

Query Workload
Tuner for DB2 for
z/OS

Query Formatter

Yes

Yes

Query Annotation

Yes

Yes

Access Plan Graph

Yes

Yes

Visual Plan Hint

Yes

Yes

Query Advisor

Yes

Yes

Access Path Advisor

Yes

Yes

Statistics Advisor

Yes

Yes

Index Advisor

Yes

Yes

Query Reports

Yes

Yes

Query Environment Capture

Yes

Yes

Workload Query Advisor

Yes

Workload Statistics Advisor

Yes

Workload Index Advisor

Yes

Workload Query Reports

Yes

Workload Environment Capture

Yes

Profile Based Monitor

Yes

IBM Information Management

© 2007 IBM Corporation

40

Database Support by Product (as of today)



Vormetrics

Vormetrics

Vormetrics

a



a

EE for DB2
and IMS

Database Encryption Expert













a



DB2 PE Extended Insight













a

Omegamon

DB2 Performance Expert



a

a

a

a

a

a

a

Optim Data Growth



a

a

a

a

a

a

a

Optim Data Privacy



a

a

a

a

a

a

a

Optim Test Data Management













a

DB2 Admin
Tool/Object
Compare

Optim Database Administrator













a

a

+
Workload

Optim Query Tuner







a

a

a

a

a

Optim pureQuery Runtime







a

a

a

a

a

Optim Development Studio

a

a

a

a

a

a

a

a

InfoSphere Data Architect



OpenAdmin
Tool



a

a

Data Studio Administration
Console

alphaworks derivative

a

a

a

a

Data Studio

MySQL

Sybase

SQL
Server

Oracle

IDS

DB2
for i

DB2
for
LUW

DB2 for
z/OS



© 2007 IBM Corporation

IBM Information Management

Future Technology



IBM Information Management

© 2007 IBM Corporation

42

Configuration Change Management


Business Scenario
–

Database server change tracking

Enterprise with 100s or 1000s of applications, databases

Applications/databases undergo lots of change during lifecycle

•
Logical and physical database schema changes

•
database configuration parameter and privilege changes

Change can negatively impact business critical applications

•
Unintended, unauthorized change: quickly identify & rectify

•
Intended, authorized change: need to identify changes with negative impact


Biggest Pain Point

Did somebody change something? … Who changed it and why?


Objectives:

Automatically detect changes to schema or configuration parameters

Identify which changes took place during a given date range

Allow simple compare/synch of these attributes across servers

IBM Information Management

© 2007 IBM Corporation

43

Configuration Change Management


Business Scenario
–
client configuration management

Enterprise with 1000s of clients

Applications/databases undergo lots of change during lifecycle

•
Spread geographically, different time zones; mobile, installed on laptops

•
Not locked down, subject to end users “twiddling” their PC apps, settings,
etc

Data clients supporting many critical business activities

•
DB app design, development, test; prod app deployment and operation


Biggest Pain Point

What is the state of the 1000s of data clients, and how to easily update them?


Objectives:

Automatically capture client configuration settings

Centrally manage settings via a managed repository

Allow simple compare/synch of these attributes across clients

IBM Information Management

© 2007 IBM Corporation

44

Optim Database Maintenance Manager


A policy
-
driven offering that not only automates routine
maintenance, but optimizes the planning and execution of such
tasks


Goals

Reduce time spent performing space management by DBAs by 50%

Reduce time spent reorganizing data by 80% and
improve

disk
efficiency

Eliminate the task of determining the frequency for statistics
refresh/collection

Eliminate backup planning with a “recovery policy”

•
Insure that I can recover an application (set of DBMS objects) within
twenty minutes to any point in time in the last week

A database administration auto
-
pilot that schedules repetitive
work into you maintenance calendar.

IBM Information Management

© 2007 IBM Corporation

45

Planning Calendar

IBM Information Management

© 2007 IBM Corporation

46

Heatchart
–

Overall Health Status

Alert List
–

Biggest Problems First

Manage by Exception with DBA Choice

of Automation

Change alert threshold

Reclaim space now

Schedule reclaim space task

Automate this task

Disk Efficiency threshold reached


More than 30% of allocated space in database objects


could be returned to the system. Choose an option:

Set

5%
-

99%

Run

reorg w/few common opts

Schedule

one time, or

Schedule repeated task

Define

Policy


Specify allowable times


Specify not
-
allowed times


Specify few common opts

Disk Efficiency

Alerts: Disk Efficiency, Data Clustering, Data Recoverability, Statistics Quality, Task Scheduling

“Engage”

IBM Information Management

© 2007 IBM Corporation

47


IBM Data Studio


www.ibm.com/software/data/studio

•
FAQs / Tutorials

•
Downloads

•
Forum

/ Blogs

•
Join the IBM Data Studio user community




IBM Information Management

© 2007 IBM Corporation

48

© Copyright IBM Corporation 2009. All rights reserved.

U.S. Government Users Restricted Rights
-

Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.


THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES
ONLY.


WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE
INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF
ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT
PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE.


IBM
SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE
RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS
PRESENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR
REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND
CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS AND/OR
SOFTWARE.


IBM, the IBM logo, ibm.com, and DB2 are trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their
first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law
trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common
law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark
information” at
www.ibm.com/legal/copytrade.shtml


Other company, product, or service names may be trademarks or service marks of others.


Disclaimer

IBM Information Management

© 2007 IBM Corporation

49