Re: Index advisor advising index on primary key

[vhamberg@xxxxxxxxxxx]

Jeff

If you can put  a job in debug mode - just run STRDBG - that uses this thing, see what the job log says. If there are messages about building a temporary file and a termporary index, see what those say. It might be building an index over the temporary - I think that might cause an advise to happen, not sure, but there is no longer, it seems, the connection to the original table.

Vern

-------------- Original message -------------- 
From: Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx> 

> There is a view that joins this table with another table of "old" item 
> numbers (OLITMMST). This table's name is DMITMMST. That view was 
> created like this: 
>
> CREATE VIEW ALITMMST AS ( 
> SELECT A.*,CAST(00010101 AS NUMERIC(8,0)) AS DLDAT FROM DMITMMST A 
> UNION ALL 
> SELECT B.* FROM OLITMMST B ) 
>
> As you can see, OLITMMST has 1 additional field, the deleted date. I do 
> not use ALITMMST anywhere in production RPG, the buyers use that view 
> for querying. 
>
> The file is not used in any WITH AS, but it is definitely used in SELECTS. 
>
> Vernon Hamberg wrote: 
> > Hi Jeff 
> >
> > Is this table used in any nested view situations? Like a WITH AS 
> > (correlated table expression) or as the file in a SELECT (nested 
> > table expression)? Or is there a view defined over it that you 
> > further use in some indirect way? In the past I have seen where an 
> > index is being created over a temporary table - and this can cause 
> > the advice. I was told at COMMON that this should not happen, but I 
> > suggest calling IBM with lots of information about the use. Does the 
> > index advice tell you which statement caused the advice? 
> >
> > HTH 
> > Vern 
> >
> > At 07:42 AM 4/11/2008, you wrote: 
> >
> >
> > > Yes, It's DDL defined and yes, the suggested index is the exact primary 
> > > key, ITNBR. That's the one and only primary key field. Weird, huh? 
> > >
> > > Wilt, Charles wrote: 
> > >
> > > Jeff, 
> > >
> > > IIRC, this will sometimes happen with a DDS defined file because 
> > > an SQL index has more statistical 
> > > information that the query engine can use. 
> > >
> > > But you say your file is a DDL defined table....so I don't know.... 
> > >
> > > Is the suggested index the exact primary key? How many fields in the PK? 
> > >
> > > Charles Wilt 
> > > Software Engineer 
> > > CINTAS Corporation - IT 92B 
> > > 513.701.1307 
> > >
> > > wiltc@xxxxxxxxxx 
> > >
> > >
> > >
> > >
> > > -----Original Message----- 
> > > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l- 
> > > bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby 
> > > Sent: Friday, April 11, 2008 8:30 AM 
> > > To: Midrange Systems Technical Discussion 
> > > Subject: Index advisor advising index on primary key 
> > >
> > > At our LUG meeting earlier this week a fellow midranger (Thanks Rob!) 
> > > gave a presentation on things you might not have known about on the i. 
> > > One of them was iNav index advisor, which I did not know about. 
> > >
> > > I cleaned out the advice since much of it was old so it would start fresh. 
> > >
> > > On our item master file, it is advising that I create an index on field 
> > > ITNBR. This file is an SQL table created via DDL (not DDS) and ITNBR is 
> > > the primary key. Does this advice make sense? Why would it advise an 
> > > index on the primary key? 
> > >
> > > Thanks. 
> > >
> > > -- 
> > > Jeff Crosby 
> > > UniPro Foodservice/Dilgard 
> > > 260-422-7531 
> > > Opinions expressed are my own and not necessarily those of my company. 
> > > Unless I say so. 
> > >
> > >
> > > -- 
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> > > list 
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> > > To subscribe, unsubscribe, or change list options, 
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> > > Before posting, please take a moment to review the archives 
> > > at http://archive.midrange.com/midrange-l. 
> > >
> > >
> > >
> > >
> > >
> > > This e-mail transmission contains information that is intended to 
> > > be confidential and privileged. If you receive this e-mail and you 
> > > are not a named addressee you are hereby notified that you are not 
> > > authorized to read, print, retain, copy or disseminate this 
> > > communication without the consent of the sender and that doing so 
> > > is prohibited and may be unlawful. Please reply to the message 
> > > immediately by informing the sender that the message was 
> > > misdirected. After replying, please delete and otherwise erase it 
> > > and any attachments from your computer system. Your assistance in 
> > > correcting this error is appreciated. 
> > >
> > >
> > > -- 
> > > Jeff Crosby 
> > > UniPro Foodservice/Dilgard 
> > > 260-422-7531 
> > > Opinions expressed are my own and not necessarily those of my 
> > > company. Unless I say so. 
> > > -- 
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> > > To subscribe, unsubscribe, or change list options, 
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> > > Before posting, please take a moment to review the archives 
> > > at http://archive.midrange.com/midrange-l. 
>
> -- 
> Jeff Crosby 
> UniPro Foodservice/Dilgard 
> 260-422-7531 
> Opinions expressed are my own and not necessarily those of my company. Unless I 
> say so. 
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l.