Software Announcement 200-087
April 18, 2000

IBM DB2 UDB Server for OS/390, Version 7 Delivers the Power for Your e-business and Data Warehouse Applications

 ENUS200-087.PDF (157KB)

At a Glance

With Version 7, DB2 UDB Server for OS/390:

  • Integrates data warehousing with the introduction of DB2 Warehouse Manager for OS/390
  • Delivers the QMF Family with DB2 Warehouse Manager
  • Offers migration and fallback support from either Version 5 or 6
  • Introduces additional tuning and management tools
  • Increases DB2 scalability, availability, and performance with:
    • Changing system parameters, without stopping DB2
    • Scrollable Cursors
    • Faster, more usable, more parallel utilities
    • Support for UNION in Views
    • Improved optimization with support for Star Join
    • Support for REXX Language

For ordering, contact:

Your IBM representative, an IBM Business Partner, or IBM Americas Call Centers at 800-IBM-CALL (Reference: LE001).


The DB2 Universal Database® Server for OS/390®, Version 7 program delivers improved performance, availability, and scalability for your e-business and data warehouse applications. A new feature, DB2® Warehouse Manager, brings together the tools to build, manage, govern, and access data warehouses based in DB2 for OS/390.

With DB2 UDB for OS/390, Version 7, your e-business and business intelligence applications can be synergized with the powerful, highly available environment provided by S/390 and OS/390. You can leverage your existing applications while developing and expanding your electronic commerce for the future.

In DB2 UDB for OS/390, Version 7, the DB2 Family is expanding the capability of the DB2 server by integrating warehouse management. The new DB2 Warehouse Manager feature gives a full set of tools for building and using a data warehouse based on DB2 for OS/390. This feature includes:

  • Data Warehouse Center, with a graphical interface for defining and managing the warehouse
  • Information Catalog, a common repository for metadata about the objects, for user understanding and access
  • QMF™, QMF HPO, and QMF for Windows™, for ease of access and user administration

Several DB2 UDB for OS/390, Version 7 enhancements to DB2 can help improve your existing applications. Take advantage of:

  • Utility improvements for increased data availability
  • The ability to change system parameters, without stopping DB2
  • Star Join optimization for better performance of complex queries
  • Index access for small tables

Extend the power and flexibility of your applications with DB2 UDB for OS/390, Version 7 enhancements. You can utilize the following:

  • Unicode support to store international data without character conversion
  • Scrollable cursors for more flexible access to a result table
  • Commit and Rollback from within your stored procedure logic with the use of Commit and Rollback
  • Subselect within an UPDATE statement
  • UNION and UNION ALL within views and nested table definitions
  • Row value expression comparison

With Version 7, DB2 UDB Server for OS/390 delivers even more tools. Use the Try and Buy opportunity to discover the benefits of the following, which are added to the existing tools available with DB2 UDB for OS/390, Version 6:

  • DB2 Forms, a drag and drop solution for rapid development of user friendly e-business front-ends to any DB2 database
  • DB2 SQL Performance Analyzer for evaluating a query's execution cost, before it runs
  • DB2 Bind Manager, which can avoid executing a bind when the existing SQL structure has not changed
  • DBRM Checker, which will only bind those packages in a plan that require it
  • DB2 Recovery Manager, which can coordinate and simplify data recovery from DB2, IMS™, or both

Planned Availability Date

Information on the Version 7 availability date will be provided in an availability announcement.

Shipment to customers selected for the ESP Program will begin on June 30, 2000.


The DATABASE 2™ Universal Database Server for OS/390 is IBM's super-server of choice for enterprise-wide data management in the twenty-first century. It is the premier relational database server solution for OS/390. The database server builds upon the momentum of previous releases furthering the themes of user productivity, e-business, business intelligence, high-performance, and continuous availability.

Warehouse Integration

DB2 Warehouse Manager, new to DB2 UDB Server for OS/390 in Version 7, delivers a complete warehouse solution along with your DB2 for OS/390. This new offering brings together the tools to build, manage, govern, and access DB2 data warehouses. It is based on proven technologies, which are enhanced to provide additional function and to provide tighter integration among the components. Provided in the optional DB2 Warehouse Manager Feature are:

  • DB2 Warehouse Center, which is a GUI that integrates with the DB2 Control Center
  • Warehouse agents, which execute process on behalf of the Data Warehouse Center and enable point-to-point data movement
  • Warehouse transformers, which are stored procedures or user-defined functions that provide commonly used transformations for building data warehouses
  • The Information Catalog, which helps end users find, understand, and access relevant information
  • The QMF family of products, which provides easy-to-use query, reporting, and updating and publishing to the Web
  • OLAP Starter Kit, which provides integrated OLAP capability combined with the power of DB2

Improved Performance, Availability, and Scalability

Version 7 includes significant new function to DB2 for OS/390. Whether your requirements are to do any of the following, DB2 UDB for OS/390, Version 7 has the power and function you are seeking to:

  • Become an effective e-business
  • Better understand your data with business intelligence applications
  • Increase your enterprise effectiveness with CRM and ERP applications
  • Derive more results from your operational applications

With each release, DB2 for OS/390 delivers improvements for performance, availability, and scalability. In DB2 UDB for OS/390, Version 7 you can take advantage of a new utility: UNLOAD, which provides faster data unloading than was available with the DSNTIAUL program. In addition to the unload functions of REORG UNLOAD EXTERNAL, you can also unload data from an image copy, invoke row sampling, specify field selection, and ordering, as well as using parallelism when unloading multiple partitions.

Online REORG is enhanced for data availability. Now, the time required to invoke a dataset rename has been eliminated. Instead you can specify a new keyword FASTSWITCH, which will leave the data set name unchanged, but update the catalog to reference the newly reorganized dataset. When hundreds of table spaces and index objects are involved, the time savings can become quite significant. Also, parallelism is added to the BUILD2 phase. This makes it possible to process logical partitions simultaneously and thereby shorten the elapsed time required for this phase of the Online REORG.

One of the causes of a planned outage for DB2 arises when there is the need to alter one or more of the system parameters (known as ZPARMS). Now, Version 7 introduces the opportunity to change the value of many of these system parameters without stopping DB2.

Data sharing customers can benefit from a new restart option. Restart Light will allow you to restart DB2 with a smaller storage footprint in order to quickly recover retained locks, following an abnormal termination. The reduced storage requirement can make a restart for recovery possible on a system that might not have enough resources to start and stop DB2 in normal mode.

Enterprise Application

Version 7 continues to enhance the flexibility of DB2 to support your enterprise applications and to ease the integration to the calling application.

DB2 Universal Database introduced support for the new object data types. Along with these objects, DB2 Extenders® provided appropriate functions for defining, accessing, storing, and searching data stored as a specific type. New in Version 7 is DB2 XML Extender with support for data using the XML data type. This extender allows you to store an XML object either:

  • In an XML column for the entire document
  • In several columns containing the fields from the document structure

Scrollable cursors give your application logic ease of movement through the result table using simple SQL and program logic. This frees your application from the need to cache the resultant data or to re-invoke the query in order to reposition within the resultant data.

In the increasingly global world of business and e-commerce, there is a growing need for data arising from geographically disparate users to be stored in a central server. Previous releases of DB2 have offered support for numerous code sets of data in either ASCII or EBCDIC format. However, there was a limitation of only one codeset per system. New in Version 7 is support for UNICODE encoded data. This new code set is an encoding scheme that is able to represent the characters (code points) of many different geographies and languages.

Stored procedures introduced in Version 5 have increased program flexibility and portability among relational databases. Now, with Version 7, DB2 for OS/390 will accept COMMIT and ROLLBACK statements issued from within a stored procedure. This enhancement will prove especially useful for applications in which the stored procedure has been invoked from a remote client.

Now, you can take advantage of Precompiler Services to perform the tasks currently executed by the DB2 precompiler. This API can be called by the COBOL compiler. By using this option, you can eliminate the DB2 precompile step in program preparation. You can take advantage of language capabilities that had been restricted by the precompiler, such as nested COBOL programs. Use of the host language compiler enhances DB2 Family compatibility, making it easier to import applications from other database management systems and from other operating environments.

Greater flexibility and family compatibility comes from several SQL enhancements. Now, you can use a subselect to determine the values used in the SET clause of an UPDATE statement. Also, you can have a self-referencing subselect. The search condition in the WHERE clause can include a subquery in which the object for the subquery and the UPDATE or DELETE is the same. Support is now extended so you can define a view with the operators UNION or UNION ALL. You can also use a row expression to compare with a subselect expression.

Server Features

Expanding the server, several new tools offer you more assistance to manage and tune your DB2 for OS/390 environment. These new tools:

  • DB2 Bind Manager and DB2 DBRM Checker, to avoid unnecessary binds
  • DB2 Forms, to assist in developing Java™-based DB2 applications
  • DB2 SQL Performance Analyzer, to allow you to evaluate the cost of a query before it executes
  • DB2 Recovery Manager, to simplify recovery of data from DB2 and IMS to a common point
are packaged with the other optional tools into the DB2 Trial Tools Package:
  • DB2 DataPropagator™ (with Capture and Apply combined into one feature)
  • DB2 Performance Monitor
  • DB2 Administration Tool
  • DB2 Buffer Pool Tool

A basic set of core utilities are included as part of DB2 since Version 1 was first delivered. These utilities initially provided a basic level of services to allow customers to manage data. Some customers have preferred to obtain such functions, however, from independent software vendors that have developed utility and tools offerings that offered additional performance, function, and features beyond that contained in the DB2 core utilities. With recent releases of DB2 for OS/390, in response to clear customer demand, IBM has invested in the improvement of the performance and functional characteristics of these core utilities.

With Version 7, many of these enhanced utility functions have been separated from the base product and are now offered as optional features of DB2 UDB for OS/390.

Included in the DB2 Trial Tools Package are two new utility features:

  • DB2 Operational Utilities
  • DB2 Recovery and Diagnostic Utilities

The DB2 Trial Tools Package Feature will be shipped with all new orders of DB2 UDB for OS/390, Version 7. Install them and give them a test run during the trial period. Discover the benefit they bring to your data administration tasks. A permanent use license is available for each tool by ordering the specific tool feature.

All of the features of DB2 UDB Server for OS/390 were developed to work with Version 7 and have been tested to help assure they install and execute in an integrated environment. This can save you valuable install test time. For detailed descriptions of the optional features, refer to the Features of DB2 UDB Server for OS/390 section.

Year 2000

This product is Year 2000 ready. When used in accordance with its associated documentation, it is capable of correctly processing, providing, and receiving date data within and between the twentieth and twenty-first centuries, provided all products (for example, hardware, software, and firmware) used with the product properly exchange accurate date data with it.

Euro Currency

This program is not impacted by euro currency.

Open Blueprint®

DB2 UDB for OS/390, Version 7 provides the relational database function described in the IBM Open Blueprint. It provides the:

  • Structured Query Language (SQL) industry-standard interface from the International Organization for Standardization (ISO)
  • SQL Call Level Interface (CLI) industry-standard from the ISO

It supports the DRDA® protocol from IBM to facilitate open, distributed, multivendor interoperability.

Open Enterprise

DB2 UDB for OS/390, Version 7 is developed at a lab where the quality management system is certified under ISO 9001.

DB2 UDB for OS/390, Version 7 conforms with the following national and international SQL standards:

  • Federal Information Processing Standards (FIPS) publication 127-2, Database Language SQL. FIPS publication 127-2 announces ANSI (American National Standards Institute) X3.135-1992 as the standard for SQL, so conformance with FIPS publication 127-2 also means conformance with:
    • ANSI X3.135-1992, Database Language SQL
    • ISO 9075-1992, Database Language SQL

    The above are referenced to collectively as the SQL92 standard . DB2 UDB for OS/390 conforms to SQL92 Entry Level .

DB2 UDB for OS/390, Version 7 is registered by IBM as Advanced ClusterProven. The IBM ClusterProven program introduces new high-availability requirements that may be applied to any combination of operating system, middleware, or end-user application. DB2 for OS/390 is a solution that satisfies the S/390® platform's technical criteria and has been registered to receive ClusterProven status. Specifically, DB2 has obtained the status of Advanced ClusterProven by providing significant benefits above those inherent at the ClusterProven level. More information on ClusterProven is available on the Web at:

Product Positioning

DB2 UDB for OS/390 is the super-server of choice for the twenty-first century providing enterprise-wide data management for e-business, business intelligence, CRM, and ERP applications such as Siebel, Vantive, Baan, PeopleSoft, and SAP R/3, and operational transaction processing. It offers large data capacity, high transaction performance, and extensive connectivity. It works with the DB2 UDB family to bring a full relational solution to the market place. DB2 supports transactions arising from Web servers, CICS®, IMS transaction management, MVS™ batch jobs, and via distributed connections from remote clients on numerous platforms.

DB2 UDB Server for OS/390 is the relational database server solution for OS/390. It combines the power and reliability of DB2 with additional features such as:

  • DB2 Warehouse Manager, including:
    • Data Warehouse Center
    • Information Catalog
    • QMF
    • QMF HPO
    • QMF for Windows
  • DB2 Trial Tools Package, including:
    • DB2 Administration Tool
    • DB2 Bind Manager
    • DB2 DBRM Checker
    • DB2 Buffer Pool Tool
    • DB2 DataPropagator
    • DB2 Forms
    • DB2 Performance Monitor
    • DB2 SQL Performance Analyzer
    • DB2 Recovery Manager
    • DB2 Operational Utilities
    • DB2 Recovery & Diagnostic Utilities
  • Net.Data®
  • DB2 REXX Language Support
  • DB2 Management Tools Package, including:
    • Control Center
    • DB2 Estimator for Windows
    • DB2 Installer
    • Stored Procedure Builder
    • Visual Explain

DB2 UDB for OS/390 extends the object-relational functions of DB2 UDB to the S/390 environment. Now the full power and capacity of OS/390 and Parallel Sysplex® are available to store object data, which can be delivered to applications originating on the S/390 or connected via DRDA from a remote requestor or from Internet and intranet applications.


QMF, IMS, DATABASE 2, DataPropagator, and MVS are trademarks of International Business Machines Corporation in the United States or other countries or both.
DB2 Universal Database, OS/390, DB2, DB2 Extenders, Open Blueprint, DRDA, S/390, CICS, Net.Data, and Parallel Sysplex are registered trademarks of International Business Machines Corporation in the United States or other countries or both.
Windows is a trademark of Microsoft Corporation.
Java is a trademark of Sun Microsystems, Inc.
Other company, product, and service names may be trademarks or service marks of others.

Features of DB2® UDB Server for OS/390®

DB2 UDB Server for OS/390 extends DB2 for OS/390, by offering features that combine with DB2 to enhance data warehousing, query access, system management, and replication of your data. The optional features of DB2 Universal Database® Server for OS/390, Version 7 include:

  • DB2 Warehouse Manager comprising:
    • DB2 Universal Database Version 7.1 Enterprise Edition — (restricted use license)
    • Data Warehouse Center
    • Information Catalog
    • QMF™
    • QMF HPO
    • QMF for Windows™
  • DB2 Trial Tools Package, a no-charge feature delivering time-limited trials of:
    • DB2 Administration Tool
    • DB2 Bind Manager
    • DB2 Buffer Pool Tool
    • DB2 DataPropagator™ for OS/390
    • DB2 Forms
    • DB2 Performance Monitor
    • DB2 SQL Performance Analyzer
    • DB2 Recovery Manager

Several optional features are available at no additional charge, including:

  • DB2 Management Tools Package, comprising:
    • DB2 Connect® Version 7.1 Personal Edition — (restricted use license)
    • DB2 Control Center
    • DB2 Estimator
    • DB2 Installer
    • DB2 Stored Procedure Builder
    • Visual Explain
  • Net.Data®
  • REXX Language Support

Following is a detailed description of each of these features.

DB2 Warehouse Manager Feature

Use DB2 Warehouse Manager to simplify and speed your warehouse prototyping, development, and deployment to production. Benefits include better control of query resources, through query governance, and cost management and usage tracking. Integration with the Information Catalog enables you to satisfy user requirements for finding, understanding, and accessing the right data. QMF gives you the ability to satisfy common reporting requirements, whatever the size of your enterprise.

DB2 Universal Database Version 7.1 Enterprise Edition: The data warehouse management infrastructure and OLAP Starter Kit are delivered as elements of DB2 UDB Version 7.1 Enterprise Edition. This copy of DB2 is included, with a restricted-use license, to permit delivery and utilization of the Windows-based components of the DB2 Warehouse Manager for OS/390.

Data Warehouse Center: The Data Warehouse Center gives you a graphical control facility, fully integrated with DB2 Control Center. Now you can more easily accomplish the diverse tasks of warehouse creation and management:

  • Registering and accessing data sources for your warehouse
  • Defining data extraction and data transformation steps
  • Directing the population of your data warehouses
  • Automating and monitoring the warehouse management processes
  • Managing your metadata utilizing standards-based metadata interchange

The Information Catalog: The Information Catalog helps end users to find, understand, and access information they need for making decisions. It:

  • Populates the catalog through metadata interchange with the DB2 Warehouse Center and other tools including: QMF, Lotus® 1-2-3®, Brio, Business Objects, Cognos, Excel, Hyperion, and others
  • Allows your users to directly register shared information objects including tables, queries, reports, spreadsheets, Web pages, and others
  • Provides navigation and search across the objects to locate relevant information
  • Displays object metadata such as name, description, contact, currency lineage, and tools for rendering the information
  • Can invoke the defined tool in order to render the information in the object for the end user

OS/390 Agents and Transformers: The warehouse agent for OS/390 executes OS/390-based processes on behalf of the Data Warehouse Center. It permits data to be processed in your OS/390 environment without the need to export it to an intermediate platform environment, allowing you to take full advantage of the power, security, reliability, and availability of DB2 and OS/390.

Warehouse transformers are stored procedures or user-defined functions that provide commonly used transformations for building data warehouses. These transformers provide complex transformations commonly used in warehouse development including:

  • Date manipulation
  • Data cleaning
  • Key generation
  • Statistical calculations

These transformers augment the rich transformations available with SQL built-in functions including:

  • String manipulation
  • Boolean operations
  • Mathematical calculations
  • Data type conversions
  • Utility invocations

DB2 OLAP Starter Kit: The DB2 OLAP Starter Kit is a limited user license of DB2 OLAP Server® and the OLAP Integration Server. Use them to quickly build and deploy OLAP applications in a Windows-based environment. The DB2 OLAP server for OS/390 is available separately. You can order and use it for OLAP applications to be run natively on OS/390.

DB2 Query Management Facility (QMF) Family: QMF, the tightly integrated, powerful, reliable, query, and reporting toolset for the DB2 relational database management system family. It provides new capabilities for the workstation environment and enhancements for the mainframe that help you access and present mission-critical data better than ever before. QMF Version 7 enables you to work with data all over the enterprise: from DB2 for OS/390, DB2 for VSE and VM, and DB2 for AS/400® to workstation servers running OS/2®, Windows NT™, AIX®, and other UNIX® operating systems to massively parallel processors. QMF coupled with the DB2 DataJoiner® product allows access to non-relational and other vendor data sources as well.

The DB2 Warehouse Manager Feature delivers the following QMF Family of integrated tools:

  • QMF for OS/390
  • QMF High Performance Option (HPO) Feature
  • QMF for Windows Feature

They offer a comprehensive data access and governing solution including:

  • Accessing large amounts of data
  • Sharing central repositories of queries and enterprise reports
  • Implementing tightly controlled distributed or client/server solutions

They also provide for report publishing to the Web for viewing with your favorite Web browser.

The QMF HPO Version 7 includes QMF HPO/Manager and QMF HPO/Compiler.

The QMF HPO/Compiler lets you convert automatically queries and reports into efficient programs in OS/VS COBOL or COBOL II. This reduces CPU consumption, DB2 catalog contention, DB2 Optimizer overhead, and security concerns as converted programs use static Structured Query Language (SQL) in place of dynamic SQL.

The QMF HPO/Manager consists of a group of functions that improves governing and object management capabilities, including a preemptive governor to analyze QMF queries. The governing capabilities allow you to establish controls that protect valuable resources. Among the many conditions available for governing are:

  • Time of day
  • Day of week
  • Maximum number of rows to fetch
  • Allow/disallow SQL verbs and QMF commands
  • Resource consumption based on the use of QMF commands and SQL statements

For customers with DB2 databases of many sizes, QMF for Windows provides a Windows-based, point-and-click query tool that gives you many benefits, including an intuitive GUI "quick start" user interface and a Java™-based query capability. Using QMF for Windows, you can automate tasks and develop powerful native Windows applications. The tool includes a powerful Windows-based API to automate database querying, updating, and report distribution tasks, so you can centralize control over resource consumption. QMF for Windows also gives you support for TCP/IP, static SQL, create and edit QMF forms and procedures, and a full screen table editor for updating enterprise data.

QMF for Windows can connect to the DB2 family of products including:

  • DB2 UDB for OS/390
  • DB2 UDB for AS/400
  • DB2 UDB on UNIX, Windows, and OS/2
  • DB2 for VSE and VM

QMF for Windows connectivity options include SNA, TCP/IP, and CLI.

QMF for OS/390 Enhancements

  • New default Edit codes for current DB2 Date (DD) and Time (TT) format. For QMF Version 7, these are the new default edit codes for date and time columns. Current QMF Forms default edit codes remain in effect.
  • New SHOW HOME QMF command.
  • Comprehensive command defaults for object type:
    • Commands include CONVERT, EDIT, ERASE, EXPORT, PRINT, RESET, RUN, and SAVE
  • Improved command prompting (better human factors) less typing, defaults preloaded in prompts.
  • Text option in the MSG command is extended — double quotes no longer need to be added when the text of a message has single quotes.
  • New DB2 data types:
    • ROWID and LOB data type support. There are LOB length restrictions for the QMF Table Editor. Refer to the Using QMF manual for more information.
  • Support for DB2 for VSE DRDA® RUOW Application Requestor (in CICS®) DB2 for AS/400, Version 4 Release 4 (or later) server support.
  • Install improvements:
      QMF is enabled to bind packages into each supported DB2 server for each supported QMF platform. That is, you can more easily use one QMF install base and simply bind (QMF) on other platforms.

QMF High Performance Option (HPO) Enhancements

  • Enhanced tracking of QMF objects to support very large numbers of simultaneous QMF users.
  • QMF processes can be preemptively governed based on estimated ASU time.
  • QMF batch processes can now be preemptively governed by QMF HPO/Manager.
  • Programs generated by QMF HPO/Compiler can be bound into packages in addition to or instead of plans.

QMF for Windows Enhancements

  • Ease of Use
    • Point-and-click/drag-and-drop interface to QMF Form creation
      • Aggregation and grouping and formatting performed in the query result
    • Enhanced sorting
      • Sort results of a query on more than one non-contiguous column
    • Enhanced object list window
      • Add Windows tree control for more intuitive navigation and location of QMF objects
    • Windows "Personal Portal" download now built into the product
    • Java-based Query from a browser
    • Human factors enhancements
      • Menu of items displayed with query results
      • More toolbar buttons
    • Option to use DB2 Forms when Table Editor invoked
    • Support for Windows NT password function (remembers password)
    • Database/Connectivity for DB2 OS/390, DRDA, and CLI
      • Support stored procedures that return multiple results sets via the CLI interface
      • Large object support (over CLI connections)
      • Large object support (over DRDA connections)
      • Enhancements to the server load-balancing algorithm (to support DB2's process of sending back addresses, which are not available for connection)
    • QMF Host Compatibility
      • Procedures with REXX logic (all types of QMF host procedures now supported)
      • Support of Form Detail variations
      • Support of Form Column Definitions
      • Support of RESET GLOBAL command
      • New toolbar/window in which single QMF commands can be entered and executed (analogous to the command line in QMF host)
    • National Languages
      • UNICODE support
      • Arabic BIDI
      • Traditional Chinese
    • CLI Connectivity Enhancements
      • Add CLI-specific information to the Connection Details dialog box, if connecting to the database server via CLI
      • Add CLI tracing to the trace types supported; only valid for CLI connections

For more information on the QMF Family of integrated tools, including a QMF Family demo and a QMF for Windows Try and Buy download, access the QMF Web site at:

DB2 Administration Tool Feature

Use DB2 Admin to:

  • Copy objects and data to other DB2 Subsystems
  • Display and interpret objects in the DB2 catalog
  • Execute dynamic SQL statements
  • Extract DDL from the catalog
  • Generate JCL and run DB2 utilities
  • Issue DB2 commands against databases and table spaces
  • Manage SQL IDs
  • Modify table structures
  • Perform complex performance and space utilization queries
  • Simplify system administration functions

As the number and complexity of DB2 applications and systems increase, having the right DB2 tools has never been more important.

DB2 Admin is the right tool to help you get the most from your DB2 system. Its comprehensive set of database management functions helps DB2 systems personnel efficiently and effectively manage your DB2 environment by:

  • Saving time in DB2 administration
  • Simplifying routine day-to-day DB2 tasks
  • Increasing knowledge and understanding of your DB2 system

DB2 Admin supports the functionality of DB2 Version 7 by providing panels and options allowing you to quickly view new pertinent catalog information. DB2 Admin works on a data sharing group member level.

DB2 Admin provides usability enhancements, lower overhead costs, better error recovery, and more extensive online help. Highlights include:

  • Copy of objects and data to alternate DB2 subsystems
  • Calculation and display of the hit ratios of the buffer pools
  • Display of all indexes and index columns for a table
  • Improved DB2 Admin print capabilities
  • EXPLAIN of long SQL statements
  • Modification of tables
  • Reuse of DB2 commands
  • User initiated stopping of SQL processing

DB2 Admin offers a powerful set of database management functions for DB2 MVS™ systems personnel. As your enterprise expands its DB2 usage and systems environment, you may need to manage multiple complex DB2 systems, perhaps in several locations. DB2 Admin simplifies database administration and allows you to manage these systems more efficiently and effectively.

DB2 Admin is an easy-to-use ISPF panel driven tool, which overviews DB2 system tables and provides relevant DB2 catalog information in a logical format. Designed to be an interactive and intuitive tool to learn and use, DB2 Admin integrates with DB2 utilities and simplifies creation of DB2 utility jobs. Among the DB2 Admin strengths is the extensive support to simplify understanding and analysis of the DB2 catalog and allow you to intelligently act on the information presented. The abundant DB2 catalog query capabilities in DB2 Admin include:

  • Predefined select criteria (predicates) for most commonly used SELECTs on the catalog
  • DB2 objects authorization and relationships can be shown
  • GRANT and REVOKE on the objects
  • BIND, REBIND, and FREE for selected plans and packages
  • Display of the static SQL for selected Database Request Modules (DBRMs) and packages

DB2 Administration Tool Version 7 provides two new functions called Alter and Migrate.

The Alter function permits users to change the name and attributes of a table column, insert new columns and drop existing columns. After the desired changes have been specified by the user, the jobs required to implement the changes are generated by the DB2 Administration Tool. These jobs unload the data, recreate the table, and reload the data.

The Migrate function supports copying all the objects and data in one or more databases or table spaces to another DB2 subsystem. DB2 Administration Tool creates the jobs needed to copy definitions and data to the target DB2 subsystem.

The DB2 Admin power-packed functionality still includes:

  • Display of the DDL for existing views
  • Generation of DB2 utility jobs for selected objects (COPY, REORG, RUNSTATS for selected TABLESPACEs)
  • Support of DB2 Distributed Database Facilities (DDF) including remote DB2 catalog support, ALIAS support, and Communications Data Base (CDB)
  • Catalog functions optionally running on tables containing a copy of the system catalog
  • Functions for administration of the Resource Limit tables (RLIMIT)
  • Dynamic SQL statement execution without knowing the exact SQL syntax
  • COMMIT/ROLLBACK issued before each display
  • Ability to be extended to display/update any DB2 table (sample function provided with the product)
  • Ability to issue any DB2 command (commands passed to IFI and the result shown in ISPF browse)
  • A quick EXPLAIN function that can show the inserted rows in the PLAN_TABLE
  • Ability to save results of a SQL SELECT as ISPF tables
  • Ability to (re) display the saved tables
  • Ability to print selected columns from the ISPF tables

With the power-packed capabilities of DB2 Admin, there's never been a better time than now to consider ordering DB2 Admin.

DB2 Bind Manager Feature

After a program with imbedded SQL has been successfully processed by the DB2 Precompiler and the appropriate host language compiler, it must then undergo a DB2 bind before it can be executed. Binding establishes a fixed relationship between a program and the database it will access. The bind process is complex and involves selecting access paths to the data, performing further SQL verification, and various other checks and validations. Unnecessary binds should be avoided, since they can be quite expensive.

Bind Manager can analyze each precompile to determine whether the SQL structure has changed. If the structure has not changed, then a bind is not needed, and this product ensures that one is not executed. When an installation installs DB2 Bind Manager, unnecessary binds become a thing of the past.

Use of Bind Manager can pay major dividends in change management, since it will detect automatically production application changes requiring a bind. This frees DBAs from analyzing bind impacts and allows them to concentrate only on the changes that affect the SQL structure.

Consistency checking may be done between an existing DBRMLIB and a DB2 subsystem using the optional feature DBRM Checker feature of Bind Manager. DBRM Checker will identify DBRMs by plan that have consistency tokens inconsistent with those in the DB2 catalog tables. You can determine which of the DBRMs you need to BIND. An application may have 100s of packages (that is, DBRMs) making up one plan. If you only changed one then you only need to BIND that one package.

DB2 Bind Manager requires:

  • Access to the DBRM used by the most recent bind
  • The high-level data set name qualifier of the DB2 subsystem
  • The PDS library containing the DBRMs to be validated against the DB2 catalog tables

APF authorization is not required.

DB2 Buffer Pool Tool Feature

While DB2 online performance monitors can tell you when something is wrong, it may be a difficult and time-consuming effort to determine what is the right corrective action to take. Take the guesswork out of tuning and avoid making costly mistakes with DB2 Buffer Pool Tool.

DB2 Buffer Pool Tool helps performance analysts evaluate tuning alternatives and achieve optimal performance with minimal effort. Accurate tuning of buffer pools is one of the single, most-critical factors to improving DB2 performance and getting the most out of your system resources. By now, we all know the best I/O is no I/O; however, eliminating I/O can be a challenging task.

DB2 Buffer Pool Tool gives you comprehensive statistical data and easy-to-read reports and graphs for all pools and objects. The type of information provided by DB2 Buffer Pool Tool is not available from any other monitor or tool in the industry.

DB2 Buffer Pool Tool provides the ability to simulate the effects of changing pool sizes and thresholds. It can also help you predict the effects of moving DB2 objects into different pools or into new pools that do not currently exist. This "what if" analysis allows you to make expert decisions on maximizing resource utilization and reducing elapsed time of your DB2 transactions through effective buffer pool usage. DB2 Buffer Pool Tool is the ultimate tuning aid. If you need a better way of realizing performance improvements, then consider the advantages DB2 Buffer Pool Tool can provide. DB2 Buffer Pool Tool makes it easier to maximize system resources and optimize DB2 performance. Eliminate I/O and realize better utilization of your existing expanded and real storage with DB2 Buffer Pool Tool. DB2 Buffer Pool Tool allows you to:

  • Reduce CPU cycle
  • Improve DB2 transaction and batch job elapsed times
  • Save money

The DB2 Buffer Pool Tool provides the following advantages:

  • Low Overhead

    The last thing you want to have is a performance tool that causes a performance problem because it uses too much overhead in collecting statistical information. The advantage of DB2 Buffer Pool Tool is it uses the IFI interface and its own data collection facility to avoid the costly overhead associated with other tools. Further, DB2 Buffer Pool Tool easily processes a statistically meaningful data collection interval (more than 30 minutes) other trace facilities cannot handle efficiently.

  • Unique Information

    No other DB2 tool provides the level and type of statistical analysis available in DB2 Buffer Pool Tool. DB2 Buffer Pool Tool shows you exactly how the pool and objects are used. The extensive statistics include:

    • System and Application Hit Ratios
    • Getpage Activity and I/O Rates
    • Random Access Activity
    • Prefetch (including Dynamic and List) Access Activity
    • Read and Write I/O Activity
    • Average and Maximum I/O Elapsed Times
    • Random Page HiperPool Retrieval
    • Average Page Residency Times
    • Average Pages/Write

    In addition, the statistical analysis is ranked for each object within each pool. Statistical object usage analysis is provided by Plan and AUTHID.

  • Expert Knowledge

    Although this tool provides the most comprehensive set of data related to DB2 activity available today, DB2 Buffer Pool Tool synthesizes, summarizes, and analyzes this information so you won't suffer from "information overload" or have "analysis paralysis," which is common with other types of tuning aids. In addition, you can optionally adjust threshold to bypass reporting for objects having very low usage rates.

    DB2 Buffer Pool Tool makes it easy to clearly see which objects are the poorest and best performing and the impact each is having on its current pool. Look no further than DB2 Buffer Pool Tool output to determine which objects will obtain the greatest benefit from changes.

    Not only does DB2 Buffer Pool Tool provide easy to read reports and graphs, it even offers general tuning advice based on information gathered from your environment. For example, these recommendations can range from separating indexes from table spaces to moving Sort Work objects into their own pool or to lowering virtual write threshold.

    Additionally, as you move to a data sharing environment, DB2 Buffer Pool Tool can be invaluable in providing initial sizing specifications for the Coupling Facility structures.

  • Simulation Analysis

    DB2 Buffer Pool Tool gives you powerful simulation capabilities to determine how to best optimize memory usage, eliminate I/O, and improve response times. DB2 Buffer Pool Tool turns tuning into a scientific process by allowing you to perform many "what if" analyses to evaluate various scenarios.

    DB2 Buffer Pool Tool is a superior solution since it can:

    • Predict system performance levels at varying pool sizes
    • Predict improved object performance within each pool
    • Model VPSEQT or HPSEQT threshold adjustments

    Further, DB2 Buffer Pool Tool is more effective and accurate than pool isolation tuning techniques because it shows the interaction between multiple objects within a pool.

    DB2 Buffer Pool Tool gives you the advantage to completely model the effects proposed changes may have without impacting the operating DB2 system. Up to eight simulation sizes for a pool can be done with one pass of the data.

DB2 Buffer Pool Tool gives you expert tuning knowledge and reduces the time and effort necessary to effectively tune your environment. Make informed decisions with confidence regarding performance improvements.

DB2 DataPropagator Feature

Data Replication is the key technology for controlling the power and potential of distributed database environments. DB2 DataPropagator, part of the comprehensive Data Replication and Warehousing Solutions, provides a highly efficient way to maintain consistent copies of relational data in the DB2 family of databases. It captures automatically the data changes against a source database and propagates those changes to any specified target database, keeping the two consistent. This is all done automatically without requiring any batch processing windows and explicit knowledge of, or changes to, business applications.

DB2 DataPropagator established the base architecture for IBM's data replication solution. The IBM replication architecture is based on individual components that interoperate, allowing for customization of the data replication environment. Capture components capture changes as they occur in the source and store them in the staging area. Apply components read the staging area and apply those changes to targets or copy data directly from the source in full refresh mode. Administration components provide the GUI for defining replication requests. The replication components can be located anywhere in your environment in order to maximize the efficiency of your replication environment.

The DB2 DataPropagator Feature consists of the following components:

  • Administration (Control Center)
  • Capture
  • Apply

The Administration component for the DB2 DataPropagator Version 7 is called the Control Center. The Control Center is the integrated administration tool for DB2 Universal Database and includes replication administration. The Control Center runs on OS/2, Windows NT, and Windows 95 platforms and does not require a local DB2 database. The Control Center is used to:

  • Define tables as sources, called replication sources
  • Define views and joins views as replication sources
  • Define target table definitions, called replication subscriptions
  • Clone replication subscriptions to other servers
  • Remove replication sources or subscriptions no longer needed

Whenever you submit a replication request from the Control Center, such as defining a replication source, the processing information is generated as SQL statements. You can choose to run the statements immediately, or you can save the SQL statements to a plain ASCII file, which you can edit and run at a later time from a replication folder object in the Control Center.

Deferred SQL files allow you to customize the replication tasks for your shop or application and give you the flexibility as to when and how you run the SQL files.

The Capture component captures changes made to data in tables defined as replication sources by reading the database log or journal — without causing any changes and running asynchronously to business applications. The captured changes are placed in staging tables. The Capture component of DB2 DataPropagator for OS/390, Version 7 supports DB2 for MVS, Version 4.1, or later.

The Apply component reads the changed data, previously captured and stored in a staging table, and applies it to the target tables. Apply components can also read data directly from source tables, for example, for a full refresh. Supporting update and refresh copying provides greater flexibility and automation in a replication environment. The Apply component also enhances the data to your specifications as it copies data to the targets. You can exploit the full power of SQL to:

  • Create new columns
  • Summarize data
  • Translate data
  • Join data

You can define run-time processing statements using SQL statements and stored procedures before and after the Apply program processes the replication subscription. The run-time processing statements can be run at the source server before the answer set is fetched, and at the target server before and after the answer set is applied. The stored procedures use the SQL CALL statement, newly supported by IBM Replication, without parameters. The run-time procedures are executed together in a single unit-of-work. Acceptable SQLSTATEs can be defined for each processing statement as well.

The Apply component allows you to create:

  • Read-only copies
    • User copy tables, which represent source data at a particular point in time
    • Point-in-time tables, which represent source data at a particular point in time plus some overhead columns
    • History tables
    • Staging tables, which can be used as a source for further copies, without recapturing changes, supporting a consistent data environment and providing flexibility in data distribution across the network
  • Copies that can be updated
    • Replica tables — updates to a Replica table are applied automatically to the original source table of the Replica, provided no conflicts are detected.

      The Apply component, running at the replica site, detects update conflicts after they occur during the subscription cycle. When transactions are rejected, the Apply compensates the transactions at the replica site. During this process, the Apply inserts rejection codes for every rejected transaction in the control table. Conflict detection is provided at three levels: no detection, standard detection, and enhanced detection, specified while defining the replication source. When a conflict is detected and compensated, the replication subscription is considered failed. All related transactions are checked for conflicts and are also rejected.

      After the Apply completes the subscription cycle, the ASNDONE exit notifies you the cycle is complete and whether there were conflicts. ASNDONE user exits can be used to manage recovery from conflicts.

You can subset data for distribution through column and row specifications, replicating only the data you need. Unique to DB2 DataPropagator is the ability to subselect for joins and unions, allowing you to subset according to a value not in the table being replicated. This can be a key requirement when replicating to mobile laptops from normalized databases where most values are not redundantly stored.

Finally, an Apply instance can process many subscriptions at a single site. Multiple Apply instances can run concurrently at different sites/platforms, each processing different numbers and types of subscriptions from the same source. Each subscription can have different definitions, refresh criteria, and timing.

The On-Demand replication capability offers a solution to the challenge of infrequent and unpredictable connections present in the mobile computing community. Data transfers are initiated from the mobile unit where control should be based. This implementation allows downloading from a central server (pulling), or uploading for consolidated processing (pushing).

DB2 DataPropagator offers a highly efficient architecture for automatic capture and asynchronous propagation of data changes to the DB2 family of databases. It contains a high-performance, log-based, change-capture component that captures all DB2 updates without response-time impact or requiring any user application changes. You can make powerful data enhancements using standard SQL including multitable joins and the use of stored procedures. There is support for full refresh and update change replication, including update of denormalized copies. DataPropagator supports seamless interoperation with DataPropagator NonRelational (IMS™), DataRefresher™, DataJoiner, and Lotus NotesPump™ products to deliver powerful, versatile replication among:

  • IMS
  • DB2
  • Oracle
  • Sybase
  • Informix
  • Microsoft™ SQL Server
  • Lotus Notes®

There is easier, intuitive administration from the DB2 Universal Database Control Center running on OS/2, Windows NT, and Windows 95 platforms. Support exists for subscription sets allowing for transaction consistency. DB2 view-based replication, including join views, is supported as well as event-driven and continuous replication. Also supported is update-anywhere replication with powerful conflict detection and compensation, along with on-demand replication for occasionally connected and mobile systems. DB2 Catalog replication is supported for speeding up of ODBC-based queries.

Beginning in Version 6, DB2 DataPropagator has the ability to replicate large objects (BLOB, CLOB, and DBCLOB), and replication for ROWID, a new data type introduced in DB2 UDB for OS/390, Version 7. Installation of DataPropagator is easier and integrated using the DB2 installer. Support for a mixed data sharing environment consisting of different versions of DB2 is now included as well as better resource utilization for continuous replication scenarios. Additional capturing choices are added allowing you to capture changes when any column changes or only when one of the registered columns changes. There is improved scalability, performance, and concurrency along with support for many popular versions of DB2 including:

  • DB2 UDB for OS/390, Version 6
  • DB2 for OS/390, Version 5.1
  • DB2 for MVS/ESA™, Version 4.1

DB2 Forms Feature

DB2 Forms, IBM's drag-and-drop solution for quickly building user-friendly, powerful e-business front ends to any DB2 database, has added three new features to further enhance its support for rapid development of Java-based applications for Web sites and intranets. DB2 Forms continues to provide a single development environment that enables users to simultaneously build Java-based and 32-bit Windows-based applications (some application features may only be available in 32-bit, Windows-based applications and not in Java-based applications).

DB2 Forms Advantages

  • Rapid development of data-intensive Web applications
  • Global Internet or intranet connectivity to mainframe, AS/400, or workstation databases
  • Broadest choice of end-user environments, including accessing:
    • Through browsers as applets embedded in Web pages
    • As stand-alone Java applications
    • Native Windows 32-bit applications
  • Applications in all environments that read and write directly to any DB2 database
  • Native support for DB2 syntax and SQL commands
  • Server-based application code allows opening applications to an unlimited number of users
  • Applications utilize existing DB2 logon security
  • Centralized management and administration, providing excellent concurrency and control over application versions and user permissions
  • Friction-free distribution of high-performance solutions powered by the number one database: DB2

Delivered as Four Modules

DB2 Forms Developer: A rapid development environment for creating custom forms end users can access from virtually any environment or location. In a drag-and-drop interface, developers quickly add command buttons, labels, text boxes, and controls for containing data and "drop down" lists. Behaviors, data sources, and data validation rules are assigned to controls from easy-to-use dialogs that require no programming. Applications can be made available only to their creators, to all users, made fully visible within a Web page, or made available as a link in a Web page. The developer can specify whether a unique DB2 logon is required to access the application.

Each application presents specific data associated with it at development time, and enforces data browsing, data entry, and data update rules embedded within it. Typical applications include:

  • Table editing
  • Inventory or product catalog access
  • Order entry
  • Customer invoice retrieval
  • Qquery-by-example front ends

Whether rolled out for access from within Web pages, as stand-alone Java applications, or as Windows-based applications, no compiling or porting is needed for any DB2 Forms application.

DB2 Forms Console: Sets up DB2 servers to centrally store Java and Windows-based DB2 Forms applications for access by end users. The Console defines servers, installs DB2 Forms packages at the servers, creates the DB2 Forms catalog objects at the servers, and sets various user group permissions. DB2 Forms Console supports and relies upon your existing DB2 security.

DB2 Forms User: The Windows client run-time that provides users with access to Windows-based DB2 Forms applications, which are stored centrally at the database server. Installation of the DB2 Forms User Module is not required when using Java-based DB2 Forms applications. End users select and launch any number of applications from the catalog (stored at the DB2 server) without having to install any additional code on their workstations. Access can be made available to local or remote workstations, including those connecting from any location via the Internet to TCP/IP supported DB2 databases.

DB2 Forms Java Player: Installed by the administrator to your HTTP server for serving your Java-based DB2 Forms applications to Web browsers. It can also be installed on Java-enabled workstations for running Java-based DB2 Forms applications stand-alone without a browser.

Applications can contain a variety of controls for performing commonly required tasks, such as:

  • Command Buttons: Multiple actions including:
    • Launch
    • Refresh
    • Search
    • Next
    • Previous
    • Insert
    • Update
    • Delete
    • Clear
    • Close
    • Exit
    • Help
  • Data Controls
    • Combo boxes
    • Multiple column list controls
    • Multiple line edit controls
    • Validation rule and formatting choices, including:
      • Valid integers
      • Range constraints
      • input format (for example, currency, phone number)
  • Form and Sub-form Layout including:
    • Labels
    • Text boxes
    • Images
    • Borders
    • Colors

Java and XML Standards Compliance: DB2 Forms Version 3 generates online applications that can be run as Java applets embedded in Web pages for access through a browser, or as stand-alone Java applications without a browser.

DB2 Forms also generates the specifications for a Java application in XML vocabulary, laying the groundwork for future integration in Web-based data interchange and leveraging of the DB2 XML Extender.

Java and XML standards compliance adds universal portability to DB2 Forms applications.

Open Group's DRDA Compliance: This product is in compliance with Open Group's DRDA.

At the same time DB2 Forms Version 3 generates your Java-based applications, it can also generate Windows-based applications, which may be distributed freely on demand over networks and are architecturally coupled to DB2 via the open standards of DRDA.

DRDA compliance allows you to easily transcend network, hardware, and operating system differences when connecting Windows-based DB2 Forms client applications to DB2 database servers on multiple platforms. The result is the elimination of database gateways, middleware, and ODBC drivers, while gaining more rapid setup, easier administration, and support for advanced features of DB2.

DRDA compliance also allows inclusion of multivendor databases (including IMS, VSAM, Oracle, and Microsoft SQL Server) via the multidatabase gateway DB2 DataJoiner.

Enhancements to DB2 Forms Version 3: Three new features are provided with DB2 Forms for OS/390 Version 3 to provide additional support to users in development of Java-based applications for Web sites and intranets. They are:

  • Enhanced data editing and referential integrity capabilities
  • Broader support for advanced DB2 data access features including SQLJ and LOBs
  • New application components including sub-forms

DB2 Performance Monitor (DB2 PM) Feature

The DB2 PM Feature enables you to monitor, analyze, and optimize the performance of DB2 UDB for OS/390, Version 7 and its subsystems. It includes an online monitor to provide an immediate "snap-shot" view of DB2 for OS/390 activities and to allow for exception processing while the system is operational. In addition, it offers:

  • A history facility to view events that happened recently and in a more distant past
  • A wide variety of reports that can be customized for an in-depth performance analysis
  • An EXPLAIN function to analyze and optimize SQL statements
  • A Sysplex wide monitoring of activities in a data sharing group:
    DB2 PM Workstation Online Monitor allows you to connect to a data sharing group. Thread information and subsystem statistics information is shown either in group scope or single member scope view.

Enhancements to DB2 PM Feature for Version 7 include:

  • Comprehensive support of all DB2 Version 7 changes

    The DB2 PM Feature supports all instrumentation, catalog, and PLAN_TABLE changes, introduced in DB2 Version 7. With DB2 PM, you can rely on a timely and comprehensive support of performance evaluation and analysis.

  • DB2 PM now provides an API to the Online Monitor Data Collector. This allows you to retrieve performance information about the subsystem being monitored. You can obtain raw data and derived performance information including snapshot information and recent history collected to a dataset. This also includes exception alerts based on DB2 events and exceeding thresholds.
  • Extended exception event processing for:
    • Activity Log Dataset full
    • Dataset extend activities
    • Unit of recovery inflight/indoubt

The DB2 PM Feature is the right solution for you, even if you have an environment of multiple DB2 releases. Ensuring protection of your investment, DB2 PM fully supports instrumentation, catalog, and PLAN_TABLE data from:

  • DB2 UDB for OS/390, Version 7 (5675-DB2)
  • DB2 UDB for OS/390, Version 6 (5645-DB2)
  • DB2 for OS/390, Version 5 (5655-DB2)

DB2 SQL Performance Analyzer Feature

Developing applications under DB2 today requires the cooperative skills of the system architect, the database administrator, the programming staff, and members of the systems support team. In the daily quest to produce applications on time and within budget, too often corners are cut such that performance is not adequately considered. Once put into production, these applications fail to perform adequately, particularly at high transaction volumes. In addition, even applications that originally performed well may deteriorate over time due to changes in data distribution, data volume, system changes, and even changes to the DB2 product itself. Therefore, installations spend a great deal of their budget managing the performance of their applications.

All DB2 problem queries have one thing in common. They run too long. These queries cause the batch production window to shrink. Too often the online queries take what seems like forever to execute, causing customers and users to become frustrated. The most cost effective solution to the problem is prevention. IBM is introducing DB2 SQL Performance Analyzer to aid in preventing queries from running too long. With this tool you can find out how long queries will take:

  • Before you run them
  • Before resources are consumed
  • Before the query exceeds your installations governor settings

Query cost can be determined regardless of which DB2 attach is used and regardless of whether static or dynamic SQL is used. Estimates are given in familiar units like CPU time, I/O count, and elapsed time and in even simpler terms, such as a single number representing overall cost. In addition, a monetary cost for each query is computed and delivered with the above.

This product can be used daily in many ways. Some examples are:

  • During design of a DB2 database structure, manipulate the major factors that influence performance. The application architect and DBA will have the opportunity to test drive a handful of critical SQL as soon as the logical data model is fleshed out. They can see what performance can be obtained with various database design alternatives.
  • Examine DB2 applications under development to determine breaking points. By providing a hypothetical "stress test," designers may be able to avoid a crisis later.
  • Support "what if" scenarios. For example, if there is a concern with response time as the number of rows returned increases, DB2 SQL Performance Analyzer may be used to determine when the number of rows returned causes unacceptable performance.
  • "Test drive" a system at different production volumes. Today, performance may not be a problem, but next year it may be when production volumes increase. Try different values (the forecasted growth) to see what happens.
  • Determine which SQL statements in major production applications are the most resource intensive. The DB2 SQL Performance Analyzer can be used to scan production DBRM libraries looking for problem queries, isolate them, and test alternatives from database redesign to predicate reconstruction.
  • Develop efficient queries for ad hoc users and store them as canned queries, so they are always executed in optimum form.
  • Observe the effect on access path selection due to data distribution changes before rebinding queries.
  • Intercept QMF ad hoc queries and provide an instant cost assessment before they execute, allowing users to cancel or continue at their option.
  • Evaluate the impact of adding a hiperpool behind the DB2 buffer pool and observe the anticipated reduction in elapsed time as the hiperpool "hits" increase.

DB2 Recovery Manager Feature

In the online transaction environment, it is not uncommon to encounter applications that access and update DB2 and IMS/DB data. When the application commits, IMS and DB2 coordinate the data changes such that all changes occur or none occur. However, if at some later time the user needs to recover IMS and DB2 to the same point, then the user must deal with different logs, different utilities, and different processes to do the recovery. This leads to complex recovery scenarios that are time consuming and error prone as each product has to have its data recovered separately. Recovery Manager is a new product that simplifies this process.

Recovery Manager works with IMS, DB2, or both. It uses image copies for either or both products, processes the individual logs and can work with incremental image copies for DB2 and the change accum process for IMS. Recovery Manager establishes sync points to which recovery may be done. Recovery Manager calls such a sync point a Virtual Image Copy (VIC). In the situation as described, after the application finishes, Recovery Manager is invoked to establish a VIC. No image copies are required. Recovery Manager establishes a quiesce point and notes where it is on the respective logs. During recovery, the user specifies the need to recover to this VIC and Recovery Manager applies the appropriate image copies and causes the log to be applied up to this point.

If you prefer, rather than using VIC for recovery, you can use Recovery Manager to automate the recovery of resources. Recovery Manager will generate the JCL, locate the proper image copies, and control the execution of the job. The product will do this for either DB2 or IMS.

DB2 Management Tools Package

The DB2 Management Tools Package is a no-charge feature of DB2 for OS/390, Version 7. The Management Tools Package is a collection of workstation-based tools you can use to work with and manage your DB2 for OS/390 environment. The elements of the DB2 Management Tools Package Feature are:

  • DB2 Control Center
  • DB2 Stored Procedures Builder
  • DB2 Installer
  • DB2 Visual Explain
  • DB2 Estimator

DB2 Control Center: Users of DB2 for OS/390 can now manage data in a new way. The Control Center capability of DB2 Universal Database Version 6 for Windows, UNIX, and OS/2 now extends support to DB2 for OS/390, Version 6. As a Java-based tool, the Control Center can run on many types of workstations and on many different operating systems. Users can now use the same tool, with its easy-to-use GUI, to manage DB2 databases on OS/390, and on workstation servers. The GUI supports DB2 for OS/390 SQL statements (such as CREATE and ALTER), DB2 commands (such as DISPLAY, START, and STOP), and utilities (such as REORG and LOAD).

With the Control Center, users can manage the family of DB2 databases on many different operating systems. DB2 for OS/390 objects are displayed on the Control Center main window along with objects of other members of the DB2 UDB family. To initiate an action or utility to manage these objects, users select the object. For example, a user can list the table spaces of a particular database and perform the following actions, and others, on one of the selected table spaces:

  • Alter (ALTER TABLESPACE statement)
  • Drop (DROP statement)
  • Copy (COPY utility)
  • Run statistics (RUNSTATS utility)
  • Check data (CHECK DATA utility)
  • Get a report (REPORT utility)
  • Modify (MODIFY utility)
  • Load data (LOAD utility)
  • Reorganize (REORG utility)
  • Recover (RECOVER utility)
  • Display (DISPLAY command)

The Control Center can run either as a Java application or as an application on your Web server that your Web browser can access. DB2 Control Center is part of the DB2 Application Development Client on Windows, delivered with all editions of DB2 Universal Database and DB2 Connect products on Linux, OS/2, UNIX, and Windows. Because the Control Center requires DB2 Connect, the DB2 Management Tools Package provides a restricted-use copy of DB2 Connect Version 7 to satisfy this functional dependency.

The Control Center approach to managing DB2 is now extended to the System/390® platform. Sites that have multiple DB2 subsystems, on the same or different operating systems, can use the Control Center as a central point of administration. Users who are more experienced in the workstation environment can manage DB2 for OS/390 more easily as a result of the GUI.

DB2 Stored Procedure Builder: The DB2 Stored Procedure Builder (SPB), an element of the DB2 Management Tools Package, provides an easy-to-use development environment for creating, installing, and testing stored procedures. With the DB2 Stored Procedure Builder, you can focus on creating your stored procedure logic rather than on the details of registering, building, and installing stored procedures on a DB2 server. The Stored Procedure Builder provides a single development environment that supports the entire DB2 family ranging from the workstation to System/390. With DB2 Stored Procedure Builder, you can develop stored procedures on one operating system and deploy them on other server operating systems.

The DB2 Stored Procedure Builder has a GUI that guides you through the tasks with the help of basic design patterns, SQL assistants, and costing information. Use the Stored Procedure Builder to perform a variety of tasks associated with stored procedures, such as:

  • Viewing existing stored procedures
  • Modifying existing stored procedures
  • Creating new stored procedures
  • Running existing stored procedures
  • Copying and pasting stored procedures across connections
  • One-step building of stored procedures on target databases
  • Customizing the settings to enable remote debugging of installed stored procedures

DB2 Stored Procedure Builder support for DB2 for OS/390 is currently available in beta test mode. It can be obtained from the Web at:

DB2 Stored Procedure Builder is part of the DB2 Application Development Client on Windows, delivered with all editions of DB2 Universal Database and DB2 Connect products on Linux, OS/2, UNIX, and Windows. Because the Stored Procedure Builder requires DB2 Connect, the DB2 Management Tools Package provides a restricted-use copy of DB2 Connect Version 7 to satisfy this functional dependency.

DB2 Installer: DB2 Installer is a workstation client delivered as an element of the DB2 Management Tools Package. DB2 Installer enhances your productivity whether you are installing DB2 for OS/390 for the first time or you are an experienced installer. From your workstation, you can:

  • Install, migrate, or update DB2 for OS/390 from a graphical interface. The graphical interface illustrates the overall installation process and keeps a graphical record of how each subsystem is defined.
  • Customize your DB2 subsystem as much or as little as you need to. You can define a basic subsystem quickly, or you can customize every installation option. The main windows display those parameters you must specify, and secondary windows display the advanced options.
  • Easily control DB2 parameters and run SMP/E, installation, migration, update, fallback, and sample jobs if you have a TCP/IP connection to the DB2 Universal Database Server for OS/390. You receive job status dynamically, and you can edit JCL and examine job output from the workstation.
  • Install optional features of DB2 for OS/390, Version 7, including DB2 Performance Monitor and DB2 DataPropagator.

Enhancements in Version 7 include:

  • Support real time DB2 subsystem parameters as input on migration or update
  • Run the DB2 Installer application for the Windows 95, and Windows 2000 platforms, as well as OS/2 and Windows NT
  • Improved usability in many dialog windows

Visual Explain: DB2 Visual Explain, a workstation client that is an element of the DB2 Management Tools Package, is an easy-to-use workstation tool that presents the output from DB2 EXPLAIN and dynamic EXPLAIN in a graphical format. Relationships between database objects, such as tables and indexes, are instantly clear as are various operations, such as table space scans and sorts. DB2 Visual Explain also includes a browser for viewing DB2 subsystem parameters. Enhancements to Visual Explain Version 7 include:

  • Visualization for new SQL, such as UNION in Views and row value expression
  • Improved graphical display for query blocks, through support for the new PLAN_TABLE column PARENT_QBLOCKNO
  • Greater platform flexibility with support for Windows 95 and Windows 2000

If you are using Control Center, you can launch Visual Explain directly from the Control Center. Because DB2 Visual Explain requires DB2 Connect, the DB2 Management Tools Package provides a restricted-use copy of DB2 Connect Version 7 to satisfy this functional dependency.

DB2 Estimator: DB2 Estimator is an easy-to-use, stand-alone tool for estimating the performance of applications for DB2 for OS/390. DB2 Estimator is one of the elements of the DB2 Management Tools Package. Run it on your desktop personal computer, or take it with you on your portable notebook computer.

With DB2 Estimator, you can model a partial DB2 application or a complete real or planned DB2 application without requiring an actual DB2 system. From simple table sizings to a detailed performance analysis of an entire DB2 application, DB2 Estimator saves time, lowers costs, and reduces risk. You can use DB2 Estimator to investigate the impact to your production system of new or modified applications before you implement them. You can do what-if analysis to assess the impact of changes you're considering. You can also answer many questions, such as:

  • What is the impact on my system if the transaction volume doubles?
  • What is the impact if my databases increase in size?
  • What is the effect on response time if I use a faster processor?
  • Is my batch window large enough for my utilities?
  • How much storage do I need for the new table and its indexes?

Use DB2 Estimator during all life-cycle phases of a DB2 application. During the initial design phase, you can easily:

  • Determine whether your design is optimal and feasible
  • Investigate alternative database designs
  • Assess the impact of using triggers and different ways of structuring queries and transactions

When creating database objects, use the models you specified in DB2 Estimator as a guide for naming columns and for specifying attribute values. You can model your system using actual DB2 information by importing information from the DB2 catalog and, if available, from EXPLAIN or DB2 Performance Monitor, thereby eliminating problems early in the design phase.

When your application is in production, use DB2 Estimator with tools such as DB2 PM to solve application performance problems. You can evaluate alternative SQL designs, without any risk to your production environment, before changing any production database objects. DB2 Estimator also helps you determine the impact of hardware or workload changes.

You can use DB2 Estimator for Versions 5, 6, and 7 of DB2 for OS/390. It runs in any environment that supports Windows (Windows 95, Windows 98, Windows 2000, and Windows NT). Use DB2 Estimator on any data imported from DB2 for OS/390, or you can model an application for which none of the tables, SQL, transactions, or configuration exist.

In Version 7, DB2 Estimator expands support for utilities and SQL statements to help you approximate your working environment more closely. Enhancements in Version 7 include:

  • Star Join
  • Index access IN non-correlated subquery
  • UNLOAD utility
  • SELECT as source of UPDATE SET
  • Selected other DB2 Version 7 line-items affecting performance estimates
  • Usability enhancements of bulk-object handling

Net.Data Feature

The Net.Data Version 7 feature provides high-performance access to DB2 data from the World Wide Web. Net.Data for OS/390, Version 7 exploits the S/390's capabilities as a premier platform for electronic commerce and Internet technology.

Businesses are finding many reasons to provide Internet and intranet connectivity from the World Wide Web to their business information. Businesses want to build business-critical Web applications to reach the global marketplace. With Net.Data, your business can have interactive Web sites with dynamic data from the sources you have in your business today: relational data on OS/390 and a variety of other platforms. Whether you are building interactive catalogs of products, allowing customers to track orders, or conducting electronic commerce, your Net.Data-driven application gets your data to (and from) a worldwide audience.

Net.Data delivers a powerful framework for Web applications. In addition to connecting to diverse data sources, Net.Data provides for:

  • High performance
  • Robust application development function
  • Exploitation of existing business logic

Net.Data provides native access to the data you need in your business environment:

  • DB2 on all platforms
  • Other heterogeneous data sources through DB2 DataJoiner
  • HFS flat file data

In addition, Net.Data can call DB2 stored procedures for additional performance from static SQL.

Net.Data has extensive application development functionality, including:

  • A rich macro language
  • Conditional logic
  • HTML and VRML support
  • HTML variable substitution

Net.Data for OS/390, Version 7 adds enhancements for performance, scalability, usability, and serviceability. With Net.Data for OS/390, Version 7, your high-performance, business-critical Web applications can more efficiently utilize the data in your enterprise.

Refer to the Net.Data Web pages for documentation, sample programs, and customer applications. You can find the Net.Data home page at:

REXX Language Support Feature

REXX Language Support lets your REXX programs access DB2 data. You can use this facility to enable your REXX execs or REXX stored procedures to issue calls directly to DB2 for OS/390.

Call IBM Education and Training at 800-IBM-TEACH (426-8322) for catalogs, schedules, and enrollments.

Technical Information

Specified Operating Environment

Hardware Requirements

Processors: DB2 for OS/390 operates on any processor supported by OS/390 Version 2 Release 7 and supports the architectural level set. In this version, DB2 for OS/390 intends to use ESA/390™ architectural enhancements, that were implemented on selected S/390® servers.

DB2 UDB for OS/390, Version 7 can run only on servers that implement the architectural enhancements, and cannot run on any servers that have not implemented them. The following IBM servers implement the architectural enhancements:

  • Models of the S/390 Parallel Enterprise Servers except for Release 1 models
  • All models of the S/390 Multiprise® 2000 and 3000
  • All models of the S/390 Application StarterPak Type 3000
  • All PC Server S/390 servers and RS/6000® with S/390 Server-on-Board models
  • All S/390 Integrated Servers

DB2 UDB for OS/390, Version 7 cannot run on the following IBM servers, because they do not implement the architectural enhancements:

  • ES/9000® Processor Unit 9021, 9121, or 9221
  • ES/3090™ models
  • ES/4381™ models
  • S/390 Parallel Transaction Server 9672 E or P models
  • S/390 Parallel Enterprise Server™ 9672 Release 1 models

Architectural enhancements provide a variety of performance and reliability improvements. For more information about the specific enhancements in ESA/390, refer to Software Announcement 299-234 , dated August 24, 1999, or the product manuals for ESA/390.

The processor must have enough real storage to satisfy the combined requirements of:

  • DB2
  • OS/390
  • Appropriate Data Facility Product
  • Appropriate access methods
  • Telecommunications
  • Batch requirements
  • Other customer required applications

The configuration must include sufficient I/O devices to support the requirements for system output, system residence, and system data sets. Sufficient direct access storage (DASD) must be available to satisfy the user's information storage requirements and can consist of any direct access facility supported by the system configuration and the programming system.

Auxiliary Storage: DB2 is independent of DASD and tape device type. The customer can use any magnetic or tape device supported by the data facilities component of OS/390 for the DB2 data sets. The following DB2 data sets are supported by the following device types:

  • Active recovery log data sets: disk
  • Archive recovery log data sets: disk, tape
  • Image copy data sets: disk, tape
  • Bootstrap data sets: disk
  • User data sets: disk
  • DB2 catalog data sets: disk
  • Work data sets (for utilities): disk, tape

If these data sets are on a disk shared with other OS/390 systems, you should use global resource serialization to prevent concurrent access by more than one OS/390 system.

The minimum DASD space requirement, based on installing DB2 using the Installation Panels default values, is approximately 600 MB. You will need additional DASD space for your data.

If you use dual logging and tape for the log archiving device, you need at least two tape drives.

Data Communications Devices: DB2 operations can be controlled from:

  • The system console
  • Authorized IMS/ESA® Transaction Manager terminals
  • Authorized CICS terminals
  • TSO terminals (by authorized users)
For the data communication devices supported by IMS/ESA Transaction Manager, CICS, and TSO, refer to the documentation for these products.

Function-Dependent Hardware Requirements

  • UNLOAD Utility

    Use of the FLOAT IEEE option requires the basic floating-point extensions facility (G5 processor or above).

Software Requirements

Operating System and Support Programs: For an OS/390 environment, DB2 requires the function provided by the following licensed programs or their equivalents; later versions or releases of any product are acceptable.

  • OS/390 Version 2 Release 7 Base Services (5647-A01)
  • OS/390 Version 2 Release 7 Application Enablement optional feature for DFSORT™

Optional Programs: You can use the following optional licensed programs with DB2 UDB for OS/390, Version 7. Unless otherwise specified, the release shown for a product and any subsequent release is acceptable. In some cases, earlier versions or releases of IBM licensed programs may also work with DB2 for OS/390, but IBM may not have tested with them at the time of this announcement. If you have questions, check with your IBM representative.

  • DRDA Connectivity

    DB2 UDB for OS/390, Version 7 supports the following IBM relational database products:

    • DB2 UDB for UNIX, Windows, OS/2, Versions 6 or 7 with DB2 Connect
    • DB2 Universal Database for AS/400, Version 4 Release 2
    • Operating System/400® (OS/400®) Version 4 Release 1 with DB2 for AS/400 (5769-SS1)
    • DB2 Server for VM and VSE, Version 6 (5648-158)
    • DB2 DataJoiner Version 2 Release 1.1 (5231-200)
    • Any other DRDA compliant database management system
  • Web Connectivity

    Net.Data for OS/390, a feature of DB2 UDB for OS/390, Version 7, provides connectivity to DB2 from the Web.

  • Capacity Planning

    DB2 Estimator for Windows, a feature of DB2 for OS/390, Version 7, works with DB2 data to estimate application feasibility, to model application cost and performance, and to estimate required CPU and I/O capacity.

  • Transaction Management

    The following transaction management products work with DB2:

    • Information Management System (IMS)
      • IMS/ESA Version 6 (5655-158)
    • Customer Information Control System (CICS)
      • CICS Transaction Server for OS/390, Release 1 (5655-147)
      • CICS/ESA® Version 4 (5655-018)
  • Query Support
    • DB2 Extenders®, elements of DB2 UDB for OS/390, Version 7
    • Query Management Facility (QMF), an element of the DB2 Warehouse Manager, a feature of DB2 UDB for OS/390, Version 7
    • QMF for Windows, an element of the DB2 Warehouse Manager, a feature of DB2 UDB for OS/390, Version 7 a feature of DB2 UDB for OS/390, Version 7
    • QMF High Performance Option (HPO), an element of the DB2 Warehouse Manager, a feature of DB2 UDB for OS/390, Version 7
  • Application Development
    • Stored Procedure Builder, an element of the DB2 Management Tools Package, a feature of DB2 UDB for OS/390, Version 7
    • VisualAge® PL/I Version 2 (refer to Software Announcement 297-372 , dated September 23, 1997)
    • VisualAge COBOL Version 2 (refer to Software Announcement 297-371 , dated September 23, 1997)
    • VisualAge Generator Version 3 (refer to Software announcement 297-395 , dated September 30, 1997)
    • C/C++ Productivity Tools for OS/390 (5655-B85)
    • VisualAge for Java, Enterprise Edition for OS/390, Version 2 (5655-JAV)
    • Application System (AS) Version 4 Release 2 (5648-092)
  • Operational Support
    • DATABASE 2™ Performance Monitor (DB2 PM) a feature of DB2 UDB for OS/390, Version 7
    • OS/390 Version 2 Release 7 System Management optional feature for DFSMS™ features
    • OS/390 Version 2 Release 7 Security Server optional feature for RACF®
    • NetView® Version 2 Release 4 (5685-111), or NetView Version 3 for MVS/ESA (5655-007)
    • Tivoli Performance Reporter for OS/390, Version 1 Release 4 (5695-101), or Performance Reporter for MVS, Version 1 Release 2 (5695-101)
    • Library Readers included on the CD-ROMs for BookManager® books
  • Replication Support
    • DataPropagator Relational Apply for MVS, Version 5 Release 1 (5655-A22), or DB2 DataPropagator, an optional feature of DB2 for OS/390, Version 7
    • DataPropagator Relational Capture for MVS, Version 5 Release 1 (5655-A23), or DB2 DataPropagator, an optional feature of DB2 for OS/390, Version 7
    • DataPropagator NonRelational MVS/ESA, Version 2 (5696-705)
    • IBM DataRefresher Version 1 (5696-703)
  • Database Administration and Systems Management Support
    • DB2 Management Tools Package, a feature of DB2 UDB for OS/390, Version 7, which includes:
      • DB2 Control Center
      • DB2 Estimator
      • DB2 Installer
      • DB2 Stored Procedure Builder
      • Visual Explain
    • DB2 Tools Trial Package, which includes a trial copy of each of the following:
      • Database 2 Administration Tool for MVS/ESA, Version 2, a feature of DB2 UDB for OS/390, Version 7
      • DB2 Bind Manager, a feature of DB2 UDB for OS/390, Version 7
      • Buffer Pool Tool, a feature of DB2 UDB for OS/390, Version 7
      • DB2 Bind Manager, a feature of DB2 UDB for OS/390, Version 7
      • DB2 SQL Performance Analyzer, a feature of DB2 UDB for OS/390, Version 7
      • DB2 DataPropagator a feature of DB2 UDB for OS/390, Version 7
      • DB2 Forms, a feature of DB2 UDB for OS/390, Version 7
      • DB2 Performance Monitor, a feature of DB2 UDB for OS/390, Version 7
      • DB2 Recovery Manager, a feature of DB2 UDB for OS/390, Version 7
    • DB2 Automated Utility Generator (DB2AUG) Version 1 Release 2 (5695-077)
  • Programming Languages: The following IBM Programming Languages in addition to Assembler have an interface to DB2:

C              AD/Cycle(R) C/370(TM) Compiler Version 1
                 Release 2 (5688-216), IBM C/370 Library
                 Version 2 Release 2 (5688-188)
C++            OS/390 Open Class(TM) Library Component,
                 which is licensed with the OS/390 base
                 operating system
COBOL          COBOL for MVS and VM, Version 1
                 Release 2 (5688-197) or COBOL for OS/390
                 and VM, Version 2 Release 1 (5648-A25)
                 or VS COBOL II Compiler and
                 Library Version 1 Release 4 (5668-958)
FORTRAN        VS FORTRAN Compiler, Library, and
                 Interactive Debugger Version 2 Release 6
Java           Java for OS/390 (5655-A46)
PL/I           VisualAge for PL/I for OS/390, Version 2
                 Release 2 (5655-B22), or IBM PL/I for MVS
                 and VM, Version 1 Release 1 (5688-235);
                 OS PL/I Compiler, Library, and Interactive
                 Test Facility Version 2 Release 3 (5668-909)
PROLOG         SAA AD/Cycle PROLOG/MVS Version 1
REXX           TSO Extensions for MVS REXX, part of
Smalltalk      VisualAge Smalltalk Version 4
                 Release 5 (5655-B14 or 5802-AAR)

Function-Dependent Software Requirements: DB2 has the following function-specific dependencies:

  • Text Extender

    Use of the Text Extender requires the IBM Text Search Engine, which is embedded in OS/390 Version 2 Release 8 (5647-A01). If you are running OS/390 Version 2 Release 7, download and install the IBM Text Search Engine in SMP/E format from the following Web site:

Virtual Storage Requirements: The amount of space needed for the common service area (CSA) below the 16 MB line is less than 40 KB for each DB2 subsystem and 24 KB for each IRLM when APAR PQ12390 and prior service is applied. High concurrent activity, parallelism, or high contention can require more CSA.

Most of the DB2 common data resides in the extended common service area (ECSA). Most modules, control blocks, and buffers reside in the extended private area. A DB2 subsystem with 200 concurrent users and 2,000 open data sets should need less than 2 MB of virtual storage below the 16 MB line.

Prerequisites of Features of DB2 UDB Server for OS/390: DB2 for OS/390, Version 7 includes many features, some of which have requirements of their own, above and beyond what DB2 for OS/390, Version 7 requires. This section identifies the requirements for using these features with DB2 for OS/390, Version 7, but it does not repeat those DB2 for OS/390 requirements that apply to the features. Also, some of these features can be used with prior releases of DB2; these requirements are not included in this section, but rather in the detailed installation information for the specific feature.

You can use subsequent versions or releases of the products mentioned in this section, unless otherwise noted.

Recommendation: Before using these features, refer to the installation information for these features to ensure you have all required and recommended products.

DB2 Installer Requirements: DB2 Installer is an element of the DB2 Management Tools Package, which is a feature of DB2 for OS/390, Version 7. DB2 Installer has hardware and program requirements.

Hardware Requirements: DB2 Installer requires:

  • 23 MB disk memory on the target drive and 2 MB of disk space for each subsystem defined
  • A monitor capable of displaying 1024-by-768 resolution

Software Requirements: DB2 Installer can run in either of the following environments, each of which has its own requirements:

  • Microsoft Windows, which requires Microsoft Windows NT Version 4.0, Windows 95, or Windows 2000
  • OS/2, which requires:
    • OS/2 Version 4
    • TCP/IP on OS/2, Version 3.0

Both environments require TCP/IP in any of the following circumstances:

  • To run jobs from the workstation
  • To use the copy jobs to host function from the workstation

If you do not have TCP/IP, you may still use DB2 Installer to customize your installation jobs on your workstation. However, you will need to use a method outside of DB2 Installer to move jobs from the workstation to OS/390 for execution.

Visual Explain Requirements: The Visual Explain element of the DB2 Management Tools Package has hardware and software requirements.

Hardware Requirements: The Visual Explain element of the DB2 Management Tools Package requires:

  • A workstation with either OS/2 Version 4 or Windows NT 4.0
  • Approximately 12 MB of HDD space
  • A monitor capable of displaying 1024-by-768 resolution

Software Requirements: DB2 Visual Explain runs on Windows NT 4.0 or OS/2 Version 4. DB2 Connect Personal Edition Version 6 must be installed on the DB2 Visual Explain workstation.

In addition, DB2 Visual Explain requires one of the following communication protocols:

  • TCP/IP
  • SNA communications using a product such as:
    • Communication Server 5.0
    • SNA Server Version 4.0
    • Integrated SNA support in DB2 Universal Database Personal Edition

Visual Explain includes a browser that lets users view current values of subsystem parameters. To use this browser, your DB2 subsystem must have:

  • Stored procedures capability
  • DSNWZP stored procedure enabled

DB2 Estimator Requirements: The DB2 Estimator, which is an element of the DB2 Management Tools Package, operates in the following environments:

  • Windows 98
  • Windows 95
  • Windows 2000
  • Windows NT

DB2 Estimator has no hardware requirements.

Net.Data Requirements: Net.Data for OS/390, Version 7 requires an HTTP Web server installed on the same server as Net.Data and DB2 for OS/390. The Web server can be another HTTP-compliant Web server or one of the following servers: This version of Net.Data requires Server for OS/390, OS/390 Version 2 Release 7 and IBM HTTP Server Version 5 Release 1, the version of the HTTP Web server included in OS/390 Version 2 Release 7. Net.Data for OS/390 has no hardware requirements.

DB2 Warehouse Manager Requirements: Data Warehouse Center, an element of DB2 Warehouse Manager, which is a feature of DB2 for OS/390, Version 7, has software requirements.

Software Requirements: Data Warehouse Center provides agents for OS/390. These agents require:

  • UNIX Systems Services
  • TCP/IP
  • Java Stored Procedures

Data Warehouse Center can support target data warehouses built on any of the following DB2 for OS/390 versions:

  • DB2 UDB for OS/390, Version 7 (5675-DB2)
  • DB2 UDB for OS/390, Version 6 (5645-DB2)
  • DB2 for OS/390, Version 5 (5655-DB2)

QMF Requirements: QMF, which is a feature of DB2 for OS/390, Version 7, has hardware and software requirements.

Hardware Requirements: The following QMF features have hardware dependencies:

  • QMF for OS/390 requires a display station supported by Graphical Data Display Manager (GDDM®)
  • QMF High Performance Option (HPO) requires a display station supported by ISPF
  • QMF for Windows requires a workstation that supports:
    • A Windows 32-bit operating system
    • Network connectivity
    • Approximately 10 MB of HDD space

Software Requirements: The following QMF features have program dependencies:

  • QMF for OS/390 requires GDDM Version 2 Release 3 (5665-356). (This requirement is satisfied by OS/390 Version 2.)

    Use of QMF forms calculations requires a Windows 32-bit operating system and IBM Object REXX Interpreter Edition Version 1.0 (5639-B73).

  • QMF for Windows requires:
    • A Windows 32-bit operating system
    • Network communication software on each user machine, plus one or both of the following programs:
      • An SNA product that provides a CPI-C interface
      • A TCP/IP product that provides a WinSock Version 1.1 interface

    Use of QMF for Windows with English Wizard natural language query requires English Wizard Release 3.1, from Linguistic Technologies.

    The QMF for Windows Administrator module requires a Windows 32-bit operating system.

    QMF for Windows CLI connectivity to DB2 UDB on UNIX, Windows, and OS/2 requires DB2 Universal Database Version 5.2 client.

DB2 Performance Monitor Requirements: DB2 Performance Monitor (DB2 PM), which is a feature of DB2 for OS/390, Version 7, has hardware and software requirements.

Hardware Requirements: DB2 Performance Monitor has the following dependencies.

  • For the host-based Online Monitor, a display station supported by Interactive System Productivity Facility (ISPF)
  • For the host-based graphics facility, an IBM color graphics display station, or equivalent, supported by GDDM

The DB2 PM Workstation-Based Online Monitor has the following dependencies:

  • A high-resolution monitor
  • A workstation that supports OS/2 Version 3 or Windows NT 4.0
  • Approximately 25 MB of hard disk space

Software Requirements: For the host-based Online Monitor and host-based graphics facility, DB2 Performance Monitor has no functional dependencies if you are monitoring DB2 for OS/390, Version 7. The DB2 Performance Monitor feature supports an environment of multiple DB2 releases, namely instrumentation, catalog, and PLAN_TABLE data from:

  • DB2 UDB for OS/390, Version 7 (5675-DB2)
  • DB2 UDB for OS/390, Version 6 (5645-DB2)
  • DB2 for OS/390, Version 5 (5655-DB2)

The DB2 PM Workstation-Based Online Monitor API is supported on the following platforms:

  • IBM AIX Version 4.3
  • Microsoft Windows NT 4.0
  • Sun Solaris 5.6 (32-bit) and Sun Solaris 5.7 (64-bit)
  • Linux Version 2.2 on Intel


DB2: DB2 UDB for OS/390, Version 7 is upwardly compatible with prior releases of DB2 for OS/390 and DB2 for MVS/ESA. Migration with full fallback protection is available for customers running on either DB2 for OS/390, Version 5 or DB2 UDB for OS/390, Version 6. Thus, existing customers should ensure they are successfully running on DB2 for OS/390, Version 5, or later, before migrating to DB2 UDB for OS/390, Version 7.

DB2 Performance Monitor Feature: The DB2 Performance Monitor Feature supports an environment of multiple DB2 releases, namely instrumentation, catalog, and PLAN_TABLE data from:

  • DB2 UDB for OS/390, Version 7 (5675-DB2)
  • DB2 UDB for OS/390, Version 6 (5645-DB2)
  • DB2 for OS/390, Version 5 (5655-DB2)

User Group Requirements: This announcement satisfies or partially satisfies 63 requirements from one or more of the worldwide user group communities. Groups include Australasian SHARE/GUIDE (ASG), COMMON, COMMON Europe, GUIDE International, G.U.I.D.E. Europe, Japan GUIDE/SHARE (JGS), Guide Latin American (LAG), SHARE EUROPE, and SHARE Incorporated. Requirements satisfied include:

REQ00033987         DB2 Unload Utility
REQ00048887         Unload Utility
REQ00073656         DB2 Utility REORG Unload External
                      Inconvenient to use
REQ00075995         REORG Unload Enhancements
REQ00039320         COPY with filtering of Table Space
REQ00039857         Image Copy by Database
REQ00039323         Thread Termination without Rollback
REQ00041522         Terminate Thread without Backout
REQ00072841         Command to Cancel Backout and
                      Recover Objects
REQ00075829         CANCEL Thread no Rollback
REQ00069335         Online REORG Switch Phase Takes Too
                      Long to Complete
REQ00039675         Cursor that can be read backwards
REQ00039774         Support scrollable cursor
REQ00023873         Fetch previous row
REQ00039675         Cursor that can be read backwards
REQ00039114         DB2 Unpredictable Results when using
                      Index instead of Temporary
REQ00039450         Ensure Predictable results when
                      fetching a row from a cursor
REQ00025551         Provide an option to make a CURSOR
                      insensitive to updates
REQ00039634         Select max on the first column in a
                      unique index in DB2
REQ00039540         Enhance DB2 to walk backwards
                      through an INDEX
REQ00026448         DB2-Precompiler support for SQL
                      statements in nested program
REQ00028200         Embedded SQL within VS COBOL II
                      Nested Programs
REQ00039163         Extra DB2 precompiler option to
                      suppress generated code
REQ00039758         Support Subscripted Host-Variable
REQ00039798         Reduce Initialization overhead for
                      SQL in programs containing SQL
REQ00029770         Dynamic Alteration of DB2 System
REQ00037642         DB2 -- Provide dynamic ZPARM
                      update capability
REQ00039026         DB2 system parameters: dynamic
REQ00039704         DSNZPARM
REQ00039772         Capability of Dynamically changing to
                      DSNZPARM parameters
REQ00062092         Dynamic Assignment of DSNZPARM
REQ00062114         Dynamic Updating of DSNZPARM
REQ00068708         DB2 DSNZPARM, ZPARM, changing
                      while DB2 is running
REQ00068708_1       DB2 DSNZPARM, ZPARM, changing
                      while DB2 is running
REQ00072132         ZPARM Alteration Function While
                      Running DB2 System
REQ00072836         Dynamic change for DSNZPARM
REQ00072848         Dynamic Modification of Unit type
                      for Archive Logs
REQ00074416         Improvement of DB2 subquery
REQ00029459         Allow DB2 Delete From Table with
                      Subselect From Same Table
REQ0007521          Reduce recovery time in data sharing
REQ00065739         Online Utilities
REQ00073348         LOAD RESUME with OLTP concurrency/
                      generic insert bmp capability
REQ00039004         COMMENT ON for all DB2 objects
REQ00039628         Only a few tables have remark columns
                      All of the tables ought
REQ00039640         Get DSNTINST values from running
REQ00075134         DB2 Optimizer
REQ00075699         Catalog column for immedwrite bind
REQ00039011         Prize-list
REQ00039296         Adding new statement SELECT FIRST (n)
REQ00040253         Allow a limit to be placed on the number
                      of rows retrieved
REQ00074553         Limit the number of rows returned on
                      a select statement
REQ00068551_1       Increase of acceptable values for
                      QUERYNO in Plan_table
REQ00068551         Increase of acceptable values for
                      QUERYNO in Plan_table
REQ00069866         Top Secret DB2 Interface
REQ00070041         Authorization exit needs to pass
                      DATABASE name for ALTER and DROP
REQ00034332         Allow DBADM to create view with
                      different qualifier
REQ00039156         DB2 -- Enhance DBADM authority
REQ00041745         DBADM authority to create views
REQ00045374         Specify a owner in CREATE VIEW
REQ00058505         DBADM-Authority for CREATE VIEW
REQ00065735         DBADM-Authority for CREATE VIEW
REQ00074704         Full RACF DB2 Security Management
REQ00072517         Accounting traces running a full day

Planning Information

Customer Responsibilities: Customers should review the sections in this announcement that describe the hardware and software dependencies for DB2 UDB for OS/390, Version 7.

DB2 UDB for OS/390, Version 7 has support for migration from Version 5 or 6. Customers not yet on Version 5, should plan to migrate to DB2 for OS/390, Version 5, or later, as preparation for a migration to Version 7.

Migration Considerations: IBM added many universal database capabilities in DB2 UDB for OS/390, Version 6 and removed support for some functions. As you prepare to migrate your subsystems, you should be aware of the following changes:

  • Type 1 indexes are no longer supported. DB2 for OS/390 requires type 2 indexes for every index. Convert all indexes to type 2 before migrating to Version 6 or 7.
  • Data set password protection is no longer supported. DB2 subsystems should protect data sets by using a security subsystem, such as RACF, which is an element of the OS/390 Security Server, rather than by passwords. Remove all passwords from all indexes and table spaces before migrating to Version 6 or 7.
  • Shared read-only data is replaced by more substantial, more usable data sharing. Another alternative is to use distributed data. Convert or drop all shared read-only databases before migrating to Version 6 or 7.
  • Host variables in SQL statements now require a preceding colon. In previous releases of DB2 for OS/390, the colons are optional. Ensure all host variable references include a preceding colon before migrating to Version 6 or 7.
  • RECOVER INDEX is renamed to REBUILD INDEX. Versions 4 and 5 of DB2 for OS/390 provide an alias REBUILD INDEX so you can prepare for the change. Convert utility jobs to use the REBUILD INDEX syntax before migrating to Version 6 or 7.
  • Using prior releases of DB2 for OS/390, you register stored procedures in the SYSIBM.SYSPROCEDURES catalog table. You control access by using the AUTHID and LUNAME of the caller. Using Version 6, you register stored procedures in a new catalog table by using the CREATE PROCEDURE statement. You can map your stored procedure definitions that use AUTHID to the schema and CURRENT PATH support. You must eliminate rows that control access with LUNAME prior to migrating to Version 6 or 7.

Installability: Customers should refer to the Program Directory shipped with the product.

Packaging: The DB2 UDB Server for OS/390, except as noted below, will be shipped on 9-track magnetic tapes (written at 6,250 bpi), 3480 cartridges, or 4-mm DAT cartridges.

Also included will be a Program Directory, and under separate cover, one copy of the entitled publications.

The workstation client functions of DB2 Management Tools Package, DB2 Extenders, and Buffer Pool Tool will be shipped on CD-ROM.

The QMF for Windows product CD-ROM also contains all supported national languages and all U.S. English and translated publications in softcopy format. Two publications are shipped:

  • Getting Started with QMF for Windows
  • Installing and Managing QMF for Windows

Security, Auditability, and Control

DB2 UDB for OS/390, Version 7 uses the security and auditability features of the host OS/390 systems. It also provides facilities for the protection and control of its resources. These facilities include controls for:

  • System access
  • Data access and control
  • Concurrent access
  • Data recovery
  • Accounting

The customer is responsible for evaluation, selection, and implementation of security features, administrative procedures, and appropriate controls in application systems and communication facilities.

Ordering Information

Ordering instructions and order processing availability will be communicated when IBM announces the availability of this product.

Terms and Conditions

Licensing: IBM Customer Agreement (ICA)

Variable Charges Apply: No

Parallel Sysplex® License Charge (PSLC) Applies: Yes

Installation License or Location License Applies: No

Usage Restriction Applies: No

Educational Allowance: Yes, to qualified education customers.

Volume Discount: Not applicable

Replaced Program            Replacement Program      Applies
DB2 Version 1               DB2 UDB Version 7          Yes
  5740-XYR                    5675-DB2
DB2 Version 2               DB2 UDB Version 7          Yes
  5665-DB2                    5675-DB2
DB2 Version 3               DB2 UDB Version 7          Yes
  5685-DB2                    5675-DB2
DB2 Version 4               DB2 UDB  Version 7         Yes
  5695-DB2                    5675-DB2
DB2 Version 5               DB2 UDB Version 7          Yes
  5655-DB2                    5675-DB2
DB2 Version 6               DB2 UDB Version 7          Yes
  5645-DB2                    5675-DB2
DB2 Version 7               To a follow-on program,    N/A
  5675-DB2                    if any.
Replaced Program            Replacement Program      Applies
DB2 Performance Monitor  (PM) Feature
DB2 PM Version 1            DB2 UDB Version 7         Yes(2)
  5665-354                    5675-DB2
DB2 PM Version 2            DB2 UDB Version 7         Yes(2)
  5685-140                    5675-DB2
DB2 PM Version 3            DB2 UDB Version 7         Yes(2)
  5655-047                    5675-DB2
DB2 PM Version 4            DB2 UDB Version 7         Yes(2)
  5655-102                    5675-DB2
QMF Feature
QMF Version 1               DB2 UDB Version 7         Yes(2)
  5668-972                    5675-DB2
QMF Version 2               DB2 UDB Version 7         Yes(2)
  5668-721                    5675-DB2
QMF Version 3               DB2 UDB Version 7         Yes(2)
  5706-254                    5675-DB2
QMF for Windows Feature
QMF for Windows,            DB2 UDB Version 7         Yes(2)
  Version 3.3               5675-DB2
DB2 DataPropagator Feature
DataPropagator              DB2 UDB Version 7         Yes(2)
  Relational Apply            5675-DB2
  for MVS,
  Version 1.2.1
DataPropagator              DB2 UDB Version 7         Yes(2)
  Relational Capture          5675-DB2
  for MVS,
  Version 1.2.1
DataPropagator              DB2 UDB Version 7         Yes(2)
  Relational Apply            5675-DB2
  for MVS,
  Version 5.1
DataPropagator              DB2 UDB Version 7         Yes(2)
  Relational Capture          5675-DB2
  for MVS,
  Version 5.1
DB2 Admin Tool Feature
DB2 Administration          DB2 UDB Version 7         Yes(2)
  Tool Version 1              5675-DB2
DB2 Buffer Pool Tool Feature
DB2 Buffer Pool             DB2 UDB Version 7         Yes(2)
  Tool Version 1              5675-DB2
Note 3 of DAWN-0040 applies

Try and Buy Program: Several of the optional priced features of DB2 Universal Database Server for OS/390, Version 7 are combined into the DB2 Trial Tools Package feature, which is shipped with each new order of DB2 UDB Server for OS/390. These tools are available for customer evaluation under a limited term trial license. The following priced features are included in the Try and Buy program:

  • DB2 Administration Tool
  • DB2 Bind Manager
  • DB2 DBRM Checker
  • DB2 Buffer Pool Tool
  • DB2 DataPropagator
  • DB2 Forms
  • DB2 Performance Monitor
  • DB2 SQL Performance Analyzer
  • DB2 Recovery Manager
  • DB2 Operational Utilities
  • DB2 Recovery & Diagnostic Utilities

These features may be installed and used on a trial basis for 90 days from the date of installation of the feature, except for the DB2 Buffer Pool Tool and DB2 Forms Features, which may be installed and used on a trial basis for 60 days from the date of installation. A full-use license for each of the above priced features may be obtained by ordering the appropriate billing feature number and media feature number, labeled as "Buy", for the optional priced feature. The trial version of these priced features contain time disabling devices that will prevent their use upon expiration of the trial period.

Limited-Use License for DB2 Connect: The DB2 Management Tools Package provides a restricted-use copy of DB2 Connect, Version 7.

Authorization to use this copy of DB2 Connect is to enable access to DB2 for OS/390 data by the following workstation tools only:

  • DB2 Control Center
  • Stored Procedure Builder
  • Visual Explain

For all other remote connections to DB2 for OS/390 that are provided through DB2 Connect, or for any use of DB2 Connect not specifically described above, you must obtain a separate license for DB2 Connect

Limited-Use License for DB2 UDB Enterprise Edition: The DB2 Warehouse Manager provides a restricted-use copy of DB2 UDB Enterprise Edition Version 7.

Authorization to use this copy of DB2 UDB Enterprise Edition is to enable database management of DB2 for OS/390 data warehouse control databases by the following workstation tools only:

  • Data Warehouse Center
  • Information Catalog
  • DB2 OLAP Starter Kit

For use of DB2 UDB Enterprise Edition other than the uses specifically described above, you must obtain a separate license for DB2 UDB Enterprise Edition from IBM.

Warranted: Yes

Licensed Program Materials Availability

  • Restricted Materials of IBM: None
  • Non-Restricted Source Materials: None
  • Object Code Only (OCO): All

Testing Period: Two months (basic license only)

Program Services

  • Support Center applies: Yes, access is available through the IBM Support Center.
  • Available until discontinued 12 months written notice
  • APAR Mailing Address:

      IBM Corporation
      APAR Processing
      P.O. Box 49023
      San Jose, CA 95161-9023
  • APAR Mailing Address (QMF for Windows Feature):

      IBM Corporation
      APAR Processing
      One Innovation Drive
      Natick, MA 01760

Order Now

 Use Priority/Reference Code: LE001
 Phone:     800-IBM-CALL
 Fax:       800-2IBM-FAX
 Mail:      IBM Atlanta Sales Center
            Dept. LE001
            P.O. Box 2690
            Atlanta, GA  30301-2690

You can also contact your local IBM Business Partner or IBM representative. To identify them, call 800-IBM-4YOU.

Note: Shipments will begin after the planned availability date.


QMF, DataPropagator, MVS, IMS, DataRefresher, MVS/ESA, ESA/390, ES/3090, ES/4381, S/390 Parallel Enterprise Server, DFSORT, DATABASE 2, DFSMS, C/370, and Open Class are trademarks of International Business Machines Corporation in the United States or other countries or both.
DB2, OS/390, DB2 Universal Database, DB2 Connect, Net.Data, DB2 OLAP Server, AS/400, AIX, OS/2, DataJoiner, CICS, DRDA, System/390, S/390, Multiprise, RS/6000, ES/9000, IMS/ESA, Operating System/400, OS/400, CICS/ESA, DB2 Extenders, VisualAge, RACF, NetView, BookManager, AD/Cycle, GDDM, and Parallel Sysplex are registered trademarks of International Business Machines Corporation in the United States or other countries or both.
Windows, Windows NT, and Microsoft are trademarks of Microsoft Corporation.
Java is a trademark of Sun Microsystems, Inc.
UNIX is a registered trademark in the United States and other countries exclusively through X/Open Company Limited.
NotesPump is a trademark of Lotus Development Corporation.
Lotus, 1-2-3, and Lotus Notes are registered trademarks of Lotus Development Corporation.
Other company, product, and service names may be trademarks or service marks of others.

Share this page

Digg Linked In

Contact IBM

Considering a purchase?