IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • No replies
blink120
blink120
1 Post

Pinned topic Parent table lock with foreign key constraint CPF502E

‏2019-05-28T16:39:24Z | constraints; cpf502e insert locks;

Consider the following:

I have a foreign key referential constraint between po_header and po_spinstr on a the primary key column in po_header (ponumber).

       // get header
       if not @pLock ;
         exec sql select * into :myhdr from ssfdata.po_header
                    where ponumber = :@pPono ;
       else ;
         exec sql select * into :myhdr from ssfdata.po_header
                    where ponumber = :@pPono
                    with RS use and keep exclusive locks ;
       endif;      

       // get spec instructions for PO
       if not @plock ;
         exec sql declare phs cursor for
          select * from ssfdata.po_spinstr
                where ponumber = :@pPono order by instrseq ;
         exec sql open phs ;
         exec sql fetch phs for :nrs rows into :myhin ;
         exec sql close phs ;
       else ;
         exec sql declare phsl cursor for
          select * from ssfdata.po_spinstr
                where ponumber = :@pPono
                  order by instrseq
                  with RS use and keep exclusive locks ;
         exec sql open phsl ;
         exec sql fetch phsl for :nrs rows into :myhin ;  

 

The @plock condition is "true", and the rows are retrieved with locks. Now, when I try to insert rows into po_spinstr AFTER updating po_header (but before a commit) i get  a CPF502E error:   Message . . . . :   Referential constraints could not be validated for member 
   PO_SPINSTR.                                                                 
 Cause . . . . . :   The operation being performed on member PO_SPINSTR file   
   PO_SPINSTR in library SSFDATA failed. Constraint PO_SPINSTR_PO_HEADER_FK for
   dependent file PO_SPINSTR in library SSFDATA and parent file PO_HEADER in   
   library SSFDATA could not be validated. Record number 5 in member PO_HEADER 
   file PO_HEADER in library SSFDATA was locked by job                         
   099045/BRADEN/QPADEV004N. If the record number is zero, then file PO_HEADER 
   in library SSFDATA is locked by job 099045/BRADEN/QPADEV004N. If the job    
   name is *N, then the job name is not available.    

How can I get the inserts to work without ditching the locking strategy and/or the constraint?                                                             

Updated on 2019-05-28T17:41:52Z at 2019-05-28T17:41:52Z by blink120