IC88642: "DB2 -A" RETURNS UNEXPECTED SQLSTATE WHEN AN ADJUSTMENT WAS MADE TO THE INVALID DATE CALCULATED USING DATE() WITH LITERAL VALUE.

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If we force to calculate an impossible date like 02/30/2012, it
  will be adjusted to 02/29/2012 and the warning with SQLSTATE
  01506 should be returned. (Note that 2012 is a leap year.)
  
  SQLSTATE 01506: An adjustment was made to a DATE or TIMESTAMP
  value to correct an invalid date resulting from an arithmetic
  operation.
  
  For example:
  $ db2 conect to sample
  
  $ date
  Fri Aug 17 05:11:20 EDT 2012
  
  $ db2 -a "values date('08/17/2012') + 13 day - 6 month"
  
  1
  ----------
  02/29/2012
  
    1 record(s) selected.
  
  
  SQLCA Information
  
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 0   sqlerrml: 0
   sqlerrmc:
   sqlerrp : SQLRI01F
   sqlerrd : (1) -2147221503      (2) 1                (3) 0
             (4) 0                (5) 0                (6) 0
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
             (7)      (8)      (9)      (10)       (11)
   sqlstate: 00000 --> This should be 01506.
  
  However, if we use "current date", the warning is returned
  properly.
  
  $ db2 -a "values date(current date) + 13 day - 6 month"
  
  1
  ----------
  02/29/2012
  
    1 record(s) selected.
  
  
  SQLCA Information
  
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 0   sqlerrml: 0
   sqlerrmc:
   sqlerrp : SQL09074
   sqlerrd : (1) 0                (2) 0                (3) 0
             (4) 0                (5) 0                (6) 0
   sqlwarn : (1) W    (2)      (3)      (4)        (5)       (6)
             (7) W    (8)      (9)      (10)       (11)
   sqlstate: 01506
  

Local fix

• Not available
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Version 10.1 FixPack 2                            *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 UDB Version 10.1 FixPack 2
  

Temporary fix

Comments

APAR Information

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

  IC86013

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP