Bart,

all excelelnt suggestions, unfortunately all tried already. Funny, db2advis gives me indexes that exist already and then when I apply an index or two that does not, it still does nothing to change the access path, even though I create the index and issue a runstats on table and indexes all. Rebind is the olnly thing I have not tried, good catch. Join column is the first column in the indthey are all type 2 indexes...

PL

To: "LazyDBA Discussion" <[Email address protected]
> From: "Coffman Bart \(STL\) " <db2udbdba-ezmlmshield-x37563539.[Email address protected]
> Date: Tue, 23 May 2006 09:51:20 -0500
> Subject: RE: Index usage
>
> Be sure you have done the runstats as mentioned below, and do a rebind
> on all affected packages. Assume your indexes are type-2? Do you have
> primary key indexes in place? Are the join columns in the leading
> position in any of the indexes?
>
> Also, try running some of the sql through db2advis tool to see what it
> says. Maybe you will see something there.
>
> J. Bart Coffman
> Application DBA
> Express Scripts, Inc.
> 314.702-7982 (w)
> 314.265-8883 (c)
> [Email address protected]
>
>
>
> -----Original Message-----
> From: DANIEL CALLAHAN [mailto:db2udbdba-ezmlmshield- Sent: Tuesday, May
> 23, 2006 9:42 AM
> To: LazyDBA Discussion
> Subject: Re: Index usage
>
>
>
>
>
> Can we assume you have done runstats?
> and maybe set the optimization to at least 3
> and make the table volatile, if the activity is high?
>
>
>
>
>
>
>
> "paul"
>
> <db2udbdba-ezmlms
>
> a.com> <[Email address protected]
>
>
> cc
> 05/23/2006 10:31
>
> AM
> Subject
> Index usage
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Hello all,
>
> Have a rather strange occerence here that is perplexing me and am in
> need
> of a little guidance. Running AIX 5.3 UDB "DB2 v8.1.1.80", "s041221",
> "U800400", and FixPak "8". I have many occurrences of specifying a join
> on
> two tables on an index, but the optimizer is determined to do a table
> scan.
> I would like to avoid optimizer hints as they could bite me in the
> preverbial rear down the road. I did find where I had the DFT_DEGREE
> set
> to one, shich of course is no paralellism at all. Are there any other
> DB
> parameters that you are aware of that I should be looking at before
> going
> after fix packs?
>
> TIA
>
> PL
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
> ******* Confidentiality Notice *******
> This email, its electronic document attachments, and the contents of its website linkages may contain confidential health information. This information is intended solely for use by the individual or entity to whom it is addressed. If you have received this information in error, please notify the sender immediately and arrange for the prompt destruction of the material and any accompanying attachments.
>
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page