Referential integrity against the system cross reference tables.

[rob@xxxxxxxxx]

Ok,

I have a file.  In this file are four fields:  KEY, DESCRIP. LIBRARY, FILE,
FIELD.  It is a trading partner file.  Sample records might be:

KEY  DESCRIP    LIBRARY     FILE        FIELD
1    Customer   BPCSF       RCM         CCUST
2    Vendor     BPCSF       AVM         VENDOR
...
I'm really big into RI and whatnot and not having my programs be
responsible for validating the data and have someone screw it up with
UPDDTA or something.

So what I want is that no record can be added, or changed if the
LIBRARY/FILE/FIELD is not in QSYS2/SYSCOLUMNS.  I tried putting a RI check
but had a huge lock time.  Then I got concerned that a RCLSTG SELECT
(*DBXREF) would get violently ill.  So I thought about a check constraint.
These are really cool for simple stuff.  For example, UPDDTA will ignore
the VALUES defined via DDS but it obey's a check constraint.  Like
ADDPFCST FILE(ROB/LOGIN) TYPE(*CHKCST) CST(LOGPWEX) CHKCST('LOGPWEX IN
(''0'', ''1'')')
or
ADDPFCST FILE(ROB/TPTYPE) TYPE(*CHKCST) CST(TPTTYPE) CHKCST('TPTTYPE <> ''
'' AND TPTTYPE IS NOT NULL')

The above works great.  However more complex ones like:
ADDPFCST FILE(ROB/TPTYPE) TYPE(*CHKCST) CST(File_Library_Field) CHKCST
('TPTLIB,TPTFILE, TPTFIELD IN(SELECT DBILIB,DBIFLD, DBIFLD FROM
QSYS2/SYSCOLUMNS)')
or
ADDPFCST FILE(ROB/TPTYPE) TYPE(*CHKCST) CST(File_Library_Field) CHKCST
('TPTLIB CONCAT TPTFILE CONCAT TPTFIELD IN(SELECT DBILIB CONCAT DBIFLD
CONCAT DBIFLD FROM QSYS2/SYSCOLUMNS)')
won't.  You'll get messages about subselects and stuff like that.  Frankly
I think we are limited to:  < > = <> <= !< !> != >= ¬< ¬> ¬= IN.  Oh well,
I guess a trigger comes into play.  Any other suggestions?

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin