如何更改 iSeries 上有约束的表？获取“*文件正在使用”。错误

发布于 2024-09-16 11:20:53 字数 1300 浏览 4 评论 0原文

我在 iSeries(IBM-i/AS400) 上有一个表，它有一些限制。该表是使用 SQL 创建的，如下所示，有一些外键从其他表链接到该表（实际的 SQL 在这里有点混淆）：

CREATE TABLE ABCLIB.ABCDE (
  DEIDN INTEGER NOT NULL WITH DEFAULT, 
  DETTL VARGRAPHIC (50) ALLOCATE(25), 
  DETYP CHAR (1) NOT NULL WITH DEFAULT);

ALTER TABLE ABCLIB.ABCDE ADD PRIMARY KEY (DEIDN);

ALTER TABLE ABCLIB.ABCFG ADD FOREIGN KEY (FGDEK) 
  REFERENCES ABCLIB.ABCDE (DEIDN) 
  ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE ABCLIB.ABCHI ADD FOREIGN KEY (HIDEK) 
  REFERENCES ABCLIB.ABCDE (DEIDN) 
  ON DELETE RESTRICT ON UPDATE RESTRICT;

现在，很久以后，我需要更改该表以添加一个字段：

ALTER TABLE ABCLIB.ABCDE ADD COLUMN DEICN VARGRAPHIC (100) ALLOCATE(50)

这会导致以下消息：

Row or object ABCDE in ABCLIB type *FILE in use.

我已经检查过，此时该表上肯定没有对象锁。当我检查作业日志时，我看到了这一点：

Constraint cannot be removed from file Q_AT000000.    
Constraint(s) not removed from file Q_AT000000.       
File ABCDE in ABCLIB not changed.                 
Row or object ABCDE in ABCLIB type *FILE in use.

现在，我当然可以删除并重新添加有问题的约束，但我觉得这应该没有必要。我添加的列与约束无关。我相信这可能是因为事实上 OS400 (i5/OS) 并没有真正改变现有的表，而是创建一个新表并复制数据，这可能就是痛苦所在

。有没有办法可以暂停密钥，然后在更改后恢复它们？

（不涉及使用 SQL 执行此操作或建议首先以不同方式创建表的答案没有帮助，因为它们在这里不适用......）

原文

I have a table on a iSeries(IBM-i/AS400) which has some constraints. The table is created with SQL like so, with a handful of foreign keys linking from other tables to this table (actual SQL has been a bit obfuscated here):

CREATE TABLE ABCLIB.ABCDE (
  DEIDN INTEGER NOT NULL WITH DEFAULT, 
  DETTL VARGRAPHIC (50) ALLOCATE(25), 
  DETYP CHAR (1) NOT NULL WITH DEFAULT);

ALTER TABLE ABCLIB.ABCDE ADD PRIMARY KEY (DEIDN);

ALTER TABLE ABCLIB.ABCFG ADD FOREIGN KEY (FGDEK) 
  REFERENCES ABCLIB.ABCDE (DEIDN) 
  ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE ABCLIB.ABCHI ADD FOREIGN KEY (HIDEK) 
  REFERENCES ABCLIB.ABCDE (DEIDN) 
  ON DELETE RESTRICT ON UPDATE RESTRICT;

Now, much later, I will need to alter that table to add a field:

ALTER TABLE ABCLIB.ABCDE ADD COLUMN DEICN VARGRAPHIC (100) ALLOCATE(50)

Which results in this message:

Row or object ABCDE in ABCLIB type *FILE in use.

I have checked and there are definitely no object locks on this table at this time. When I check the joblog, I see this:

Constraint cannot be removed from file Q_AT000000.    
Constraint(s) not removed from file Q_AT000000.       
File ABCDE in ABCLIB not changed.                 
Row or object ABCDE in ABCLIB type *FILE in use.

Now, I could of course remove and re-add the constraints in question, but I feel like this should not be necessary. The column I am adding has nothing to do with the constraints. I believe this probably is a result of the fact that in fact OS400 (i5/OS) is not really altering the existing table but instead is creating a new table and copying data in, and that is probably where the pain comes in.

But is there a way to possibly suspend the keys and then resume them after the alter?

(Answers that do not involve doing this with SQL or suggest creating the table differently in the first place are not helpful as they are not applicable here...)

分享到QQ

分享到微博