× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.