Budgets are shrinking and management’s eyes are clearly on the bottom line. Under these conditions, what would a DB2 database administrator (DBA) want on their holiday wish list?
Early Inclusion in Application Design
We start our list at the beginning; application design.
Far too often projects begin without including the database administrator. This is understandable, as initial discussions tend to concentrate on business rules, screen layouts and process definitions. Regrettably, some of the decisions made at this juncture will affect the final database design. Further, if there are any capacity or performance requirements, database design alternatives may cost out differently.
One common example is an application that accesses a highly volatile database. Volatility can occur in situations such as:
- An influx of new data, such as a data warehouse or educational institution course registration;
- Constant and frequent data updates, such as a financial system or parcel delivery application;
- Aged data purge criteria; for instance, a telephone-based customer information system where customer data entered or spoken is retained for the duration of the call.
In these cases, a typical database design begins with partitioning and index choices that will permit a high volume of data access and updates. Such a design must also avoid having multiple transactions simultaneously attempting to change data in the same location, sometimes known as a ‘hot spot’.
When the application development team includes a representative from database administration, the DBA can anticipate what logical and physical database design may support the data volatility requirements. After analyzing and reviewing alternatives, the DBA can then present the applicable designs and their potential costs, advantages, and disadvantages.
Here is an example scenario.
An application team for a financial enterprise is designing a data warehouse. The warehouse will contain time-based data loaded daily from multiple production databases, and must retain data for seven years due to regulatory requirements.
In this case, the DBA will most likely recommend a database scheme that is partitioned by date. This allows the daily data load to affect only the current day’s partition, while other partitions remain available for access by applications. The DBA will define seven years’ worth of partitions, with a design that allows for re-use of old partitions after seven years has elapsed.
Give Standards a Higher Priority
Standards in the DBA’s world usually include:
- Naming conventions for database objects (tables, indexes, columns, aliases, etc.)
- Regular image copy backups
- Regular execution of database reorganizations (reorgs) and runstats
- Physical object parameters (free space, locking criteria, etc.)
- SQL guidelines for efficient coding such as the use of cursors and the Explain process
- Application standards including commit frequency and locking
Naming standards for objects evolved from two desires during logical database design: to force consistency when names were abbreviated, and to allow data element domains and attributes to be embedded as part of an object name. Consider a column name like A_AMGR_BD_ BAL. The abbreviated words stand for account manager bond balance, while the A prefix indicates a currency amount.
A useful byproduct of standard names is the ability to group similar or related objects using wildcarding. Consider a financial application implemented in DB2 where the physical tablespaces are named using the convention SFINnnnn (where S indicates a tablespace, FIN is a three-letter application ID and nnnn is a sequential number). The DB2 DBA can implement utility processes such as backups, reorgs, and runstats by specifying SFIN*. Here, the asterisk is interpreted by the utility to mean “all objects whose names begin with SFIN”.
If designers add new tables and tablespaces to this application all utilities are pre-defined to work with the new objects. No changes are necessary to DBA processes.
One last note. In some instances management insists that it has neither staff, budget nor time to implement or enforce such standards. The DBA must then explain that such standards are an investment: they will pay back many times over in the ability to implement processes that need little or no maintenance, freeing the DBA from excessive maintenance work.
Expanding on a point made previously, the DBA needs to automate standard processes in order to get out of the “reactive rut”. This happens when the majority of their time is spent on doing the same process over and over again. Some examples include:
- Creating standard recovery processes such as regular image copies
- Creating standard performance processes such as reorgs and runstats
- Executing EXPLAINs of SQL queries and interpreting the results
- Creating DB2 objects such as tablespaces, tables, and indexes based on designer specifications
- Running regular performance statistics and capacity measurement reports
- Executing audit reports that capture data access errors or security violations
Each of these standard processes should be automated, including setup, execution and results interpretation. Many third-party vendor tools exist for each of those mentioned above. If necessary, the DBA can code simple JCL, SQL or even applications to accomplish what is needed. The goal is to free the DBA from repetitive tasks and allow them to concentrate on work having a higher value.
For example, assume that you are responsible for executing the EXPLAIN process for SQL queries. These queries come from a variety of sources, including real-time production performance reports, new application development, and e-mails from developers or users. Typically, the DBA will capture the SQL in text form, execute one of several EXPLAIN tools available, interpret the results and develop recommendations such as re-coding, new indexes, etc.
The DBA should be able to automate this process. Give requestors access to an EXPLAIN tool that they can execute themselves. Point them to one of the many web-based SQL tutorials. For currently executing production SQL devise a regular report that captures the top ten worst performing SQL statements. DB2 includes tools for such performance reporting.
The purpose of such automation is to remove repetitive, standard work from the DBA’s schedule and either delegate it or outsource it. Along with freeing the DBA to do other work, such automation allows other parts of the organization to obtain skills and experience not otherwise easily achieved.
Autonomics is the ability of a system to manage itself. DB2 is a database management system (DBMS) that incorporates several autonomic processes. In general, these processes are able to detect certain conditions or situations and then react or change the DBMS’s behavior. Some examples of autonomic processes include:
- Real-time statistics that are automatically gathered and used to execute utilities such as reorgs or image copies based on thresholds
- Memory pool management where DB2 will accelerate the writing of updated data to disk files when memory starts to fill
- SQL query re-write when DB2 detects that an equivalent SQL statement will execute faster
As the DBMS becomes more complex and versatile the DBA needs more information in order to assist in decision making, especially if performance will be affected. Autonomic processes are all about trading constrained or poorly-performing resources for others that are more lightly used.
Consider the first item above, that of real-time statistics. In this case, the intention is to gather data distribution information for later use. This takes CPU time. The tradeoff is that the gathered statistics can be used to make intelligent and timely decisions about when or if to execute a utility. If a database hasn’t been updated today, then perhaps you can defer tonight’s scheduled database reorg. Similarly, if many updates have been applied to the database recently, perhaps tonight is the best time to reorg the database.
However, the DBA needs to know when and if these processes are taking place. For example, what if the DBMS decides that a database reorg is needed for tonight, but the application needs to run multiple month-end processes? The DBA needs to be informed and involved.
The issues addressed here were some of my favorites based on experience. Along with the holiday season it is now budget time for most organizations. Take this wish list, and any others you may have, and approach management. While you can’t always get what you want, it’s possible that management will see the value in giving you what you need.
# # #
Logical Database Design using ER Modeling
DB2 Capturing EXPLAIN Information
DB2 Interpreting Data Access using EXPLAIN