1 / 10

Gaining an understanding of complex application database relationships

Gaining an understanding of complex application database relationships. UNYOYG November 14, 2008 Ray Makela. The Data Explosion?. Mergers & acquisitions Organic business growth eCommerce ERP/CRM Records retention: HIPAA SOX Data multiplier effect

jereni
Download Presentation

Gaining an understanding of complex application database relationships

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Gaining an understanding of complex application database relationships UNYOYG November 14, 2008 Ray Makela

  2. The Data Explosion? • Mergers & acquisitions • Organic business growth • eCommerce • ERP/CRM • Records retention: • HIPAA • SOX • Data multiplier effect • According to industry analysts, annual compound growth rates for databases will exceed 125% With all of the change and growth, we need to understand how it affects the enterprise

  3. Database Understanding your Enterprise • A business application can have many relationships between the various Database objects, such as tables • Some of these relationships can be discovered easily, while others are hard to find (or not documented) and manage. • The Challenge….As the complexity of data relationships increases, it becomes increasingly difficult to discover and keep track of this information.

  4. A DBA may not always know how tables are related • Personnel changes • Experienced DBA, new to the system • New DBA • Little or no documentation • Legacy applications • Packaged applications • Environment Changes • Applications changes • Changes not documented ? ? ? ?

  5. Why is knowing relationship data useful? • Ensure data consistency by keeping data relationally intact for: • Archiving • Backup and Recovery • Test Data Management • Understand application relationships • Application change validation • Impact analysis

  6. DBA Database Relationship Analyzer SQL Trace Database Catalog User Input • Application relationships • Dynamic SQL • System-managed RI • Triggers • Packages • User-defined RI Analyzing Database Relationships • Discover all, or specific database relationships, based on your parameters • Identify hard-to-find relationships defined and enforced by the application logic • Provides the information required to enabling analysis of the impact of relationships changes across applications • Ready-to-use Java APIs ready for user applications and tools such as Optim Solutions and Recovery Expert.

  7. Application 1 Ends Trace off Application 1 Starts Trace on Discover hard-to-find relationships- Three easy steps • User Initiated – Collect trace data • Collect SQL trace data while application is running • Data Relationship Analyzer – Prepare data • Extract SQL trace data from a table • Data Relationship Analyzer – Analyze data • Run Group Discovery with “Trace Analysis” option

  8. Group – 1st Run Emp, Address Group Compare Results Group – 2nd Run Emp, Address, Salary Compare Group Discovery Results • Compare Group Discovery results between a baseline run and a subsequent run to determine if changes have occurred and understand differences

  9. Compare Group Discovery Results • Compare database relationships between baseline and update relationship analysis to understand differences

  10. Summary: Analyzing Relationships • Analyze data relationships to improve accuracy and data integrity • Obtain a complete view of your application database environment • Promote database accuracy and consistency

More Related