不允许对表执行DB2操作，原因代码为“1”

kuhbmx9i 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(243)

当我执行批量加载复制时，我收到了类似 [代码：-668，SQL状态：57016]不允许对表“MASS_TABLE”执行操作，原因代码为“1”。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

之后我观察到，我无法在表上执行 Select，直到我执行

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

如何在存储过程中处理它？

db2

来源：https://stackoverflow.com/questions/73746759/db2-operation-not-allowed-for-reason-code-1-on-table

1条答案

按热度按时间

t2a7ltrp1#

--#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@

| 识别码|名称|型号|
| - -|- -|- -|
| 三个|名称1|类型1|
| 三个|名称2|类型2|
| 五个|名称1|类型1|
| 五个|名称2|类型2|

赞(0）回复(0）举报 2022-11-07

我来回答

不允许对表执行DB2操作，原因代码为“1”

1条答案

相关问题

热门标签

最新问答