DSNUTILB various parameter details

Rohit Umarjikar · Posted: Mon Apr 16, 2012 9:06 pm

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?

prino · Posted: Mon Apr 16, 2012 9:14 pm

Why should we be using Google when you can do it yourself?

dick scherrer · Posted: Mon Apr 16, 2012 9:21 pm

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.

Rohit Umarjikar · Posted: Mon Apr 16, 2012 9:23 pm

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.

dick scherrer · Posted: Mon Apr 16, 2012 9:24 pm

It has already been moved

One must be a global moderator to relocate topics. . .

Rohit Umarjikar · Posted: Mon Apr 16, 2012 9:25 pm

Yes Dick....

Thank you!!

Rohit Umarjikar · Posted: Wed Apr 18, 2012 8:34 pm

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.

dick scherrer · Posted: Thu Apr 19, 2012 1:21 am

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.

GuyC · Posted: Fri Apr 20, 2012 1:58 pm

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.

Rohit Umarjikar · Posted: Mon Apr 23, 2012 7:52 pm

Thanks you GuyC, just one question
How these options affects the performance? which one can be choosen for better performance?

GuyC · Posted: Mon Apr 23, 2012 9:32 pm

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.

Rohit Umarjikar · Posted: Tue Apr 24, 2012 4:29 pm

Thanks GuyC