PM78551: ABEND0C4 RC10 AT DSNXOTDA +0926 OCCURS FOR QUERY WITH A COMPLEX VIEW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• D2PE,ABND=0C4-00000010,U=IRIPRD01,M=N  ,C=101..RDS -RSCTCACH,
  M=DSNTFRCV,LOC=DSNXGRDS.DSNXOTDA+0926
  
  occurs for a query that references a complex view.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and 10 for z/OS users of           *
  *                 views that contain UNION/UNION ALL.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND0C4 RC00000010 DSNXOTDA OFFSET0926 *
  *                      may occur for an SQL statement that     *
  *                      references to a view that satisfies     *
  *                      the following conditions:               *
  *                      1. The view contains UNION/UNION ALL    *
  *                      2. The statement that references to     *
  *                      this view contains predicates that      *
  *                      can cause some of the branches of the   *
  *                      UNION/UNION ALL inside the view to be   *
  *                      pruned.                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  ABEND0C4 RC00000010 DSNXOTDA OFFSET0926 may occur for an
  SQL statement that references to a view that satisfies the
  following conditions:
  1. The view contains UNION/UNION ALL
  2. The statement that references to this view contains
  predicates that can cause some of the branches of the
  UNION/UNION ALL inside the view to be pruned.
  
  An example of the possible failing case is like :
  
  CREATE VIEW V1 (VC1) AS
                    SELECT C1 FROM T1 WHERE C1 = 1
                    UNION ALL
                    SELECT C2 FROM T2 WHERE C2 = 2
                    UNION ALL
                    SELECT C3 FROM T3 WHERE C3 = 3
                    UNION ALL
                    SELECT C4 FROM T4 WHERE C4 = 4;
  
  
  SELECT * FROM V1 WHERE VC1 IN (1,4);
  
  An abend may occur for the SELECT statement above because DB2
  does not handle the view correctly after the branches of the
  UNION/UNION ALL inside the view are pruned.
  

Problem conclusion

• DB2 was fixed to handle the view correctly when some of the
  branches of the UNION/UNION ALL inside view are pruned.
  
  Additional KeyWords: SQLUNION SQLUNIONALL SQLVIEW
                       SQLPRUNING
  

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:

  UK91561 UK91562

Modules/Macros

•    DSNXOTDA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91561

     UP13/03/01 P F302

• R910 PSY UK91562

     UP13/03/01 P F302

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.