Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DSNUTILB various parameter details

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Apr 16, 2012 9:06 pm    Post subject: DSNUTILB various parameter details
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

Active Member


Joined: 07 Feb 2009
Posts: 982
Location: Oostende, Belgium

PostPosted: Mon Apr 16, 2012 9:14 pm    Post subject:
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

Site Director


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

PostPosted: Mon Apr 16, 2012 9:21 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Apr 16, 2012 9:23 pm    Post subject:
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

Site Director


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

PostPosted: Mon Apr 16, 2012 9:24 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

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

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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Apr 18, 2012 8:34 pm    Post subject:
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

Site Director


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

PostPosted: Thu Apr 19, 2012 1:21 am    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Apr 20, 2012 1:58 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Apr 23, 2012 7:52 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Apr 23, 2012 9:32 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Apr 24, 2012 4:29 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts How to display value of an exit param... lind sh CICS 2 Sun Oct 09, 2016 12:28 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm
No new posts Send parameter for macro STIMERM from... Daniel Teolotitla PL/I & Assembler 13 Tue Jul 05, 2016 9:23 pm
No new posts COND parameter vs IF THEN ELSE Alks JCL & VSAM 11 Fri Jun 17, 2016 5:28 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us