Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Performance Down theTube...
- Thread starter rhnewfie
- Start date
gregsimpson
Programmer
- Apr 18, 2002
- 521
rhnewfie,
have you checked out the differences in both the db and dbm config. Maybe some have gone back to defaults?
Also it may be worth you doing a reorg and runstats on everything to ensure the V8 optimiser can make the most of what's available to it.
Cheers
Greg
have you checked out the differences in both the db and dbm config. Maybe some have gone back to defaults?
Also it may be worth you doing a reorg and runstats on everything to ensure the V8 optimiser can make the most of what's available to it.
Cheers
Greg
- Thread starter
- #3
Can you tell me more about reorg and runstats? I use DB2 and am familiar with some things but as an SE I don't have in depth knowledge of it.
Also, the original install is gone, is there some way that I can still check the config params?
Thanks!
Also, the original install is gone, is there some way that I can still check the config params?
Thanks!
If you made no precautions before performing the upgrade you will probably have little chance of checking the old settings..
Reorg will rebuild a table so accessing data will require minimum amount of I/O. Runstats will enable the optimizer to choose the most efficient query path.
Personallly , I do not expect that upgrading would have effect on these matters, but I cannot back that up with proof ..
Some other elements to consider:
1. DFT_QUERY_OPT
This is a setting ranging from 1 to 9. Default is 5. The higher the value the longer DB2 takes to calculate a efficient path. For complex SQL this can be beneficial. Perhaps upgrading has reset this value back to a default.
2. HASH Joins
This setting has to be enabled by the DBA to be available. If your queries use joins over non-indexed fields, then a HASH join could very well speed up things by 3-fold.
Since enabling hash joins is pretty 'vague' upgrading may have reset this option.
Reset it with:
from db2 clp
If hash joins are enabled you should check the SORTHEAP size. This is set at 256x4Kb by default, but it may have been enlarged during working with version 7.
I think you should try to find out whether your version 7 may have tuned in the past and rectify accordingly...
Ties Blom
Information analyst
Reorg will rebuild a table so accessing data will require minimum amount of I/O. Runstats will enable the optimizer to choose the most efficient query path.
Personallly , I do not expect that upgrading would have effect on these matters, but I cannot back that up with proof ..
Some other elements to consider:
1. DFT_QUERY_OPT
This is a setting ranging from 1 to 9. Default is 5. The higher the value the longer DB2 takes to calculate a efficient path. For complex SQL this can be beneficial. Perhaps upgrading has reset this value back to a default.
2. HASH Joins
This setting has to be enabled by the DBA to be available. If your queries use joins over non-indexed fields, then a HASH join could very well speed up things by 3-fold.
Since enabling hash joins is pretty 'vague' upgrading may have reset this option.
Reset it with:
Code:
db2set DB2_HASH_JOIN=ONfrom db2 clp
If hash joins are enabled you should check the SORTHEAP size. This is set at 256x4Kb by default, but it may have been enlarged during working with version 7.
I think you should try to find out whether your version 7 may have tuned in the past and rectify accordingly...
Ties Blom
Information analyst
- Status