DB2 FOR LUW / DB2FORLUW / B10 - DB2

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.

****************************************************************
* 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.                    *
****************************************************************

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 ;

not known / see Local fix

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

17.04.2018
17.12.2018
17.12.2018