DB2 FOR LUW / DB2FORLUW / 970 - DB2

The TO_DATE function - or its synonym TIMESTAMP_FORMAT - may 
return SQL20448N when used within an IF or ELSE statement.  The 
TO_DATE function is executed even though the condition is false. 
 For example: 
 
IF option = 'TIMESTAMP' THEN 
  SET result = TO_DATE( input, 'YYYY-MM-DD-HH24-MI-SS'); 
END IF; 
 
Regardless of the value of "option", the TO_DATE expression is 
evaluated using the value "input".  This may not be noticed if 
the evaluation is successful, however, if "input" is not a valid 
timestamp SQL20448N is returned.  If an error does not occur, 
the result is discarded, it is not assigned to "result". 
 
This only occurs in Compound SQL (inlined).

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.7                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The TO_DATE function - or its synonym TIMESTAMP_FORMAT - may * 
* return SQL20448N when used within an IF or ELSE statement.   * 
* The TO_DATE function is executed even though the condition   * 
* is false.                                                    * 
* For example:                                                 * 
*                                                              * 
* IF option = 'TIMESTAMP' THEN                                 * 
* SET result = TO_DATE( input, 'YYYY-MM-DD-HH24-MI-SS');       * 
* END IF;                                                      * 
*                                                              * 
* Regardless of the value of "option", the TO_DATE expression  * 
* is evaluated using the value "input".  This may not be       * 
* noticed if the evaluation is successful, however, if "input" * 
* is not a valid timestamp SQL20448N is returned.  If an error * 
* does not occur, the result is discarded, it is not assigned  * 
* to "result".                                                 * 
*                                                              * 
* This only occurs in Compound SQL (inlined).                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 FixPack 4.                            * 
****************************************************************

Ensure the TO_DATE function arguments are valid regardless of 
the condition.  A simple way to do this with the example above 
is to use another variable that is initialized with a valid 
timestamp string: 
 
DECLARE to_date_input VARCHAR(14) DEFAULT '20081231233015'; 
... 
IF option = 'TIMESTAMP' THEN 
  SET to_date_input = input; 
  SET result = TO_DATE( to_date_input, 'YYYY-MM-DD-HH24-MI-SS'); 
END IF;

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 4.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC72816 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

24.11.2010
05.05.2011
05.05.2011

9.7.FP4