Each is a complete, full-function RDBMS. An organization can install and use the DBMS as delivered, but will quickly recognize that the functionality needed to adequately support large-scale
database development is not provided adequately by the RDBMS alone.
The administration and maintenance of database applications is time-consuming if you use only the standard features of the DBMS. Fortunately, many tools that enhance the functionality of relational
database management systems 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 organizations implement at least one or more add-on tools for their DBMS.
But, many varieties of tools are available that fulfill market niches not adequately supported the major RDBMS vendors.
In the first of two parts, this article addresses database tools that focus on the developmental aspects of database administration. The second part of the article will address database tools that
focus on analysis and reporting.
The following is a rundown of the categories of products.
Covered in Article Part 1 – Development Tools
Client/Server Tools
Compression Tools
Database Modeling and Design Tools
Database Object Altering Tools
Database Object Migration Tools
Data Warehousing Tools
Debugging Tools
Programming and Development Tools
Propagation Tools
Operational Support Tools
Referential Integrity Tools
Replication Tools
Security Tools
Table Editors
Testing Tools
Synopsis
Covered in Article Part 2 – Analysis, Reporting, and Utility Tools
Auditing Tools
Catalog Query and Analysis Tools
Database Analysis Tools
Miscellaneous Tools
Online Standards Manuals
Performance Monitors
Query Tools
Repositories
Space Management Tools
SQL Analysis Tools
Utility Enhancement Tools
Utility Management Tools
Database Object Altering Tools – back to top of part 1
The structure of relational databases can modified using the ALTER 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 no RDBMS supports this. For example, most RDBMS products enable you to add columns to an existing table but only at the end; further you
can not 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 relational structures is the cascading drop effect. If a change to a database object mandates it being dropped and re-created, all dependent objects
are dropped when the database object is dropped. This includes tables, all indexes on the tables, all primary and foreign keys, any related synonyms and views, any triggers, 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 database object alteration cannot be performed using the generic ALTER statement. Depending on the RDBMS, you may not be able to
- change the name of a database, table, alias, view, column, tablespace, dbspace, trigger, stored procedure, user-defined function, relationship, or index.
- change database partitioning.
- move a table from one database, dbspace, or tablespace to another.
- rearrange column ordering.
- change a column’s data type and length.
- remove columns from a table.
- change the primary key without dropping and adding the primary key.
- add columns to a view or remove columns from a view.
- change the SELECT statement on which a view is based.
- change the indexing columns.
- change the uniqueness specification of an index.
- change the clustering of an index or table.
- change the index order (ascending or descending).
This list should provide all the justification needed to obtain an alter tool–and this is not an exhaustive list. An alter tool provides an integrated environment for altering database objects.
The burden of ensuring that a change to a database object does not cause other implicit changes is moved from the DBA to the tool.
At a minimum, an alter tool should:
- Maintain tables easily without manually coding SQL.
- Retain or reapply all dependent objects and authorization affected by the requested alter if a drop is required.
- Navigate hierarchically from object to object, making alterations as you go.
- Provide GUI-based modification showing before and after definitions of the objects before the changes are applied.
- Batch requested changes into a work list that can be executing in the foreground or the background.
- Analyze changes to ensure that the requested alterations do not violate any DDL rules. For example, if a series of changes is requested and one change causes a subsequent change to be invalid
(an object is dropped, for instance), this should be flagged before execution. - Controlled environment in which alters are executed.
- Capability to monitor changes as they are applied.
Compression Tools – back to top of part 1
A standard tool for reducing storage 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, overall storage 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. Additionally, some tools are available that compress database logs enabling more log information to be retained on disc before it is off-loaded to
tape, some other form of cheaper storage media, or is discarded.
Third-party compression routines are usually specified using APIs to the database or at the operating system level. Sometimes tables must be dropped and re-created to apply an the compression
routine.
In general, a compression algorithm increases CPU costs while providing benefits in the areas of decreased disk 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 storage costs but increases CPU without decreasing I/O, the tradeoff is probably not beneficial.
A compression tool can decrease storage 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.
Some DBMS products support compression “out-of-the-box” without requiring a third party product. The major advantage of third party compression tools is that most of the vendors provide multiple
compression algorithms for different types of data.
Data Warehousing Tools – back to top of part 1
Data Warehousing tools ease the burden of data movement and transformation because the tools understand 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:
- How many DBMS products need to be supported?
- To what extent is the data replicated across the DBMS products?
- Does the data have to be synchronized across DBMS products?
- Is the data static or dynamic?
- If it is dynamic, is it updated online; in batch; both?
The answers to these questions will help to determine the type of data warehousing tool necessary. Two basic types of data conversion tools are popular in the market today:
Replication Tools – back to top of part 1
These tools extract data from external application systems and other databases for population into DB2 tables. This type of tool can extract data from VSAM, IMS, Oracle, flat files, and/or other
structures and insert the data into DB2.
Propagation Tools – back to top of part 1
Inserts data from external applications and other database products into DB2 tables. A propagation tool is similar in function to a replication tool. Propagation tools, however, are active. They
constantly capture updates made in the external system either for immediate application to DB2 tables or for subsequent batch updating. This differs from the extract tool, which captures entire
data structures, not data modifications.
At times, multiple database management systems co-exist in data processing shops. This is increasingly true as shops embark on client/server initiatives. 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 can be useful for converting data from DBMS to DBMS even
outside of warehousing needs.
Client/Server Tools – back to top of part 1
Client/server processing has been very successful in recent years because it provides a flexible, distributed computing environment. Databases are large participants in the client/server plans for
many shops. This is true because many modern applications require efficient access to large amounts of data.
This being the case, there are many tools on the market that can ease the burden of implementing and administering databases in a client/server environment. Middleware products and database
gateways that sit between the client workstation and the database server. These products can provide access to mainframe databases as well as access to other DBMS products running on UNIX or
Windows NT servers. Additionally, many third party ODBC drivers are being made available to ease access to heterogeneous data.
Another valid type of client/server tool is a 4GL programming environment that provides seamless access to heterogeneous databases. These type of products typically split the application workload
between the workstation and the server aiding the programmer to rapidly develop database-enabled client/server applications.
Database Modeling and Design Tools – back to top of part 1
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.
Database modeling and design tools do not have to be unique to a specific database, but many are. Tools developed specifically to support your DBMS of choice, however, add a dimension to the
application development effort. They can significantly reduce the development timeframe by automating repetitive tasks and validating the models. However, if you use multiple DBMSs in your
organization, you will be better off choosing a single tool that can support each of them than multiple tools geared for a single, specific database.
When choosing a modeling and design tool, look for one that can
- Provide standard features of logical data modeling (such as entity-relationship diagramming and normalization).
- Create a physical data model geared to each of your target DBMS platforms. This model should support all features of each DBMS. For example, for DB2 on the mainframe it should be able to depict
all DB2 objects, referential integrity, VCAT and STOGROUP-defined tablespaces, and capacity planning. - Provide an expert system to verify the accuracy of the physical data model and to suggest alternative solutions.
- Cross-reference the logical model to the physical model, capturing text that supports physical design decisions such as denormalization and the choice of tablespace type.
- Automatically generate standard DDL to fully implement the database defined in the physical data model. The DDL created by the tool from the physical model should support all of the options for
each DBMS supported (instead of a generic DDL). - Interface with application development tools and repository products available to the organization.
Table Editors – back to top of part 1
The only method of updating RDBMS data is with the SQL data manipulation language statements DELETE, INSERT, and UPDATE (or with a database load). 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 table editing tool can reduce the time needed to make simple data alterations by providing full-screen edit capability for database tables. The user specifies the table to edit and is placed in
an edit session. 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.
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 programs 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.
Database Object Migration Tools – back to top of part 1
Many RDBMS products do not provide a feature to migrate database 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
database authorizations.
Database object migration tools facilitate the quick migration of database objects from one environment to another (e.g. from test to production). 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 is typically driven by panels or GUI screens 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, user id’s, and other objects are renamed according to the standards of the receiving instance, subsystem, or server. When the parameters of the
migration have been specified completely, the tool creates a job to implement the requested database objects in the requested environment.
A migration tool can reduce the time required by database administrators to move databases from environment to environment. Quicker turnaround results in a more rapid response to user needs,
thereby increasing the efficiency of your business.
Operational Support Tools – back to top of part 1
Many avenues encompass operational support in a database 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 database standards and procedures. These tools are commonly populated with model standards and procedures that can be modified or
extended. Tools of this nature are ideal for a shop with little experience in a specific DBMS that wants to launch a new project using that DBMS. As the shop grows, the standards and procedures can
grow with it.
Some DBMS products provide no inherent capability for storing checkpoint information. Tools that store checkpoint information which can be used by application programs during subsequent restarts
are useful for large batch database applications that issue many commits.
One final type of operational support tool assists in change management. 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 bundling database and program change together into cohesive packets of change.
Performance Enhancing Tools – back to top of part 1
Performance is an important facet of database administration. Many shops dedicate several analysts to tweaking and tuning the database, the SQL, the server, and its environment to elicit every
performance enhancement possible. Several tools on the market enhance the performance of databases by adding functionality directly to the DBMS. These tools can interact with the database kernel
and provide enhanced performance. Typically, these products take advantage of known DBMS shortcomings.
For example, products exists which:
- enable database parameters to be changed without recycling the DBMS instance, subsystem, or server
- enhance the performance of reading a database page or block
- enhance data caching and/pr bufferpool processing
Care must be taken when evaluating this type of performance tool. New releases of the DBMS may negate the need for these tools because functionality can be added or known shortcomings can be
corrected. However, this does not mean that you should not consider performance tools. They can pay for themselves after only a short period of time. Discarding the tool when the
DBMS supports its functionality is not a problem if the tool has already paid for itself in terms of better performance.
One final caution: Because these tools can interact very closely with the database kernel, be careful when migrating to a new DBMS release or a new release of the tool. Extra testing should be
performed with these tools because of their intrusive nature.
Programming and Development Tools – back to top of part 1
Many tools enhance database application development efforts. These programming and development tools can
- Enable the testing of SQL statements in a program editor as the programmer codes the SQL.
- Perform predictive performance estimation for programs and SQL statements.
- Explain SQL statements in an edit session.
- Generate complete code from in-depth specifications. Some tools even generate SQL. When code generators are used, great care should be taken to ensure that the generated code is efficient
before promoting it to production status. - Use 4GLs (fourth-generation languages) that interface to the database and extend the capabilities of SQL to include procedural functions (such as looping or row-at-a-time processing).
Due to the variable nature of the different types of database programming tools, they should be evaluated case by case.
Referential Integrity Tools – back to top of part 1
Referential integrity (RI) is used by relational databases to ensure the validity of primary key to foreign key relationships. However, RI can been difficult to administer and implement. RI tools
eliminate the difficulty by:
- analyzing data for both system and user managed referential integrity constraint violations
- executing faster than the DBMS-provided integrity checking facility or utility
- enabling additional types of RI to be supported; for example, analyzing primary keys for which no foreign keys exists and deleting the primary key row (pendant delete processing)
Security Tools – back to top of part 1
Database security is usually provided internal to the database using GRANT and REVOKE data control language SQL statements. Using this mechanism, authorization is granted explicitly and implicitly
to database users. Some DBMS products provide authorization exits to enable communication with external security management packages. This eases the administrative burden of database security by
enabling the corporate data security function to administer groups of users.
The implementation of security in most relational databases 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 database 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.
Database security tools provide other functions. Consider the administrative overhead when database 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 database authority from one database object to another or from one database 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 table is
created for an existing 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 database security product. Rather than augment database security, however, this type of product replaces database security with an external package.
The primary benefit is the consolidation of security. Security administration for regular data security and database security can be consolidated in a single unit. A second benefit is that the
cascading revoke effect can be eliminated because most external data security packages do not cascade security revocations.
There are weaknesses of this type of tool, too. 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 database security. For example, system level authorizations are frequently omitted. Another weakness is that if the external security package fails, the data is unprotected.
Testing Tools – back to top of part 1
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 database tables.
Debugging Tools – back to top of part 1
SQL debugging tools are useful for identifying and helping to fix syntax and logic errors in difficult SQL statements. These tools are most useful for procedural SQL dialects such as Oracle PL/SQL
and Sybase Transact-SQL.
Synopsis – back to top of part 1
Third party add-on tools can significantly improve the efficiency of developing applications that access relational data. 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, most RDBMS products leave quite a bit to be desired in the administration, data
access, performance monitoring, and application development areas. Third party tools can help alleviate these pains.
