75 ways to demystify DB2 #69: Why does SYSPROC.ADMIN_MOVE_TABLE fail with SQL0205N?

Body

Commands:

CREATE TABLE TAB1 ( COLTIME TIMESTAMP NOT NULL WITH DEFAULT, COLVAR VARCHAR(40) GENERATED ALWAYS AS (VARCHAR(RIGHT(DIGITS (YEAR(TAB1.COLTIME)), 4) || RIGHT (DIGITS(MONTH(TAB1.COLTIME)), 2)|| RIGHT (DIGITS(DAY(TAB1.COLTIME)), 2), 40) ) ) IN USERSPACE1
DB20000I  The SQL command completed successfully.

CREATE INDEX Z1_COLVAR ON  TAB1 (COLVAR ASC)
DB20000I  The SQL command completed successfully.

CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT SQLID,'TAB1','USERTABLE','USERINDEX','USERLOB',NULL,NULL,NULL,NULL,'COPY_USE_LOAD "MESSAGES ON SERVER NONRECOVERABLE"','MOVE')
SQL0205N  Column or attribute "TAB1.COLTIME" is not defined in
"YGAWALI.TAB1AABYVRt".  SQLSTATE=42703

Reason:

The admin_move_table does not expect the table name in front of column names in regular expression defined for 'generated always' column. This is documented in following link:

ADMIN_MOVE_TABLE procedure - Move tables online
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html?cp=SSEPGG_9.7.0%2F3-6-1-3-18-2

It says:
"The stored procedure will not support column expressions that include the table name (that is, table 'T1' with expression (T1.C *5)) in either the source table or the target table. To remedy this, the user can alter the column to change the expression to not include the table name."

Solution:

First of all, please CANCEL the last failed incomplete SYSPROC.ADMIN_MOVE_TABLE as follows:

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT SQLID,'TAB1','USERTABLE','USERINDEX','USERLOB',NULL,NULL,NULL,NULL,'COPY_USE_LOAD "MESSAGES ON SERVER NONRECOVERABLE"','CANCEL')"

Now, we are suggesting you to alter the table column as follows to remove the table name from 'generated always' expression:

SET INTEGRITY FOR TAB1 off
DB20000I  The SQL command completed successfully.

alter table TAB1 alter column COLVAR  set EXPRESSION AS (VARCHAR(RIGHT(DIGITS (YEAR(COLTIME)), 4) || RIGHT (DIGITS(MONTH(COLTIME)), 2)|| RIGHT (DIGITS(DAY(COLTIME)), 2), 40) )
DB20000I  The SQL command completed successfully.

SET INTEGRITY FOR TAB1 IMMEDIATE CHECKED FORCE GENERATED
DB20000I  The SQL command completed successfully.

Now, rerun the ADMIN_MOVE_TABLE.

CALL SYSPROC.ADMIN_MOVE_TABLE(CURRENT SQLID,'TAB1','USERTABLE','USERINDEX','USERLOB',NULL,NULL, NULL,NULL,'COPY_USE_LOAD "MESSAGES ON SERVER NONRECOVERABLE"','MOVE')

  Result set 1
  --------------

  KEY                              VALUE                                                                                                
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           YGAWALI                                                                                              
  CLEANUP_END                      2015-09-13-08.56.46.307662                                                                           
  CLEANUP_START                    2015-09-13-08.56.46.120430                                                                           
  COPY_END                         2015-09-13-08.56.43.284811                                                                           
  COPY_OPTS                        BY_KEY,OVER_INDEX,LOAD NONRECOVERABLE,WITH_INDEXES,NON_CLUSTER                                       
  COPY_START                       2015-09-13-08.56.41.918769                                                                           
  COPY_TOTAL_ROWS                  2                                                                                                    
  INDEXNAME                        PK_TAB1                                                                                  
  INDEXSCHEMA                      YGAWALI                                                                                              
  INDEX_CREATION_TOTAL_TIME        0                                                                                                    
  INIT_END                         2015-09-13-08.56.41.123098                                                                           
  INIT_START                       2015-09-13-08.56.39.074268                                                                           
  REPLAY_END                       2015-09-13-08.56.44.866663                                                                           
  REPLAY_START                     2015-09-13-08.56.43.294669                                                                           
  REPLAY_TOTAL_ROWS                0                                                                                                    
  REPLAY_TOTAL_TIME                0                                                                                                    
  STATUS                           COMPLETE_WITH_WARNINGS                                                                               
  SWAP_END                         2015-09-13-08.56.45.927758                                                                           
  SWAP_RETRIES                     0                                                                                                    
  SWAP_START                       2015-09-13-08.56.45.570014                                                                           
  VERSION                          09.07.0008                                                                                           
  WARNINGS                         LOAD message file could not be removed using "CALL SYSPROC.ADMIN_REMOVE_MSGS('804_19395_YGAWALI')". Reason: SQL20193N  An error

  22 record(s) selected.

  Return Status = 0

Hope this helps!