DB2 - Problem description
Problem IT35705 | Status: Closed |
DISTRIBUTION KEY ORDER WAS NOT INHERITED FROM THE AS-RESULT-TABLE | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
Extension of APAR IT33871, when the select query has a sub query then distribution keys are not being inherited. CREATE TABLE A (A INT, B INT) ORGANIZE BY COLUMN DISTRIBUTE BY (B); CREATE TABLE B (I INT) ORGANIZE BY COLUMN; CREATE TABLE C AS (SELECT * FROM A WHERE A IN (SELECT I FROM B)) WITH DATA; SELECT TABNAME, COLNAME, PARTKEYSEQ FROM SYSCAT.COLUMNS WHERE TABNAME IN ('A','C') ORDER BY TABNAME, COLNO; TABNAME|COLNAME|PARTKEYSEQ -------|-------|---------- A |A | 0 A |B | 1 C |A | 1 C |B | 2 As only table A contributes to SELECT list, distribution keys of table A should be copied to Table C. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * V.11.5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to 11.5.6. or 11.5.5.1 * **************************************************************** | |
Local Fix: | |
The only workaround currently available is to specify the distribution key in CTAS statement: e.g. create table as (select ...) with data distribute by () | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * V.11.5. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to 11.5.6. or 11.5.5.1 * **************************************************************** | |
Comment | |
upgrade to 11.5.6. or 11.5.5.1 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 26.01.2021 22.03.2021 22.03.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |