Technical Blog Post
Abstract
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!
UID
ibm11140910