Good points. Is there anything in your app scripts that messes with any
optimization parms?
If I didn't get anything different when doing steps below, I would open
a ticket with IBM. Some setting seems to be amiss, or else there is
corruption...
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 10:30 AM
To: LazyDBA Discussion
Subject: RE: RE: Index usage
Maybe even better drop the indices. run db2advis , create the new
indices
exactly like what db2advis says, runstats and rebind.
Is it possible this application is resetting the optimization level
before
executing ?
"Coffman Bart
\(STL\) "
<db2udbdba-ezmlms
To
a.com>
cc
05/23/2006 11:20
Subject
AM RE: RE: Index usage
If db2advis is calling out indexes that already exist, doesn't this mean
that he cannot see them? Anyone confirm this? But then again your prod
is acting the same way. Were all the db's created from a restore of one
another?
Seems like maybe you need to drop and re-create indexes???
J. Bart Coffman
Application DBA
Express Scripts, Inc.
314.702-7982 (w)
314.265-8883 (c)
[Email address protected]
-----Original Message-----
Sent: Tuesday, May 23, 2006 9:59 AM
To: LazyDBA Discussion
Subject: Fwd: RE: Index usage
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]
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.
DB2 & UDB email list listserv db2-l LazyDBA home page