TSM V6 comes with a great feature - online reorg. However, it's disable by default. If the database is growing every day, you might want to check if the online reorg is enable.
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.
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.
No comments:
Post a Comment