RE: Referential Constraints and RPG - couple questions

Igor,

As far as journaling goes, yes commitment control is required when using 
referential constraints with update or delete actions other than *RESTRICT.

Now, you say you've started journaling.  But have you also told RPG to use 
commitment control, via the COMMIT keyword on the f-spec, and have you done a 
STRCMTCTL prior to starting the RPG program?  Don't forget to use the COMMIT 
op-code in your RPG program.

If the above has already been done, can you tell us which table (tableA, 
tableB, tableC, tableD, tableCD) corresponds to the PV_BRC file mentioned in 
your message.


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