Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Improving DB2 recovery performance

Here are several steps to reduce the time it takes to perform a restore operation in DB2.

You can take a number of steps to reduce the time it takes to perform a restore operation. First, you should optimize the use of your buffers. Make sure that the restore buffer is a multiple of the buffer that was used during your backup, and that that buffer is a multiple of the extent size of your table spaces. When more than one table space with different extent sizes, use the largest extent size as the size you multiply to get your optimal restore buffer. If you increase the number of buffers used, you improve concurrent operation and also improve performance. When using multiple I/O channels and buffers, use two times as many buffers as channels, or as a minimum use the number of buffers that is the number of devices or containers added to the specified number in the PARALLELISM option.

You will also improve performance for restores if you set the PARALLELISM so that it is larger than the number of source devices used. The more source devices used, in theory, the higher the performance.

Some restores with LOB data can require very long restore times. When you perform a RESTORE using a roll-forward recovery scheme you have the option to define which table spaces you want to recover. By carefully determining which large data fields do not need to be restored and by separating that data along with LOB data into tables that aren't part of planned recovery operations you can greatly improve your RESTORE performance. Assuming that your LOB and long data fields data exists in other locations, you can also turn off logging of them. When you restore any table spaces without restoring those fields you need to roll forward to the log's end in order to preserve data consistency.

About the Author

Barrie Sosinsky (barries@sosinsky-group) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.