DB2 - Problem description
Problem IT19637 | Status: Closed |
DB2 MAY CONVERT VIEW COLUMN TYPES INCORRECTLY OR RETURN SQL0418NUPON REVALIDATION OF A VIEW WITH UNTYPED EXPRESSIONS | |
product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problem description: | |
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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * To be considered for future release. View sysrouted APAR * * for details. * **************************************************************** | |
Local Fix: | |
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. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : IT12781 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 09.03.2017 10.03.2017 10.03.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |