Understanding the packaging of Optim Solutions for database development, administration, and performance management

Use Optim tools to manage data across the data lifecycle

Get a brief overview of the functionality and packaging of the development, administration, and performance management offerings in the IBM Optim™ products. The products described in this article include IBM Data Studio, Optim Development Studio and pureQuery Runtime, Optim Query Tuner and Query Workload Tuner, Optim Database Administrator, Optim High Performance Unload, DB2® Recovery Expert, DB2 Merge Backup, InfoSphere™ Data Architect, and Optim Performance Manager Extended Edition. [18 Feb 2010: This article has been updated from its original Sep 2009 publication to include changes that were announced or made available in Nov/Dec 2009, including family name changes from Data Studio to Optim. --Ed.] [13 May 2010: This article has been updated to include changes that were announced or made available in April 2010. --Ed.] [11 Aug 2011: This article has been updated to include changes that were announced or made available between April 2010 and July 2011. –Ed.]

Holly Hayes (hollyann@us.ibm.com), Program Director, Optim Solutions, IBM

holly hayes Holly Hayes is a Program Director for the Optim Solutions team. A 29-year IBM veteran, she has held development, strategy, marketing, and management positions working with operating systems microcode, replication technology, data warehouse infrastructure, database management, and information integration technology. She has been a prominent speaker at industry events and customer briefings and a frequent contributor to industry articles, analyst research, and other publications. She holds a U.S. patent in replication technology.



Anson Kokkat (ansonk@ca.ibm.com), Senior Software Engineer, IBM

Photo of Anson KokkatAnson Kokkat works at IBM as a Product Manager in the Optim Database Tools Group. Anson's professional career spans more than 12 years during which time he worked on application development technologies related to data servers. He has an extensive background working as a product manager for the IBM data modeling tool InfoSphere Data Architect. Now Anson concentrates on creating a marketplace for Database Backup and Recovery Tooling Solutions. He has written many articles and whitepapers related to his expertise, and he continues to work with sales, marketing, development, and especially customers out in the field.



11 August 2011 (First published 25 September 2009)

Also available in Portuguese

Introduction

This article gives an overview of the InfoSphere Optim solutions for database development, administration, and performance management. The product descriptions help you understand which databases are supported, how to differentiate between the no-charge capabilities and the additional capabilities that are available for purchase, and how to download no-charge and trial products. The article does not list and fully describe every feature of each product. For more in-depth information, use the links provided in Resources.

Summary of July 2011 changes

This update includes new products, new releases, and fix packs for all referenced products. The updates are summarized in the following developerWorks blog entries (see Resources for links):

  • New Optim and Data Studio Fix Packs and mod releases – Check them out! (July 2011)
  • DB2 Advanced Recovery Solution for Linux, UNIX and Windows (June 2011)
  • New Optim fixpacks with cool function added (April 2011)

A number of the offerings are now also included in DB2 and InfoSphere Warehouse editions. Refer to these links to view entitled Optim software (see Resources for links):

  • DB2 features and functions by edition
  • Components of InfoSphere Warehouse

The following bundled offerings were removed from market in June 2011:

  • IBM Optim Performance Acceleration Edition for DB2 for Linux, UNIX, and Windows
  • IBM Optim Database Management Professional Edition

Exploring InfoSphere Optim solutions for data lifecycle management

InfoSphere Optim is the family name of a portfolio of products that delivers on the IBM vision of managing data across your data's lifecycle (see Table 1). This portfolio is designed to increase productivity for all roles throughout the data life cycle and to help reduce the complexity and cost of managing database applications. InfoSphere Optim offerings help you manage and optimize tasks in each phase of the application life cycle from a data management viewpoint. This article focuses on an overview of some of these offerings and how the products are packaged.

Table 1. Data lifecycle management capabilities by InfoSphere Optim solutions
                                                     Information governance core disciplines lifecycle management
Discover and defineDevelop and testMonitor and optimizeConsolidate and retire
InfoSphere Discovery
IBM Business Glossary
InfoSphere Data Architect
Optim Development Studio
Optim Database Administrator
Optim Test Data Manager and Data Privacy
Optim Performance Manager
Optim Query Tuner
Optim pureQuery Runtime
DB2 Merge Backup
DB2 Recovery Expert
Optim Data Growth
Optim Application Consolidation solutions

The following list gives a high-level description of the products highlighted in Table 1.

Quicklinks for z/OS developers and database administrators

For many years, IBM has had a great set of products to manage DB2 for z/OS data and applications. Over time, more capabilities for z/OS will be delivered in these products, as well as in products in the Optim portfolio. Of the products described in this article, the following are currently enabled for z/OS databases:

Although not described in this article, the IBM Optim set of solutions also has great support for z/OS to manage data growth, to protect data privacy, and to streamline test data.

  • InfoSphere Data Architect: Simplifies data modeling and integration design, enabling architects to discover, model, relate, and standardize diverse and distributed data assets.
  • Optim Development Studio: Provides a complete development and testing environment for building database objects, queries, database logic, and Java™ pureQuery applications.
  • Optim pureQuery Runtime: Delivers a high-performance data access platform that provides the capability to visualize, optimize, and lock down SQL without modifying the application. Complements Optim Development Studio by enabling pureQuery support in production systems, including support for static execution of .NET and Java applications.
  • Optim Database Administrator: Manages complex structural changes while preserving data, privileges, dependent objects, and application bindings.
  • Optim High Performance Unload: Formerly known as DB2 High Performance Unload, provides a high-speed unload utility as an alternative to the export feature. Whether used alone or with Optim Database Administrator, this product significantly reduces the time required to migrate databases.
  • DB2 Merge Backup: Shortens recovery time on production servers, ensures full backups are available as needed, and enables organizations to reduce resources and outages for full backups.
  • DB2 Recovery Expert: Localizes recovery processes to impacted objects in order to reduce outages without having to resort to full database recovery.
  • Optim Performance Manager offerings: Provides a comprehensive and proactive performance-management solution to help you identify, diagnose, solve, and prevent database performance issues for applications that access DB2 databases on Linux®, UNIX®, or Windows®.
  • Optim Query Tuner offerings: Includes single-query tuning for both DB2® for z/OS® and DB2 for Linux, UNIX, and Windows. For DB2 for z/OS, you can extend the capability to entire SQL workloads using Optim Query Workload Tuner for DB2 for z/OS (formerly DB2 Optimization Expert for z/OS). These products provide a set of advisors and tools to help make it easier to tune SQL.

While there are more products that are part of the lifecycle management portfolio, including InfoSphere Optim Test Data Management solutions and InfoSphere Optim Data Growth solutions, this article focuses only on the listed products.


Viewing supported databases

Optim is the family name for a portfolio of products used to deliver a modular, integrated data management environment for managing data across its lifecycle. As part of this definition, IBM considers providing heterogeneous solutions a must to ensure that you can get the highest levels of productivity. While not all the InfoSphere Optim products currently provide full heterogeneous support, the strategic vision for InfoSphere Optim products is to provide heterogeneous support in the future. Table 2 shows which database servers each product in this article supports.

Table 2. Supported databases
Databases supportedDB2 for LUW V9.1, V9.5, V9.7DB2 for LUW V9.8DB2 for i® V5R4DB2 for z/OS V8.1, V9.1, V10Informix Dynamic Server V10, V11.10, V11.50, V11.7Oracle 10g R1, R2, 11g R1Sybase Adaptive Server Enterprise Versions 12.x, 15.0Derby Versions 10.0, 10.1, 10.2Generic JDBC Version 1.0Microsoft® SQL Server Enterprise 2005MySQL Versions 4.0, 4.1, 5.0, and 5.1Teradata 2.x
Data StudioXXXXX
InfoSphere Data ArchitectXXXXXXXXXXXX
Optim Development StudioXXXXXX
Optim pureQuery Runtime (platform-specific versions)XXXX
Optim Database AdministratorXX
Optim Query Workload TunerX
Optim Query TunerXXX
Optim Performance ManagerXX
Optim High Performance UnloadXXz/OS version available
DB2 Merge BackupX
DB2 Recovery ExpertXz/OS version available

¹Only supported for Java

²ASE 15 only


Understanding the packaging

IBM Data Studio is a downloadable product that contains no-charge capabilities and supports the DB2 family and Informix® database servers. It provides database developers and DBAs with the basic set of required tools for database development and administration, including an Eclipse-based client (known as Data Studio) and a server-based monitoring application (known as Data Studio Health Monitor).

The Data Studio Eclipse-based client is available in two different packaging options (see Resources for links to the downloads):

  • The stand-alone package is a lighter-weight offering designed specifically for administrators to get up and running quickly and easily. This package is a proper subset of its sibling as of the 2.2.1 release.
  • The integrated development environment (IDE) package targets developers or DBAs who require an integrated (shell sharing) Eclipse development environment for Java, XML, and web services. (All of these capabilities are also available in Optim Development Studio.) Also, this package is designed for anyone who wants to shell-share this package with compatible Eclipse-based products, such as InfoSphere Data Architect, Optim Query Tuner, or Rational Application Developer. See the Technote in Resources for compatibility information regarding Eclipse offerings.

The capabilities of the stand-alone package compared to the IDE package (as of Version 2.2.1.1) are shown in Table 3.

Table 3. Data Studio stand-alone capabilities compared to IDE capabilities
GroupDetailsData Studio stand-aloneData Studio IDE
Architecture data modeling
Shell sharing with other Rational or Optim productsX
Database overview diagramsXX
Application development
Integrated Query Editor (SQL and Xquery) with query formattingXX
SQL BuilderXX
Advanced query formattingX
Visual ExplainXX
Access plan graph (enhanced Visual Explain)XX
Single query statistics advisorXX
SQL query environment capture for serviceability (z/OS only)XX
SQL routine editor and debugger (including PL/SQL for DB2 for Linux, UNIX, and Windows deployments)XX
Java routine editor and debuggerX
SQLJ developmentX
XML editor, schema editor, and annotated XSD mapping editorX
Data Web Services development and deploymentX
Instance management
Start, stop, quiesce, unquiesce a DB2 for Linux, UNIX, and Windows instanceXX
Start, stop, quiesce, unquiesce a DB2 pureScale memberXX
Configure a DB2 for Linux, UNIX, and Windows instanceXX
Configure a DB2 pureScale memberXX
Object management
Create, alter, drop DB2 or IDS server objectsXX
View and edit privileges for DB2 and IDS data server objects and authorization IDSXX
Impact analysis - report on dependenciesXX
Generate DDLXX
Generate commands for DB2 for Linux, UNIX, and Windows - start, stop, quiesce, etc.XX
Generate utilities for DB2 for Linux, UNIX, and Windows - backup restore, reorg, etcXX
Data distribution viewerXX
Data management
Export and import tableXX
Extract data, extract as XML (from SQL results)XX
Sample contentsXX
Load dataXX
Unload using Optim High Performance Unload (separate purchase)XX
Edit dataXX

The Data Studio Health Monitor provides database health monitoring, notification, and status displays for monitored databases. Health monitoring is focused on assuring that there are no blocking conditions in the database, such as out-of-storage conditions, that would prevent applications from running. Performance monitoring capabilities are provided by purchasable offerings, including Optim Performance Manager


Exploring additional capabilities

The rest of this article focuses on the additional capabilities you get over the IBM Data Studio no-charge capabilities. For example, although SQL scripts and stored procedure development are included in Data Studio, pureQuery development capabilities are included only in Optim Development Studio. Basic schema management is in Data Studio, but you need Optim Database Administrator for handling complex schema changes.


InfoSphere Data Architect

Overview

Although not named Optim, IBM InfoSphere Data Architect 7.5.3 plays a key role in managing data across its lifecycle. It provides enterprise data modeling and integration design modeling to help data architects to understand information assets and their relationships, to design federated databases, and to streamline database projects. InfoSphere Data Architect provides advanced features to help data architects discover, model, visualize, relate, standardize, and develop heterogeneous data assets. It gives data professionals the capability to:

  • Intelligently design a relational or federated database
  • Perform impact analysis across models
  • Compare and synchronize models to deployed databases
  • Create enterprise data standards, verify compliance, and generate compliant models

Features

Table 4 shows some of the key features of InfoSphere Data Architect as of 7.5.3. For basic visualization of physical databases, the no-charge option in Data Studio does include physical overview diagrams.

Table 4. InfoSphere Data Architect features
GroupDetailsData Studio *no charge*InfoSphere Data Architect
Data modeling
Database overview diagramsXX
Enhanced diagrams (ILOG)X
Create logical and physical data modelsX
Import from COBOL copybooksX
Import from other modeling toolsX
Auto-discover or identify relationshipsX
Compare and synchronize two data modelsX
Analyze models for standards conformanceX
Privilege modeling on physical modelX
Integration with Rational Software ArchitectX
Integration with InfoSphere DiscoveryX
Data privacy domain modeling and integration across Optim solutionsX

Getting the product

InfoSphere Data Architect is a priced offering. If you are already an InfoSphere Data Architect or Rational Data Architect customer, you are automatically entitled to download the 7.5.3 version (of InfoSphere Data Architect) from Passport Advantage. InfoSphere Data Architect is now included with select versions of InfoSphere Data Warehouse (see the InfoSphere Warehouse Advanced Enterprise Edition announcement letter in Resources).

If you are not a current customer, you can download a 30-day trial from developerWorks (see Resources). After the trial expires, you can reactivate the product by purchasing the product and downloading a product activation kit from Passport Advantage.

To purchase the product, contact your IBM sales representative, contact an authorized IBM Business Partner, or purchase online (see Resources).

Changes in V7.5.3 and 7.5.3.1

  • Enhanced multi-dimensional modeling, including automatic discovery and notation of facts, measures, dimensions, and outriggers. The new release also includes the automatic de-normalization of operational data for designing warehouses.
  • Lossless integration with InfoSphere Warehouse and Cognos® Framework Manager.

Optim Development Studio and pureQuery Runtime

Overview

IBM Optim Development Studio provides an integrated development environment for creating and testing database and Java applications that access DB2, Informix, Oracle, or Sybase databases. Combined with pureQuery Runtime, Optim Development Studio provides a data access layer for Java-based applications to ease development coding and to provide superior performance and manageability. These offerings:

  • Provide an easy-to-use data access layer within Java applications to assist in development of Java database applications that access DB2, IDS, or Oracle databases.
  • Develop and debug stored procedures using SQL/PL and PL/SQL.
  • Deploy static SQL within new or existing Java applications to improve application performance, manageability, and security within your applications.
  • Correlate SQL to original Java source code for quicker problem diagnosis.
  • Prevent errors by enabling you to understand the impact that changing a database object has on the application and by directing you to the relevant place in the source code.
  • Eliminate SQL injection risk by allowing only approved SQL to be executed.
  • Deploy efficient SQL by examining new SQL metrics, including number of executions, aggregated elapsed time, and minimum and maximum execution times, to help identify hot spots in the application.
  • Integrate with Optim Performance Manager for problem SQL identification and improved problem isolation.
  • Replace poorly performing SQL with a more optimal SQL statement without modifying the program source code.
  • Use the SQL Repository Perspective to manage the work flow of static SQL, and manage captured SQL by application using the SQL Repository.
  • Deploy procedures, functions, and SQL scripts across multiple databases to facilitate keeping development and test databases synchronized.

Optim pureQuery Runtime also supports the capability to run existing .NET and CLI applications against DB2 using static SQL, which can lead to reduced CPU and enhanced performance over dynamic access.

Features

Table 5 shows some of the key features of Optim Development Studio and pureQuery Runtime and how they complement the basic capabilities in Data Studio.

Table 5. Development capabilities in Optim Development Studio and pureQuery Runtime
GroupDetailsData Studio *no charge*Optim Development StudioOptim pureQuery Runtime
Application development
Integrated Query Editor (SQL and Xquery)XX
SQL BuilderXX
Visual Explain for IBM databasesXX
Visual Explain for Oracle databasesX
Create Stored Procedure and UDF templatesX
Deployment ManagerX
SQL Routine DebuggerXX
PL/SQL Routine Debugger against DB2 LUW databasesXX
PL/SQL Routine Debugger against Oracle databasesX
Java Routine DebuggerXX
XML EditorXX
Data Web ServicesXX
Create, alter, drop IBM database objectsXX
Create, alter, drop Oracle database objectsX
Manage security privileges for IBM database objectsXX
Manage security privileges for Oracle or Sybase database objectsX
Deploy Web Services on DataPower appliances & JMSX
Advanced XML editors & document generationX
Tooling to build pureQuery codeX
Tooling for Static SQL Binding for JavaXX
Correlate SQL to Java source codeXX
Impact Analysis for Java (object, SQL, source code)XX
SQL execution statisticsXX
Execute pureQuery codeXX
JDBC Batch UpdatingX
Approval of SQL to be executedX
SQL replacement and lockdownX
SQL Repository PerspectiveXX

Getting the product

If you are already an Optim Development Studio and Optim pureQuery Runtime customer, you are automatically entitled to download the 2.2.1 versions of those products from Passport Advantage. Optim Development Studio is now included with select versions of DB2 for Linux, UNIX, or Windows, InfoSphere Data Warehouse, DB2 Connect, and Informix. You can download Optim Development Studio from the Windows or Linux eAssembly of the most recent version of those products on Passport Advantage. A 30-day trial of Optim Development Studio is also available (see Resources). When downloading the Optim Development Studio 2.2.1, you also receive the Optim pureQuery Runtime to use only on your workstation.

To purchase Optim pureQuery Runtime for production use, contact your IBM sales representative, authorized IBM Business Partner, or purchase online (see Resources).

Changes in V 2.2.0.3, V2.2.1, and V2.2.1.1.

Optim Development Studio and Optim pureQuery Runtime now include the following features:

  • Improved application SQL management with the SQL repository, as described in the article on fix pack 3 listed in Resources.
  • Capability to convert SQL scripts into pureQuery capture files.
  • Easier synchronization of application logic across development and test databases using a deployment manager to deploy procedures, functions, and SQL scripts.
  • Integration with Optim Performance Manager Extended Edition in order to import performance metrics for problem SQL identification and to export application metadata to facilitate problem isolation to specific Java source code.
  • Support for Sybase and the ANTs Software product IBM DB2 SSacSA.

Optim Database Administrator

Overview

If your job involves managing database changes, you can make good use of the expanded change management functions now included in Optim Database Administrator. Although the no-charge Data Studio can provide simple DDL functionality, you need the full Optim Database Administrator to perform extended alters (those alters that require a drop and recreate) and to compare and synchronize test databases to production databases. Optim Database Administrator does the following:

  • Manages complex structural changes while preserving data, privileges, dependent objects, and application binding.
  • Compares and synchronizes schemas with custom mapping features.
  • Migrates database objects, data, and privileges and generates maintenance utility commands
  • Reports on the impact of proposed changes to identify dependencies and mitigate risk.
  • Documents a complete history of changes, including scripts, models, logs, and reports to capture the entire change from start to finish.
  • Integrates with InfoSphere Data Architect for seamless transition from logical to physical design and deployment

Features

Table 6 shows some of the key features of Optim Database Administrator as of Version 2.2.3 when compared with no-charge Data Studio. Popular functions from the DB2 Control Center are included in Data Studio. The full Optim Database Administrator product expands on basic DDL generation with features that include extended alter, migrate, and compare.

Table 6. Table 5. Database administration capabilities of Data Studio augmented with Optim Database Administrator
GroupDetailsData Studio *no charge*Optim Database Administrator (DB2 for Linux, UNIX and Windows only)
Object management
Create, alter, drop database objectsXX
Impact analysis for databases - report on dependenciesXX
Generate DDLXX
Rebind packageXX
Generate commands (start, stop, quiesce, etc.)XX
Generate utilities (backup, restore, reorg, etc.)XX
Support for database partitioning feature (DPF)XX
Privilege managementXX
Compare single objectXX
Configure database loggingXX
Configure automatic maintenanceXX
Manage configuration parametersXX
HADR managementXX
pureScale managementXX
Federated database managementX
Copy and paste database objectsX
Extend alters (requiring drop and recreate)X
Data preservation with extended altersX
Export admin and change scripts as CLP scriptsX
Compare multiple objectsX
Migrate objects and their dependenciesX
Integration of Optim High Performance Unload in change scriptsX
Generation of an undo change scriptX
Object change reportsX

Getting the product

If you are a current Optim Database Administrator or Data Studio Administrator customer, you are automatically entitled to download the 2.2.3 version of this product from Passport Advantage. Optim Database Administrator is now included with select versions of DB2 and InfoSphere Data Warehouse (see the DB2 Advanced Enterprise Server Edition announcement letter in Resources). You can download Optim Database Administrator from an appropriate eAssembly for Windows or Linux on Passport Advantage. If you are not a current customer, you can download a 30-day trial (see Resources). When the trial license expires, you can reactivate that functionality by purchasing the product and downloading a product activation kit from Passport Advantage.

To purchase the product, contact your IBM sales representative, authorized IBM Business Partner, or purchase online (see Resources).

Changes in V2.2.3 and 2.2.3.1

  • Catalog navigation enhancements, including the Administration Explorer.
  • Object List Editor, customization, and performance improvements.
  • Enhanced compare filtering,
  • Table data editor enhancements for DB2 databases

Optim High Performance Unload

Overview

Optim High Performance Unload for DB2 for Linux, UNIX, and Windows (formerly DB2 High Performance Unload) provides high-speed unloading, extracting, and repartitioning of data. DB2 High Performance Unload remains available as a separate product for DB2 z/OS databases. It helps database administrators to work with very large quantities of data with less effort and with faster results. Optim High Performance Unload can:

  • Enable creation of customizable unloads with use of SELECT syntax to filter columns and rows.
  • Scale, with support for partitioned databases and the capability to unload to multiple directories in parallel.
  • Reduce the risk of impacting online production by unloading from a backup rather than the live table.
  • Recover from single table failures or accidental table drops by extracting a single table from a backup for restoration.
  • Easily migrate data to a partitioned database or warehouse using the splitter function to unload into pre-split files ready for parallel loading.

Features

Data Studio provides a number of data management features to facilitate unloading, editing, and loading data. These features are all available as no-charge features accessible from the Data Studio Eclipse console. When you purchase Optim High Performance Unload, you can also execute high-performance unload directly from the Data Studio console. See Table 7 for more details.

Table 7. Data management features of Data Studio with Optim High Performance Unload
GroupDetailsData Studio *no charge*Optim High Performance Unload
Data management
Export tableX
Extract, extract as XMLX
Sample contentsX
Load dataX
Edit dataX
Unload using Optim High Performance UnloadXX
Use Optim High Performance Unload with change scriptsX
Use parallel processing when unloadingX
Split unloading into multiple files for parallel loadingX
Unload directly to another table (without disk)X
Unload from incremental image copiesX
Unload from backupsX

Getting the product

If you are already a DB2 High Performance Unload for Multiplatforms customer, you are automatically entitled to download the 4.1 version from Passport Advantage. If you are not a current customer and you would like to purchase the product (or obtain a trial license), contact your IBM sales representative or authorized IBM Business Partner. To purchase the product or obtain a trial license, contact your IBM sales representative, authorized IBM Business Partner, or purchase online (see Resources).

Changes in V4.2

The newest version of Optim High Performance Unload V4.2 provides support for the highly scalable DB2 pureScale. There are also a number of user enhancements such as the capability to unload from a schema, which gives you more options to quickly unload data than the standard approach. For more information, see the full announcement listed in Resources.


DB2 Recovery Expert

Overview

DB2 Recovery Expert for Linux, UNIX and Windows recovers database objects safely, precisely, and quickly without having to resort to full database recovery. It can help you avoid accidental data loss or corruption by providing a faster, less costly method of recovery.

The log analysis features enable organizations to monitor database changes and to provide intelligent analysis of altered, incorrect, or missing database assets.

Database administrators can use the product to do the following:

  • Get very granular with database object recovery automating the recovery even as the system remains online.
  • Automate the process of rebuilding these assets to a specified point in time.
  • Determine the most efficient technique for a recovery situation.
  • Recover database objects without using resource intensive disaster recovery.
  • Analyze and provide diagnostics of altered, incorrect, or missing database assets, including tables, indexes, or data.
  • Roll back unwanted data changes precisely throughout a database.

Features

Table 8 shows some of the main features of DB2 Recovery Expert as compared to the no-charge Data Studio. You can see that many of the advanced recovery capabilities are available in DB2 Recovery Expert.

Table 8. Data Studio stand-alone capabilities compared to DB2 Recovery Expert capabilities
GroupDetailsData Studio stand-aloneDB2 Recovery Expert
Data management
Drop DB2 server objectsXX
Support for database partitioning feature (DPF)XX
Support for DB2 LUW 9.7 featuresXX
Web-based user interfaceX
Command-line clientX
Read DB2 transaction logs for reportingX
Generate UNDO/ REDO SQLX
Point-in-time object recoveryX
DB2 object recovery guidanceX
Dropped object recoveryX
Saved recovery: log analysis session supportX

Getting the product

If you are already a DB2 Recovery Expert for MultiPlatforms V2.1 customer, you are automatically entitled to download the 3.1 version from Passport Advantage. If you are not a current customer and you would like to purchase the product, contact your IBM sales representative or authorized IBM Business Partner (see Resources). There is also a 30-day trial version of DB2 Recovery Expert available on the product website.


DB2 Merge Backup

Overview

DB2 Merge Backup for Linux, UNIX, and Windows reduces DB2 backup times for your production system. This ensures that your backup and recovery strategy lines up with your service level agreements (SLAs) for accuracy and responsiveness.

DB2 Merge Backup merges incremental and delta backups with the latest full backup to create a new, more current full backup. You essentially never have to take a full backup again.

Database administrators can use the product to do the following:

  • Ensure a complete, accurate DB2 backup even as data volumes grow.
  • Provide the necessary recovery assets with low production impact.
  • Perform backup processes with minimal impact on production systems to support business objectives.
  • Develop a scalable approach to responding to unplanned service interruptions.

All this processing runs outside and independently of the current DB2 process. If a restore is later required, DB2 Merge Backup can help to speed the recovery.

Features

Table 9 shows some of the main features of DB2 Merge Backup as compared to the no charge Data Studio. You can see that a lot of the advanced capabilities with respect to your backup are contained in DB2 Merge Backup.

Table 9. Data Studio stand-alone capabilities compared to DB2 Merge Backup
GroupDetailsData Studio stand-aloneDB2 Merge Backup
Data management
Support for database partitioning feature (DPF)XX
Support for DB2 LUW 9.7 featuresXX
Merging delta/incremental with full backupsX
Command-line clientX
Integrates with DB2 history file for complete informationX
Mix online/offline backupsX
Stand-alone mode: Run DB2 MBK separately from DB2 ServerX
Integration with Optim High Performance UnloadX

Getting the product

If you are not a current customer and you would like to purchase the product, contact your IBM sales representative or authorized IBM Business Partner (see Resources). There is also a 30-day trial version of DB2 Merge Backup (see Resources).


Optim Performance Manager Extended Edition

Overview

IBM Optim Performance Manager Extended Edition for DB2 for Linux, UNIX, and Windows provides in-depth database monitoring, problem isolation, and trend analysis for DB2 for Linux, UNIX, and Windows databases. Database administrators can use the product to:

  • Quickly identify potential problems using interactive, detailed dashboards that can detect overall environment problems or specific database or application issues. Alerts can be read more easily on a new Health Summary dashboard as well as on other diagnostic dashboards. Alerts can be sent using email or pager to the on-call administrator when problems reach warning and error thresholds.
  • Gain unprecedented insight into the database transaction software stack when running Java or CLI applications, including the database client, the application server, and the network, giving immediate insight into where database workloads, transactions, and SQL requests are spending their time. The product Includes out-of-the-box configurations for SAP, WebSphere®, Cognos, InfoSphere DataStage®, and InfoSphere SQL Warehouse applications.
  • Enhance Tivoli® monitoring solutions, such as ITCAM for Transactions, to provide detailed database diagnostics while remaining in the context of the overall enterprise application. This reduces the time and effort needed to diagnose and trace a problem from the application through to the database server.
  • Launch into Optim Query Tuner (if installed) directly from SQL-related dashboards to do immediate tuning of problematic SQL, closing the loop on performance problem resolution.
  • Prevent performance problems using out-of-the-box, interactive reports for trend analysis and growth planning using a powerful repository of historical performance metrics.
  • Monitor DB2 Workload Manager (WLM) entities, statistics, and activities over time to validate WLM definitions, which can be set up and defined using integrated WLM configuration tooling.

For more details on the features and enhancements, see the developerWorks article "What’s new in Optim Performance Manager Extended Edition" listed in Resources.

Features

Optim Performance Manager is available in multiple editions: Enterprise Edition, Workgroup Edition, Content Manager Edition, and Extended Edition. The first three editions contain the same function with differing license terms reflecting the environments to which they are targeted. Those three editions replace DB2 Performance Expert for Linux, UNIX, and Windows. Optim Performance Manager Extended Edition is a superset of the others, and it includes the Extended Insight feature that provides transaction response time monitoring, Tivoli integration, and enhanced DB2 Workload Manager features. Table 10 provides a visual overview of the features available in Data Studio Health Monitor, Optim Performance Manager Enterprise Edition, Optim Performance Manager Workgroup Edition, Optim Performance Manager Content Manager Edition, Optim Performance Manager Extended Edition, and DB2 Performance Optimization Feature.

Table 10. Features of performance management
FeaturesData Studio Health MonitorOptim Performance Manager Enterprise Edition, Optim Performance Manager Workgroup Edition, Optim Performance Manager Content Manager EditionOptim Performance Manager Extended EditionDB2 Performance Optimization Feature
Multi-system monitoringXXXX
Health summary dashboardXXXX
Health monitoring with customizationXXXX
Alert visualization and notificationXXXX
Alert drill downXXXX
View current or historical dataXXXX
Performance alertsXXX
Database server performance KPI templates and customizationXXX
Database server performance KPI monitoringXXX
Database server overview dashboardXXX
Active SQL analysis dashboardXXX
Buffer pool and I/O analysis dashboardXXX
Memory analysis dashboardXXX
Locking analysis dashboardXXX
Logging analysis dashboardXXX
Workload analysis dashboardXXX
System analysis dashboardXXX
Utilities analysis dashboardXXX
Performance warehouseXXX
Out-of-the-box interactive reportingXXX
Data Studio integration to visual explainXXX
Optim Query Tuner integration to query analysis and tuningXXX
DB2 Workload Manager configuration and reportingXXX
Application accelerators (SAP, ISW, Cognos, Information Server)XXX
Response time monitoring for complete database transaction (spans application stack)XX
Response time component analysisXX
SQL statement detail analysisXX
DB2 Workload Manager automation based on response time objectivesXX
WebSphere connection pool detailXX
Optim Development Studio integration (trace problem to Java source code)XX
Tivoli offering integration (contextual drill-down from ITCAM for transactions, contextual drill through to detailed OS metrics with ITM for servers)XX
DB2 Workload Manager featureX
Query Patroller (deprecated)X

Note: Q-replication monitoring capability is available as a separate, no-charge download (see Resources).

Getting the product

  • Optim Performance Manager editions are available to customers whose subscriptions are current on Passport Advantage.
  • If you are a DB2 Performance Expert customer who is current on maintenance, you are automatically entitled to download the equivalent 4.1 edition of Optim Performance Manager from Passport Advantage (that is, Enterprise Edition, Workgroup Edition, or Content Manager Edition).
  • If you own the DB2 Performance Expert Extended Insight Feature, you can download Optim Performance Manager Extended Edition.
  • Optim Performance Manager Enterprise Edition is available as part of DB2 Advanced Enterprise Server Edition and InfoSphere Warehouse Departmental Edition. Download Optim Performance Manager from the respective e-assemblies on Passport Advantage.
  • Optim Performance Manager Extended Edition is also available as part of the DB2 Performance Optimization Feature, which is included in InfoSphere Warehouse Enterprise Edition and Advanced Enterprise Edition. Download Optim Performance Manager from the respective e-assemblies on Passport Advantage.
  • If you are not a current customer, to purchase the product or obtain a trial license, contact your IBM sales representative, authorized IBM Business Partner, or purchase online (see Resources).

Changes in V4.1.0.1 and V4.1.1

  • Up-and-running simplification, including an integrated console server
  • pureScale-specific monitoring enhancements
  • More flexible partition monitoring capabilities
  • Integrated health monitoring
  • More flexible alert management
  • Enhanced Tivoli integration
  • Optim Development Studio integration to pinpoint SQL source code

Optim Query Tuning solutions

Overview

Optim query tuning solutions provide an environment to identify and tune poorly performing SQL statements with advisors and tools to help guide you to a solution. Formerly known as DB2 Optimization Expert for z/OS, Optim Query Workload Tuner for DB2 for z/OS provides this support for both single SQL statements and for SQL workloads to help database administrators make the best decision to optimize entire workloads. A subset of functionality is available for both DB2 for z/OS and DB2 for Linux, UNIX, and Windows. Both Optim Query Tuner for DB2 for Linux, UNIX, and Windows and Optim Query Tuner for DB2 for z/OS focus on single query tuning. They can help developers and application database administrators focus on query tuning efforts early in the development cycle.

Some query tuning and query formatting capabilities are available at no charge in Data Studio for both DB2 for z/OS and for DB2 for Linux, UNIX, and Windows. For z/OS, the previous no-charge tool, Optimization Service Center, has been deprecated, which means that no new development is occurring on that product.

Features

Table 11 provides a visual comparison of Optimization Service Center for DB2 for z/OS, Data Studio, Optim Query Tuner, and Optim Query Workload Tuner.

Table 11. Table 8. Features of query tuning
GroupDetailsOptimization Service Center (z/OS) *deprecated*IBM Data Studio *no charge*Query TunerQuery Workload Tuner (z/OS)
SQL tuning
Analyze stored procedures and packages from catalogXXXX
Access Plan Graph (enhanced Visual Explain)XXXX
Access Plan ExplorerXX
Analyze queries from statement cacheXXXX
Analyze queries from Data Studio and Optim editorsXXX
Query FormatterXXXX
Query AnnotationXXX
Visual Plan HintXXX
Profile-based monitoringXX
Statistics Advisor - Single QueryXXXX
Statistics Advisor - SQL WorkloadXX
Index Advisor - Single QueryXX
Index Advisor - SQL WorkloadX
Query Advisor - Single QueryXX
Query Advisor - SQL WorkloadX
Access Path AdvisorXX
Single query environment capture (z/OS only)XXXX
Workload environment capture (z/OS only)XX
Query reportsXXX
Versioning of statements and resultsX
Integration with Query Monitor, OMEGAMON XE for DB2, Performance Expert, or user-defined SQL repositoriesXX

¹OSC integrates only with Query Monitor

Getting the product

Optim Query Tuner offerings are available in the following versions:

  • Optim Query workload Tuner for DB2 for z/OS provides tuning advice for single queries and query workloads.
  • Optim Query Tuner for DB2 for z/OS provides tuning advice for single queries.
  • Optim Query Tuner for DB2 for Linux, UNIX, and Windows provides tuning advice for single queries

If you are already a DB2 Optimization Expert for z/OS customer, you are automatically entitled to obtain Optim Query Workload Tuner for DB2 for z/OS. Optim Query Tuner for DB2 for Linux, UNIX, and Windows is now included with select versions of InfoSphere Data Warehouse (see the InfoSphere Warehouse Advanced Enterprise Edition announcement letter in Resources). Otherwise, you must purchase a license from IBM to use any of the query tuning products. See your IBM Sales Representative or see Resources for more information about purchasing this product.

Changes in V2.2.1 and 2.2.1.1

The new release includes a streamlined UI for query tuning workflow plus additional support in validating and establishing the DB2 environment to use the query tuning features. The fixpack introduces a new access plan explorer to provide textual-based explain detail to ease navigation and analysis of very large queries resulting from enhanced query captures from DB2 Query Monitor or resulting from user-defined SQL repositories.


Conclusion

Acknowledgment

Thank you to Deb Jenson and Kathy Zeidenstein from IBM for writing, updating, and reviewing previous versions of this article.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=430613
ArticleTitle=Understanding the packaging of Optim Solutions for database development, administration, and performance management
publish-date=08112011