PI70164: SQLCODE -150 IS MISTAKENLY ISSUED FOR AN INSERT OR UPDATE TARGETVIEW WHERE VIEW IS DEFINED ON AN ARCHIVE-ENABLED TABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -150 is mistakenly issued for an INSERT or UPDATE target
  view where the view is defined on an archive-enabled table and
  the built-in global variable MOVE_TO_ARCHIVE is set to 'E'.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * DB2 12 for z/OS users who insert, update, delete, or merge   *
  * on a view and the view is defined on an archive-enabled      *
  * table                                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * SQLCODE -150 can be incorrectly issued if the following      *
  * conditions are met:                                          *
  *                                                              *
  *      1. an INSERT, UPDATE, DELETE, or MERGE statement target *
  * is a view                                                    *
  *      2. the view is defined on an archive-enabled table and  *
  * the same view appears at least twice in the data change      *
  * operations                                                   *
  *      3. for dynamic statement, ARCHIVESENSITIVE bind option  *
  * is set to 'YES' and SYSIBMADM.GET_ARCHIVE built-in global    *
  * variable                                                     *
  *          is set to 'Y'                                       *
  *     4.  for static statement, ARCHIVESENSITIVE bind option   *
  * is set to 'YES'                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SQLCODE -150 can be incorrectly issued if the following
  conditions are met:
  
       1. an INSERT, UPDATE, DELETE, or MERGE statement target is
  a view
       2. the view is defined on an archive-enabled table and the
  same view appears at least twice in the data change operations
       3. for dynamic statement, ARCHIVESENSITIVE bind option is
  set to 'YES' and SYSIBMADM.GET_ARCHIVE built-in global variable
           is set to 'Y'
      4.  for static statement, ARCHIVESENSITIVE bind option is
  set to 'YES'
  
  Here is an example where SQLCODE -150 is issued:
  
     Assuming ARCHIVESENSITIVE bind option is set to 'YES' and
  SYSIBMADM.GET_ARCHIVE built-in global variable  is set to 'Y'.
  
      ALTER TABLE WAREHOUSE_AET
             ENABLE ARCHIVE USE WAREHOUSE_AET_ARCH;
      COMMIT;
  
      CREATE VIEW VIEW_WAREHOUSE_AET AS
           SELECT * FROM  WAREHOUSE_AET
                  WHERE WAREHOUSE_YTD > US_DOLLAR(100000);
      COMMIT;
  
      SET SYSIBMADM.MOVE_TO_ARCHIVE = 'E';
  
      INSERT INTO VIEW_WAREHOUSE_AET SELECT * FROM
  VIEW_WAREHOUSE_AET
  
  WHERE WAREHOUSE_ID = '0000000008' ;
  
  DB2 mistakenly issues SQLCODE -150 to the above INSERT
  statement.
  

Problem conclusion

• DB2 has been modified to allow INSERT, UPDATE, DELETE, or MERGE
  statement as described above successful.
  
  Additional keywords: SQLCODE150 SQLARCHIVE SQLINSERT SQLUPDATE
  SQLDELETE SQLMERGE
  

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:

  UI42740

Modules/Macros

• DSNXOVC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI42740

     UP16/12/06 P F612

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.