linda hunt program director product management and strategy n.
Skip this Video
Download Presentation
Optim Query Capture and Replay Accelerate testing and deploy with confidence

Loading in 2 Seconds...

play fullscreen
1 / 24

Optim Query Capture and Replay Accelerate testing and deploy with confidence - PowerPoint PPT Presentation

  • Uploaded on

Linda Hunt, Program Director, Product Management and Strategy. Optim Query Capture and Replay Accelerate testing and deploy with confidence. Please note.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Optim Query Capture and Replay Accelerate testing and deploy with confidence' - hasana

Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Please note

IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion.

Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision.

The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion.

Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user’s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here.

IBM Confidential

  • Overview
  • Architecture
  • IBM Differentiation
  • Client Stories and Success
  • What’s new?
  • Questions
business challenge
Business Challenge
  • Challenges of lifecycle management
  • Realistic testing relieves lifecycle management challenges
  • Example scenarios:
    • Upgrading to new DB2 versions and maintenance updates
    • Performance Validation, Tuning and Troubleshooting
    • Deeper data layer testing to complement application stakeholder testing
    • Migrating from Oracle Database to IBM DB2
    • Upgrade or Migrating the operating system
business impact of deployment day disasters the real cost of production outages
Business impact of deployment day disastersThe *real* cost of production outages

Delays due to extra testing cycles

Loss of customer satisfaction

Higher Costs

$90,000 - $6.4M USD

Per hour cost of production outage

Information Weekb


of availability & performance errors

result from misconfigurationse


Of Fortune 500 companies

experience a minimum of 1.6 hours of downtime/ weekaccording to IT Directora

$10,000 USD

Per minute cost of production outage

Gartner reportc

$2.8M USD

Average cost of production downtime IBM Global Business Servicesd

a. IT Director: Assessing the Financial Impact of Downtime, August 2009

b. Information Management Magazine: How much does downtime really cost?, April 2010

c. Gartner Report: The Challenges and Approaches of Establishing IT Infrastructure Monitoring SLAs in IT Operations, September 2010

d. IBM Global Business Services: Managing unplanned IT outages, January 2010

e. Costs and Scope of Unplanned Outages

ibm infosphere optim query capture and replay
IBM InfoSphere Optim Query Capture and Replay

Capture production workloads and replay them in testing environments


Record and replay SQL


Source Database

  • Minimize unexpected production problems
  • Shorten testing cycles
  • Develop more realistic database testing scenarios


InfoSphere Optim Query Capture and Replay



  • Identify database problems sooner with validation reports and performance tuning
  • Use actual production workloads for testing rather than fabricated scenarios
  • Extend quality testing efforts to include the data layer


Note: Capabilities can vary across DB vendors

PureData - System for Analytics (Netezza)

PureData – System for Operational Analytics – Single coordinator node


enable realistic testing using actual production workloads
Enable realistic testing using actual production workloads

Deploy Change

DB2 for z/OS

DB2 for z/OS





Production System

Test System

Optim Query Capture and Replay

  • Original application timing
  • Order of execution
  • Transaction boundaries
  • Isolation levels
  • Performance metrics
  • Key SQL & application characteristics


infosphere optim query capture and replay architecture overview
InfoSphere Optim Query Capture and Replay Architecture Overview










Query Capture

and Replay (OQCR) Server






  • S-TAP
    • Installed on database server
    • Intercepts database traffic
  • OQCR server
    • Software component running in


    • Processes, replays and analyzes workloads
  • User interfaces
    • Browser-based
    • Administrative command line interface
coexistence w guardium database activity monitor
Coexistence w/ Guardium Database Activity Monitor
  • Optim Query Capture and Replay Appliance/Collector
  • Log Full Details
  • Guardium Data Base Activity Montior Appliance/Collector
  • Monitor Priv Users
  • Ignore Session
  • Log Full Details on Sensitive Objects Only

select * from sensitive_info

select * from sensitive_info

select * from sensitive_info

Database Server

select * from sensitive_info



All collectors receive the same information

Each collector stores all or just a portion of the overall information collected

Policy filters on collectors determines what information to store

extending performance analysis with database tools
Extending performance analysis with database tools




Compare and Analyze


ibm differentiation
IBM Differentiation

DB2 for z/OS -> DB2 for z/OS is an IBM Exclusive offering

Broadest platform support on distributed platforms:

DB2 LUW, Informix, Netezza, Oracle DB, Teradata, Microsoft SQL Server, Sybase, MySQL

Exact Solutions iReplay does not support Local Replay

Note: for DB2 for z/OS source, target can only be DB2 for z/OS

Performance and scalability

use case and client mapping
Use case and client mapping

Reduce Risk

  • Realistic workloads not fabricated workloads
  • Identify problem areas before production deployment
  • Increased visibility

Deploy on time

  • Create tests from months to days
  • Conserve need for mainframe resources
  • Reduced test cycle time

Within budget/Increased Productivity

  • No laborious script creation / Application setup
  • Automated repetitive tasks
  • Extended support / Cost of OS

Life Insurance and Financial Services – With the growing business, this company wants to not only support growing customer base but also innovate on latest technology to help its business team roll out new products and services to expand to new clients

Link to the case study


  • It takes 4 years fo the tech team to upgrade databases across the enterprise
  • Lack of test rigor have resulted in production instability
  • Benefits
  • Reduced Risk with realistic testing using actual patterns of transactions for test to help ensure a stable upgrade
  • Establish repeatable process across the database instances while also not increasing labor effort




Global Bank

DBA global team wants to upgrade to new capabilities but Line of Business resistant to any infrastructure change due to risk of sluggishness or downtime


  • Lack of test rigor in testing upgrades and patches resulting in limited functional testing and no performance testing
  • Difficult to get application owner’s time
  • Benefits
  • Establish repeatable process across database and reduce effort from a month to a week across hundreds of databases.
  • Accelerate time to value by Increasing LOB confidence with deeper level testing across database instances
  • Leverage existing Guardium platform for testing




Electrical Component Company relies on SAP as their business critical application interconnecting with other business critical applications.


  • Creating and configuring a SAP instance is very labor intensive.
  • As a result, testing with new DB2 versions and maintenance packs is labor intensive and gaps exists in testing methodology and what is actually occurring in production.
  • When issues occur, it is very time consuming to troubleshoot the issue
  • Benefits
  • Risk avoidance of $400K+ /hour with deeper level testing without creating and configuring an SAP instance
  • Improved troubleshooting by over 50%




Large Brokerage Firm – The first minutes of the opening of the market and last minutes of close of market are the two most critical periods of the day


  • Zero outage initiative
  • Inability to create tests that mirror distributed workloads to validate the hot standby can meet the performance needs


  • Risk avoidance of $1M+/hour with ability to validate performance of hot standby
  • Ability to create realistic tests of distributed workloads in days where previously impossible
  • Single repeatable process for testing at the data layer across all subsystems



what s new
What’s New?
  • Optim Workload Replay for DB2 for z/OS v2.1 - GA October 25, 2013
    • Name change effective 10/1/13
    • Enhanced Coverage
      • Day 1 Support DB2 for z/OS v11 “Sequoia” GA
      • Full LOB/XML support 32KB – (partial support available today)
      • Enhanced language support: Chinese, Japanese, Portuguese, Brazilian
    • Enhanced Usability
      • Ability to filter capture during replay
      • Ability to Export and Import workloads from one appliance to another
      • View report after Transform
      • View report after Capture
    • Enhanced Scalability and Performance
      • Enhanced Scalability
      • Enhanced STAP performance
  • Optim Workload Replay v2.1 12/13/13 EGA (Dist)
    • Name Change effective 12/10/13
    • Enhanced Coverage
      • Full LOB/XML support 32KB – (partial support available today)
      • Enhanced language support: Chinese, Japanese, Portuguese, Brazilian
    • Enhanced Usability
      • DB2: Connections reuse
      • DB2:Ability to Export and Import workloads from one appliance to another
    • Enhanced scalability

IDUG Barcelona

Mon Oct 14th, DB2 NEXT ESP - The process of verifying the performance and accesspath selection in a new DB2 version, Frank Petersen, JN Data


Mon Nov 4th, ILM-2382InfoSphere Optim Workload Replay: Accelerate database deployment and automate database testing

Wed Nov 6th, IDB-1265Preparing for IBM DB2 Migration: How to Monitor, Capture and Replay Your Workload on the Target System

Thu Nov 7th, IDZ-3757 Improve the quality and efficiency of your DB2 for z/OS migrations with the right tools and best practices

summary feel confident before you flip the switch
SummaryFeel confident before you flip the switch

How do you test infrastructure changes?

How long does it take you to upgrade across the enterprise?

Optimize and improve end user satisfaction

Increase Productivity

Build confidence and lower risk

Reduce Testing Effort from month(s) to days per instance

Increase Ensure well tuned, high performing workloads before deployment

Establish robust testing processes

downtime costs
Downtime costs - $$$


Cost of downtime can vary by industry and can range from hundreds of thousands to millions of dollars per hour

Financial Impact of Downtime Per Hour

Sources: Robert Frances Group

Per IDC, a major hotel supplier who has used this type of solution has said “The ROI .. was in avoiding potential problems in the future; if the company has an outage, the cost to downtime with its core transactional system can be $1+ million per hour”

Source: IDC 236616 12/2012 I D C M a r k e t S c a p e : W o r l d w i d e E n t e r p r i s e A u t o m a t e d S o f t w a r e Q u a l i t y 2 0 1 2 – 2 0 13 V e n d o r A n a l y s i s — D r i v i n g M u l t i m o d a l I n n o v a t i o n w i t h T e s t i n g P o r t f o l i o S t r a t e g i e s