RE: Referential Constraints and RPG - couple questions

["MD, Sadique" <Sadique.MD@xxxxxxx>]

Hi,

The error I could see from your mail " CPF3134" is having a reason code '1'

their meanings are:  
    1 -- A clear operation was requested and the file is a parent file 
with  established and enabled referential constraints.   


That is you are trying to clear the parent file not the dependent file, so
is the error


My understanding on Constraint type (*REFCST) and Delete (*Cascade) is 

When you have a parent file and say you have two records EMPNO = 100 and 101
And a dependent file has values 

EMPNO   EMPNA

100     XXXXXX
        YYYYYY
        ZZZZZZ

101     Sadiq         

Then when you try delete a record in parent file for EmpNo = 100, then all
the three records in dependent file are deleted, remaining with only for
record whose EMPNO = 101

When I tried to clear the dependent file , it gets cleared but when I try to
clear parent file then I have that error message " CPF3134"

Steps to add Constraints are

Step 1 

ADDPFCST FILE(HSCDC01/EMPDTL) TYPE(*REFCST) KEY(EMPNO) PRNFILE(EMPPF)
DLTRULE(*CASCADE)

Step 2 

Journal both the parent and dependent files in the same journal 

Step 3 

Try to update and delete the files, you may also try simply with DFU 

Thanks
Sadiq ....

  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Igor.Beslic@xxxxxxxxxxxx
Sent: Thursday, April 21, 2005 4:06 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Referential Constraints and RPG - couple questions 

hello everybody,

I'm just working on one program that maintans relational database with 
defined referential constraints. This is simplified db:
tableA
----------
fieldA1 - UNIQUE (PARENT QEY)
fieldA2
fieldA3

tableB
----------
fieldB1 - UNIQUE (PARENT QEY)
fieldA1 - (FOREIGN KEY - tableA/fieldA1) - *delete CASCADE, *update 
RESTRICT
fieldB3

tableC
----------
fieldC1 - UNIQUE (PARENT QEY)
fieldB1 - (FOREIGN KEY - tableB/fieldB1) - *delete CASCADE, *update 
RESTRICT
fieldA1 - (FOREIGN KEY - tableA/fieldA1) - *delete CASCADE, *update 
RESTRICT
fieldC2

tableD
----------
fieldD1 - UNIQUE (PARENT KEY)
fieldD2

tableCD - UNIQUE PARAENT KEY(fieldC1,fieldD1)
-----------
fieldC1 -  (FOREIGN KEY - tableC/fieldC1) - *delete CASCADE, *update 
RESTRICT 
fieldD1 - (FOREIGN KEY - tableD/fieldD1) - *delete CASCADE, *update 
RESTRICT
ValueCD

When I created db and defined referential constraints I believed that 
DELETE operation on TOP PARENT file tableA would cascade delete apropriate 
records in tableB, tableC, tableCD. Althougth beleieved that DELETE 
operation on any level (eg. in tableC) would DELETE referenced records 
from depending tables.

Now I have big problem...

1st I couldn't start my RPG program until I didn't START JOURNALING all 
tables that I used for INSERT.
DO ANYBODY KNOWS WHY???

2nd I Can't DELETE ANYTHING from ANY TABLE. There is message:
  
Message ID . . . . . . :   CPF3134       Severity . . . . . . . :   40  
Message type . . . . . :   Escape  
Date sent  . . . . . . :   21/04/05      Time sent  . . . . . . : 12:23:01 
 
  
Message . . . . :   Referential constraint error processing member PV_BRC. 
 
Cause . . . . . :   The operation being performed on member PV_BRC file 
PV_BRC
  in library VBIGOR failed because of reason code 1. The reason codes and  
 
  their meanings are:  
    1 -- A clear operation was requested and the file is a parent file 
with 
  established and enabled referential constraints.  
    2 -- The file specified on the TOFILE parameter is a dependent file 
with 
  established and enabled referential constraints.  
    3 -- Referential constraint  prevented the initialization of a default 
 
  record in member *N of dependent file *N in library *N because a 
matching 
  key value was not found in member *N of parent file *N in library *N.  
    4 -- A copy operation was requested and the file is a dependent file 
with 

The point of designing and establishing constraints was to make easier 
database clearing (clearing top parent file would empty all other files in 
chain). Now I don't know what mistake I did or where I did it?? I read all 
abauth constraints in IBM InformationCenter even used them but on Informix 
and I have no any clue why this wont work.
I tried clrpfm, DELETE in strsql, DELETING IN RPG and always same error.
For now the only way to delete files is disableing constraints and that is 
little stupid (why should I define them if i'm going to disable them when 
I need to delete something).

Please HELP or suggest me where to find examples. I found a lot of 
interesting things in IBM's Modernizing ILE Applications but still I 
didn't found anything that could help me with this.


Thanks a lot!
Igor Bešlić, dipl. ing. rač. 
VOLKSBANK d.d.
OJ Informatika
Zelinska 2, 10000 Zagreb
tel: +385 1 6326422
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.