Periodically the database size and internal organization can progress to where it is no longer internally efficient. To improve database performance, the database can be unloaded and reloaded in an optimal manner that will:
The database and recovery log buffer pool sizes can also affect TSM performance. A larger database buffer pool can improve performance, and a larger recovery log buffer pool reduces how often the server forces records to the recovery log.
See Reorganizing the Database for more information about restoring database efficiency.
TSM can dynamically adjust the size of the database buffer pool, or you can do this procedure yourself.
The SELFTUNEBUFPOOLSIZE option has two values: YES or NO. NO is the default. If YES is specified, TSM will dynamically adjust the database buffer pool.
If the SELFTUNEBUFPOOLSIZE option is specified as YES in the server options file, buffer pool cache hit ratio statistics will be reset at the beginning of expiration. After expiration processing completes, the BUFPOOLSIZE will be adjusted dynamically.
Server expiration processing resets the database buffer pool before the next processing starts and examines if the database buffer pool cache hit ratio is above 98%. If the cache hit ratio is lower than 98%, the database buffer pool will be increased; if it is higher, the buffer pool size will not change. Increasing the database buffer pool will not be more than 10 % of available real storage.
Perform the following steps to track the database buffer pool statistics and adjust the buffer pool size:
Reset the buffer pool statistics. Initially, you might want to reset the statistics twice a day. Later, you can reset them less often. To reset, enter:
reset bufpool
To see if the database buffer pool is adequate for database performance, enter:
query db format=detailed
The server displays a report, like this:
+--------------------------------------------------------------------------------+ | Available Space (MB): 196 | |Assigned Capacity (MB): 196 | |Maximum Extension (MB): 0 | |Maximum Reduction (MB): 176 | | Page Size (bytes): 4,096 | | Total Pages: 50,176 | | Used Pages: 4,755 | | %Util: 9.5 | | Max. %Util: 9.5 | | Physical Volumes: 5 | | Buffer Pool Pages: 128 | | Total Buffer Requests: 1,193,212 | | Cache Hit Pct.: 99.73 | | Cache Wait Pct.: 0.00 | +--------------------------------------------------------------------------------+
Use the following fields to evaluate your current use of the database buffer pool:
Use the BUFPOOLSIZE server option to set the size of the database buffer pool.
Do the following to adjust the size of the recovery log buffer pool:
To see how the recovery log buffer pool size affects recovery log performance, enter:
query log format=detailed
The server displays a report, like this:
+--------------------------------------------------------------------------------+ | Available Space (MB): 12 | |Assigned Capacity (MB): 12 | |Maximum Extension (MB): 0 | |Maximum Reduction (MB): 8 | | Page Size (bytes): 4,096 | | Total Pages: 3,072 | | Used Pages: 227 | | %Util: 7.4 | | Max. %Util: 69.6 | | Physical Volumes: 1 | | Log Pool Pages: 32 | | Log Pool Pct. Util: 6.25 | | Log Pool Pct. Wait: 0.00 | +--------------------------------------------------------------------------------+
Use the following fields to evaluate the log buffer pool size:
If this value is greater than 0, increase the recovery log buffer pool size.
Use the LOGPOOLSIZE server option to set the size of the database buffer pool.
Over time, database volumes become fragmented. You can restore the efficiency of the database by unloading and reloading it. First unload the database in key order. Then reload the database, which will, in effect, compress and reorganize it. When the server is offline, use the DSMSERV UNLOADDB utility. After unloading the database, use the DSMSERV FORMAT utility (which initializes the database) or the DSMSERV LOADFORMAT utility (which does not initialize the database) followed by the DSMSERV LOADDB utility. After using the DSMSERV UNLOADDB, the DSMSERV FORMAT, or DSMSERV LOADFORMAT, and the DSMSERV LOADDB utilities, the DSMSERV AUDITDB utility may be required to locate and correct any database inconsistencies. Here is an example of reorganizing the database, where the database is unloaded to tape:
query db
This estimate is usually larger than necessary. The space required will likely be less than your estimate.
dsmserv unloaddb devclass=tapeclass scratch=yes
halt
dsmserv format 1 log1 2 db1 db2
dsmserv loaddb devclass=tapeclass volumenames=db001,db002,db003