PI31472: INCORROUT AFTER UPDATE OF XML COLUMN USING XMLMODIFY ONLY ONE ROW UPDATED WHEN MORE QUALIFY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrout after UPDATE of XML column using XMLMODIFY only one
  row is updated ; even though more rows qualify and the message
  indicates all rows were updated
  SELECT executed after update with same WHERE clause shows only
  TOTAL ROWS - 1 is being updated
  
  Additional symptoms and search keywords
  XMLINCORR XMLUPDATE
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of          *
  *                 XMLMODIFY function.                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using XMLMODIFY function to update *
  *                      XML documents in multiple rows, if the  *
  *                      SET clause includes a sideway reference *
  *                      of the table, then only one row is      *
  *                      updated, even though the message says   *
  *                      SUCCESSFUL UPDATE OF more then one      *
  *                      ROW(S).                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using XMLMODIFY function to update XML documents in
  multiple rows, if the SET clause includes a sideway reference
  of the table, then only one row is updated, even though the
  message says SUCCESSFUL UPDATE OF more then one ROW(S).
  
  For example:
    The table T1 is create by following CREATE statement.
  
      CREATE TABLE T1
      (
        I1   INTEGER,
        X1   XML
      );
  
    Three records are inserted into the table as below.
  
      INSERT INTO T1 VALUES(1, '<a><b>n</b></a>');
      INSERT INTO T1 VALUES(2, '<a><b>n</b></a>');
      INSERT INTO T1 VALUES(3, '<a><b>n</b></a>');
  
    Below is the UPDATE statement to change the value of b node
    to 'y', if the value of b node is not already in 'y'.
  
      UPDATE T1 A
        SET X1 =
            XMLMODIFY(
                'replace value of node /a/b with $Z',
                'y' AS "Z")
        WHERE (SELECT XMLCAST(XMLQUERY('/a/b' PASSING X1)
                      AS CHAR(1))
               FROM T1 E
               WHERE A.I1 = E.I1)
           <> 'y';
  
    The UPDATE statement returns zero SQLCODE with following
    message.
  
      SUCCESSFUL UPDATE     OF        3 ROW(S)
  
    But after querying the data, only the third row is updated as
    below.
  
      <?xml version="1.0" encoding="IBM037"?><a><b>n</b></a>
      <?xml version="1.0" encoding="IBM037"?><a><b>n</b></a>
      <?xml version="1.0" encoding="IBM037"?><a><b>y</b></a>
  

Problem conclusion

• DB2 is modified so all the qualified rows are updated.
  
  Additional keywords: SQLXML XMLALL
  

Temporary fix

Comments

APAR Information

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

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

  UI25537 UI25538

Modules/Macros

•    DSNILREP DSNNQMD  DSNNQTOP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI25537

     UP15/03/17 P F503

• RB10 PSY UI25538

     UP15/03/17 P F503

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.