PI53174: ABEND04E RC00E70005 IN DSNXGRM1 M111 MAY HAPPEN FOR A NATIVE SQLPL STATEMENT WITH A FOR LOOP AND REFERENCING A VIEW

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00E70005 IN DSNXGRM1 M111 MAY HAPPEN FOR A NATIVE
  SQLPL STATEMENT WITH A FOR LOOP AND REFERENCING A VIEW AND
  THE VIEW REFERENCES THE SPECIAL REGISTER THAT IS DATE OR TIME
  RELATED
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of a native *
  *                 SQL procedure or compiled SQL scalar         *
  *                 function that contains a FOR statement with  *
  *                 a view referencing a special register        *
  *                 CURRENT DATE or CURRENT TIMESTAMP.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00E70005 at the location     *
  *                      DSNXGRDS DSNXGRM1 M111 could happen     *
  *                      when creating a native SQL procedure or *
  *                      compiled SQL scalar function satisfying *
  *                      all of the following conditions:        *
  *                                                              *
  *                      1. It contains a FOR statement.         *
  *                      2. The FOR statement references a view. *
  *                      3. The view references a special        *
  *                         register CURRENT DATE or             *
  *                         CURRENT TIMESTAMP.                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1 (C1 INT, C2 INT)
  #
  
  CREATE VIEW V1(C1, C2) AS
    SELECT CURRENT TIMESTAMP, C2
    FROM T1
  #
  
  CREATE PROCEDURE SP1(INOUT NUM  INT)
  BEGIN
    FOR T AS CURS1 CURSOR FOR
        SELECT V1.C1
        FROM V1
      DO
        SET NUM = 1;
      END FOR;
  END
  #
  
  DB2 did not correctly process the aforementioned FOR statement,
  and this caused the ABEND.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  FOR statement, so there will be no ABEND.
  
  Additional keywords: SQLSP SQLNATIVESQLPL SQLUDF SQLFORLOOP
                       SQLTIMESTAMP SQLDATE
  

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:

  UI34715 UI34716

Modules/Macros

• DSNXOYFR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI34715

     UP16/02/10 P F602

• RB10 PSY UI34716

     UP16/02/10 P F602

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.