View previous topic :: View next topic
|
Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Hello,
In my load control card, we refer to below parameters,
LOG NO
NOCOPYPEND
SORTKEYS 150000000
STATISTICS TABLE(ALL) INDEX (ALL KEYCARD)
Can you please explain how would INDEX (ALL KEYCARD) helps optimising the access path? |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1314 Location: Vilnius, Lithuania
|
|
|
|
Why should we be using Google when you can do it yourself? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
This looks more like a DB2 question than JCL. Topic being relocated to the DB2 part of the forum.
Also, suggest you read about the INDEX (ALL KEYCARD) in the DB2 manual. At the top of the page is a link to IBM Manuals, among which are manuals for several releases of DB2. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Thanks Dick for the updates,
Surely, I shall go thru it rather I am going thru it.
But any practical instances related to this parameter was the intention. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
It has already been moved
One must be a global moderator to relocate topics. . . |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Yes Dick.... Thank you!! |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Manual says:
INDEX Specifies indexes for which information is to be gathered. Column information is gathered for the first column of the index. All the indexes must be associated with the same table space, which must be the table space that is specified in the TABLESPACE option.
(ALL)
Specifies that the column information is to be gathered for all indexes that are defined on tables in the table space.
(index-name)
Specifies the indexes for which information is to be gathered. Enclose the index name in quotation marks if the name contains a blank.
KEYCARD Requests the collection of all distinct values in all of the 1 to n key column combinations for the specified indexes. n is the number of columns in the index.
Can any one explain KEYCARD in more detail? specific to the performance perspective. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
At the top of the page is a link to "IBM Manuals". Suggest you look in the Utility Guide for your release of DB2 (or even a more current one).
If you find something in the manuals that is not clear, post what you found and your doubt. Someone should be able to clarify. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
If an index is on more than 1 column. lets say Index1(Col1,Col2,Col3,Col4)
Then by default (although the default might have changedin v10) DB2 only gathers statistics on
- number of distinct values of Col1 (FirstKeycard)
- total number of entries in the index = (col1,col2,col3,col4) (Fullkeycard)
Options KEYCARD gathers additional statistics on
- number of distinct values of (Col1,col2)
- number of distinct values of (Col1,col2,col3)
This is usefull for estimating filterfactors of ix-scan with matching columns 2 or 3. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Thanks you GuyC, just one question
How these options affects the performance? which one can be choosen for better performance? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
the more accurate the statistics, the better DB2 can choose the best accesspath.
So it is possible that DB2 already chooses the best accesspath and then nothing will be seen performance wise.
after running KEYCARD stats & rebind, some accesspaths might change.
Normally for the better (=faster) but it is always possible that for some it is worse (=slower).
Using KEYCARD is advised. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3076 Location: NYC,USA
|
|
|
|
Thanks GuyC |
|
Back to top |
|
|
|