Home » DB2 » Checklist of points to improve backup &restore performance on SAP on DB2

Checklist of points to improve backup &restore performance on SAP on DB2

Pages

February 2014
M T W T F S S
« Jan   Jun »
 12
3456789
10111213141516
17181920212223
2425262728  

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Twitter Updates

Blog Stats

  • 345,611 hits

Archives

Pengunjung


Here is a list of points you can check to improve backup performance:

1.) DB2 backup command performance options

There are four options on the DB2 backup command that can improve backup performance. We will give some suggestions for the values to use for these parameters, but review of DB2 documentation is recommended.

OPEN n SESSIONS

This parameter is only valid if the use tsm option is used at the DB2 backup. If specified the backup process will open n I/O sessions to the Tivoli Storage Manager server to send the data to. The number of sessions is limited by the Tivoli Storage Manager node parameter MAXNUMMP which should not be greater than the maximum number of drives at the Tivoli Storage Manager server. If the backup goes directly to a tape storage pool and there are not enough tape drives available at the Tivoli Storage Manager server at the time of backup, some of the sessions have to wait until there are free tape mounts available again. In some cases, the backup may fail if there are not enough mount points available within a defined amount of time (IDLETIMOUT parameter at Tivoli Storage Manager server).

WITH num-buff BUFFERS

The numbers of buffers should be: #sessions + #parallelism +2. Also, the following calculation must fit: (num-buffers * buffer-size) < UTIL_HEAP_SZ (UTIL_HEAP_SZ is the database utility heap size).

BUFFER buff-size

This value is used as the buffer allocation size in pages (4 KB) when building the backup image. For a buffer size of zero, the value of the database manager configuration parameter BACKBUFSZ will be used as the buffer allocation size. When backing up a database, the data is first copied to an internal buffer. Data is then written from this buffer to the backup media when the buffer is full. Tuning BACKBUFSZ can help improve the performance of the backup utility as well as minimize the impact on the performance of other concurrent database operations.

We recommend setting the buffer size to a multiple of the extent size. If multiple table spaces have different extent sizes, the buffer size value should be a multiple of the largest extent size.

PARALLELISM n

Using this parameter can dramatically reduce the time required to complete the backup (especially if the backup is going to a disk). This parameter defines the number (n) of processes that are started to read data from the database. Each process is assigned to backup a specific table space. When it completes backing up the tablespace, it requests another. Each process will be assigned a tablespace to complete, therefore, to get better performance, let this value be less than the number of tablespaces, since setting up the value higher than the number of tablespaces does not show any significant difference in performance. If backing up to different targets (for example, using multiple sessions to send the data to Tivoli Storage Manager) parallelism should not be greater than the number of targets (sessions)).

In the next example, we assume that we have two tape mount points available at the Tivoli Storage Manager server. We can use two sessions and also a parallelism of 2 (no matter how many tablespaces we have):

db2 backup db sample use tsm open 2 sessions with 6 buffers parallelism 2

2.) TSM Server Parameters

COLLOCATION

If collocation is set to yes for the storage pool on the TSM Server, this would mean that a single node’s data would be placed on the “same” tape.  If there are multiple sessions specified for a DB2 backup and this is going to a tape storage pool that has collocation enabled, the second/subsequent sessions will queue in a Media Wait state behind the first session in an attempt to place all the data on the same tape.  Even if the backup is large enough to span multiple tape volumes, a second session would wait for the first session to complete in order to obtain the same tape for the data storage.

If you see the second session in a wait state, query the session to see what it is waiting on:

Query Session   F=D

If it is in a MediaWait, the Detailed Format output will show which tape volume it is waiting on.

Collocation can also be a consideration during a restore if the backup was performed with multiple sessions to disk and subsequently migrated to a collocated tape storage pool.  If the data is all on one volume, then a multi-session restore would witness sessions waiting for the same tape volume.  Even if the tape storage pool is not collocated, a multi-session DB2 backup does not guarantee that a DB2 restore will be able to use multiple sessions.  If for example the backup is performed to the TSM Server to the Disk storage pool with 5 sessions, this will save the data randomly within the disk storage volumes.  If this data is subsequently migrated to a TSM Server tape storage pool, it could be saved with all the objects on one tape volume.  Then during a restoration, regardless of how many sessions were started for the DB2 restore, only one session would be able to access the tape at a time. The first DB2 session would be restoring from the tape volume containing the data and the other sessions would be in a MediaWait for this same tape.

MAXIMUM NUMBER OF MOUNT POINTS (MAXNUMMP)

The Maximum Number of Mount Points applies to any sequential type of storage on the TSM Server.  The MAXNUMMP will affect how many volume (tape) mounts can be made by a specific TSM Node for those backups that are going straight to tape.  If the MAXNUMMP is not high enough for the number of DB2 sessions that are started to tape, the DB2 backup will fail when it can’t obtain a tape mount.  If the drives are all in use, then the session will go into a MediaWait.

The Maximum Number of Mount Points would need to be set to a value that will accommodate the number of session writing to a sequential type of storage.  If you are using Server to Server virtual storage, this is also sequential and would utilize the MAXNUMMP.  The only place where the MAXNUMMP does not come into play is for Random Disk Storage.

To set the MAXNUMMP, this is performed on the TSM Server for the Node definition.  For example:

Update Node NodeName MAXNUMMP=5

DB2 SizeEstimate and CACHE=YES for the TSM Storage pool

When caching is enabled for a TSM storage pool, duplicate copies of the files remain cached in the disk storage pool after they are migrated to the next pool (usually tape).  The cached copies are only deleted when space is needed.  During the backup process, the size of the object to be backed up is first sent to the TSM server to determine where the object should be stored.  At this point, the TSM server deletes enough cached files from the disk stgpool to contain the size of this object.  As the object is backed up, if it grows even a small amount, this causes it to not fit in the space provided by the original size estimate.  The TSM server attempts to obtain more space, but since the storage pool has cached files in it, the additional space can not be obtained (the cached files are only deleted by an incoming backup request and not during an extension of space).  This can cause the backup to fail with an out of storage space message even though the storage pool appears to have space available.

The SizeEstimate sent to the TSM Server from the DB2 backup process is determined by looking at the total SMS tablespace pages divided by the number of sessions performing the backup. (the tablespace pages are usually 4K).  The SizeEstimate is sent to the TSM Server at the beginning of each DB2 session.  As the DB2 backup progresses, one session can get more data than another session.  This can cause a problem if the SizeEstimate was not large enough to accommodate all the data that is sent in an individual session.  If this SizeEstimate is not large enough for the amount of data for this session, and caching is enabled, then additional space for saving the data would not be able to be obtained due to the caching, and the backup would fail.

To address this, the caching can be turned off for the storage pool. This only stops new files from being cached, but does not delete the cached files that currently exist in the storage pool.  To free the space utilized by the cached file, a MOVE DATA would have to be issued after the cache option was set to no.  This will cause the storage pool to be emptied and the cached files removed.  If this Move Data is not done, the cached files remain in place until they are deleted by a request for space (which could take time depending on the percentage of your storage pool that is full).

It is not recommended to use caching for the disk storage pool that is being utilized for a DB2 backup.

3.) DB2 restore command performance options

There are also factors that affect the restore process. We will discuss only the restore command options that will bring a better restore performance. They are almost the same as for the backup command.

OPEN n SESSIONS:

This parameter is only valid if the use tsm option is used on the DB2 restore command. If specified, the restore process will open n I/O sessions to the Tivoli Storage Manager server to receive the data. The number of sessions is limited by the Tivoli Storage Manager node parameter MAXNUMMP, which cannot be greater than the maximum number of drives at the Tivoli Storage Manager server. This number should also not be higher than the number of sessions used for backup. If there are not enough tape drives available at the Tivoli Storage Manager server at the time of restore, some of the sessions have to wait until there are free tape mounts available again.

WITH num-buff BUFFERS

The numbers of buffers should be: #sessions + #parallelism +2. Also, the following calculation must fit: (num-buffers * buffer-size) < UTIL_HEAP_SZ (UTIL_HEAP_SZ is the database utility heap size).

BUFFER buff-size

We recommend setting the restore buffer size to a multiple of the extent size. The value given must be equal to, or a multiple of, the backup buffer size that was specified when the backup image was taken. Otherwise, the smallest acceptable size of the buffer (4KB) is used for the restore buffer. If you do not specify the number of pages, each buffer will be allocated based on the database manager configuration parameter RESTBUFSZ.

PARALLELISM

This specifies the number of buffer manipulators to be spawned during the restore process. The default value is 1. For restore, the number can be increased, but not higher than the number of Tivoli Storage Manager client sessions that have been started for the restore. The number of buffers should be at least the value selected for parallelism.

In the next example, we assume that the backup was made using two Tivoli Storage Manager sessions. For restore, it is also reasonable to use two Tivoli Storage Manager sessions and a parallelism of 2 (no matter how many tablespaces we have):

db2 restore db sample use tsm open 2 sessions with 6 buffers parallelism 2

4.) Related  URL Links:

I would recommend to refer following Tech artical to get better understanding of how backup/restore works

http://www.ibm.com/developerworks/data/library/techarticle/dm-0501zikopoulos/index.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006205.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0011912.html

5.) Incremental DB2 Database Backup

There is a bit per tablespace that let’s incremental backup know whether there is a changed page in that tablespace or not, that’s the extent of our knowledge. If the bit is set to a dirty page, we have to

read the entire tablespace. If the bit is set to clean we can skip the tablespace altogether.

If all the tablespaces have dirty pages, then we have to read every page in the database for incremental backup. In this sense the reading will be no different for nonincremental and incremental backups.

so it may take same amount of time during incremental db2 database backup. However, the backup image though can be significantly smaller for incremental backups.

Hope this helps you to determine your incremental scenario.


3 Comments

  1. To support operating system or database migrations, SAP offers the remote

    service “SAP OS DB MIGRATION ONLINE TRAINING”. The elements of services, tools and documentation form the basis for successfully executing the migration. The

    tools support the R3load procedure.

  2. This SAP OS DB MIGRATION ONLINE TRAINING course not only gives an overview but provides you with
    a thorough in-depth knowledge about how the SAP HANA works. we also provide online training on SAP OS DB MIGRATION ONLINE TRAINING .
    The SAP OS DB MIGRATION ONLINE TRAINING is mandatory if you are going to migrate a productive system.

  3. qfvhvfhv says:

    Checklist of points to improve backup &restore performance on SAP on DB2 | Life is about extreme choice
    qfvhvfhv http://www.gamtr043qz2036e731tn85z65ro0bwv3s.org/
    aqfvhvfhv
    [url=http://www.gamtr043qz2036e731tn85z65ro0bwv3s.org/]uqfvhvfhv[/url]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: