PM24566: INZU063I UNSUPPORTED SELECT USING A WITH COMMOM-TABLE-EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In DB2 HPU for z/OS processing.
  Rows missing from the unloaded data where a logical unload
  involves an unsupported SQL statement starting with a
  common-table-expression followed by a fullselect with UNION.
  
  Only the part of the SQL statement before the first UNION
  keyword of the fullselect is passed on to DB2.
  
  For instance, with the following SQL statement:
  
  WITH
  MYTBL (MYCOL1) AS
  (
     SELECT 'MYCOL1-ROW1' FROM SYSIBM.SYSDUMMY1
  )
  SELECT
     MYCOL1 !! '-FIRST' AS SINGLE_COLUMN
     FROM   MYTBL
  UNION
  SELECT
     MYCOL1 !! '-SECOND'  AS SINGLE_COLUMN
     FROM   MYTBL
  OUTDDN OUTPUT
  FORMAT DSNTIAUL
  
  the statement passed on to DB2 is:
  
  WITH
  MYTBL (MYCOL1) AS
  (
     SELECT 'MYCOL1-ROW1' FROM SYSIBM.SYSDUMMY1
  )
  SELECT
     MYCOL1 !! '-FIRST' AS SINGLE_COLUMN
     FROM   MYTBL
  
  Additional reference: PB10913
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 High Performance Unload.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: Rows missing from the unloaded data     *
  *                      where a logical unload involves an      *
  *                      unsupported SQL statement starting      *
  *                      with a common-table-expression          *
  *                      followed by a fullselect with UNION.    *
  *                      Only the part of the SQL statement      *
  *                      before the first UNION keyword of the   *
  *                      fullselect is passed on to DB2.         *
  *                      For instance, with the following SQL    *
  *                      statement :                             *
  *                                                              *
  *                      WITH                                    *
  *                      MYTBL (MYCOL1) AS                       *
  *                      (                                       *
  *                         SELECT 'MYCOL1-ROW1' FROM            *
  *                       SYSIBM.SYSDUMMY1                       *
  *                      )                                       *
  *                      SELECT                                  *
  *                         MYCOL1 !! '-FIRST' AS SINGLE_COLUMN  *
  *                         FROM   MYTBL                         *
  *                      UNION                                   *
  *                      SELECT                                  *
  *                         MYCOL1 !! '-SECOND'  AS              *
  *                        SINGLE_COLUMN                         *
  *                         FROM   MYTBL                         *
  *                      OUTDDN OUTPUT                           *
  *                      FORMAT DSNTIAUL                         *
  *                                                              *
  *                      the statement passed on to DB2 is:      *
  *                                                              *
  *                      WITH                                    *
  *                      MYTBL (MYCOL1) AS                       *
  *                      (                                       *
  *                         SELECT 'MYCOL1-ROW1' FROM            *
  *                        SYSIBM.SYSDUMMY1                      *
  *                      )                                       *
  *                      SELECT                                  *
  *                         MYCOL1 !! '-FIRST' AS SINGLE_COLUMN  *
  *                         FROM   MYTBL                         *
  *                                                              *
  *                      PB 10913                                *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  inzhyacc module was fixed to concatenate all the parts of the
  fullselect.
  

Problem conclusion

• APPLY the PTF.
  

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:

  UK62315 UK62316 UK62317

Modules/Macros

•    INZUTILK
  

Fix information

• Fixed component name

  DB2 HI PERF UNL

• Fixed component ID

  5697F4400

Applicable component levels

• R310 PSY UK62315

     UP10/11/24 P F011

• R320 PSY UK62316

     UP10/11/24 P F011

• R410 PSY UK62317

     UP10/11/24 P F011

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.