Primary key on IBM i implicitly creates a 'data space index' internal object
that is uniquely keyed and links it to the 'data space' object itself (aka
member data object), so it's not wrong to think of it as a unique
constraint. In fact, they're identically implemented internally, so to me
they're the same, except that PK becomes part of the file object itself, not
a standalone index object.
Redundant index advice has been around forever and has bugged the heck out
of me. I consider it a bug, but it's not high enough on IBM's radar to be
addressed as it doesn't cause any functional issues (if you create an index
with same keys as an existing index, it'll share the existing access path so
you have no additional overhead, except for the clutter of yet another
useless file on your system; you're in luck in your case as system won't let
you create two unique constraints with identical keys).
There is more than one scenario that creates redundant index advice. One
valid one is where the sort sequence differs... but does the index advice
reflect that?
You are correct to open a PMR. I wish more people would do this.
One possibility is that system is creating an 'index out of index' or a
'sparse index' at run time and believes that since it created one, you don't
already have one. To me though, this is the case of left hand not knowing
what the right hand is doing.
I suppose if this is truly the scenario you're seeing AND you're on V6R1,
query optimizer is not far off, since you could create a sparse index on
V6R1... but would the index advice reflect that fact (i.e. told you which
specific keys to include in that sparse index). And would it use the newly
created sparse index?
I don't know, I'm getting ahead of myself here with conjectures.
It's far better to open a PMR and let IBM experts figure out what's
happening and if anything should be changed.
As for your CRTDUPOBJ test... that seems a bit bizarre. Looks like a
separate PMR is in order for that one.
Good luck and keep us posted.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Re: Index advisor advising index on primary key
I opened a PMR.
Here's an interesting tidbit. Just because, I did the following:
CRTDUPOBJ OBJ(DMITMMST) FROMLIB(DBMSTF) OBJTYPE(*FILE)
TOLIB(JEFF) CST(*NO) TRG(*NO)
Note that I said CST(*NO). A DSPFD on the newly created file says:
Number of constraints . . . . : 0
but further down the DSPFD says:
Access Path Description
Access path maintenance . . . : MAINT *IMMED
Unique key values required . : UNIQUE Yes
Access path journaled . . . . : No
Access path . . . . . . . . . : Keyed
Constraint Type . . . . . . . : NONE
Number of key fields . . . . : 1
Record format . . . . . . . . : MBRECORD
Key field . . . . . . . . . : ITNBR
Sequence . . . . . . . . : Ascending
Sign specified . . . . . : UNSIGNED
Zone/digit specified . . : *NONE
Alternative collating seq : Yes
So there's no 'constraint' but there's still a unique access path.
Furthermore I *was* able to do an ALTER TABLE and *add* a unique
constraint on ITNBR in this duplicated file.
Are we having fun yet?
rob@xxxxxxxxx wrote:
Keep me posted. One attendee at the session said he built the recommended
index and it still wanted him to build the recommended index. I'm curious
if that is the case, or the unique constraint, or ...
By the way, the person who really shines at these sessions is an IBMer by
the name of Tom McKinley. He's the "John Sears" of our time.
Rob Berendt
midrange.com
