Overview of the InfoSphere Optim data management solution

Click on a product for more information or scroll down to read about all of the products in the InfoSphere Optim data management solution.

Figure 1. The key products that help IT staff manage the various phases of the data lifecycle. Click on a product in the graphic for more information.

IBM® Data Studio is the no-charge offering that includes many fundamental capabilities for database administration and development, including basic health monitoring and single query tuning. Some of the priced offerings are included with certain IBM data server or PureSystems® offerings at no additional charge. You can check the product information for your data server to see which offerings might already be included.

InfoSphere Data Architect

Supported servers: InfoSphere Data Architect supports many database servers, including DB2®, Oracle, SQL Server, Netezza®, and Sybase.

The data architect’s key tool is InfoSphere Data Architect, which is used for discovering, modeling, relating, and standardizing data.

Figure 2. InfoSphere Data Architect diagram.

InfoSphere Data Architect supports logical and physical modeling and makes it easy to reverse engineer models from existing databases, generate physical models from logical models, generate DDL from physical models, and visualize the impact of changes.

For warehouse development, InfoSphere Data Architect includes automatic discovery and annotation of facts, measures, dimensions, and outriggers. Normalized schemas can be denormalized into star, snowflake, and starflake schemas. To support IBM Cognos®, InfoSphere Data Architect provides the unique capability to push a dimensional model to Cognos Framework Manager. The generated Cognos Framework Manager model is a hub model that can be used as the input for building cubes in Cognos. InfoSphere Data Architect can also import warehouse models from IBM Industry Models, which provide a set of pre-defined data model templates that are based on industry best practices. Data architects can use these models to design a data model for use on a Netezza appliance.

InfoSphere Data Architect also integrates with other InfoSphere, InfoSphere Optim, and Rational® offerings across the data and application lifecycle. For example:

IBM Rational RequisitePro®

InfoSphere Data Architect integrates with IBM Rational RequisitePro, which enables data modelers to associate data model elements with the originating requirements from a business analyst.

Rational Software Architect

To enable top-down development, InfoSphere Data Architect can take a UML diagram from Rational Software Architect and convert it to a logical data model and then to a physical data model, which the DBA can then review and deploy into the target database in support of the application. The inverse is also true; to support bottom-up development, a physical model can be converted to a logical data model in InfoSphere Data Architect and then converted to a UML application model in Rational Software Architect.

InfoSphere Information Server and InfoSphere Business Glossary

InfoSphere Data Architect is useful as an integration engine for data integration projects. You can set up an IBM InfoSphere Information Server metadata repository, and you can import metadata from InfoSphere Data Architect to the InfoSphere Information Server metadata repository. You can combine the business semantics of the IBM InfoSphere Business Glossary with the modeling capability of InfoSphere Data Architect to create standardized data models that comply with your business rules.

InfoSphere Data Architect integrates with many other offerings depending on your needs.

Figure 3. The different types of offerings that integrate with InfoSphere Data Architect.

↑ Back to the top.

Data Studio

Supported servers: Many data servers including DB2 for Linux, UNIX, and Windows, DB2 for i, DB2 for z/OS®, and IBM Informix®. See a complete list of the capabilities that are supported for each particular data server.

Data Studio is a foundational offering that includes support for application development, tuning, and administration tasks across the data management lifecycle:

↑ Back to the top.

InfoSphere Optim pureQuery Runtime

Supported servers: DB2 for Linux, UNIX, and Windows, DB2 for i, DB2 for z/OS, IBM Informix, and Oracle

For data-oriented developers or DBAs, Data Studio contains all the database administration and data development capabilities that a database developer needs. For Java developers, Data Studio with pureQuery Runtime takes Java development to a new level.

Java developers will find it easy to generate the Data Access Object (DAO) pattern for their applications by using the pureQuery API, which is much simpler to code than JDBC.

Any application, including those that use DB2 CLI, .NET, ODBC, JDBC, or Java frameworks such as Hibernate, can use pureQuery Runtime to enhance database performance. For example, with pureQuery Runtime, dynamic SQL can be converted to static SQL for better performance and security.

Figure 6. InfoSphere Optim pureQuery Runtime enhances application performance for existing applications; in addition, Java developers can use Data Studio with pureQuery runtime to develop new applications with high-performance Java data access layers.

↑ Back to the top.

InfoSphere Optim Test Data Management Solution

Supported servers: DB2, Oracle, SQL Server, and more. See a list of supported data servers.

Historically, testers have used clones or extracts of live customer data to attempt to provide contextually accurate data, but a simple extract might not be sufficient and full clones can break the budget. In addition, enterprises must protect confidential data and personally identifiable information ("PII"), such as bank account numbers and national identifiers.

The InfoSphere Optim Test Data Management Solution helps DBAs create a right-sized, production-like test environment based on both DBMS-managed and application-managed relationships, whether within a single source or across heterogeneous sources. In addition, data manipulation capacity supports manual and automated creation of test cases within the test data. The solution supports an iterative testing model that simplifies specifying error and boundary conditions and comparing test results to baseline data.

Figure 7. InfoSphere Optim Test Data Management Solution converts real data to test data for a test system.

For compliance requirements, DBAs can optionally extend the InfoSphere Optim Test Data Management Solution with the InfoSphere Optim Data Privacy Solution to ensure that sensitive information is de-identified. The privacy solution includes pre-defined masking algorithms for common, sensitive information.

↑ Back to the top.

InfoSphere Optim Workload Replay

Supported servers: DB2-to-DB2, DB2 for z/OS-to-DB2 for z/OS, and non-DB2 (heterogeneous) capture and replay is supported.

In contrast to the InfoSphere Test Data Management solution, which helps enterprises create realistic test data, InfoSphere Optim Workload Replay (InfoSphere Workload Replay) helps DBAs capture and save live database workloads. They can then use these workloads to help assess and predict the impact of changes, such as:

• Moving to a new database vendor
• Consolidating database servers
• Upgrading a database or the underlying operating system to a new version or fix pack
• Changing configuration parameters, adding indexes, or increasing the amount of data
• Updating the database application
• Moving to new hardware

For example, to test the behavior of the workloads in a changed database environment, a DBA can capture a workload and replay it on a database server that was upgraded to a new database level, or even to a different database vendor. The workloads can also be replayed at different speeds to simulate increased traffic, additional users, and other differences.

InfoSphere Workload Replay can capture a production workload, including all the information that is needed for real-life simulation, such as the original application timing, order of execution, transaction boundaries, isolation levels, and other SQL and application characteristics. By using InfoSphere Workload Replay, DBAs can test changes to the database environments without compromising the production database performance.

Comparison reports compare the execution of a workload with the execution of another. Comparison reports can be used to understand how well a captured workload replays on another database system, including differences that might be caused by missing objects in the test environment, incorrect schema mappings, subsets of data, and more. DBAs can compare response times and drill down to see more detail about each SQL statement and its response time compared with the baseline.

From the reports, DBAs can remove unmatched SQL statements and transactions that for example originated from a specific application, and then replay the modified workload to isolate performance issues related to that appliance. From the same interface, DBAs can export a workload as XML and feed it to InfoSphere Optim Query Workload Tuner for advice on how to achieve better query performance. They can then update their test environment accordingly, and replay the workload again to verify the changes.

Figure 8. With InfoSphere Optim Workload Replay, compare workload executions to determine if changes to your test system affect performance.

↑ Back to the top.

InfoSphere Optim Configuration Manager

Supported servers: DB2 or Linux, UNIX, and Windows and DB2 for z/OS

InfoSphere Optim Configuration Manager helps administrators centrally manage the complicated world of client and database server configurations. To avoid outages, most IT shops test managed resources in pre-production environments with specific, verified versions of software and then roll out those changes into production. In general, production, staging, and recovery configurations are designed to be identical in certain aspects. When these configurations differ over time because of hardware and software changes, the resulting configuration gap can cause outages, impact performance, or impact the ability to recover quickly in a disaster situation.

InfoSphere Optim Configuration Manager provides a central management repository for managing and identifying changed configurations and connections. In addition, InfoSphere Optim Configuration Manager gives administrators the tools to dynamically respond to problem situations by enabling them to reroute work while troubleshooting the problem database. The ability to move work to another system is also helpful when planning and managing the rollout of a new environment.

Figure 9. With InfoSphere Optim Configuration Manager, DBAs can reroute a misbehaving application to a member of a DB2 for z/OS data sharing group known as the penalty box.

With InfoSphere Optim Configuration Manager, administrators can:

• Automatically discover and display client information for databases, such as host name, IP address, user ID, client version information, and more, and store it all in a centralized repository.
• Display and track changes to server and client configurations to quickly determine the root cause of performance degradation.

For DB2 for Linux, UNIX, and Windows, InfoSphere Optim Configuration Manager also provides storage information and can provide recommendations for when and where compression can save space. Seldom-used objects can be relocated to storage that has been allocated but is not being used.

By setting up a multi-temperature storage scheme (DB2 V10.1 or later), enterprises can optimize the use of their storage devices. A multi-temperature storage scheme keeps the data that you expect to access most frequently on the fastest devices and moves the data that you expect to access infrequently to slower devices. A DBA can define storage groups for different classes of storage devices by using IBM Data Studio, and can then use InfoSphere Optim Configuration Manager to schedule a job that migrates table spaces to storage groups based on the age of the data.

↑ Back to the top.

DB2 Advanced Recovery solution for Linux, UNIX, and Windows

Advanced Recovery solution is a set of tools that help DBAs manage to service level agreements for availability and is comprised of the following products:

• InfoSphere Optim High Performance Unload
• DB2 Merge Backup
• DB2 Recovery Expert

↑ Back to the top.

InfoSphere Optim High Performance Unload

Supported servers: DB2 for Linux, UNIX, and Windows. (A version that supports DB2 for z/OS is also available.)

InfoSphere Optim High Performance Unload provides a high-speed unload utility as an alternative to the DB2 export feature. Because unloads are so fast, DBAs can use this product as a means for data migration. Large amounts of data can be moved from one system to another or be backed up without impacting production environments.

The product is fast because it accesses data files directly, bypasses the database manager altogether, and does not interfere with or slow down production databases. The product can also perform unloads from multiple database partitions, and it provides repartitioning capability in a single step for rapid data redistribution on the same or different system. Single-step repartitioning is particularly useful in warehouse environments in which repartitioning can be a very manual process.

Figure 10. InfoSphere Optim High Performance Unload for repartitioning and data transformation.

↑ Back to the top.

DB2 Merge Backup

Supported servers: DB2 for Linux, UNIX, and Windows

DB2 merge backup enables DBAs to create full database backups by merging existing full backups and incremental and delta backups, which reduces the resources that are needed to back up large databases. Creating the traditional full backups can possibly make the database inaccessible to users and applications for a significant amount of time, depending on the size of the database. IT staff can perform incremental backups to back up data in a way that is transparent to end users, without taking databases offline or disrupting applications.

With DB2 Merge Backup, DBAs can restore data to get applications running quickly when planned or unplanned service interruptions occur. DBAs can run the merge processing on a different computer, which reduces the amount of resources that are being consumed on the production computer.

Figure 11. DB2 Merge Backup can create a full merged backup by merging existing full backups and incremental backups.

↑ Back to the top.

DB2 Recovery Expert

Supported servers: DB2 for Linux, UNIX, and Windows. (A version that supports DB2 for z/OS is also available.)

DB2 Recovery Expert helps minimize database recovery times by isolating recovery to only particular impacted objects so that DBAs do not need to resort to full database recovery. For example, if a user deletes data from a table in a multi-table space, a DBA can use DB2 Recovery Expert to identify which data was deleted and recover just that data rather than having to recover the whole table space.

DB2 Recovery Expert also enables recovery from data corruption that is caused by flawed applications. For example, assume that a weekly batch job was run by mistake instead of the monthly job and so too much data was deleted or updated. DB2 Recovery Expert can analyze the DB2 log and generate the appropriate UNDO and REDO statements to back out the bad changes. (See the following figure.) Those statements can be exported for review before being run.

Figure 12. DB2 Recovery Expert can generate UNDO and REDO statements to back out changes by analyzing the DB2 log.

↑ Back to the top.

InfoSphere Optim Performance Manager Extended Edition

Supported servers: DB2 for Linux, UNIX, and Windows. (Extended Insight capability is available for DB2 for z/OS in the Tivoli® OMEGAMON® Performance Expert for DB2 for z/OS.)

InfoSphere Optim Performance Manager provides robust and detailed database performance monitoring and alerting capability. The at-a-glance performance overview helps DBAs identify problem areas quickly, and shows where deviations from the baseline are occurring. Diagnostic dashboards provide detailed metrics for particular areas, including locking, SQL statement executions, storage and memory use, and log performance.

Figure 13. The Overview dashboard.

Pre-defined templates provide configuration settings for monitoring different environments, including SAP, InfoSphere Warehouse, OLTP, business intelligence, test, production, and development.

Workload management:

InfoSphere Optim Performance Manager includes the tools to administer and manage the DB2 workload management (WLM) feature, including defining workloads, assigning business priorities to workloads to ensure service level agreements, and monitoring service classes, workloads, and work classes.

Figure 14. The Workload Manager dashboard.

Extended Insight feature:

The Extended Insight feature provides valuable performance information that goes beyond the database engine. With Extended Insight, each SQL statement in a Java, DB2 Call Level Interface (CLI), or .NET application is measured as it traverses the software stack. Each statement is visualized in graphs and charts to help DBAs identify where a response-time issue is occurring: either in the application server (such as WebSphere®), the network, or the database.

Pre-defined Extended Insight templates provide configuration settings for SAP, WebSphere, Cognos, InfoSphere DataStage®, InfoSphere Warehouse, and Java applications.

Integration with Tivoli Composite Application Manager (ITCAM):

InfoSphere Optim Performance Manager Extended Edition integrates its deep database performance insights with the broad, enterprise-wide insights of IBM Tivoli monitoring products. For any transaction problems in the DB2 database component, operators can invoke the Extended Insight dashboard from within the Tivoli Enterprise Portal. This integrated view maintains the context of the problematic database transactions, so that DBAs and operators work together to solve performance problems more easily.

↑ Back to the top.

InfoSphere Optim Query Workload Tuner

Supported servers: DB2 for Linux, UNIX, and Windows and DB2 for z/OS

InfoSphere Optim Query Workload Tuner helps DBAs, developers, or query tuners tune individual queries or query workloads by providing advice for how to achieve better query performance. The user interface is included in Data Studio, and the features are activated by applying the InfoSphere Optim Query Workload Tuner license on DB2.

InfoSphere Optim Query Workload Tuner can tune workloads or queries from numerous sources, such as the statement cache, the DB2 catalog, QMF™, DB2 Query Monitor for z/OS, and the InfoSphere Optim Performance Manager repository.

InfoSphere Optim Query Workload Tuner provides advice for statistics, queries, access paths, and indexes. The advisors analyze the query or workload and provide prioritized recommendations and rationales to help less experienced DBAs or developers learn as they go. Statistics collection recommendations include the RUNSTATS command to run, and index recommendations include the DDL that is required to create the indexes. DBAs can iteratively run the advisors and apply changes until they are satisfied with the performance results.

DBAs can also compare the access paths before and after optimization to see if the optimizer has selected a better access path for the query.

Figure 15. Comparing access plans with InfoSphere Optim Query Workload Tuner.

InfoSphere Optim Query Workload Tuner can provide analysis and advice for single queries, and can also take as input an entire SQL workload, such as all of the SQL statements used in an order processing application. By analyzing an entire workload, InfoSphere Optim Query Workload Tuner can help DBAs determine, for example, which indexes or updated statistics might provide the most benefit for the overall performance of the workload.

InfoSphere Optim Query Workload Tuner fits well within various phases of the data lifecycle; developers can use it iteratively during application development because the query tuning capability is embedded with the Data Studio client. While developing new queries and routines, developers can invoke query tuning directly from the SQL statement in the Data Studio editor. Because the client is Eclipse-based, mainframe developers who use Rational Developer for System z® can also easily install InfoSphere Optim Query Workload Tuner into that environment.

To correct problems that are discovered in test or production systems, database administrators can invoke query tuning from the InfoSphere Optim Performance Manager web console or Tivoli OMEGAMON Performance Expert for DB2 for z/OS.

↑ Back to the top.

InfoSphere Optim Data Growth Solution

Supported servers: See the list of supported data servers.

InfoSphere Optim Data Growth Solution helps DBAs manage data growth, archival, and retention. Oftentimes, both active and inactive data is stored on the production system because inactive data might be required for audit and compliance or for other reporting applications. However, by archiving inactive data, DBAs can help their production applications perform better. InfoSphere Optim Data Growth Solution safely moves sets of relational data from one or more data sources to an archive, which can be stored in a variety of environments. The Data Growth Solution can archive data on the basis of both DBMS and application-managed relationships, whether within a single data source or across heterogeneous data sources.

If needed, the archived data can be easily retrieved to an application environment when additional business processing is required. Users and applications can still access this data by using traditional access methods, such as ODBC and JDBC, and so they can use the data in report writers, such as Cognos, Microsoft Excel, and enterprise applications.

Figure 16. The InfoSphere Optim Data Growth Solution archiving workflow.

↑ Back to the top.