title: DB2 storing results from final table clause
body: <p>The <a href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.routines.doc/doc/c0011133.html" rel="nofollow">FINAL TABLE</a> clause is great for getting values back from DML in DB2, for example:</p>
  
  <pre><code>SELECT id
  FROM FINAL TABLE
  ( 
    INSERT INTO mySchema.myTable (val)
    VALUES ('data')
  )            
  </code></pre>
  
  <p>However, there doesn't seem to be a way to store the results of this query into another table, persisting the contents somewhere. For example, both of the following fail with the error "Data change table reference not allowed where specified." (I am running DB2 for i v7.1):</p>
  
  <pre><code>CREATE TABLE mySchema.otherTable AS (
  SELECT id
  FROM FINAL TABLE
  ( 
    INSERT INTO mySchema.myTable (val)
    VALUES ('data')
  )
  ) WITH DATA      
  </code></pre>
  
  <p>After creating mySchema.otherTable in a separate CREATE TABLE statement, this also fails:</p>
  
  <pre><code>INSERT INTO mySchema.otherTable (ID)
  SELECT id
  FROM FINAL TABLE
  ( 
    INSERT INTO mySchema.myTable (val)
    VALUES ('data')
  )
  </code></pre>