PM94844: SQLCODE -530 WHEN UPDATING PARTITIONED T/S CONTAINING BRF AND RRF DATA AND USING REFERENTIAL INTEGRITY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The customer receives a SQLCODE -530 when updating rows in a
  classic partitoned tablespace under the following conditions:
  
  (1) the partitioned tablespace is a child table
  (2) all partitions in the child table contain data in RRF format
  except 1 partition, which is in BRF
  (3) the parent table is a classic segmented tablespace
  containing data in RRF format
  (4) An UPDATE is done to data in an RRF formated partition that
  modifies the partition key to values that belong in the BRF
  partition resulting in the following message:
  
  DSNT408I SQLCODE = -530, ERROR:  THE INSERT OR UPDATE VALUE OF
  FOREIGN KEY F7741B16 IS INVALID
  
  If the same UPDATE statement is run and all partitions are in
  RRF format as well as the parent, the UPDATE is successful.
  
  
  Keywords:
  
  SQLCODE530
  SQLRI
  SQLUPDATE
  

Local fix

• The customer reorged their partitoned tablespace and so that
  the data in all parttions were in RRF format.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS users of referential        *
  *                 integrity, with dependent (child) table      *
  *                 containing variable length columns, where    *
  *                 SQL UPDATE of foreign key column moves row   *
  *                 from reordered row format (RRF) partition    *
  *                 to basic row format (BRF) partition or vice  *
  *                 versa                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -530 (new foreign key has no    *
  *                      parent key) may be issued by mistake    *
  *                      when SQL UPDATE of foreign key column   *
  *                      moves row from RRF partition to BRF     *
  *                      partition or vice versa                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  During SQL UPDATE of the foreign key column that involved cross-
  partition update such that the source and target partitions had
  different row formats, DB2 issued SQLCODE -530 (new foreign key
  has no parent key) even though the update should have been
  allowed.
  
  The reason for this problem is that the code that extracts the
  foreign key from the new row assumed that the target partition
  had the same row format as the source partition, which resulted
  in an incorrectly extracted foreign key value, which did not
  have a match in the parent table.
  
  For this problem to happen, the foreign key itself need not be
  of variable length. The problem may happen as long as the child
  table has one or more variable length columns.
  

Problem conclusion

• Code has been modified to work with the correct row format when
  extracting the foreign key from the new row during SQL UPDATE
  of the foreign key column.
  
  To bypass the problem until this APAR is applied, REORG can be
  run (potentially with the ROWFORMAT option) to ensure that the
  row formats of the source and target partitions match.
  
  Additional keywords: SQLCODE530 SQLUPDATE
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI11767 UI11768 UI11769

Modules/Macros

• DSNILREP DSNIRELK DSNIRELV
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI11767

     UP13/11/13 P F311 ½

• RB10 PSY UI11768

     UP13/11/13 P F311 ½

• R910 PSY UI11769

     UP13/11/13 P F311 ½

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.