DB2 FOR LUW / DB2FORLUW / B10 - DB2

If a view containing untyped expressions becomes invalid, DB2 
may incorrectly convert certain columns within that view to an 
incorrect data type upon revalidation. This means that the data 
type of the untyped expression is determined successfully by 
view creation, but is incorrectly determined upon view 
revalidation. Alternatively, DB2 may return SQL0418N. 
 
Conditions that cause this issue: 
 - View contains an untyped expression AND 
 - View contains a UNION / UNION ALL on the untyped expression 
AND 
 - View is invalidated AND 
 - View is revalidated 
 
Example of sql that could convert a view column to an incorrect 
data type: 
 
CREATE TABLE t1 (c1 CHAR(3)); 
 
CREATE VIEW v AS 
WITH 
inline_view AS (SELECT NULL AS c2 FROM SYSIBM.SYSDUMMY1) 
SELECT c1 FROM t1 
UNION 
SELECT c2 FROM inline_view; 
 
When describing this view, c1 should appear as a CHAR(3). 
However when revalidating this view (for example after t1 is 
dropped and recreated), c1 appears as type VARCHAR(3) which is 
incorrect.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               * 
****************************************************************

There are two fixes available: 
 
1) Perform an explicit cast of the untyped expression to the 
desired type. From the above example, change "inline_view AS 
(SELECT 
NULL AS c2 FROM SYSIBM.SYSDUMMY1)" to "inline_view AS (SELECT 
CAST(NULL as CHAR(3)) AS c2 FROM SYSIBM.SYSDUMMY1)". 
 
2) Recreate the view before revalidating.

DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in DB2 11.1 Mod 2 Fix Pack 2

not known / see Local fix

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

08.03.2017
27.06.2017
27.06.2017