PH06984: CM BATCH COMPARE WSL RUN SQLCODEN206 / SQL ERROR -206 RUN SQLID CTE COMMON TABLE EXPRESSION CREATE VIEW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When a change includes a create of a view containing a COMMON
  TABLE EXPRESSION, and the RUN SQLID contains either an SQLID or
  <NONE>, the table qualifier 'COLS' of a column may be
  incorrectly replaced by 'SYSIBM.SYSCOLUMNS', and the WSL run
  job may result in the following error:
  DSNT408I SQLCODE = -206, ERROR:  SYSIBM.SYSCOLUMNS.column_name
  IS NOT VALID IN THE CONTEXT WHERE IT IS
  USED
  DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING
  ERROR
  DSNT416I SQLERRD    = -225  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'FFFFFF1F'  X'00000000'  X'00000000'
  X'FFFFFFFF'
           X'00000000'  X'00000000' SQL DIAGNOSTIC
  INFORMATION
  

Local fix

• Run with blank RUN SQLID.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the Db2 Administration Tool for     *
  *                 z/OS who use Change Management to import     *
  *                 and analyze a change, or users of Db2        *
  *                 Object Comparison Tool for z/OS.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: A view containing a CTE may be          *
  *                      analyzed incorrectly when RUN           *
  *                      SQLID is not blank (either an           *
  *                      sqlid or <NONE>). A column              *
  *                      COLS.XXX becomes                        *
  *                      COMMAND POWERINP                        *
  *                      "SYSIBM".SYSCOLUMNS.XXX, and            *
  *                      the create is rejected by DB2           *
  *                      at run time with return code            *
  *                      -206.                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When a view contains a CTE, and RUN SQLID is not blank (either
  a sqlid or <NONE>), the analyze job may change a column
  COLS.XXX to "SYSIBM".SYSCOLUMNS.XXX.  This may result in the
  following error at run time:
  
  DSNT408I SQLCODE = -206, ERROR:
  SYSIBM.SYSCOLUMNS.column_name
  IS NOT VALID IN THE CONTEXT WHERE
  IT IS USED
  
  When RUN SQLID is blank, this column is generated correctly.
  

Problem conclusion

• Problem has been resolved.
  

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:

Modules/Macros

•    ADB2CM3
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI61644

     UP19/03/06 P F903

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.