PM28618: AN INVALID SQLCODE -180 IS ISSUED FOR A SQL STATEMENT CONTAINING A CAST TO TIME WITHIN A GROUP BY CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• AN INVALID SQLCODE -180 IS ISSUED FOR A SQL STATEMENT CONTAINING
  A CAST TO TIME WITHIN A GROUP BY CLAUSE
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS V8 users of complex SQL     *
  *                 statements having a CAST to DATE or TIME in  *
  *                 a GROUP BY or FETCH FIRST N ROWS clause.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Complex SQL statements having a CAST to *
  *                      DATE or TIME in the GROUP BY clause or  *
  *                      that contain a FETCH FIRST N ROWS       *
  *                      clause which can result in an           *
  *                      SQLCODE180 or SQLCODE181 can exhaust    *
  *                      the Diagnostic Work Area and prevent    *
  *                      the query from returning a proper       *
  *                      result.                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A complex query with a GROUP BY clause containing a CAST to a
  DATE or TIME value which can result in an SQLCODE180 (-180) or
  an SQLCODE181 (-181) can exhaust the DWA (Diagnostic Work Area)
  storage in DB2 and prevent the proper result from being returned
  for the query.
  
  The following simplified query is an example of a failing query
  which generates numerous SQLCODE180's (followed by many
  SQLCODE445's) from DSNXRTIM which exhausts the Diagnostic Work
  Area and prevents the query from completing its execution.
  Thus, a result is not returned.
  
  
  SELECT
    CAST(SUBSTR(CAST(T1.ORDER_DATE AS VARCHAR(23)),
    POSSTR(CAST(T1.ORDER_DATE AS VARCHAR(23)), ' ') + 1 )
    AS DATE)
  FROM  ORDER_DESCRIPTION T1 ,
        ORDER_DETAILS T2
  WHERE T1.ORDER_NUMBER = T2.ORDER_NUMBER
  GROUP BY
    CAST(SUBSTR(CAST(T1.ORDER_DATE AS VARCHAR(23)),
    POSSTR(CAST(T1.ORDER_DATE AS VARCHAR(23)), ' ') + 1 )
    AS DATE) ;
  
  Another failing query example is as follows
  
   SELECT A.CARD_GDS_NO, A.AA FROM
   (SELECT A.CARD_GDS_NO, HEX(DATE('201103'||'01000000')) AA
      FROM BCCARD.TAFM0040 A
   WHERE A.FUNDS_RCRU_FEE_CLCD = '1'
     AND A.COC_DATE = ''
     AND A.SMRRY_ACM_YN = 'N'
     AND A.AFFI_STL_CYCL_CD = 'Y'
     AND CAST(MOD(DECIMAL(SUBSTR(A.AFFI_FUNDS_STL_STD_NO,10,1)),5)
     AS CHAR(1))= '4'
   FETCH FIRST 413 ROWS ONLY ) A ;
  
  
  Please note that the problem can also occur for simpler queries
  as well.
  
  An additional problem being corrected by this apar is that of
  issuing an SQLCODE181 (SQLCODE -181) when an SQLCODE180 (SQLCODE
  -180) is expected from a DATE or TIME unicode CAST routine.
  This is an inconsistency in the DB2 DATE and TIME routines.
  
  The following example, using a UNICODE Table, illustrates such a
  query.  It returns an SQLCODE -181.  Instead, an SQLCODE -180
  should be issued.
  
  
  SELECT LAST_DAY(DATE(SUBSTR(CHAR(DATE1)              ||
                               CHAR(CURRENT TIMESTAMP) ||
                               RECID,1,26))),
          LAST_DAY(DATE(SUBSTR(CHAR(DATE1)             ||
                               CHAR(CURRENT TIMESTAMP) ||
                               RECID,1,26))),
          LAST_DAY(TIMESTAMP('1999-01-01-'             ||
                   CHAR(TIME(CURRENT TIMESTAMP)))),
          LAST_DAY(DATE(CHAR(DATE(CURRENT TIMESTAMP))  ||
                               UX'002D'                ||
                               '12.58.57')),
          LAST_DAY(DATE('1999-01-01')),
          LAST_DAY(DATE(CHAR(DATE(CURRENT TIMESTAMP))  ||
          CHAR(TIME(UX'00310032002E00350038002E00350037'))))
   FROM TBQP2701;
  

Problem conclusion

• The code in DB2 is modified to reduce the use of the DWA during
  the CAST to DATE or CAST to TIME routines which will prevent it
  from being exhausted and allow the query to complete and return
  the proper result.
  
  
  Additional Keywords: SQLTIME SQLDATE SQLCAST SQLGROUPBY SQLFFNR
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  PM24365

• APAR is sysrouted TO one or more of the following:

  UK68462

Modules/Macros

• DSNXRTIM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK68462

     UP11/06/21 P F106

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.