DBA Productivity Tools
28 June 2010
Do you have more and more growing databases to administer, but no additional help to get your work done? This is too often the case these days, but the good news is that there are a lot of tools to help you automate many tasks so that you can still be the hero. Many of the tools that automate administration also now integrate with each other and work with both IBM and non-IBM databases. These tools must be purchased, but they provide lots of value. Here are a sampling of functions that can be automated with a short summary of each and links where you can get more information.
Automated Scripting of Database Changes
Name: Optim Database Administrator (ODA)
Databases: DB2/LUW
Trial Download: Download Site
This tool is great if you are implementing frequent, significant database changes where your data needs to be preserved through the changes. As you know, even minor changes take a lot of scripting if a table has to be dropped and recreated to implement that change because the data must be copied somewhere and loaded back in after the change, plus you have to remember to get all of the grants and put them back along with all dependent objects including indexes, primary keys, foreign keys, views, etc., etc. Creating and testing these scrips is time consuming and it is easy to miss something like RUNSTAS that will cause the data to be less available or unavailable after you believe that the change is complete and everything is great. ODA allows you to make the changes in a graphical tool and then it generates the complete script including unloading and loading of the data and gives several options for that. It also has a feature that allows you to compare two databases and generate a script to make one look like the other. This is especially useful when you have been doing months of changes to your test database and now you are ready to go into production. Instead of having to remember how each change was made, you can just tell ODA to compare the two databases and generate a script to make production look like test while preserving all production data. In addition to the many other things it can generate a detailed change report that you can put into your change control system.
Database Modeling
Name: InfoSphere Data Architect (IDA)
Databases: DB2/all, Oracle, Informix, SQL Server, Several Others
Trial Download: Download Site
IDA (Formerly knows as Rational Data Architect (RDA)) allows you to do logical and physical data modeling where you can enter your text descriptions of all entities, attributes, relationships, tables and columns. Further you can generate HTML reports and diagrams that you can send to all of the developers or put on your internal web site. This tool has tons of features like defining data type domains and a glossary that can be used to validate naming standards and provide automatic abbreviations, but the best part of the tool is its integration with Optim Database Administrator. When you have these two tools on your workstation, you can make your changes to your data model, and have ODA generate a script that will implement those changes, preserving all of your data.
Static SQL from JAVA JDBC
Name: Optim preQuery Runtime
Databases: DB2/all platforms
Trial Download: Download Site (Must be downloaded with ODS)
Optim pureQuery Runtime (pQ) does several things, but the one that we are discussing today is its ability to convert dynamic SQL from Java applications to static SQL. It plugs into your JDBC driver and can capture the SQL that a JDBC application is issuing to DB2 and put it into a file. Once you feel comfortable that all of the SQL from the application has been captured, you can bind that file to the database. Once you have bound the SQL file, you can set a switch in the pQ configuration to tell it that when the JDBC driver sees a dynamic SQL being issued to instead make a static SQL call. In cases where you are issuing lots of dynamic SQL, this can save a lot of server resources on the database server. You can also set another switch to tell the JDBC driver to reject any SQL that is not in the capture file if you want to prevent hackers from using SQL injection. Using pQ's companion product, Optim Development Studio (ODS), you can just extract all of the SQL from the Java program if you have the source code instead of capturing it on the application server. Also using ODS, you can evaluate SQL from a pQ capture file, since pQ also collects statistics about the statements such as number of times executed, elapsed time, CPU time, etc. Using ODS you can sort the report to see the most used or most expensive SQL and explain it right in place.
Query Tuning
Name: Optim Query Tuner (OQT)
Databases: DB2/LUW
Trial Download: None. Talk to your IBM Rep for an evaluation.
OQT is a great tool to understand what an existing query is doing, without you having to know all of the things that the DB2 Explain shows you. It also helps you improve the query. There is a companion tool for DB2/zOS that also tunes sets of queries.
Encrypt DB2/LUW Data on Disk and Tape
Name: Database Encryption Expert (DEE)
Databases: DB2/LUW, Informix
Trial Download: None. Talk to your IBM Rep for an evaluation.
DEE does high performance encryption of your data on disk that does not require any database or application changes. It provides centralized encryption key management for all of your databases. An agent executes on each database server and encrypts and decrypts data as it moves into and out of the file system. DB2 does not even know that the encryption is happening. Because of this performance is gained because data in the bufferpool is not encrypted, so databases with a high bufferpool hit ratio will not see any encryption costs for the many logical reads done from the bufferpool. A best practice is to encrypt all data in the database, but you can specify that only certain file systems or certain files are to be encrypted. It is a great tool to pass PCI, HIPAA and other audits, because it can also be set up such that “root” or other users can run utilities like exports on the database server, but only user id's that are granted access to the data can actually read the data. To all others the data appears encrypted.
Fast Data Export
Name: High Performance Unload (HPU)
Databases: DB2/LUW
Trial Download: None. Talk to your IBM Rep for an evaluation.
DB2/LUW has always had the load utility that can add data to a database very fast, but has only had the export utility that unloads at the speed of SQL. We now have HPU that all will copy data out of a database at high speeds. So if you need to move data from one database to another very quickly, you may want to consider HPU. It is great in Partitioned Database environments (DPF) because it can read your proposed partition map and unload data into files that correspond to your proposed partition scheme making the loads even faster.
Find Database Relationships
Name: InfoSphere Discovery
Databases: DB2/LUW, DB2/zOS, Oracle, Sybase, SQL Server, Several Others
Trial Download: None. Talk to your IBM Rep for an evaluation.
Many organizations have very old databases that keep on running, but no one seems to know what is in them or how they relate to each other. This is an undesirable situation for a number of reasons. For one thing it is difficult to do new development on databases where if you don't understand the relationships among tables and can lead to audit compliance issues if you have sensitive data like Social Security Number in columns or imbedded in column in several databases, but don't know it. Discovery craws through databases looking at the data in them and shows the relationships among tables in the same database and among different databases. Once the relationships are discovered they can be imported into InfoSphere Data Architect so you can have nice diagrams to visually represent the relationships and if you are using DB2, you can create Informational Constraints Foreign Keys to improve query performance without otherwise changing the behavior of the database.
***
You should note that IBM is working to integrate these tools more to help you manage this data throughout its life cycle. You can read more about how we are integrating these tools. You can also find lots of other information about many of these tools with examples of how you can use them at our Integrated Tools site.