Re: Foreign key constraint problem

["Brian Johnson" <brian.johnson.mn@xxxxxxxxx>]

Why is the DDL-created table using a different sort sequence than the
DDS-created file?  My experience is that both types use SRTSEQ *HEX.


On 5/31/07, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx> wrote:
> I'm horsing around with redefining some files with SQL instead of DDS
> based
> on the redbook authored by Birgitta and others.
>
> I retrieved the SQL for my item master file, prettied it up, and created
> it
> in a test library.  I put some constraints on fields and everything.
>
> I decided to try a foreign key, like this:
>
> ALTER TABLE JEFF/DMITMMST
>   CONSTRAINT CatalogFK  FOREIGN KEY (CATCD1)
>                         REFERENCES DMCATMST (CTCODE)
>                         On Delete Restrict
>                         On Update Restrict ;
>
> CATCD1 is the field name in the item master, and CTCODE is the field name
> in
> the catalog master.  Both defined 1A.  The constraint fails with the
> following in the job log:
>
> Message ID . . :   CPD32B9       Severity . . . :   20
> Message type . :   Diagnostic
> Date sent  . . :   05/31/07      Time sent  . . :   14:42:12
>
> Message . . . . :   Physical file constraint not valid.
> Cause . . . . . :   An add physical file constraint operation was not
> valid
> because of reason 1.
>     1 -- The sort sequence tables are not compatible.
>
> <snip>
>
> Recovery  . . . :
>     1 -- Referential constraints between file DMCATMST in library JEFF and
> file DMITMMST in library JEFF cannot be added.  The sort sequence tables
> must match before a referential constraint can be added.
>
>
> The SRTSEQ of the SQL defined item master is:
>
> Sort Sequence                 SRTSEQ
>   Table Name  . . . . . . . :            QLA10025S
>     Library . . . . . . . . :              QSYS
>
> The SRTSEQ of the DDS defined catalog master is:
>
> Sort Sequence . . . . . . . : SRTSEQ     *HEX
>
> Just for fun I tried a CHGPF FILE(JEFF/DMITMMST) SRTSEQ(*HEX).  Does not
> work, I get "Parameter not allowed with SRCFILE(*NONE)".  Both files are
> CCSID 37.
>
> Does this mean I cannot put any foreign key constraints from an SQL file
> to
> a DDS file?
>
> Thanks.
>
> PS.  Interestingly, I assumed I would get level checks if I tried to use
> this new file.  I don't.  I can do a CPYF from my production DDS item
> master
> to this SQL item master without a hiccup.  The application program I tried
> against it also works without issue.  I didn't expect that.
>
> --
> Jeff Crosby
> UniPro FoodService/Dilgard
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
>
> The opinions expressed are my own and not necessarily the opinion 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.