DB2 FOR LUW / DB2FORLUW / 970 - 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 release of DB2                                       *
****************************************************************

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.

not known / see Local fix

forerunner  : IT12781 
follow-up : 

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

09.03.2017
10.03.2017
31.07.2017