Re: PK's enforced via constraint or unique indexes?

I disagree with setting up separate unique indexes to enforce integrity, 
but you just can't get some people to get past 1980's technology. Software 
vendors are the last to make changes.
I have seen instances where people have deleted the logical files 
(separate unique indexes) for initial file conversions.  Then they add on 
the indexes.  And ignore the message that the duplicate key situation 
exists.  Some vendors don't even specify UNIQUE on these indexes.  They 
assume that since they do a chain prior to an add then all is well.  The 
problem is when the customer does an add on to convert data, or batch feed 
from EDI, etc and they muck it up.  Maybe they assume that the write 
should fail if there is a duplicate?  Anyway, without the constraint, or 
the UNIQUE, you're toast.  SSA's BPCS is one such product (see file 
IIML02).

CREATE TABLE ROB/RYANHUNT (MYKEY CHAR (1 ) NOT NULL WITH DEFAULT,
MYDATA CHAR (5 ) NOT NULL WITH DEFAULT, PRIMARY KEY (MYKEY)) 
Then in iNav expand schema ROB, expand Constraints and I see it there.

However, if I create it with this DDS
                            UNIQUE
R RYANHUNTR 
  MYKEY          1A 
  MYDATA         5A 
K MYKEY 
Then I don't.  However, if I expand tables instead of Constraints, wait... 
 No, there's nothing there that will tell you that infomation either.  I 
suggest you DCR IBM into providing this information.  Actually if you use 
the "Generate SQL" option you will get
--  SQL150B   10   REUSEDLT(*NO) in table RYANHUNT in ROB ignored. 

        MYKEY CHAR(1) CCSID 37 NOT NULL DEFAULT '' , 

        MYDATA CHAR(5) CCSID 37 NOT NULL DEFAULT '' , 

        PRIMARY KEY( MYKEY ) ) 

        NOT VOLATILE 

        RCDFMT RYANHUNTR  ;

Granted that last RCDFMT option is rather new to i5/os.

I think what you are looking for in iNav is what you see here in DSPFD
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 . . . . . . . . . . . . . . . :            RYANHUNTR
    Key field . . . . . . . . . . . . . . . . :            MYKEY 
      Sequence  . . . . . . . . . . . . . . . :            Ascending
      Sign specified  . . . . . . . . . . . . :            UNSIGNED 
      Zone/digit specified  . . . . . . . . . :            *NONE 
      Alternative collating sequence  . . . . :            No 
  Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ     *HEX 
  Language identifier . . . . . . . . . . . . : LANGID     ENU 

And, there are parts in iNav that will show you the number of key fields. 
However, I haven't found where it will tell you what those key fields are.

Rob Berendt