Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted August 19, 2013

The Database Administrator's Back-to-School Checklist

By Lockwood Lyon

As summer (in the northern hemisphere) comes to an end and summer vacations wrap up, it's time to prepare for the upcoming end-of-year rush. The months of November and December are characterized by a significant increase in consumer transactions including holiday-related purchases of food and gifts, travel, bank transactions, and winter clothing.

Many retail organizations call this period the Peak Season, and for good reasons: not only are transaction rates higher during this time of year, but a significant amount of a company's profit (sometimes as much as 40%) is realized.

To meet the upcoming demands on IT systems database administrators (DBAs) need to prepare the database and its supporting infrastructure for increased resource demands. Being proactive now can pay big dividends by maintaining service level agreements (SLAs), avoiding outages and resource shortages, and ensuring a positive overall customer experience.

Where to Begin

The DBA is responsible for recoverability, data availability, data access performance, and security. Within each of these areas there are many possible places to begin. Below, I provide the three most important areas for the DBA to address immediately. The current quiet period between summer vacations and peak season provides time for contingency planning in order to be ready for the expected upcoming resource and capacity constraints.

Disaster Recovery

Most IT enterprises already have disaster recovery plans, usually specific plans for each resource or application that may need to be recovered. Each of these separate plans contains entries for the following subjects:

  • The steps required to recover the application
  • The recovery time objective (RTO); that is, the amount of time required to execute the recovery steps
  • The recovery point objective (RPO), detailing the point in time prior to the disaster to which data will be recovered;
  • The disaster recovery classification of the application; usually a letter (A, B, etc.) indicating the priority or criticality of recovering that resource.

Most database management systems (DBMSs) have a DR classification of A, indicating that they are a critical component of the IT infrastructure. This usually means that they must be recovered as soon as possible after a disaster, perhaps in less than an hour. Further, this recovery may include not only the DBMS software and related hardware but also all data in databases.

What should the DBA do to prepare for peak season? After all, DR plans have already been made, documented, and perhaps even tested.

DBAs should review the DR plan for all DBMSs under their control and re-familiarize themselves with their contents. Items that should get particular attention include the following.

Review recovery procedures. Peak season is a terrible time for a disaster to strike. Application and system outages have a greater effect on the company's bottom line during periods of high transaction rates. DBAs should ensure they are familiar with their DR plan and how it interfaces with other plans so that they are ready when the time comes to recover without wasted time and effort.

Verify actual recovery times. Many DR plans include a recovery time objective, indicating the maximum time allowed to complete recovery. For a database, this may mean recovering data from a backup file and applying log records from the time of the backup to the recovery point. During peak season data and transaction volumes are higher; consequently, recovery times may be extended. To mitigate against this risk, the DBA should consider implementing more frequent backups for critical databases. Since during recovery of a table it is usually necessary to take time to rebuild indexes, DBAs should consider DB2's ability to backup and restore indexes.

Database Access Performance

Another area for analysis is that of database access. It is typical during peak season to have high transaction rates and high volumes of data to store. To mitigate against slowdowns in data access the DBA should plan for the following.

Extend table internal free space. Most DB2 tables are designed to have internal areas of free, or unused space. This allows addition or insertion of more data while maintaining clustering sequence. Consider a product table. Data may be stored in the table in ascending product number sequence. If a new product is added to the table, the DBMS will attempt to store the new product near the location of rows containing similar product numbers. If there is no free space to store the new product, DB2 will place that data somewhere else.  This has the tendency to slow down access to multiple products in the same query, as DB2 will not find products with similar numbers located near each other.

To mitigate this risk, the DBA can re-define such critical tables to have internal freespace and follow this with a table reorganization. This will spread the data rows out across the table dataset with evenly distributed empty space to accommodate new rows. In this case, the DBA is balancing disk resources (by making the table larger) and performance, allowing quicker insert of new data and permitting faster access

Be wary of maximum table and index size. Some tables and indexes may be defined with a maximum size. This may be due to the specific parameters used during object creation, or due to internal DB2 or operating system limits on dataset sizes.  In any case, the DBA should review current object sizes and potential maximum sizes in an effort to detect whether any object is in danger of reaching the limit. Object parameters such as PIECESIZE and DSSIZE should be reviewed, along with total disk space used.

Should the DBA determine that a table or index object may reach maximum size during peak season, several fixes are possible. If the maximum size is dependent upon an object's attributes (such as DSSIZE), the DBA may be able to increase the size of the parameter and, if required, reorganize the object. If a segmented table is reaching its maximum size, consider converting to a partitioned tablespace type, which allows for much greater maximum sizes. If table rows are not defined to be compressed, the DBA can consider turning on data compression and reorganizing the table.

Review plans for Big Data. Many IT organizations have implemented pilot projects of applications making use of a Big Data solution.  This usually takes the form of one or more very large databases, and sometimes includes special-purpose hardware for data storage or analytical processing. While these systems are, by their nature, designed to handle large volumes of data, the upcoming peak season may cause problems.  Review your Big Data implementation, paying particular attention to the possible effects of an even larger increase in data volume than was planned.

Review currency of data distribution statistics. The RunStats utility gathers information about table and index data distribution so that the DB2 optimizer can choose the lowest cost access paths for SQL statements. With data volumes increasing, it becomes even more important to keep these stats up-to-date.  Review critical tables and indexes, and consider additional executions of the RunStats utility.

Review data access paths. With the preparations mentioned above (more free space, object reorgs, more frequent RunStats, etc.) it is possible that some SQL data access paths will change.  Indeed, as data volumes increase and table and index sizes swell, some SQL statements may suddenly perform poorly.

The first line of defense against access path changes is the Explain utility. You should have a process in place that regularly executes Explain against critical SQL statements. The results of this utility show how tables and indexes are accessed, what indexes are used (or not), sorts  performed, join methods used, and so forth.  Your process should include a method for regularly gathering this information and storing it historically, perhaps in a DB2 table.

Now, as data volumes increase, re-execute the Explains and compare the results. SQL statements whose access paths change are noted as exceptions for further analysis.

Ensure that disk storage is available. Maybe this is obvious; however, the DBA should review current disk usage for tables and indexes, factor in any increases due to adding free space or increased data volumes, and report to the storage team on the requirements for peak season.

Summary

The time immediately after the summer vacation months should be used pro-actively to address issues that may arise during the upcoming peak season. A bit of time and effort spent while not under the pressure of high transaction rates and busy applications will pay great dividends in the form of smoothly running systems and the lower probability of possible issues.

See all articles by Lockwood Lyon



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.