IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DSNUTILB various parameter details


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Mon Apr 16, 2012 9:06 pm
Reply with quote

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
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1315
Location: Vilnius, Lithuania

PostPosted: Mon Apr 16, 2012 9:14 pm
Reply with quote

Why should we be using Google when you can do it yourself?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon Apr 16, 2012 9:21 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Mon Apr 16, 2012 9:23 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon Apr 16, 2012 9:24 pm
Reply with quote

It has already been moved icon_smile.gif

One must be a global moderator to relocate topics. . .
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Mon Apr 16, 2012 9:25 pm
Reply with quote

Yes Dick....icon_smile.gif Thank you!!
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Wed Apr 18, 2012 8:34 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Thu Apr 19, 2012 1:21 am
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Apr 20, 2012 1:58 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Mon Apr 23, 2012 7:52 pm
Reply with quote

Thanks you GuyC, just one question
How these options affects the performance? which one can be choosen for better performance?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Apr 23, 2012 9:32 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Tue Apr 24, 2012 4:29 pm
Reply with quote

Thanks GuyC
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using the Jobname parameter in a Qual... ABENDS & Debugging 1
No new posts Demand with DEADLINE TIME parameter CA Products 4
No new posts JCL SORT to compress the student's de... DFSORT/ICETOOL 7
No new posts Option DYNALLOC second parameter. DFSORT/ICETOOL 11
No new posts Writing the output file name from a p... JCL & VSAM 7
Search our Forums:

Back to Top