PM78213: ABEND0C7 RC0 DSNXRINT +2304 WHEN USING TIMESTAMPDIFF FUNCTION WITH DSNTIAUL.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND0C7 RC0 DSNXRINT +2304 when using TIMESTAMPDIFF function
  with DSNTIAUL.
  Simple query produces SQLCODE -171 outside of DSNTIAUL. Within
  DSNTIAUL, this simple query results in ABEND0C7 RC0.
  SELECT
  DIGITS(TIMESTAMPDIFF(16,DIGITS(TIMESTAMP(CURRENT TIMESTAMP)
                  - TIMESTAMP(CURRENT TIMESTAMP))))
    FROM SYSIBM.SYSDUMMY1 ;
  

Local fix

• SELECT
  CHAR(TIMESTAMPDIFF(16,CHAR(TIMESTAMP(CURRENT TIMESTAMP)
                  - TIMESTAMP(CURRENT TIMESTAMP))))
    FROM SYSIBM.SYSDUMMY1 ;
  In the query, DIGITS should be replaced with the CHAR function.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of SQL statements  *
  *                 that reference the TIMESTAMPDIFF built-in    *
  *                 function.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND0C7 RC0 can occur at location   *
  *                      DSNXRINT +2304 or OFFSET2304 when       *
  *                      executing the DSNTIAUL program with an  *
  *                      SQL statement that contains the         *
  *                      TIMESTAMPDIFF built-in function.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND0C7 RC0 can occur at location DSNXRINT +2304 or
  OFFSET2304 when executing an SQL statement that contains the
  TIMESTAMPDIFF built-in function.  The problem was reported when
  executing the DSNTIAUL program with an SQL statement that
  contains the TIMESTAMPDIFF built-in function, but the problem
  can also occur otherwise.
  
  
  The following example illustrates a failing case.
  
  Step 1. Create Table T1.
  
    CREATE TABLE T1
      (C1             TIMESTAMP           NOT NULL);
  
  
  Step 2. Insert a row into Table T1.
  
    INSERT INTO T1 VALUES(CURRENT TIMESTAMP) ;
  
  
  Step 3. Execute the following query.
  
    SELECT
         TIMESTAMPDIFF(16,DIGITS(TIMESTAMP(CURRENT TIMESTAMP)
                             - TIMESTAMP(C1)))
    FROM T1 ;
  
  
  Step 4. Note the result.
  
  An SQLCODE171 or SQLCODE -171 is issued from DSNXRINT.
  
  
  Step 5. Execute the following query from within the DSNTIAUL
          program.
  
    SELECT
         TIMESTAMPDIFF(16,DIGITS(TIMESTAMP(CURRENT TIMESTAMP)
                               - TIMESTAMP(C1)))
    FROM T1 ;
  
  
  Step 6. Note the result.
  
  An ABEND0C7 RC0 occurs from location DSNXRINT +2304.  Instead,
  an SQLCODE -171 issued from DSNXRINT is expected as in Step 4
  above.
  

Problem conclusion

• The TIMESTAMPDIFF routine code in DB2 has been modified to
  correct the conditions leading to the abend when executing a
  query that references it (from DSNTIAUL or otherwise).
  
  Additional Keywords: SQLCODE171 SQLTIMESTAMP
  

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:

  UK90757

Modules/Macros

•    DSNXRINT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90757

     UP13/01/22 P F301

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.