Dec 02

Tivoli Storage Manager V6 active log files not in correct location

DB LOG, DB2 Comments Off on Tivoli Storage Manager V6 active log files not in correct location

Problem(Abstract)

The log files for a Tivoli Storage Manager V6 server might be being written to a location different from the ACTIVELOGDIR option that is set in the dsmserv.opt.

Resolving the problem

It is possible the active logs might be create/written to a directory other than what is specified in the dsmserv.opt file (ACTIVELOGDIR). If this is discovered, the DB2 configuration will need to be reviewed. Issue the following commands, as the instance owner:

db2 connect to tsmdb1
db2 get db cfg for tsmdb1

The “Path to log files = ” value should be reviewed to see if it matches with what the Tivoli Storage Manager has defined for the active log (‘ActiveLogDir’ from QUERY OPTION output). If these paths are not the same, the DB2 configuration can be updated by using the following steps:

1. Halt the Tivoli Storage Manager server
2. As the instance owner and from the instance home directory, issue the following commands:

db2start
db2 connect to tsmdb1
db2 update db cfg using NEWLOGPATH </path/to/active/logs>
db2 terminate
db2stop
db2start
db2 connect to tsmdb1; db2 get db cfg | grep “Path to log files”

3. If the path has been corrected, then stop DB2 (db2stop) and start the server normally

In order to find out what might have happened, the db2diag.log file should be reviewed. One reason this could happen is if DB2 is not able to access the log directory, DB2 can then update the log path to the default. Below is an example from a db2diag.log:

2010-11-08-13.52.25.668822-360 E38541A479 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2700
MESSAGE : ADM1814E The current log path “/tsmactivelog/NODE0000/” is invalid.

2010-11-08-13.52.25.749264-360 E39021A545 LEVEL: Error
PID : 5636338 TID : 1544 PROC : db2sysc 0
INSTANCE: tsminst1 NODE : 000 DB : TSMDB1
APPHDL : 0-7 APPID: *LOCAL.tsminst1.101108194849
AUTHID : TSMINST1
EDUID : 1544 EDUNAME: db2agent (TSMDB1) 0
FUNCTION: DB2 UDB, data protection services, sqlpgnlp, probe:2960
MESSAGE : ADM1811E DB2 will now switch to the default log path
“/home/tsminst1/NODE0000/SQL00001/SQLOGDIR/”

 

written by Bosse

Jul 01

IC69506: AUTOMATIC REORGS OF DATABASE INDEXES DOES NOT OCCUR

DB LOG, DB2 Comments Off on IC69506: AUTOMATIC REORGS OF DATABASE INDEXES DOES NOT OCCUR

Error description

The TSM Server will perform automatic reorgs of database tables,
however, online reorgs of the table indexes do not occur
automatically.  By default, the DB2 policy definitions do not
allow for automatic, online reorgs of database table indexes,
thus preventing the TSM Server from performing reorgs of the
various indexes.  This can result in the continued growth of the
TSM database.

Platforms affected:
All TSM 6.1.x and 6.2.x Servers

Customer/L2 Diagnostics:
The DB2 ‘reorgchk’ utility can be used to determine if a reorg
of the table indexes is required.

Platforms affected:
All TSM 6.1.x and 6.2.x Servers

The DB2 ‘reorgchk’ utility can be used to determine if a reorg
of the table indexes is required.

Local fix

The following actions can be performed to modify the DB2 policy
definitions to allow for automated, online reorgs of the table
indexes:

1. Login to the host machine where the TSM Server is running as
   the DB2 instance owner (eg. 'tsminst1')
2. Issue the following command to connect to the TSM database:

   $ db2 connect to tsmdb1

3. Issue the following command to export the existing DB2
   automatic maintenance definitions to a file named
   AutoReorg.xml:

   $ db2 "call sysproc.automaint_get_policyfile(
   'AUTO_REORG','AutoReorg.xml')"
4. The file which contains the exported data is placed by the
   stored procedure in the following location:

   <instance dir>/sqllib/tmp/AutoReorg.xml

5. Make a copy of the AutoReorg.xml file as this file can be
   used later in the event it becomes necessary to revert back
   to the original configuration.
6. Modify the AutoReorgNew.xml file as follows:

   - change the indexReorgMode value from "Offline" to "Online"
   - change the useSystemTempTableSpace value from "false" to
     "true"

7. Issue the following command to activate the new DB2 automatic
   maintenance definitions:

   $ db2 "call sysproc.automaint_set_policyfile(
   'AUTO_REORG','AutoReorgNew.xml')"

Automatic, online reorgs of the database table indexes can now
be performed by TSM.  The following command can be issued
periodically to verify that automatic reorgs of the indexes are
occurring as expected:

   $ db2 list history reorg all for db tsmdb1

NOTE: Should it be necessary to disable automated reorgs of the
table indexes, issue the following command to update the DB2
automatic maintenance definitions back to their original values:

   $ db2 "call sysproc.automaint_set_policyfile(
   'AUTO_REORG','AutoReorg.xml')"

NOTE: The procedures listed above are specific to UNIX hosts.
These procedures may also apply to DB2 running on Windows hosts
with the following distinctions:

   - all DB2 commands must be run in a DB2 command window
   - the policy definition file on Windows is named
     DB2AutoReorgPolicy.xml.  Substitute this policy file name
     in place of AutoReorg.xml.

It is recommended that the enabling/disabling of automatic index
reorgs be performed at a time when the TSM Server is quiesced.

written by Bosse

Jun 29

Estimate DB space utilization with reorganization.

DB LOG, Kommands Comments Off on Estimate DB space utilization with reorganization.
Question
How much space will be reclaimable in the TSM Server database if a DB reorganization is done?
 
 
Cause
After a reorganization of the TSM Server database, there was much more space reclaimed than the estimate command initial suggested.
 
 
Answer
The ESTIMATE DBREORGSTATS command only analyses the used pages. Any pages that are completely empty will not be included in the analysis. This means that heavily fragmented databases will be able to reduce their assigned capacity to much greater degree than the ESTIMATE DBREORGSTATS command suggests. From the example Q DB F=D below:
                    Available Space (MB): 115,000
                  Assigned Capacity (MB): 114,600
                  Maximum Extension (MB): 400
                  Maximum Reduction (MB): 20
                       Page Size (bytes): 4,096
                      Total Usable Pages: 29,337,600
                              Used Pages: 8,117,180
                                Pct Util: 27.7
                           Max. Pct Util: 27.7
                        Physical Volumes: 6
                       Buffer Pool Pages: 104,858
                   Total Buffer Requests: 24,711,191
                          Cache Hit Pct.: 99.11
                         Cache Wait Pct.: 0.00
                     Backup in Progress?: No
              Type of Backup In Progress:
            Incrementals Since Last Full: 0
          Changed Since Last Backup (MB): 414.17
                      Percentage Changed: 1.31
          Last Complete Backup Date/Time: 12/10/07 15:02:51
      Estimate of Recoverable Space (MB): 6,953
 Last Estimate of Recoverable Space (MB): 12/10/07 21:48:07
Currently the database can only be reduced by 20 MB, even though more space is technically free and empty. With an assigned capacity of 114,600 MB to the database, and a utilization of 27.7% we would expect that after a DB reorganization at least 82,800 MB of space would be recoverable – the amount between the actually utilized space in the database and the empty space up to the assigned capacity. However as seen the Estimate of Recoverable Space is only 6,953 MB.After the DB reorganization was completed, the assigned capacity was reduced by more than 82,800 MB.

The reason for this drastic difference, is that the estimate only covers the utilized data. Any portion of the database that is assigned, but not utilized, will not be included in the estimate. Therefore, the difference between the utilized data and the assigned capacity should be roughly included in the final estimate by the TSM Administrator when making a determination on if the reorganization is time and space effective.

written by Bosse

Jun 29

Getting the correct db2diag.log for the correct Server instance on Windows

DB LOG Comments Off on Getting the correct db2diag.log for the correct Server instance on Windows
Problem(Abstract)
On Windows, the db2diag.log can be in different locations depending on the OS version, and there may be more than one Tivoli Storage Manager Server instance.
 
Symptom
Incorrect db2diag.log for the Server instance.
 
 
Resolving the problem
Depending on the version of the Windows server, the db2diag.log can be in different locations. Also, there is a db2diag.log for each DB2 instance on the server. Use the following information to obtain the correct db2diag.logWindows 2008 server:The default location is:

C:\ProgramData\IBM\DB2\DB2TSM1\

Windows 2003:

The default location is:

C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2TSM1

The example below shows two Server instances, SERVER1 and SERVER2.

written by Bosse

Jun 09

Managing the DB2 LOCKLIST Configuration Parameter with Tivoli Storage Manager

DB LOG, Info, Kommands Comments Off on Managing the DB2 LOCKLIST Configuration Parameter with Tivoli Storage Manager
Question
How can the LockList configuration parameter be adjusted to minimize the potential for DB2 deadlocks in a Tivoli Storage Manager environment?
 
 
Cause
If the DB2 LOCKLIST parameter is managed incorrectly, it may cause deadlocks to be returned to the Tivoli Storage Manager application for insert, delete and update requests.
 
 
Answer
DB2 LOCKLIST PARAMETERThe DB2 LOCKLIST parameter indicates the amount of storage that is allocated within the Database Heap to store lock information. There is one LockList per database and it contains the locks held by all applications, or in this case Tivoli Storage Manager Server transactions, concurrently connected to the database. Locks are stored and manipulated by DB2 in the form of a request block which consumes 40 bytes of memory on 32-bit platforms or 64 bytes on 64-bit systems. By default, DB2 manages the LockList size automatically and increases or decreases the total LockList allocation based on current activity. To determine the current value, issue the following command:

db2 “get db cfg for <db name>”

The LockList is comprised of memory allocated as 4K pages. A LOCKLIST parameter value of 10000 (4KB pages) is equivalent to 40,960,000bytes (about 39MB) of memory allotted to the LockList from the Database Heap. A single lock request may require up to 128 bytes out of the LockList heap as one request block is used for the lock descriptor and the second block is used to associate the Tivoli Storage Manager server transaction with the resource being locked. If the lock resource has already been requested then only one request block is required for future transactions requesting that lock resource.

IMPACT TO THE TIVOLI STORAGE MANAGER SERVER

The proper management of the DB2 LockList is crucial to prevent deadlocks within the Tivoli Storage Manager server. When the server is under average workload and the amount of concurrent activity is not driving large spikes of lock requests, the DB2 automatic handling of the LockList works well for the Tivoli Storage Manager server. However, when the amount of concurrent data movement activity is high, the inherent spikes to the DB2 LockList can cause lock escalations which will ultimately result in deadlocks within the server.

The best case scenario is that the Tivoli Storage Manager server will re-drive the request and the subsequent operation will not fail due to the deadlock. Most Tivoli Storage Manager server operations effectively recover from this but in the case where a lot of data had been moved, deleted, or updated, the time required to process the data a second, or even third time can result in severe performance degradation on the Tivoli Storage Manager server. The worst case scenario is that the operation cannot be retried and the entire operation fails.

If the amount of concurrent data being moved exceeds 500GB at any given time, it is recommended that the DB2 LOCKLIST parameter be adjusted as explained below. If the Tivoli Storage Manager server is involved with deduplicating large files either from the client, or server, properly tuning the LockList manually is extremely critical as large file deduplication inherently drives the number of rows needing to be managed by DB2 very high. This is because a large file is managed in many small pieces which result in many row locks within the DB2 LockList.

The following Tivoli Storage Manager server messages are issued when a transaction is being rolled back due to a deadlock within DB2.
ANR0159E dbieval.c(863): Database deadlock detected on XX:X.
ANR0162W Supplemental database diagnostic information:
-X:XXXXX:-XXX ([IBM][CLI Driver][DB2/XXXXX] SQL0911N
The current transaction has been rolled back because of a
deadlock or timeout. Reason code “2”. SQLSTATE=40001).    

TUNING THE DB2 LOCKLIST

To choose a LOCKLIST parameter value that will minimize the amount of deadlocks that may occur within a Tivoli Storage Manager server environment, the maximum amount of data that can be moved, or managed, within the Tivoli Storage Manager server at any given time must be known or at least approximated with a fair amount of precision. .

Tuning Best Practice: Increase the DB2 LOCKLIST value by twice the amount of anticipated workload so that there are no unexpected failures during daily and nightly operations.

It is important to note that increasing the DB2 LOCKLIST parameter value has a direct effect on the amount of memory that is required within the instance memory region. Increasing the parameter should be done with the understanding of what the potential memory implications will be on the system. The following information and examples can be used to tune the DB2 LOCKLIST parameter to an appropriate value based on the Tivoli Storage Manager server environment and workload:

KEY FORMULA FOR DB2 LOCKLIST ADJUSTMENTSConcurrent data movement

500GB = 2,500,000 locks = 122000 LOCKLIST

1TB = 5,000,000 locks = 244000 LOCKLIST

5TB = 25,000,000 locks = 1220000 LOCKLIST

MEMORY REQUIREMENTS FOR LOCKLIST

122000 LOCKLIST = 499MB (122000 X 4096)

244000 LOCKLIST = 1GB (244000 X 4096)

1220000 LOCKLIST = 4.9 GB (1220000 X 4096)

Example #1

100 Tivoli Storage Manager clients store 1TB of data in a 4 hour nightly window

Migration has the ability to move 1TB of data in the same 4 hour window

Total concurrent data movement – 2TB

DB2 LOCKLIST recommendation – 588000

Additional impact to system memory (potential) – 2.4 GB

Example #2

Migration has the ability to move up to 1TB of data in an 8 hour window

Reclamation can run in the same window and process up to 2TB of data

Expiration runs in the same window and can process up to 10 Million files which represent 500GB of data

Total concurrent data movement/management – 3.5 TB

DB2 LOCKLIST recommendation – 954000

Additional impact to system memory (potential) – 3.9GB

UPDATING THE DB2 LOCKLIST PARAMETERThe following command can be used to update the LOCKLIST parameter:

db2 “update db cfg for <db name> using LOCKLIST XXXXXXXX”

XXXXXXXXX = LOCKLIST value

The new value can be verified by issuing the following command:

db2 “get db cfg for <db name>”

NOTE: As a result of updating the DB2 LOCKLIST parameter the MAXLOCKS parameter will be set to manual as well. The MAXLOCKS parameter controls what percentage of the LockList that a single transaction can own at any given time. Please ensure this value is set from 95-100. If it is not in this range, the parameter can be updated with the following command:

db2 “update db cfg for <db name> using maxlocks 97 “

written by Bosse