DB2 Operation not allowed for reason code "1" on table

mastermistik 0
Viewed 93

when I am executing a mass load copy I got an error like [Code: -668, SQL State: 57016] Operation not allowed for reason code "1" on table "MASS_TABLE".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.28.11

    CREATE OR REPLACE PROCEDURE MASS_LOAD
    ()
   LANGUAGE SQL
    SPECIFIC SQL220916104128256
    BEGIN
    call SYSPROC.ADMIN_CMD(
    'LOAD FROM (SELECT 5, NAME, TYPE FROM MASS_TABLE WHERE ID = 3) OF CURSOR INSERT INTO MASS_TABLE'
    );
    END

What I observed afterwards I cannot make a Select on the table, until I execute

SET INTEGRITY FOR MASS_TABLE CHECK, FOREIGN KEY, MATERIALIZED QUERY, STAGING, GENERATED COLUMN IMMEDIATE UNCHECKED;

How can I handle it inside the stored procedure?

db2 sql
1 Answers
--#SET TERMINATOR @

CREATE TABLE MASS_TABLE AS 
(
  SELECT *
  FROM 
  (
    VALUES 
      (3, 'NAME1', 'TYPE1')
    , (3, 'NAME2', 'TYPE2')
  ) T (ID, NAME, TYPE)
) WITH DATA@

BEGIN
  EXECUTE IMMEDIATE 
    'DECLARE GLOBAL TEMPORARY TABLE SESSION.T AS (SELECT * FROM MASS_TABLE WHERE ID = 3) '
  ||'WITH DATA WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED';
  CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT 5, NAME, TYPE FROM SESSION.T) OF CURSOR INSERT INTO MASS_TABLE (ID, NAME, TYPE) NONRECOVERABLE');
END@

SELECT * FROM MASS_TABLE@
ID NAME TYPE
3 NAME1 TYPE1
3 NAME2 TYPE2
5 NAME1 TYPE1
5 NAME2 TYPE2
Mark Barinstein0
Related