DB2 - Problem description
Problem IT24761 | Status: Closed |
SQL20478N REASON CODE 30 MAY OCCUR WHEN EXECUTING A DATA-CHANGE-STATEMENT | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
When a data-change-statement uses a table as a source for an UPDATE or INSERT operation, and the source table has a column mask enabled, SQL20478N with reason code 30 may be incorrectly returned at execution time. This will happen when all of the following conditions are true: - At least one masked column from a source table is used as a source for the UPDATE or DELETE operation - Both the schema of the source table, and the schema of the mask are greater than 8 - The schema of the source table has a different length than the schema of the mask - At least one row is returned from the fullselect over the source table For example: CREATE TABLE SOURCESCHEMA.SOURCE ( C1 int, C2 int ); ALTER TABLE SOURCESCHEMA.SOURCE ACTIVATE COLUMN ACCESS CONTROL; CREATE MASK MASKSCHEMA.MASK ON SOURCESCHEMA.SOURCE FOR COLUMN "C1" RETURN CASE WHEN 1=1 THEN C1 END ENABLE ; CREATE TABLE T (C1 int, C2 int); insert into SOURCESCHEMA.SOURCE values (1, 2); MERGE INTO T USING( SELECT C1, C2 FROM SOURCESCHEMA.SOURCE) AS S ON S.C2 = T.C2 WHEN NOT MATCHED THEN INSERT (C1, C2) VALUES ( S.C1, S.C2); SQL20478N The statement failed because the column mask "MASKSCHEMA.MASK" defined for column "SOURCESCHEMA.SOURCE.C1" exists and the column mask cannot be applied or the column mask conflicts with the failed statement. Reason code "30" SQLSTATE=428HD MASKSCHEMA has length 10 and SOURCESCHEMA has length 12. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Drop and recreate either the mask or the table with a schema * * the * * same length as the other object's schema. * **************************************************************** | |
Local Fix: | |
Drop and recreate either the mask or the table with a schema the same length as the other object's schema. In the example above, the following would avoid the problem by recreating the mask with a schema length of 12, to match the schema length of the source table: DROP MASK MASKSCHEMA.MASK; CREATE MASK MASKSCHEMA00.MASK ON SOURCESCHEMA.SOURCE FOR COLUMN "C1" RETURN CASE WHEN 1=1 THEN C1 END ENABLE ; | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 17.04.2018 17.12.2018 17.12.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |