 Mullins Consulting, Inc.Database Performance Management | |||||
| 1996 |
|||||
 |
Tools for a Comprehensive DB2 Environment By Craig S. Mullins DB2, as offered by IBM, is a complete, full-function RDBMS. An organization can install and use DB2 as delivered, but will quickly recognize that the functionality needed to adequately support large-scale DB2 development is not provided adequately by DB2 alone. The administration and maintenance of DB2 applications is time-consuming if you use the standard features of DB2. Fortunately, many tools that enhance the functionality of DB2 are available from third party vendors. These tools ease the administrative burden and reduce the possibilities of database error. The true need for these tools becomes apparent simply by reviewing the sheer number of products that are available. Most DB2 shops implement at least one or more add-on tools for DB2. Of these, IBM's QMF and DB2-PM are among the most popular. Many more tools from other vendors fill market niches not adequately supported by IBM. Following is a rundown of the categories of products. DB2 Object Altering Tools DB2 provides the capability to modify the structure of existing objects using the ALTER DDL statement. The ALTER statement, however, is a functionally crippled statement. You should be able to alter all of the parameters that can be specified for an object when it is created, but DB2 does not support this. For example, you can add columns to an existing table (only at the end), but you never can remove columns from a table. The table must be dropped, then re-created without the columns targeted for removal. Another problem that DBAs encounter in modifying DB2 objects is the cascading drop effect. If a change to a table space mandates its being dropped and re-created (for example, changing the limit keys of a partitioned table space), all dependent objects are dropped when the table space is dropped. This includes all tables in the table space, all indexes on the tables, all primary and foreign keys, any related synonyms and views, and all authorization. Ensuring that DDL is issued after the modification to reverse the effects of cascading drops can be a tedious, complex, and error-prone procedure. Additionally, many types of DB2 object alteration cannot be performed using the generic DB2 ALTER statement. For example, you
At a minimum, an alter tool should:
An audit is the examination of a practice to determine its correctness. DB2 auditing software therefore should help in monitoring the data control, data definition, and data integrity in the DB2 environment. Several mechanisms provided by DB2 enable the creation of an audit trail, but this trail can be difficult to follow. The primary vehicle provided by DB2 for auditing is the audit trace. This feature enables DB2 to trace and record auditable activity initiated by specific users. When the DB2 audit trace is activated, the following type of information can be captured to the trace destination:
This information is written to the output trace destination specified for the audit trace. DB2 trace records can be written to GTF, SMF, or an OP buffer. After the information has been written to the specified destination, the problem of how to read this information still exists. If you have DB2-PM, you can run the appropriate audit reports, but even these can be insufficient for true auditing. An audit tool should provide five important features that DB2's audit tracing capability does not. DB2 auditing requires a trace to be activated, and this can quickly become expensive if many tables must be audited. The first feature an auditing tool should provide is the capability to read the DB2 logs, which are always produced, and report on update activity as needed. This reduces overhead because it uses the regular processing features of DB2 rather than an additional tracing feature, which increases overhead. The DB2 audit trace records a trace record only for the first statement in a unit of work. The second feature of the auditing tool is reporting all data modification from the DB2 logs. The DB2 audit trace facility does not record the specifics of the data modification. The third feature of an auditing tool is reporting who (by authorization ID) makes each change, and also showing a before and after image of the changed data. The fourth feature the auditing tool should provide is the capability to report on the DB2 audit trace data if so desired. Finally, the auditing tool should provide both standard reports and the capability to create site-specific reports (either from the log or from the DB2 audit trace data). If your shop has strict auditing requirements, an auditing tool is almost mandatory because of DB2's weak inherent auditing capabilities. DB2 Catalog Query and Analysis Tools The DB2 Catalog contains a wealth of information essential to the operation of DB2. Information about all DB2 objects, authority, and recovery is stored and maintained in the DB2 Catalog. This system catalog is composed of DB2 tables, and can be queried using SQL. The data returned by these queries provides a base of information for many DB2 monitoring and administrative tasks. But coding SQL can be a time-consuming process. Often, you must combine information from multiple DB2 Catalog tables to provide the user with facts relevant for a particular task. Add-on tools can ease the burden of developing DB2 Catalog queries. The basic feature common to all DB2 Catalog tools is the capability to request DB2 Catalog information using a screen-driven interface without coding SQL statements. Analysts can obtain rapid access to specific facts stored in the DB2 Catalog without the burden of coding (sometimes quite complex) SQL. DB2 Catalog tools that provide only this level of capability are rudimentary tools at best. Most DB2 Catalog tools provide much more capability. Instead of merely enabling data access, many DB2 Catalog tools can do one or more of the following:
Compression Tools A standard tool for reducing DASD costs is the compression utility. This type of tool operates by applying an algorithm to the data in a table such that the data is encoded in a more compact area. By reducing the amount of area needed to store data, DASD costs are decreased. Compression tools must compress the data when it is added to the table and subsequently modified, then expand the data when it is later retrieved. Third-party compression routines are usually specified for DB2 tables using the EDITPROC clause of the CREATE TABLE statement. The load module name for the compression routine is supplied as the parameter to the EDITPROC clause. A table must be dropped and re-created to apply an EDITPROC. In general, a compression algorithm increases CPU costs while providing benefits in the areas of decreased DASD utilization and sometimes decreased I/O costs. This tradeoff is not beneficial for all tables. For example, if a compression routine saves 30 percent on DASD costs but increases CPU without decreasing I/O, the tradeoff is probably not beneficial. A compression tool can decrease DASD by reducing the size of the rows to be stored. CPU use usually increases because additional processing is required to compress and expand the row. I/O costs, however, could decrease. Prior to DB2 V2.3, compression was unavailable using standard DB2 features unless the user coded an algorithm. As of V2.3, DB2 provides a basic compression routine called DSN8HUFF. However, most third party compression tools provide more efficient compression algorithms and advanced analysis to determine the costs and benefits of compression for a specific table. This changed dramatically with DB2 V3. In fact, the internal compression capabilities of DB2 V3 will probably cause most third party compression tools to become obsolete. The major advantage of third party compression tools is that most of the vendors provide multiple compression algorithms for different types of data. DB2 provides only Ziv-Lempel compression. Data Warehousing Tools At times, multiple database management systems co-exist in data processing shops. This is increasingly true as shops embark on client/server initiatives. Additionally, the same data may need to be stored in each of the DBMS products. In a multiple DBMS environment, the movement of data from DBMS to DBMS is a tedious task. Data Warehousing tools ease the burden because the tool understands the data format and environment of each DBMS it works with. The warehousing tool(s) that a shop chooses will depend upon the following factors:
DB2-Related Client/Server Tools Client/server processing has been very successful in recent years because it provides a flexible, distributed computing environment and decreases reliance on the mainframe. However, DB2 is a large participant in the client/server plans for many shops. Providing efficient access to large amounts of data, DB2 MVS can function as the ultimate database server in a client/server environment. This being the case, there are many tools on the market that can ease the burden of implementing and administering DB2 in a client/server environment. Middleware products and database gateways that sit between the client workstation and the mainframe enable access to DB2 as a server. These products can provide access to DB2 MVS as well as access to other server DBMS products (DB2/2, DB2/6000, Sybase SQL Server, Oracle, etc.). Additionally, many third party ODBC drivers are being made available to ease workstation access to mainframe DB2 data. Another valid type of client/server tool is a 4GL programming environment that provides seamless access to DB2. These type of products typically split the application workload between the workstation and the server aiding the programmer to rapidly develop DB2 client/server applications. Database Analysis Tools DB2 does not provide an intelligent database analysis capability. Instead, a database administrator or performance analyst must keep a vigilant watch over DB2 objects using DB2 Catalog queries or a DB2 Catalog tool. This is not an optimal solution because it relies on human intervention for efficient database organization, opening up the possibility of human error, forgetting to monitor, and misinterpreting analyzed data. Fortunately, database analysis tools can proactively and automatically monitor your DB2 environment. This monitoring can
A repository stores information about an organization's data assets. Repositories are used to store meta-data, or data about data. They frequently are used to enhance the usefulness of DB2 application development. In choosing a repository, base your decision on the meta-data storage and retrieval needs of your entire organization, not just DB2. Typically, a repository can
Database Modeling and Design Tools Database modeling and design tools do not have to be unique to DB2 design, although many are. Application development should be based on sound data and process models. The use of a tool to ensure this is a good practice. Database modeling and design tools are often referred to as CASE tools. CASE, or computer-aided software engineering, is the process of automating the application development life cycle. A CASE tool, such as a data modeling tool, supports portions of that life cycle. Many excellent database design and modeling tools are not specifically designed for DB2, but can be used to develop DB2 applications. Tools developed specifically to support DB2 development, however, add a dimension to the application development effort. They can significantly reduce the development timeframe by automating repetitive tasks and validating the models. If your organization decides to obtain a CASE tool that specifically supports DB2, look for one that can
DB2 provides basic statistics for space utilization in the DB2 Catalog, but the in-depth statistics required for both space management and performance tuning are woefully inadequate. The queries presented in Chapter 16 can form a basis for DB2 DASD management, but critical elements are missing. Chief among the missing elements of DASD space management in DB2 is the capability to monitor the space requirements of the underlying VSAM data sets. When these data sets go into secondary extents, performance suffers. Without a DASD management tool, the only way to monitor secondary extents is to periodically examine LISTCAT output. This is a tedious exercise. Additionally, the manner in which DB2 allocates space can result in the inefficient use of DASD. Often space is allocated but DB2 does not use it. A DASD management tool is the only answer for ferreting out the amount of allocated space versus the amount of used space. DASD management tools often interface with other DB2 and DASD support tools such as standard MVS space management tools, database analysis tools, DB2 Catalog query and management tools, and DB2 utility JCL generators. DB2 Table Editors The only method of updating DB2 data is with the SQL data manipulation language statements DELETE, INSERT, and UPDATE. Because these SQL statements operate on data a set at a time, multiple rows-or even all of the rows-can be affected by a single SQL statement. Coding SQL statements for every data modification required during the application development and testing phase can be time-consuming. A DB2 table editing tool reduces the time needed to make simple data alterations by providing full-screen edit capability for DB2 tables. The user specifies the table to edit and is placed in an edit session that resembles the ISPF editor. The data is presented to the user as a series of rows, with the columns separated by spaces. A header line indicates the column names. The data can be scrolled up and down as well as left and right. To change data, the user simply types over the current data. This type of tool is ideal for supporting the application development process. A programmer can make quick changes without coding SQL. Also, if properly implemented, a table editor can reduce the number of erroneous data modifications made by beginning SQL users. Following are a few words of warning pertaining to the use of table editors. Remember that the table editor is issuing SQL in the background to implement the requested changes. This can cause a lag between the time the user updates the data and the time the data is committed. Table editor updates usually are committed only when the user requests that the data be saved or when the user backs out of the edit session without canceling. Remember too that table editors can consume a vast amount of resources. Ensure that the tool can limit the number of rows to be read into the editing session. For example, can the tool set a filter such that only the rows meeting certain search criteria are read? Can a limit be set on the number of rows to be read into any one edit session? Without this capability, large table space scans can result. Remember that a DB2 table editor should be used only in the testing environment. End users or programmers might request that a table editor be made available for production data modification. This should be avoided at all costs. The data in production tables is critical to the success of your organization, and should be treated with great care. Production data modification should be accomplished only with thoroughly tested SQL or production plans. When a table editor is used, all columns are available for update. Thus, if a table editor is used to change production data, a simple mis-keying can cause unwanted updates. Native SQL should be used if you must ensure that only certain columns are updated. One final note: Tested SQL statements and application plans are characterized by their planned nature. The modification requests were well thought out and tested. This is not true for changes implemented through a table editor. DB2 Object Migration Tools DB2 does not provide a feature to migrate DB2 objects from one subsystem to another. This can be accomplished only by manually storing the CREATE DDL statements (and all subsequent ALTER statements) for future application in another system. Manual processes such as this are error-prone. Also, this process does not take into account the migration of table data and DB2 security. DB2 object migration tools facilitate the quick migration of DB2 objects from one DB2 subsystem to another. They are similar to a table altering tool but have a minimal altering capability (some interface directly with an alter tool or are integrated into a single tool). The migration procedure usually is driven by ISPF panels that prompt the user for the objects to migrate. Migration typically can be specified at any level. For example, if you request the migration of a specific database, you could also migrate all dependent objects and security. Minimal renaming capability is provided such that database names, authorization IDs, and other objects are renamed according to the standards of the receiving subsystem. When the parameters of the migration have been specified completely, the tool creates a job stream to implement the requested DB2 objects in the requested DB2 subsystem. A migration tool reduces the time required by database administrators to move DB2 databases from environment to environment (for example, from test to production). Quicker turnaround results in a more rapid response to user needs, thereby increasing the efficiency of your business. Typically, migration tools are the second DB2 tool that an organization acquires (right after a DB2 Catalog query product). Operational Support Tools Many avenues encompass operational support in a DB2 environment, ranging from standards and procedures to tools that guarantee smoother operation. This section describes tools from several operational support categories. One type of operational support tool provides online access to DB2 standards and procedures. These tools are commonly populated with model DB2 standards and procedures that can be modified or extended. Tools of this nature are ideal for a shop with little DB2 experience that wants to launch a DB2 project. As the shop grows, the standards and procedures can grow with it. Another type of product delivers online access to DB2 manuals. With this tool, you avoid the cost of purchasing DB2 manuals for all programmers, and DB2 information and error messages are always available online. In addition, analysts and DBAs who dial in to the mainframe from home can reference DB2 manuals online rather than keep printed copies at home. Standard batch DB2 programs run under the control of the TSO terminal monitor program, IKJEFT01. Another operational support tool provides a call attach interface that enables DB2 batch programs to run as a standard MVS batch job without the TSO TMP. DB2, unlike IMS, provides no inherent capability for storing checkpoint information. Tools that store checkpoint information which can be used by the program during a subsequent restart are useful for large batch DB2 applications issuing many COMMITs. One final type of operational support tool assists in managing changes. Typically, these tools are integrated into a change control tool that manages program changes. Change control implemented for DB2 can involve version control, plan and package management, and ensuring that timestamp mismatches (SQLCODE -818) are avoided. Some tools can even control changes to DB2 objects. PC-Based DB2 Products Personal computers are everywhere nowadays. Most data processing professionals have one on their desk. Most end users do too! As such, the need to access DB2 from the PC is a viable one. However, not everyone requires to do this in a client/server environment. Sometimes, just simple access from a PC will suffice. For this reasons, a PC query tool can be used. Data requests originate from the PC workstation. The tools sends the requests to the mainframe for processing. When processing is finished, the data is returned to the PC and formatted. These types of tools typically use a graphical user interface with pull-down menus and point-and-click functionality. These features are not available on mainframe products. Another increasingly popular approach to developing DB2 applications is to create a similar environment on the PC. This can be done using a PC DBMS that works like DB2 and other similar PC products that mimic the mainframe (COBOL, IMS/TM, CICS, JCL, etc.). Quite often, tools that can be used in a straight PC environment can also be used in a client/server environment. Plan Analysis Tools The development of SQL to access DB2 tables is the responsibility of an application development team. With SQL's flexibility, the same request can be made in different ways. Because some of these ways are inefficient, the performance of an application's SQL could fluctuate wildly unless it is analyzed by an expert before implementation. The DB2 EXPLAIN command provides information about the access paths used by SQL queries by parsing SQL in application programs and placing encoded output into a DB2 PLAN_TABLE. To gauge efficiency, a DBA must decode the PLAN_TABLE data and determine if a more efficient access path is available. SQL code reviews are required to ensure that optimal SQL design techniques are used. SQL code walkthroughs typically are performed by a DBA or someone with experience in SQL coding. This walkthrough must consist of reviews of the SQL statements, the selected access paths, and the program code in which the SQL is embedded. It also includes an evaluation of the RUNSTATS information to ascertain whether production-level statistics were used at the time of the EXPLAIN. A line-by-line review of application source code and EXPLAIN output is tedious and prone to error, and can cause application backlogs. A plan analysis tool can greatly simplify this process by automating major portions of the code review process. A plan analysis tool typically can
A plan analysis tool probably will not flag the SQL statement because the predicate value is for the primary key, which causes an indexed access. It could be more efficient to code a cursor, without a predicate, to retrieve every row of the table, then fetch each row one by one. This method might use sequential prefetch or query I/O parallelism, reducing I/O and elapsed time and thereby enhancing performance. This type of design problem can be caught only by a trained analyst during a code walkthrough. Although a plan analysis tool significantly reduces the effort involved in the code review process, it cannot eliminate it. There are two features that are required for any plan analysis tool:
Performance monitoring and tuning can be one of the most time-consuming tasks for large or critical DB2 applications. DB2 performance monitoring and analysis tools support many performance-oriented requests in many ways. For example, DB2 performance tools can operate
DB2 Performance Enhancing Tools Performance is an important facet of DB2 database administration. Many shops dedicate several analysts to tweaking and tuning SQL, DB2, and its environment to elicit every performance enhancement possible. If your shop falls into this category, several tools on the market enhance the performance of DB2 by adding functionality directly to DB2. These DB2 performance tools can interact with the base code of DB2 and provide enhanced performance. Typically, these products take advantage of known DB2 shortcomings. For example, products exists which:
One final caution: Because these tools interact very closely with DB2, be careful when migrating to a new release of DB2 or a new release of the tool. Extra testing should be performed with these tools because of their intrusive nature. DB2 Programming and Development Tools Many tools enhance the DB2 application development effort. These DB2 programming and development tools can
QMF Enhancement Tools A special category of tool, supporting QMF instead of DB2, automatically creates COBOL programs from stored QMF queries. QMF provides a vehicle for the ad hoc development, storage, and execution of SQL statements. When an ad hoc query is developed, it often must be stored and periodically executed. This is possible with QMF, but QMF can execute only dynamic SQL. It does not support static SQL. A method of running critical stored queries using static SQL would be beneficial, because static SQL generally provides better performance than dynamic SQL. QMF enhancement tools convert the queries, forms, and procs stored in QMF into static SQL statements embedded in a COBOL program. The COBOL program does all the data retrieval and formatting that are performed by QMF, providing the same report as QMF would. However, the report is now created using static SQL instead of dynamic SQL, thereby boosting performance. Query Tools DB2 provides the SPUFI query tool bundled with the DBMS. Most organizations find SPUFI inadequate, however, in developing professional, formatted reports or complete applications. It can be inadequate also for inexperienced users or those who want to develop or execute ad hoc queries. QMF addresses each of these deficiencies. The capability to format reports without programming is probably the greatest asset of QMF. This feature makes QMF ideal for use as an ad hoc query tool for users. Another important feature is the capability to develop data manipulation requests without using SQL. QMF provides QBE and Prompted Query in addition to SQL. QBE, or Query By Example, is a language in itself. The user makes data manipulation requests graphically by coding keywords in the columns of a tabular representation of the table to be accessed. Prompted Query builds a query by prompting the end user for information about the data to be retrieved. The user selects a menu option and Prompted Query asks a series of questions, the answers to which are used by QMF to build DML. Both QBE and Prompted Query build SQL "behind the scenes" based on the information provided by the end user. QMF can be used also to build application systems. A QMF application accesses DB2 data in three ways:
Another benefit of QMF is that you can use inherent QMF commands such as EXPORT, DRAW, and SET to accomplish tasks that are difficult to perform with a high-level language such as COBOL. QMF, however, is not the only game in town. Other vendors provide different DB2 table query and reporting tools that can be used to enhance DB2's ad hoc query capabilities. Some of these products are similar in functionality to QMF, but provide additional capabilities. They can
Finally, fourth-generation languages (4GLs) are gaining more and more popularity for accessing DB2 data. Though not a typical type of DB2 add-on tool, these products provide more functionality than a report writing tool, but with the GUI front-end that makes them easier to use than 3GL programming languages such as COBOL and C. 4GL tools typically work in one of three ways:
Referential integrity has been available DB2 since DB2 V2.1. However, it has always been difficult to administer and implement. RI tools eliminate the difficulty by:
DB2 security is provided internal to DB2 with the GRANT and REVOKE data control language components of SQL. Using this mechanism, authorization is granted explicitly and implicitly to users of DB2. Authorization exits enable DB2 to communicate with RACF, IBM's mainframe security management package. This eases the administrative burden of DB2 security by enabling the corporate data security function to administer groups of users. DB2 authorization then is granted to the RACF groups, instead of individual userids. This decreases the volume of security requests that must be processed by DB2. DB2's implementation of security has several problems. Paramount among these deficiencies is the effect of the cascading REVOKE. If an authority is revoked from one user who previously granted authority to other users, all dependent authorizations also are revoked. This problem can be addressed by a DB2 security add-on tool. These tools typically analyze the effects of a REVOKE. These tools enable the user to revoke the authority and optionally reassign all dependent authority either by storing the appropriate GRANT statements to reapply the authorizations implicitly revoked or by revoking the authority and automatically reapplying all implicit revokes in the background. These tools provide other functions. Consider the administrative overhead when DB2 users are hired, quit, or are transferred. Security must be added or removed. A good security tool enables a user to issue a GRANT LIKE command, which can copy DB2 authority from one DB2 object to another or from one user to another. Suppose that a DBA is transferred to another department. A security tool can assign all of that DBA's authority to another user before revoking his authority. Or suppose that a new DB2 table is created for an existing DB2 application, and it requires the same users to access its data as can access the other tables in the application. This type of tool enables a user to copy all security from one table to the new table. There is one other type of DB2 security product. Rather than augment DB2 security, however, this type of product replaces DB2 security with an external package. First let's discuss its benefits. The primary benefit is the consolidation of security. If your organization uses a security package from another vendor rather than RACF for regular data security, security administration for regular data security and DB2 security can be consolidated in a single unit. A second benefit is that the cascading revoke effect can be eliminated because MVS data security packages do not cascade security revocations. The weaknesses of this type of tool, however, far outweigh the benefits. These tools do not conform to the rigorous definition of the relational model, which states that the DBMS must control security. Some do not provide all types of DB2 security. For example, INSTALL SYSADM still is required in DB2 for installation of DB2 and DB2 Catalog and Directory recovery. Another weakness is that if the external security package fails, DB2 data is unprotected. Finally, these types of external security packages do not use supported DB2 exit control points. As such, they might be unable to provide support for new releases of DB2 in a timely fashion. DB2 On-line Standards Manuals Products exists which provide "canned" standards for implementing, accessing, and administering DB2 databases. These tools are particularly useful for shops new to DB2. By purchasing an on-line standards manual these shops can quickly some up-to-speed with DB2. However, mature DB2 shops can also benefit from these types of products if the third party vendor automatically ships updates whenever IBM ships a new release of DB2. This can function as cheap training in the new DB2 release. A product containing DB2 standards should:
Testing tools enable an application developer or quality assurance analyst to issue a battery of tests against a test base and analyze the results. Testing tools typically are used for all types of applications, but some have been specifically extended to support testing against DB2 tables. Utility Enhancement Tools The DB2 CHECK, COPY, LOAD, RECOVER, REORG, and UNLOAD utilities are notorious for their inefficiency, sometimes requiring days instead of hours to operate on very large DB2 tables. However, these utilities are required to populate, administer, and organize DB2 databases. Several vendors provide support tools that replace the DB2 utilities and provide the same functionality more efficiently. For example, one vendor claims that its REORG utility executes six to ten times faster than the DB2 REORG utility. These claims must be substantiated for the applications at your organization, but enough inefficiencies are designed into the IBM DB2 utilities to make this claim believable. Before committing to an alternate utility tool, be sure that it conforms to the following requirements:
One last category of DB2 tool is the utility manager. This type of tool provides administrative support for the creation and execution of DB2 utility jobstreams. These utility generation and management tools can
Many types of DB2 tools are available. The categories in this article cover the major types of DB2 tools, but not all tools can be easily pigeonholed. For example, consider a DB2 table space calculator. It reads table DDL and information on the number of rows in the table to estimate space requirements. A space calculator often is provided with another tool such as a DASD management tool or a database design and modeling tool. Synopsis Third party add-on tools can significantly improve the efficiency of DB2 application development. When evaluating products, look for features important to your organization. Consider adopting checklists for product comparisons based upon the features discussed in this article. And remember, although DB2 is a fantastic RDBMS, it leaves quite a bit to be desired in the administration, data access, performance monitoring, and application development areas. From the IDUG Solutions Journal, Issue 2, 1996.
© 2005 Mullins Consulting, Inc. All rights reserved. | ||||