RE: Referential Constraints and RPG - couple questions

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Should have read the message a little closer.

To expand upon what Sadique is saying.  It is my understanding that you can't 
use CLRPFM to clear a parent file with referential constraints in place.

But you should be able to use SQL or RPG to delete all the individual records.

In your original message, you say the message is the same.  Are you sure about 
that?  As Sadique indicates, the message you posted makes sense if you were 
trying to use CLRPFM, but it doesn't make sense if you deleting all records 
individually.

FYI: Related to the prohibition of CLRPFM.  You can't open a parent file in RPG 
as OUTPUT - only, it must be UPDATE.  In addition, you can't use the file as an 
output file for anything, ie. commands SQL results, ect.


HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 


Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of 
> Igor.Beslic@xxxxxxxxxxxx
> Sent: Thursday, April 21, 2005 6:36 AM
> 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.
> 
>