RE: Referential Constraints and RPG - couple questions

Thanks!
Important thing is that ALL FILES THAT constraints applay on MUST be 
journaled in same journal... My top parent file wasn't journaled so delete 
wasn't posibile! Now DELETE from RPG and SQL work as expected!! Yes!

Well with almost two years expirience in RPG can say just this: I'm still 
at the bigining! It's interesting how sometimes good and modern RPG skils 
are not only condition to finish job well! There is complete iSeries 
universe of things that programer has to know and which are not clearly 
described in literature :(

Best regards!
Igor Bešlić, dipl. ing. rač. 
VOLKSBANK d.d.
OJ Informatika
Zelinska 2, 10000 Zagreb
tel: +385 1 6326422



"MD, Sadique" <Sadique.MD@xxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
21.04.2005 14:13
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Referential Constraints and RPG  - couple questions






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.
-- 
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.