DB2 for z/OS SQL: OVERRIDING USER VALUE not permitted

810 Views Asked by At

I have two tables with identical definition but different owners. Created a new table based on one of these:

CREATE TABLE SHADOW.ADM LIKE USR1.ADM;
ALTER TABLE SHADOW.ADM ADD SHTYPE CHAR(1);
ALTER TABLE SHADOW.ADM ADD SHCOUNTER INTEGER GENERATED ALWAYS AS IDENTITY;
CREATE UNIQUE INDEX SHADOW.IXADM ON SHADOW.ADM(SHCOUNTER) USING STOGROUP SG1;

When I try to execute the SQL below I get syntax error:

INSERT INTO SHADOW.ADM
  OVERRIDING USER VALUE
  SELECT A.*,0 FROM     
    (SELECT O.*,'B' FROM USR1.ADM O UNION ALL
     SELECT N.*,'A' FROM USR2.ADM N) A;

The error is:

  OVERRIDING USER VALUE CLAUSE IS NOT PERMITTED. SQLCODE=-109, SQLSTATE=42601, DRIVER=3.72.44

The strange thing is, the SQL below executes without error:

INSERT INTO SHADOW.ADM
  OVERRIDING USER VALUE
  SELECT A.*,'B',0 FROM USR1.ADM A;     

Is this a bug in DB2's SQL parser or am I missing something?

btw: This is on DB2 for z/OS V7, accessed from DBeaver as client.

0

There are 0 best solutions below