Data Management Tools | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 1. Product Overview

Data Management Tools

IBM has made a commitment to delivering a quality set of tools as viable alternatives to third-party tools. These tools are offered in the Data Management toolset and are designed to perform at the level that today's systems required. The tools are divided into four categories: Database Administration, Database Performance Management, Database Recovery and Replication, and Database Application Management.

DB2 Administration Tool. Provides a comprehensive set of functions to efficiently and effectively manage DB2 environments.
DB2 Automation Tool. Continuously and automatically coordinates the execution of DB2 tools, enabling you to realize the full potential of your DB2 system.
DB2 Forms. Provides a drag-and-drop solution for quickly building robust, user -friendly e-business front ends to any DB2 database.
DB2 High-Performance Unload. Offers sequential reading and the capability to access your DB2 data at top speed.
DB2 Log Analysis Tool. Provides immediate pinpoint focus for recovering data changes in real time during normal operations.
DB2 Object Restore. Allows for the restoration of dropped objects and all related dependencies automatically.
DB2 Buffer Pool Analyzer. Helps database administrators (DBAs) manage buffer pools more efficiently by providing information about the buffer pools and the behavior of the objects in them.
DB2 Performance Monitor. Helps you get optimum performance from your database management system and provides useful support for resolving critical performance issues.
DB2 Query Monitor. Provides the ability to monitor queries and to quickly pinpoint and resolve problems without going through large amounts of output.
DB2 SQL Performance Analyzer. Gives you the ability to estimate the costs of database queries prior to execution.
DB2 DataPropagator. This product provides replication capabilities for DB2 databases and is integrated in DB2 on the UNIX and Intel platforms.
DB2 Recovery Manager. Significantly speeds up and simplifies the access to and recovery of databases for productive use throughout an enterprise.
DB2 Row Archive Manager. Provides a complete solution for selecting, archiving, managing, and retrieving aged data.
DB2 Bind Manager. Helps reduce the time and cost of connecting new application programs to a database.
DB2 Web Query Tool. Lets end users access, create, share, and execute SQL queries through their browsers against multiple IBM DB2 databases.
DB2 Data Export Facility. This enables DBAs to quickly identify database dependencies and extract referentially intact sets of data into a DB2 LOAD utility-ready file format so they can be copied to other subsystems.
DB2 Table Editor. A multiplatform tool that provides a variety of ways for users to quickly and easily update, create, and delete data across multiple DB2 database platforms.
DB2 Archive Log Compression Tool. Provides a way to automatically compress the IBM DB2 logs at the time of storage and decompress them at retrieval.
DB2 Change Accumulation Tool. Quickly restores database objects with precision and minimal disruption.
DB2 Object Comparison Tool. Maximizes system availability by keeping production and test catalogs in sync.
DB2 Path Checker. Identifies potential effects of performing a bind on one or many programs.

Each of these tools is further described in the following sections.

Database Administration

The following tools can help with the daily activities of database administration and can also provide some automation of redundant tasks .

DB2 Administration Tool for OS/390

IBM DB2 Administration Tool for OS/390 can help you derive the maximum performance and results from your DB2 system, dramatically reducing the effort required to support DB2. This tool provides a comprehensive set of functions that help efficiently and effectively manage DB2 environments. It is an interactive, intuitive, and easy-to-use tool that provides a cost-effective solution for a full range of database administration functions, such as

Display and interpret objects in the DB2 catalog.
Execute dynamic SQL statistics.
Issue DB2 commands against objects.
Manage SQL IDs.
Perform complex performance and space utilization queries.
Reverse-engineer objects in the DB2 catalog.
Display and cancel threads.
Manage the CDB and RLIMITS tables.
Manage the DB2 environment effectively.
Manage resource limit specification tables (RLSTs)
Display and estimate storage allocations .

It has ALTER capabilities that allow you to change name and attributes of a table or column, modify the definitions of columns, change the sequence of columns, drop columns, and insert new columns . The MIGRATE function allows you to create a separate DB2 test system, move the test system into a production system, and consolidate two separate database systems into one. It can also be used to generate Job Control Language (JCL) to migrate the DDL and data to a target subsystem.

It integrates with other DB2 utilities and simplifies creation of DB2 utility jobs. It also has catalog update functions and has facilities for issuing any DB2 command, issuing GRANT and REVOKE on objects, and performing BIND, REBIND, and FREE operations on selected plans and packages.

DB2 Automation Tool

This tool allows for the automation of some maintenance tasks and provides a statistical history-reporting capability in order to do trend analysis and forecasting. This ability to automate the execution of DB2 tools enables the DBA to focus on more important tasks, such as system optimization and performance.

The DB2 Automation Tool can be used to automate the generation of utility JCL on an as-needed basis to REORG or COPY DB2 objects as necessary.

It provides an interface for the creation of reusable profiles used to define utility control cards. These profiles allow for definition of the utility, identification of the objects to be maintained , and the thresholds for the JCL to be generated.

It also supports workload balancing based on past historical execution data. The product has a Data Management facility, which allows for viewing and evaluating various datasets. It can also be used to move datasets.

DB2 Table Editor

IBM DB2 Table Editor is a powerful, multiplatform tool providing a variety of ways for users to quickly and easily update, create, and delete data across multiple DB2 database platforms with security, referential integrity, and support for the advanced capabilities of DB2. DB2 Table Editor is accessible by both Windows-based and Java-based users, including those with browsers, making it a viable tool for users at all levels. Customers who need to safely provide data editing and data entry capabilities to end users at the front lines of a business or to administrators will find DB2 Table Editor a valuable offering. This tool features

Data editing and data entry capabilities for DBAs and end users accessing the table data directly or via forms.
Ability to edit DB2 tables everywhere with a choice of end-user entry points: Java-enabled Web browsers, Java interfaces launched from IBM DB2 Control Center, Microsoft Windows, or an ISPF interface.
A variety of ways to navigate, create, update, and delete data across multiple platforms with security, referential integrity, and support for the advanced capabilities of DB2.
The ability to drag-and-drop or use wizards to rapidly create versatile, task-specific Java-based or Windows-based table editing forms containing built-in data validation and business rules.
A Java-based application that allows you to do a wide variety of table editing tasks, including the ability to edit tables on multiple DB2 database platforms, to maintain referential integrity while performing updates, and to search for and locate specific rows quickly and jump to tables of related data immediately.
Ability to use administrative and forms-building modules with the Windows-based interface to do these table editing tasks and many others that include saving forms for Java or Windows deployment, building sets of forms and linking them with parentchild relationships, and embedding forms into Web pages as tightly controlled data access and data entry applications.
Support for a wide range of database platforms, including IBM DB2 Universal Database for z/OS and OS/390, DB2 for iSeries, and DB2 UDB for multiple platforms, including on IBM AIX, Linux, Microsoft Windows, Sun Solaris, and HP/UX.

DB2 High-Performance Unload for OS/390

DB2 High-Performance Unload offers sequential reading and the capability to access your DB2 data at top speed. It can scan a table space and create output files in the format in which you need them. All you have to do is select the criteria. You can select from many types of conversions, giving your output the desired appearance, such as delimited file for export to another platform or DSNTIAUL (a DB2 supplied unload utility) compatibility. This tool provides the following benefits:

Increased speed for unload functions.
High-speed unloads of DB2 data using native VSAM.
Ability to unload from image copies as well as active tables.
Multiple output files during a single unload with minimal additional cost.
Multiple output formats, including the opportunity to tailor your own.
Comprehensive and powerful SELECT statement.
More efficient unloads of DB2 data.
Help with batch window constraints.
Ability to unload multiple tables each to a separate file or a single table many times.

Large scan performance can become critical when several unloads have to read the same table space concurrently. Performance symptoms can be caused by multiple reads of the same DB2 pages. Sometimes there are channel conflicts. IBM DB2 High-Performance Unload can help solve these problems.

You can code as many SELECT statements as you want for any tables belonging to the same table space. Different output files can be created during the same unload process at very little additional cost. Multiple tables can be unloaded at once, each to its own file, as long as all the tables belong to the same table space. You can unload the same table many times with different SELECT statements, using only one table space scan. You can also run this utility against incremental image copies or any full image copy as well as against active tables.

DB2 Log Analysis Tool for OS/390

The DB2 Log Analysis tool provides you many benefits in the area of recovery and availability. It allows you to locate, UNDO, and REDO changes with ease and to quickly isolate accidental or undesired changes to your database tables. You can then selectively reverse these changes from an easy-to-use, menu-driven interface. The tool also allows you to selectively REDO specific transactions after large-scale reversals of data changes, and locate changes made to one database and copy those changes to another database. These types of activities can be performed while the DB2 systems continue to run as normal. This helps to minimize downtime from conventional recovery sessions and maximize uptime for e-business availability.

DB2 Log Analysis Tool provides a simple interface that lets you quickly specify the scope and magnitude of data changes you wish to viewby dates, users, tables, and much more. Valuable time and system resources are further saved by support of data compression in DB2 for OS/390 and by the elimination of the need to capture data changes and associated administrative, I/O, and storage overhead. Once you've spotted changes that must be reversed , they're easily undone with the tool. It generates the necessary SQL, which is sent to a file for execution by any method referred by the database administrator.

The tool has a comprehensive set of reports . Some of the data change reporting features include

Set filtering criteria for reporting data changes by authid, job name, plan name, table, database, table space, or date ranges.
Produce summary reports from which drill-down detailed reports can be obtained.
View filtered summary of desired date range, tables, and more.
View detailed reports showing all data change events, including original state, current state, and INSERT, UPDATE, and DELETE operations executed.
Save filtered reports as JCL to be run at will.

DB2 Object Restore for OS/390

IBM DB2 Object Restore for OS/390 can restore dropped objects and all related dependencies automatically. This can include dropped storage groups, databases, tablespaces, tables, indexes, table and column authorizations, and data.

This tool eliminates the need for a shadow copy of the catalog to recover objects. The result is that DB2 Object Restore saves direct access storage device (DASD) space. With this tool you can feel secure in cleaning up your DB2 system, because you can restore discarded DB2 objects if necessary.

Some highlights of DB2 Object Restore include

DDL generation, which recreates the dropped objects.
Versioning repository to generate the DDL and to run analyses on the repositories to map old database identifiers (DBIDs), page set identifiers (PSIDs), and object identifiers (OBIDs) to new IDs for recreated objects.
Move/copy object feature, which allows you to move the objects from one DB2 subsystem to another.
Restore object hierarchy, which automatically rebuilds the structure of dependent objects.

DB2 Data Export Facility

DB2 Data Export Facility enables DBAs to quickly identify database dependencies and extract referentially intact sets of data into a DB2 LOAD utility-ready file format for immediate copying to other DB2 subsystems. Such efficient extraction and copying capabilities are valuable not only for application testing, but also when migrating applications to production environments. The tool can also

Export and copy data for application development and testing using an ISPF interface that easily extracts RI sets of data or individual tables and/or rows, into a DB2 LOAD-ready file format for copying to other DB2 subsystems.
Copy selected source data to test environments for faster application development and testing.
Find relationships between tables and allow easy selection and export of precisely the desired scope of data among the referential set, such as exporting a table, a table and all its dependencies, or only certain dependencies.
Navigate through table relationships with a simple step-through interface that extracts desired subsets of data so users can see the attributes of tables, such as columns, indexes, and parentchild relationships.
Identify referential integrity properties as you navigate, including parentchild relationships, indexes, and columns.
Support highly selective data copying that helps cut through the complexity normally associated with surgical problem resolution, populating test environments, or migrating applications to new or additional environments.
Provide exporting without referential integrity from a single table so that data can be narrowed down to the precise columns and rows desired.

DB2 Object Comparison Tool

This comparison tool for z/OS maximizes system availability by keeping your production and testing database environments running in parallel. It is used to compare the structure of DB2 objects. It can be used to identify differences between objects via a report.

Provides the ability to compare objects (and dependent objects) from one source to those in another, (e.g., test and development database system can be a mirror image of the production system).
Runs as an extension to DB2 Administration Tool for OS/390, version 2.1.
Includes a DB2 catalog extract function, a batch job generator, and a dynamic-link library (DLL) extract feature.
Provides an ISPF front end and a batch-compare capability.
Can be used to make a target reflect a source.
JCL is generated to accomplish all of the necessary steps in the correct sequence.

Database Performance Management

The following tools are designed to help with monitoring system and SQL performance in the DB2 environment.

DB2 Buffer Pool Analyzer

IBM DB2 Buffer Pool Analyzer helps DBAs manage buffer pools more efficiently by providing information about current buffer pool behavior and using simulation to anticipate future behavior. This tool provides data collection of virtual buffer pool activity via the DB2 instrumentation facility interface (IFI). It also offers comprehensive reporting of the buffer pool activity, including

Ordering by various identifiers such as buffer pool, plan, object, and primary authorization id.
Sorting by GETPAGE requests , sequential prefetch, and synchronous read.
Filtering capability, and loading into tables.

The Buffer Pool Analyzer allows you to simulate buffer pool usage for varying buffer pool sizes and different object placement. It can also produce a display report and simulation results on workstations in form of spreadsheets, graphs and diagrams.

DB2 Performance Monitor for OS/390

DB2 Performance Monitor is designed to help you get optimum performance from your database management system and provides useful support for resolving critical performance issues. It gives you the ability to analyze, control, and tune the performance of DB2 and DB2 applications. It also includes a real-time online monitor, a wide variety of reports for in-depth analysis, and an EXPLAIN feature to analyze and optimize SQL statements. It can be executed using the ISPF panels or on a Windows workstation. DB2 Performance Monitor can help you monitor

DB2 and DB2 applications online and in batch by reporting alerts immediately when problems occur.
Usage of workstations, which allows parallel monitoring of different DB2 subsystems from one point of control.
The status of a DB2 subsystem and its applications while they are currently active or events and performance problems that happened in the past.
Individual data sharing members or entire data sharing groups in online or batch modes.
Applications running in a query parallelism environment on a Parallel Sysplex cluster, even if the parallel tasks are executed on different processors.

This tool also allows you to analyze performance problems online or through a vast set of detailed reports and to obtain tuning recommendations. It also has the ability to run EXPLAINs, recognize periodic and event exceptions, and take appropriate actions by means of a user exit, by restricting user authorization to specific threads, by recognizing trends and anticipating potential bottlenecks, and by saving DB2 trace data.

DB2 Query Monitor for OS/390

The DB2 Query Monitor for OS/390 lets you quickly pinpoint the kinds of activity that matter to you. It provides both real-time and historical views into query activity throughout your DB2 subsystems. It also provides extensive choice in determining what data is gathered during monitoring, when it is gathered, and about whom. When the thresholds you specify are crossed, activity is logged for rapid drill-down and, if needed, termination. At intervals also under your control, real-time activity is saved from memory to disk and, if desired, from disk to database. So, even while your world of DB2 subsystems is completely open to your view (both as current activity and histories), you incur the lowest system monitoring overhead. The DB2 Query Monitor also allows you to

Navigate database activity with ease.
Identify trouble in real time before DB2 resources are wasted .
Locate problem queries during execution.
Identify users and locations issuing problem queries.
Set thresholds that automate early warnings and corrective action.
Terminate problem queries.
Fix processes that are hurting DB2 availability.

This tool has the ability to have multiple monitoring agents , each capable of watching up to 64 DB2 subsystems, to track the activity you specify. You can customize it also via user-defined profiles that highlight real-time events of interest. You also have the ability to set thresholds by CPU time, elapsed time, GETPAGE requests, rows returned, delay time, number of SQL calls, lock failures, lock escalations, invalid SQL codes, or application abends (abnormal ends).

DB2 SQL Performance Analyzer for OS/390

The DB2 SQL Performance Analyzer makes it easier to reduce the escalating costs of database queries by estimating their cost prior to execution. Many times applications fail to perform according to needs, particularly at high transaction volumes . Frequently, the problem is that database queries run too long, causing the batch production window to shrink, which frustrates users and adds to costs.

This tool can perform data modeling during the design phase of an application, determine which SQL statements in major production are the most resource- intensive , and provide a tool that allows efficient queries to be developed and saved. It also can be used to analyze existing SQL statements that have demonstrated performance problems.

It can also approximate how long queries will take before database resources are unnecessarily consumed and can even estimate the cost of a query before it is delivered. Estimates are given in familiar units, such as CPU time, I/O count, elapsed time, or monetary cost. This allows you to perform what-if scenarios to determine what performance can be obtained now and in the future with various database design alternatives and production volumes.

DB2 Performance Expert for OS/390

This tool is a combination of the DB2 Performance Monitor and the DB2 Buffer Pool Analyzer. It is enhanced by the ability to provide recommendations regarding the tuning of the environment.

Database Recovery and Replication Management

The following tools assist the DBA with activities surrounding replication and recovery.

DB2 DataPropagator for OS/390

DB2 DataPropagator offers numerous features for a comprehensive replication solution, such as

A highly efficient architecture that automatically captures and asynchronously propagates data changes to the DB2 family of databases.
A high-performance log-based change-capture component, which captures all DB2 updates without application changes.
Powerful data transformation using standard SQL, including multitable joins and stored procedures.

The IBM replication solution enables full refresh and change propagation across a heterogeneous database environment. DB2 DataPropagator supports a wide variety of databases, including DB2, Oracle, Microsoft, Sybase, Informix, IMS, Lotus Notes, and flat files. Replication is also a core technology that enables mobile users to keep their data synchronized with corporate data residing on a DB2 server. Some of the highlights of this product include

Replicates information across the enterprise.
Captures data changes against a source database automatically and propagates the changes to a target database.
Replicates across MVS, OS/390, VM, VSE, AS/400, OS/2, AIX, HP-UX, Sun's Solaris, Linux for S/390, NUMA-Q, and Microsoft Windows platforms.
Unites distributed, heterogeneous databases into an integrated database environment.

The IBM DB2 DataPropagator can help you improve data availability and operational database performance. By replicating data between your central database and regional transactional databases, you can make business data available to the regional databases for prompt transaction processing. It also has sophisticated data transformation capabilities, maximizing the value of your data warehouse and business intelligence systems. It will allow you to derive data using arithmetic (Boolean or any valid SQL expression), aggregate data to produce sums or averages using SQL column functions, convert data by translating encoded fields to descriptive fields, and consolidate data through joins or unions.

DB2 Recovery Manager for OS/390

The IBM DB2 Recovery Manager function significantly speeds and simplifies the access to and recovery of databases for productive use throughout an enterprise. It enables versatile database recovery functions in e-business transaction environments. It can be used to recover a DB2 or IMS application individually and coordinate the recovery of both DB2 and IMS data to a common point, cutting the time and cost of data recovery and availability. This ability eliminates the error-prone complexity of managing different logs, utilities, and processes to do recovery from both databases.

DB2 Recovery Manager works with either DB2 or IMS, or both. It uses image copies for both databases and establishes synchronization points in each called a Virtual Image Copy (VIC) to which recovery may be done. After an application is run in which both DB2 and IMS are accessed, DB2 Recovery Manager is invoked to establish a VIC. It also establishes a quiesce point and notes where the point is located in the respective logs. During recovery, the user specifies the need to recover to this VIC, and DB2 Recovery Manager applies the appropriate image copies and causes the log to be applied up to this point.

You also have the option to automate the recovery of resources. This tool will generate the JCL, locate the proper image copies, and control the execution of jobs for either DB2 or IMS.

DB2 Row Archive Manager for OS/390

DB2 Row Archive Manager is a complete solution for selecting, archiving, managing, and retrieving aged data. By applying appropriate judgment on the need to access your aged data, you can use DB2 Row Archive Manager to reduce the required DASD storage, improve your DB2 performance, and reduce the cost of your overall DB2 environment. This can be achieved by archiving aged DB2 data and doing this archiving at a table row or column level to reduce storage requirements. Performance improvements can be seen by removing aged data, which reduces time spent on table scans and decreases index size . This would also reduce the amount of data processed and time consumed by costly maintenance activities such as reorganizations, image copies, and recoveries .

The DB2 Row Archive Manager administrator implements a set of rules to determine what data is eligible to be archived. There are no programs to write. Only the selected rows are archived at any specified time. DB2 Row Archive Manager also allows you to determine which columns of a row should be archived. Referentially linked rows from related tables or systems of related tables can be archived as a unit. This archived data can be selectively retrieved on demand. The Row Archive Manager catalog is used to locate and retrieve the needed data, usually running as an overnight batch job.

This tool also includes additional utilities, such as REMOVE, which can discard erroneous or obsolete archive specifications from the system. Storage management functions are also performed with DB2 Row Archive Manager utilities to effectively manage the physical storage used by the archived table spaces. It currently does require a PL/1 compiler.

DB2 Archive Log Compression Tool

IBM DB2 Archive Log Compression Tool reduces storage and I/O requirements associated with comprehensive disaster recovery preparedness and automatically compresses IBM DB2 logs at the time of storage and decompresses them at retrieval. It can compress the log at the time of archival or after the log has been archived. This tool provides a way to

Restore data directly from highly compressed, off-site copies of DB2 logs.
Enable DBAs to reduce the volume of archived logs, resulting in shorter I/O and recovery times.
Help lower storage costs by enabling the storage of logs on DASD instead of tape.

DB2 Change Accumulation Tool

IBM DB2 Change Accumulation Tool quickly restores database objects with precision and minimal disruption, setting the scope and specificity of image copy creation through the use of control cards.

Makes accurate point-in-time recovery of database objects simple and reliable by restoring database object in a precise manner with minimal disruptions.
Allows recovery routines to focus on single objects and previous states.
Produces SHRLEVEL REFERENCE image copies without the associated overhead and data locking.
Allows you to produce more frequent SHRLEVEL REFERENCE image copies, thereby potentially reducing recovery time.

Database Application Management

The following tools are optional and can help with performing DB2 tasks that may require decisions made upon the current state of the data. There is also a tool to assist with executing SQL statements via a browser.

DB2 Bind Manager for OS/390

The DB2 Bind Manager helps reduce the time and cost of connecting new application programs to a database. It reduces costs by avoiding unnecessary binding steps between application programs and the database. It allows application developers to safely bypass the DB2 bind process for source code changes that do not alter the existing SQL structure.

This tool analyzes each precompile to determine whether the SQL structure has changed. If it has not, then a bind is not needed and will not be executed. This can result in major cost savings in change management. Since DB2 Bind Manager detects only production application changes requiring a bind, it frees database administrators from doing unnecessary binds and allows them to concentrate only on changes that affect the SQL structure.

DB2 Bind Manager offers optional checking functions that further simplify the bind process via the Path Checker function, which gives developers and DBAs the tool they need to avoid performance problems created by unexpected access path changes as programs are migrated or rebound. Another function, database request module (DBRM) Checker, enables consistency checking for an existing DBRMLIB and a DB2 subsystem. DBRM Checker will identify DBRMs by plan that have consistency tokens inconsistent with those in the DB2 catalog tables. DBRM Checker determines which of the packages needs binding.

DB2 Web Query Tool for OS/390

The DB2 Web Query Tool for OS/390 lets end users access, create, share, and execute SQL queries through their browsers against multiple IBM DB2 databases. They can save query results in multiple formats, including XML, and integrate query results with popular PC applications such as spreadsheets.

This tool allows for maximum DB2 availability for e-business by providing global data access to any user without new software or plug-ins on client machines. It has tightly controlled data access with support for existing DB2 security and can be made available to multiple DB2 database servers, safely and simultaneously , regardless of size, hardware, operating system, or location.

It sets a new standard for business responsiveness. Now, anyone in your organization can take robust data access for granted, virtually anywhere and at any time. This responsiveness means more opportunity and more velocity for every business decision. With the trusted architecture of DB2 behind it, DB2 Web Query Tool enables pervasive connectivity over the Internet to every desktop, for the novice or the expert. Query operations available include the ability to query any DB2 database server made available by the administrator, create ad hoc queries, store queries for access by others, browse stored queries by name or description, execute any DB2-specific SQL, view query results in a browser or, download results as .TXT or .CSV files for use in spreadsheets or other tools, export results as XML, and import table data from XML files.

DB2 Path Checker

IBM DB2 Path Checker identifies potential effects of performing a bind on one or many programs. DB2 Path Checker initiates an EXPLAIN into the plan table of the new DBRM and provides you with a report on path changes. DB2 Path Checker

determines whether a bind of a DBRM will result in a changed access path.
displays available statement table cost estimates.
provides easily understood EXPLAIN statements, selected DB2 catalog statistics on path change, and wildcard processing of DBRM library entries.
allows statement level EXPLAIN to be passed to DB2 SQL Performance Analyzer on path change.


Team-Fly

Top