PM75170: SQLCODE187 AFTER SETTING DATE BACK

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -187 with any SQL statement after SET DATE (MVS Command)
  to prior date which is more then 24h from oridinal timestamp.
  
  Setting a date back, was doen during DR test (so is not a very
  common scenario)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of the CURRENT     *
  *                 TIME ZONE, CURRENT TIMESTAMP, CURRENT DATE,  *
  *                 and CURRENT TIME special registers or the    *
  *                 TIMESTAMP WITH TIME ZONE data type with z/OS *
  *                 1.11 or above.                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 issued an SQLCODE -187 (SQLCODE187) *
  *                      for a valid CURRENT DATE, TIME,         *
  *                      TIMESTAMP or TIME ZONE special          *
  *                      register, or for a TIMESTAMP WITH TIME  *
  *                      ZONE data type after the MVS TOD clock  *
  *                      value has been changed for z/OS 1.11.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 issued an SQLCODE -187 for a valid CURRENT DATE, TIME,
  TIMESTAMP, or TIME ZONE which results from a special register,
  or for a TIMESTAMP WITH TIME ZONE data type, after the MVS TOD
  clock value has been changed on z/OS 1.11.  Instead, DB2 should
  not issue the SQLCODE.
  
  The following example helps to illustrate the problem.
  
  1. Change the MVS TOD clock value to 48 hours past (later than)
     the UTC date on  z/OS 1.11.
  
     For example, if the current UTC DATE on the system
     is 2012.341 (December 6th), change the DATE to 2 days
     later (to be December 8th).
  
     SET DATE=2012.343
  
  
  2. Execute the following statement.
  
     SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;
  
  
  3. Check the results.
  
     DB2 issues an SQLCODE -187. The expected result to be
     returned is the date  2012-12-08.
  
  
  DB2 does not allow a TIME ZONE beyond +/-24:00 (hours) once the
  TOD value has been changed to a value beyond or earlier than the
  system UTC time +/-24:00. This affects the field representing
  the TOD clock in z/OS 1.11.  As a result, DB2 incorrectly issues
  an SQLCODE -187 when retrieving the changed value to use in
  setting the special register.
  
  PLEASE NOTE.  The above problem can also occur when the current
  system UTC DATE is changed to 2 days earlier.
  

Problem conclusion

• DB2 code has been changed to adjust the TIME ZONE value within
  the range of +/-12:00 when the system TOD has been changed to (a
  later or earlier time) a value beyond or earlier than the system
  UTC time +/-24:00.
  
  Additional Keywords: SQLTIMESTAMP SQLTIME 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:

  UK90208

Modules/Macros

• DSNHVCTS DSNILECV DSNUVCTS DSNUVVTS DSNXVCTS
  DSNXVTS9
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90208

     UP12/12/27 P F212

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.