In recent times I have noticed lot of SAP customers having backup performance issues ( Long backup running times )
Here is some information I would like to mention to check them long running back up issues.
1. Page Size of different tablespace. For ideal and optimal performance it is recommended to have same page size (eg: 16KB ) on all tablespaces.
2. For Backup performance issues as a start up of analysis, I would focus on tablespace layout eg: I will try to keep containers starting with 4 to 32 number of containers. Also I would make sure that free extents is always under high water mark on tablespace. If you noticed large number of free extents you need to strongly consider to perform reorg of the tablespace ( this is a whole lot of big discussion, I will try to detail in next document). Also make sure that the DB parameters are optimally set for BACKUP ( eg : NUMIO_SERVERS ) Further details can be found in backup performance note : 1269697 DB6: Backup Performance.
3. As said in point 2. a very large tablespace may determine the backup runtime, if it is much larger than all the other tablespaces, it may be a good idea to identify the largest tables in that particular tablespace and move them to other (new) tablespaces. Note that the DB2 backup always starts with the largest tablespaces first to alleviate this problem.
You can also collect backup runtime information as per steps in SAP Note : 2038748 – DB6: Data collection for database online backup performance issue.
These scripts should be run at regular intervals during backups, which tells about long backup running tablespace. This means the size of the long running tablespace should be bigger than other tablespaces.
To analyse further you can consider to perform below list of activities something like the following.
1. First of all identify largest tables in a tablespace eg:<PSAPABC>D/I
2. Create one or more sets of new data/index tablespaces
3. Now can move the tables identified in step 1. to the new tablespaces using DB6CONV v6 ( 1513862 )
4. After above steps are done, this could leave tablespace fragmented so you need to perform reorg of tablespace
eg :move <PSAPABC>D/I to FRAG_<PSAPABC>D/I
5. Create new tablespaces <PSAPABC>D/I
6. Now have to move all tables from <FRAG_PSAPABC>D/I to <PSAPABC>D/I using DB6CONV V6.
Please note that the duration of the move is hard to estimate. I would suggest to create a test table of defined size and move it. This should provide an idea about performance of the move. If you prefer online case, this adds one other additional degree of uncertainty. Fact is that the online move is not faster that the offline. Emphasis is the availability of the table throughout the process. Performance heavily depends on the usage of the table during the process. I well know of cases that very large tables (200gb) may take a week to be moved online … or even longer or (<= 50GB/day).
You for sure will see performance impact during the move – particularly if this is online and the table to be accessed is being processed (moved). Again, the impact depends on many factors.
2038748 – DB6: Data collection for database online backup performance issue
1269697 – DB6: Backup Performance
1513862 – DB6: Table conversion using DB6CONV version 6 or higher
Hope it helps