title: How to ALTER a table on iSeries that has constraints? Getting "*FILE in use." error
body: <p>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):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Now, much later, I will need to alter that table to add a field:</p>
  
  <pre><code>ALTER TABLE ABCLIB.ABCDE ADD COLUMN DEICN VARGRAPHIC (100) ALLOCATE(50)     
  </code></pre>
  
  <p>Which results in this message:</p>
  
  <pre><code>Row or object ABCDE in ABCLIB type *FILE in use.
  </code></pre>
  
  <p>I have checked and there are definitely no object locks on this table at this time.  When I check the joblog, I see this:</p>
  
  <pre><code>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.  
  </code></pre>
  
  <p>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.</p>
  
  <p>But is there a way to possibly suspend the keys and then resume them after the alter?</p>
  
  <p>(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...)</p>