IBM Information Management Integrated Data Management Vision and

IBM Information Management What do Businesses Have? A Collection of Disparate, Single-Purpose Products Embarcadero ER/Studio IBM Info. Sphere Data Architect Oracle Tuning Pack Quest Spotlight Design Sybase Power. Designer IBM Optim Data Growth Solution Optimize CA ERwin IBM Data Studio Developer Guardium Solix EDMS Quest In. Trust Oracle JDeveloper IBM Optim Develop Govern Oracle Vault Oracle Diagnostic Pack Embarcadero Rapid SQL IBM Comparison Tool for DB 2 z/OS Operate Quest Central Deploy 2 Data Studio Administrator Embarcadero Change Manager BMC Patrol IBM DB 2 tools Quest TOAD Oracle Change Management Pack © 2009 IBM Corporation

IBM Information Management 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 noncompliance 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 © 2009 IBM Corporation

IBM Information Management 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 = $140 B unutilized server assets 4 © 2009 IBM Corporation

IBM Information Management 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 and platforms § Facilitate cross-functional collaboration Within IT Among Line of Business, Compliance functions Across disparate skill sets § Optimize business value 5 Respond quickly to emerging opportunities Improve quality of service Reduce cost of ownership Mitigate risk © 2009 IBM Corporation

IBM Information Management Introducing Integrated Data Management An integrated, modular environment to design, develop, deploy, operate, optimize and govern enterprise data throughout its lifecycle on the System z platform Enabling organizations to more efficiently and effectively § Respond to emergent, data-intensive business opportunities § Meet service level agreements for data-driven applications § Comply with data privacy and data retention regulations § Grow the business while driving down total cost of ownership 6 © 2009 IBM Corporation

IBM Information Management Integrated Data Management § Deliver increasing value across the lifecycle, from requirements to retirement Design Develop Optimize Models Policies Metadata Operate Deploy § Facilitate collaboration and efficiency across roles, via shared artifacts automation and consistent interfaces § Increase ability to meet service level agreements, improving problem isolation, performance optimization, capacity planning, and workload and impact analysis § Comply with data security, privacy, and retention policies leveraging shared policy, services, and reporting infrastructure 7 © 2009 IBM Corporation

IBM Information Management The broadest range of capabilities for managing the value of your data throughout its lifetime Info. Sphere Data Architect Design Data Studio Developer Optim Data Growth Solutions Optim Query Tuner (a. k. a. Optimization Expert) Develop Optim Test Data Management Optimize Policies Models DB 2 Metadata Optim Data Privacy Solutions DB 2 Performance Expert and Extended Insight Feature Operate Deploy Data Studio pure. Query Runtime Data Studio Administrator DB 2 Audit Management Expert Database Encryption Expert 8 © 2009 IBM Corporation

IBM Information Management Model-driven Governance – Automating Governance Policies Data Architect emits runtime metadata Data Architect specifies column CCN Our Design tool has been for Optim so that it knows which Design columns to anonymize, etc. Discover, Data Architect contains a credit card number, and the data masking algorithm. import, Design model, relate, standardiz e Develop Optimize Standards Models Policies Operate Deploy Data Studio Administrator would create fine grained access control rules to prevent DBAs or other unauthorized people from viewing CCN values. 9 extended to include application context information about the customer’s data. For example: semantic meaning (SSN, home phone number, medical privacy data, credit card number, PIN code, etc. ) Data Studio Developer would prevent copy of rows containing CCN column values from PROD to TEST masking algorithm that due to PCI DSS rules, unless Optim product is used should be used to present to anonymize data. the data in reports Data Studio Administrator automatically checks that encryption is used for the table containing CCN due to PCI DSS rules. © 2009 IBM Corporation

IBM Information Management Info. Sphere 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 10 © 2009 IBM Corporation

IBM Information Management Data Governance Manage Lifecycle Model policies Integrate tools Secure Data Protect Privacy Audit Data Prevent Access Restrict Access Monitor Access Retain Data De-identify Data Encrypt Data Audit Access Audit Privileges Audit Users Data Archival Data Retention Data Retirement DB 2 Audit Management Expert Tivoli Security Information and Event Manager Label Based Access Control Trusted Context Data Studio Developer and pure. Query Runtime Optim Data Privacy Solution Database Encryption Expert Optim Data Growth Solution Info. Sphere Data Architect Optim Test Data Management © 2009 IBM Corporation

IBM Information Management Data Studio Administrator § GA July 2008 for DB 2 LUW servers Compare, Sync and Alter DDL roundtrip support Extended Alter Impact Analysis Change model Physical modeling, Unified Change Project Advanced Data Movement (HPU) Scheduling & Enhanced Advanced Deployment 12 © 2009 IBM Corporation

IBM Information Management 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 DB 2 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 DB 2 data. Been used in a number of disaster recovery situations by extracting individual tables from DB 2 backups. Speeding up the process of migrating a DB 2 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 DB 2 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 13 13 © 2009 IBM Modified 12/07/2006 Corporation

IBM Information Management Optimizing Your Web. Sphere Applications with Data Studio © 2009 IBM

IBM Information Management What’s so Great About DB 2 Accounting for CICS Apps? CICS AOR 1 Txn 1 - Pgm 2 z/OS LPAR CICS AOR 2 Txn. A - Pgm. X - Pgm. Y DB 2 PROD CICS AOR 3 Txn 1 - Pgm 2 DB 2 Accounting for CICS apps allows you to study performance data from many perspectives: • By transaction (PLAN name) • By program (package level accounting) • By address space (AOR name) • By end user ID (CICS thread reuse) App CPU Txn 1 2. 1 TN 1 PLN Txn. A 8. 3 TNAPLN PLAN This flexibility makes it very easy to isolate performance problems, perform capacity planning exercises, analyze program changes for performance regression, compare one user’s resource usage to another’s, etc. 15 © 2009 IBM Corporation

IBM Information Management JDBC Performance Reporting and Problem Determination – Before pure. Query Application Server A 6 A 5 What is visible to the DBA? - IP address of WAS app server - Connection pooling userid for WAS - app is running JDBC or CLI What is not known by the DBA? - which app is running? - which developer wrote the app? - what other SQL does this app issue? - when was the app last changed? - how has CPU changed over time? - etc. 16 DB 2 Java Driver A 2 Persistence Layer A 3 Data Access Logic A 4 EJB Query Language A 1 DB 2 or IDS User CPU PACKAGE USER 1 2. 1 USER 1 8. 3 USER 1 22. 0 USER 1 JDBC © 2009 IBM Corporation

IBM Information Management What’s so Great About Data Studio pure. Query Accounting for Web. Sphere Applications? z/OS LPAR CICS AOR 2 Txn. A (PLANA) - Pgm. X - Pgm. Y Data Studio and pure. Query provide the same granularity for reporting Web. Sphere’s DB 2 resources that we have with CICS: • By transaction (Set Client Application name ) • By class name (program - package level accounting) • By address space (IP address) • By end user ID (DB 2 trusted context and DB 2 Roles) Unix or Windows WAS 21. 22. 3. 4 Txn. A (Set Client App=Txn. A) - Class. X - Class. Y App Txn. A Txn. B CPU 2. 1 8. 3 This flexibility makes it very easy to isolate performance problems, perform capacity planning exercises, analyze program changes for performance regression, compare one user’s resource usage to another’s, etc. 17 © 2009 IBM Corporation

IBM Information Management Simplifying Problem Determination Scenario Application Developer Java Profiling § Available for each db access SQL text generated Access path Cost estimates Estimated response time Elapsed & CPU time Data transfer (getpages) Tuning advice Database Administrator § Available for each SQL pure. Query Application name Java class name Java method name Java object name Source code line number Source code context J-Lin. Q transaction name Last compile timestamp DRDA Extentions 18 © 2009 IBM Corporation

IBM Information Management Using pure. Query to Foster Collaboration and Produce Enterprise-ready Apps Application Server DB 2 or IDS Dev System A 4 A 5 Application Meta data A 1 A 2 A 3 A 1 A 2 A 4 DB 2 or IDS A 1 Prod A 4 A 3 A 4 A 6 A 5 Catalog data for SQL A 1 st t te n ni re u uctio a mp prod co kly ults to ic Qu f res per 19 A 1 A 2 A 3 A 4 A 5 A 6 Application Developer Application Meta data Use pure. Query app metadata as a way to communicate in terms familiar to both DBA and developer Performance Data Warehouse A 1 A 2 A 3 A 4 A 5 A 6 Database Administrator © 2009 IBM Corporation

IBM Information Management Data Studio Developer -- pure. Query Outline Speed up problem isolation for developers – even when using frameworks § Capture application-SQL-data object correlation (with or without the source code) § Trace SQL statements to using code for faster problem isolation § Enhance impact analysis identifying application code impacted due to database changes § Answer “Where used” questions like “Where is this column used within the application? ” § Use with modern Java frameworks e. g. Hibernate, Spring, i. Batis, Open. JPA 20 © 2009 IBM Corporation

IBM Information Management Java Persistence Technologies with pure. Query JDBC JPA API pure. Query API i. Batis Spring SQLJ JPA Runtime pure. Query Runtime High Speed API JDBC w/pure. Query IBM Database pure. Query Metadata, Manageability 21 © 2009 IBM Corporation

IBM Information Management Client Optimization Improve Java data access performance for DB 2 – without changing a line of code § Captures SQL for Java applications Custom-developed, framework-based, or packaged applications § Bind the SQL for static execution without changing a line of code New bind tooling included § Delivers static SQL execution value to existing DB 2 applications Making response time predictable and stable by locking in the SQL access path preexecution, rather than re-computing at access time Limiting user access to tables by granting execute privileges on the query packages rather than access privileges on the table Aiding forecasting accuracy and capacity planning by capturing additional workload information based on package statistics Drive down CPU cycles to increase overall capability § Choose between dynamic or static execution at deployment time, rather than development time 22 © 2009 IBM Corporation

IBM Information Management Data Studio pure. Query Runtime for z/OS § In-house testing shows double-digit reduction in CPU costs over dynamic JDBC § § § IRWW – an OLTP workload, Type 4 driver § Cache hit ratio between 70 and 85% § 15% - 25% reduction on CPU per txn over dynamic JDBC 23 © 2009 IBM Corporation

IBM Information Management Have You Heard of SQL Injection? 24 © 2009 IBM Corporation

IBM Information Management Toughest issue for Web applications – Problem diagnosis and resolution Web Browser Users Web Server DB 2 Java Driver Persistence Layer Data Access Logic EJB Query Language Business Logic 25 DB 2 Server Application Server JDBC Package © 2009 IBM Corporation

IBM Information Management Customer Job Roles – A Barrier to a “Holistic View” Application Developer DB Java Driver 26 Persistence Layer 5 4 Network Admin Data Access Logic 2 DB Server DBA System Programmer EJB Query Language 3 Web. Sphere Connection Pool Business Logic 1 Application Server JDBC Package © 2009 IBM Corporation

IBM Information Management How do we plan to help? 27 © 2009 IBM Corporation

IBM Information Management Scenario It seems that the first application server has a problem. Double-click to drill-down. 28 In this situation, all applications are equally affected, and the problem seems not to be in the data server. © 2009 IBM Corporation

IBM Information Management Scenario - continued Most of the time is spent for „WAS

IBM Information Management Scenario – continued 5 second wait time indicates that the maximum number of allowed connections is not sufficient… … which becomes also evident when comparing the parameters and metrics of this client with other clients. 30 © 2009 IBM Corporation

IBM Information Management Future enhancements to Data Studio and pure. Query © 2009 IBM

DB 2 Performance Expert futures -- Associate SQL with Java Source IBM Information Management Heat Chart Alerts Dashboard In-flight analysis SLAs Database: Accounting TOP 3 currently running SQL Statements DS Proc occurrences schema E 2 E elapsed Statement text SELECT TIME FROM UNIVERSE phys. I/O 132. 13 1323 123. 32 Statement SYSIBM SELECT SALARY FROM PAYMENT … information 323. 4 221 11. 3 32. 1 DELETE FROM ACCOUNT WHERE AID Stmt text = 3… 23. 3 435 32322. 3 PROC SELECT TIME FROM UNIVERSE DS user ID Client IP addr / hostname Client user ID Client workstation name Client application name Client accounting application name application contact package class method source line KARN TPKARN. de. ibm. com KARN TPKARN Jawaw. exe N/A Online banking hkarn@de. bm. com West. OLBank Account Transfer() 314 Statement elapsed time Current last day last week 32 TOP by 1. 303 Application SAP 3 sort time X DS elapsed DS CPU time Physical I/O Sort time 32. 1 Analyze -+ -+ Time distribution sorting DS sorting Unacc wait USER CPU SYSTEM CPU Resource usage Query cost estimates Buffer Pools Data – hit ratio (%) Data – physical reads / min Index – hit ratio (%) Index – physical reads / min 18. 456 -+ 43. 4% 4323 54. 2% 3214 132. 13 sec 239. 40 sec 15. 60 sec Stop SQL Force application © 2009 IBM Corporation

IBM Information Management Open. JPA and Hibernate -SQL Query Generation JPA Query Select emp_obj(), dept_obj() SQL JPA query transform Select * from EMP WHERE … Select * from DEPT WHERE … • Hibernate and Open. JPA often rewrite queries • No database statistics are used – entirely heuristic!!! • Can often result in poorly performing queries 33 © 2009 IBM Corporation

IBM Information Management pure. Query -- More Visibility, Productivity, and Control of Application SQL § Capture SQL § Share, review, and optimize SQL § Revise/optimize SQL and validate equivalency without changing the application § Bind for static execution to lock in service level or run dynamically § Restrict SQL to eliminate SQL injection Capture 34 Review Optimize Revise Restrict © 2009 IBM Corporation

IBM Information Management Visualize application SQL Replace SQL without changing the application Position in Database Explorer 35 Visualize execution metrics Execute, tune, share, trace, explore SQL © 2009 IBM Corporation

IBM Information Management Open. JPA, Hibernate, i. Batis -Batch Queries JPA Query new dept_obj … new emp_obj … JPA query rewrite SQL INSERT INTO DEPT … INSERT INTO EMP … • Open. JPA, Hibernate, and i. Batis “batch” queries to reduce network traffic • Batches must contain executions of a single prepared statement • Referential integrity constraints can change batch size: • 2 network trips without RI (one for EMP, one for DEPT) • 4 network trips if RI disables batching • pure. Query can convert the above example to a single network trip, regardless of whether RI is used or not… 36 © 2009 IBM Corporation

IBM Information Management Support for Oracle in Data Studio Developer © 2009 IBM Corporation

IBM Information Management Oracle Object Management support in Data Studio User Defined Types Space Integrity objects • Create/Alter/Drop • Constraints (primary, unique, check, foreign) Performance objects • Create/Alter/Drop • Partitions (Range, Hash, List) • Indexes 38 • Create/Alter/Drop • Tablespace, Extents, Free Lists, logging • LOB Attributes • Buffer pools Events • Table Types • Object Types • Array Types • Triggers • Before/After/Foreach types • Trigger events upport Oracle S thening Streng Physical objects Procedures and Functions • Create/Alter/Drop for all objects • Tables, Synonyms, Sequences • Functions • Views/materialized views © 2009 IBM Corporation

IBM Information Management Design Lifecycle § Logical Modeling Capture business requirements Represent an organization’s Data Abstract complex heterogeneous environments Often associated with a Domain Model • Dedicated vocabulary § Physical Modeling Platform specific implementation • Tables, Constraints, Data Types • Disk and Security requirements • Caching and Fast access strategies Leverage and Validate against platform key features and constraints 39 © 2009 IBM Corporation

IBM Information Management Advanced heterogeneous support Logical Data Modeling Oracle Support Physical Data Modeling IBM 40 IBM Data Studio • Visualize • Design Privileges • Storage and Data Partition • Advanced Code Generation • Analyze Impact • Validate ORACLE © 2009 IBM Corporation

IBM Information Management Visualize Oracle Data Sources § High fidelity display of the Catalog Information § Load on Demand technology Instantaneous connection Fast retrievals § Enable Physical Data Model transformation 41 © 2009 IBM Corporation

IBM Information Management Managing Oracle Tables Object Properties Editor Tree-Based Representation Context-Sensitive Actions 42

IBM Information Management Oracle Privileges Support § Physical Model enables Design capability Grant appropriate privileges and roles to users § More detailed display allows finer-grained control 43 © 2009 IBM Corporation

IBM Information Management Oracle Storage § Storage properties display § Ability to design Table

IBM Information Management Data Partition § Table and Materialized View support Range partition List

IBM Information Management PL/SQL Development § Integrated Query Editor support Content Assist Parser support

IBM Information Management PL/SQL Deployment § Execution Configuration Separation of concerns • Better decouple configuration from definition and implementation Deployment and Debugging Runtime Target initialization Authorization configuration 47 © 2009 IBM Corporation

IBM Information Management Optimization Expert © 2009 IBM Corporation

IBM Information Management Understanding Query with Query Annotation Original and transformed Query Formatted, reorganized query text Annotations (catalog stats, cost estimation) 49 © 2009 IBM Corporation

IBM Information Management VISUAL PLAN HINT 50 © 2009 IBM Corporation

IBM Information Management Provide hints for Access. Type 51 © 2009 IBM Corporation

IBM Information Management ACCESS PLAN GRAPH (APG) The Access Plan Graph screen displays the

IBM Information Management Query Advisor Predicate that should be considered for re-write to get

IBM Information Management Access Path Advisor 54 © 2009 IBM Corporation

IBM Information Management STATISTICS ADVISOR 55 © 2009 IBM Corporation

IBM Information Management Index Advisor Index Recommendations DDL to create the new index statement

IBM Information Management Common Connection Repository § Enhancing the value propositions on Team support Centralized connection properties for sharing between DBA and Developers Improve usability and up-and-running scenarios Give controls to DBAs on connection properties settings Eliminates the need to configure each database server on each client desktop “push down” of client properties to allow DBAs to control and override application behaviors § Key Features 57 Integrated solution to Eclipse Data Source Explorer Integration with upcoming Web DBA tooling Create or connect to Connection Repository Connect to database using existing definitions Create new definition Logical grouping of connection definitions © 2009 IBM Corporation

IBM Information Management 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 58 © 2009 IBM Corporation

IBM Information Management pure. Query Runtime improvements § “client optimization” for non-Java SQL applications (convert dynamic SQL to static) . NET apps CLI apps (including Ruby, PHP, etc. ) 59 © 2009 IBM Corporation

IBM Information Management Where to get IBM Data Studio ? § IBM Data Studio www. ibm. com/software/data/studio • • 60 FAQs / Tutorials Downloads Forum / Blogs Join the IBM Data Studio user community © 2009 IBM Corporation

IBM Information Management Disclaimer © 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, DB 2, and Web. Sphere 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. 61 © 2009 IBM Corporation