Why IBM DB2 DBAs Love Load Testing

A load test gives the database administrator quite a lot of valuable information and may make the difference between poor and acceptable application performance. Here are some proactive tips to make your IBM DB2 production implementation a success.

Many software development projects
produce software that moves into the production environment without a formal
load test. This is unfortunate! A load test gives the database administrator
quite a lot of valuable information. It may make the difference between poor
and acceptable application performance. Here are some proactive tips to make
your production implementation a success.

How is Load Testing Used?

The term load testing is one
encompassing several different types of pre-production test scenarios. Depending
upon your enterprise standards and the type or size of project, this may
include one or more of the following:

  • Integration
    application modules join together well.
  • Systems
    Confirm the
    application works well with other applications.
  • Recovery
    Can the
    application recover correctly and within service level agreements after a failure?
  • Security
    Verify that
    users have necessary and sufficient security to perform functions.
  • Stress testing. Tests various combinations of
    transactions loads, including number of concurrent users, volume of input data,
    and transaction load on the database. In some cases, it is necessary to
    determine at what load point the application or database will fail (sometimes
    called test-to-failure).

Usually, the application team and the
release manager have discussed and decided upon the testing schedule and level
of testing to be performed prior to releasing the application to production.
Reasons for executing various test scenarios may depend upon various factors
including staff availability and budget, project deliver date, and availability
of a suitable database environment for testing.

In some cases, the reasons for doing
load testing are deemed insufficient and this process is bypassed on the way to
production. This is unfortunate: the database administrator (DBA) gains quite
a lot of valuable information during load testing.

In point of fact, the DBA should insist
upon a load test, and do whatever is necessary to assist developers and release
managers with this process. Here are the most important items the DBA looks
for, and how they should be addressed.

  • Deadlocks and
    symptoms may be caused by one or more separate issues. The most common in load
    testing is the “initialization problem”. Since the application is processing
    for the first time, many transactional tables may be empty (e.g., customers,
    current orders, account activity). Since table and index I/O is done at the
    block level (usually by the 4K page), many active table rows and index entries
    will be stored on the same page. Simultaneous insert and update activity for
    different rows on the same page may cause deadlock or timeout issues. As the tables
    and indexes grow this issue tends to disappear. Later, the occurrence of these
    symptoms is most likely due to poor application design, transactions that are
    too long (i.e., do far too many database updates per unit of work), or
    poor database design. I will cover these issues in a later article.
  • Hot spots. These are a common occurrence in
    transactional systems that assign keys to new entities or transactions. Typical
    examples are an order entry system where new orders are assigned sequentially
    ascending keys or a financial management system where money transactions are
    keyed by the date/time of their entry (again, an ascending key). Tables and
    indexes may be structured to support application functionality and performance
    for such key assignment. One common method is to store new table rows at the
    physical end of the table. While this may help performance by clustering the
    data in key sequence, the table pages at the end of the table will be very
    frequently referenced. The same is true for indexes that support ascending
  • SQL capture for
    access path analysis.

    DBAs prefer to design database table and index structures to support data
    retrieval. Knowing the SQL that will access the data is key to performance
    tuning. The DBA can use EXPLAINs of the SQL to note access paths and to make
    proper index choices and other structural decisions prior to production
  • Monitor growth
    As the
    application matures, performance may suffer due to table and index size. DBAs
    consider a pre-defined purge process to be a critical factor to prevent this.
    However, many times the application development team does not define or create
    a purge process. The usual reason: “We won’t be purging for a year, so there’s
    plenty of time to get it defined.” Regrettably, in many cases, the purge
    process is not defined, and the DBA is left to deal with a combination of huge
    tables and application performance problems. Far better to deal with this
    before production implementation, and the load test provides a good place for
    the DBA to demonstrate how data volume can affect performance.
  • Confirm object
    moment of production implementation is not the time to discover that
    tables, indexes, or associated structures are not defined correctly. A
    successfully completed load test gives everyone confidence that all table
    columns have the correct attributes and that all SQL statements update and
    retrieve the proper data.

During the Load Test

In preparation for the load test, the
DBA needs to have several processes ready. Here are the most common.

  • Reorgs and
    chooses access paths for SQL statements based on object statistics. For the
    initial execution of the load test, many tables will be empty and their
    statistics will reflect that. However, as the tables and indexes grow the
    statistics become outdated and possibly detrimental to performance. A typical
    example: For a simple table access, DB2 may choose a tablespace scan access
    path (after all, the statistics show that the table is empty!). To avoid this
    issue (the “initialization problem” discussed earlier), the DBA should be
    prepared to run index and tablespace reorgs and runstats during the beginning
    stages of the load test.
  • Full Backups
    after Initial Testing.
    the initial state of the application (many empty tables) will not be a normal state,
    performance measures during the load test may not be valid. The usual fix for
    this is to begin load testing and exercise the application for a predetermined
    period. At this point, the DBA would run reorgs and runstats as described
    previously, and then execute full image copies of all tables involved in the
    test. This provides a recovery point that can be used in subsequent rounds of
    testing, thus bypassing the need for any priming transactions and repeated
    reorgs and runstats.
  • Metrics for
    Resource Constraints.
    most common resource constraints encountered in load testing by mainframe DB2
    applications are I/Os, application throughput, and waits due to object locking.
    Decide ahead of time how these will be measured and what criteria will be used
    for database service levels.

Tips for a Successful Load Test

  • Achieve Agreement
    on Purge Processes.
    issues provide a good lesson for developers and management. Application staff
    now have a direct experience where data volume and distribution patterns lead
    to performance problems. Future growth may cause problems too; so, they need to
    make sure that all purge processes are defined and implemented prior to the
    move to production.
  • Capture and
    save SQL.
    In addition
    to executing EXPLAINs on captured SQL, the DBA can store the captured
    statements for future documentation or analysis purposes. This can be of great
    assistance when introducing a new DBA to the application, or when analyzing a
    production performance issue.
  • Full Backups after
    initialization is complete, execute image copies for all tables. This provides
    a recovery point for later testing, speeding up the process.
  • Address Production
    Issues before Production.
    prefer to be proactive and deal with potential issues before they become
    production nightmares. In reacting to production performance problems, the DBAs
    commonly use tools in the following list. Read it, and imagine being able to
    apply the correct fixes during load testing rather than during a production
    • Adding new indexes
    • Implement
      row-level locking
    • Tune SQL
    • Partition or
      re-partition tables
    • Implement an

      emergency purge process

    • Reset invalid or
      missing security

Additional Resources

IBM Information Management Software for z/OS Solutions Information Center


See All Articles by Columnist

Lockwood Lyon

Lockwood Lyon
Lockwood Lyon
Lockwood Lyon is a systems and database performance specialist. He has more than 20 years of experience in IT as a database administrator, systems analyst, manager, and consultant. Most recently, he has spent time on DB2 subsystem installation and performance tuning. He is also the author of The MIS Manager's Guide to Performance Appraisal (McGraw-Hill, 1993).

Latest Articles