admin_move_table在线添加删除字段.txt
IBM Knowledge Center - 通过使用 ADMIN_MOVE_TABLE 过程来以联机方式移动表.
pdf
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dm.doc/doc/t0054864.
html
[db2inst1@dbmon ~]$ db2 connect to ibm
Database Connect
ion Inf
ORMation
Database server = DB2/
linuxX8664 10.5.8
sql authorization ID = DB2INST1
Local database alias = IBM
[db2inst1@dbmon ~]$ db2 "create table xpf(id int,name char(10))"
DB20000I The SQL command completed successfully.
[db2inst1@dbmon ~]$ db2 "insert into xpf values(1,'james'),(2,'kobe')"
DB20000I The SQL command completed successfully.
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
> 'DB2INST1',
> 'XPF',
> '',
> '',
> '',
> '',
> '',
> '',
> 'ID INT,ADDRESS CHAR(20),NAME CHAR(10)',
> '',
> 'MOVE')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
CLEANUP_END 2018-04-23-10.35.58.611108
CLEANUP_START 2018-04-23-10.35.58.517979
COPY_END 2018-04-23-10.35.57.966628
COPY_OPTS ARRAY_INSERT,NON_CLUSTER
COPY_START 2018-04-23-10.35.57.939488
COPY_TOTAL_ROWS 2
INDEXNAME
INDEXSCHEMA
INIT_END 2018-04-23-10.35.57.890671
INIT_START 2018-04-23-10.35.57.474514
ORIGINAL_TBLSIZE 1024
PAR_COLDEF ID INT,ADDRESS CHAR(20),NAME CHAR(10)
REPLAY_END 2018-04-23-10.35.58.454841
REPLAY_START 2018-04-23-10.35.57.967075
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2018-04-23-10.35.58.503173
SWAP_RETRIES 0
SWAP_START 2018-04-23-10.35.58.456034
UTILITY_INVOCATION_ID 01000000B8000000080000000000000000002018042310355789175100000000
VERSION 10.05.0008
23 record(s) selected.
Return Status = 0
[db2inst1@dbmon ~]$ db2 describe table xpf
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes
ADDRESS SYSIBM CHARACTER 20 0 Yes
NAME SYSIBM CHARACTER 10 0 Yes
3 record(s) selected.
[db2inst1@dbmon ~]$ db2 "select * from xpf"
ID ADDRESS NAME
----------- -------------------- ----------
1 - james
2 - kobe
2 record(s) selected.
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
'DB2INST1',
'XPF',
'',
'',
'',
'',
'',
'',
'GUOMIN CHAR(10),ID INT,ADDRESS CHAR(20),NAME CHAR(10),CHANG ID',
'',
'MOVE')"
SQL0204N "ID" is an undefined name. SQLSTATE=42704
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
'DB2INST1',
'XPF',
'',
'',
'',
'',
'',
'',
'GUOMIN CHAR(10),ID INT,ADDRESS CHAR(20),NAME CHAR(10),CHANG INT',
'',
'MOVE')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
CLEANUP_END 2018-04-23-10.37.37.555312
CLEANUP_START 2018-04-23-10.37.37.449049
COPY_END 2018-04-23-10.37.37.151351
COPY_OPTS ARRAY_INSERT,NON_CLUSTER
COPY_START 2018-04-23-10.37.37.136013
COPY_TOTAL_ROWS 2
INDEXNAME
INDEXSCHEMA
INIT_END 2018-04-23-10.37.37.052532
INIT_START 2018-04-23-10.37.36.791936
ORIGINAL_TBLSIZE 1024
PAR_COLDEF GUOMIN CHAR(10),ID INT,ADDRESS CHAR(20),NAME CHAR(10),CHANG INT
REPLAY_END 2018-04-23-10.37.37.433045
REPLAY_START 2018-04-23-10.37.37.151726
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2018-04-23-10.37.37.437124
SWAP_RETRIES 0
SWAP_START 2018-04-23-10.37.37.433898
UTILITY_INVOCATION_ID 01000000B9000000080000000000000000002018042310373705312000000000
VERSION 10.05.0008
23 record(s) selected.
Return Status = 0
[db2inst1@dbmon ~]$ db2 "select * from xpf"
GUOMIN ID ADDRESS NAME CHANG
---------- ----------- -------------------- ---------- -----------
- 1 - james -
- 2 - kobe -
2 record(s) selected.
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
'DB2INST1',
'XPF',
'',
'',
'',
'',
'',
'',
'GUOMIN CHAR(10),ID INT,ADDRESS CHAR(20),NAME CHAR(10),CHANG INT,
GoOD INT DEFAULT 0',
'',
'MOVE')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
CLEANUP_END 2018-04-23-10.38.02.334491
CLEANUP_START 2018-04-23-10.38.02.229604
COPY_END 2018-04-23-10.38.01.964369
COPY_OPTS ARRAY_INSERT,NON_CLUSTER
COPY_START 2018-04-23-10.38.01.954613
COPY_TOTAL_ROWS 2
INDEXNAME
INDEXSCHEMA
INIT_END 2018-04-23-10.38.01.902304
INIT_START 2018-04-23-10.38.01.590889
ORIGINAL_TBLSIZE 1024
PAR_COLDEF GUOMIN CHAR(10),ID INT,ADDRESS CHAR(20),NAME CHAR(10),CHANG INT,GOOD INT DEFAULT 0
REPLAY_END 2018-04-23-10.38.02.209146
REPLAY_START 2018-04-23-10.38.01.964878
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2018-04-23-10.38.02.213404
SWAP_RETRIES 0
SWAP_START 2018-04-23-10.38.02.210102
UTILITY_INVOCATION_ID 01000000BA000000080000000000000000002018042310380190273200000000
VERSION 10.05.0008
23 record(s) selected.
Return Status = 0
[db2inst1@dbmon ~]$ db2 "select * from xpf"
GUOMIN ID ADDRESS NAME CHANG GOOD
---------- ----------- -------------------- ---------- ----------- -----------
- 1 - james - 0
- 2 - kobe - 0
2 record(s) selected.
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
'DB2INST1',
'XPF',
'',
'',
'',
'',
'',
'',
'ID INT',
'',
'MOVE')"
SQL2105N The ADMIN_MOVE_TABLE procedure could not be completed because a
prerequisite for running the procedure was not satisfied. Reason code: "23".
SQLSTATE=5UA0M
[db2inst1@dbmon ~]$ db2 ? sql2105
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "b" was found following "a ". Expected tokens
may include: "JOIN ". SQLSTATE=42601
[db2inst1@dbmon ~]$ db2 ? sql2105n
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "b" was found following "a ". Expected tokens
may include: "JOIN ". SQLSTATE=42601
[db2inst1@dbmon ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.8
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few key
Words of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more det
ailed help, refer to the Online Reference Manual.
db2 => ? SQL2105N
SQL2105N The ADMIN_MOVE_TABLE procedure could not be completed because
a prerequisite for running the procedure was not satisfied. Reason
code: "".
Explanation:
You can use the SYSPROC.ADMIN_MOVE_TABLE procedure to move data from one
table space to a another table space. For example, you can migrate that
data to a new automatic storage table space using this procedure. The
ADMIN_MOVE_TABLE procedure requires certain information or objects to
exist before it can run successfully. The reason code indicates what was
not satisfied:
10
Index information cannot be found in the protocol table.
11
Expected data cannot be found in the protocol table.
23
The column could not be dropped as specified because a unique
index must exist before a column can be dropped. The column
that is to be dropped cannot exist in that unique index.
24
A table space for regular data, large object (LOB) data, or
indexes was specified but not all three of these table spaces
were specified.
25
A table with a LONG, LOB, structured type, or XML column was
specified to be moved but a unique index (excluding unique
indexes that include an XML column) is not defined on that
table.
30
The column could not be added as specified because any column
that is being added and defined as NOT NULL must have a DEFAULT
value specified.
User response:
Respond according to the given reason code:
10
Start the online move table operation again, which should fix
the error in the protocol table.
11
Start the online move table operation again, which should fix
the error in the protocol table.
23
Create a new unique index for the table, remove the column that
is to be dropped from the unique index, or do not attempt to
drop the column and retry the online move table operation.
24
Specify all three table space parameters (regular data, large
object data, and index) or none of these parameters.
25
Create a unique index on the table, or do not attempt to move
the table.
30
Specify a DEFAULT value for any added column that is defined as
NOT NULL.
sqlcode: -2105
sqlstate: 5UA0M
Related information:
Authorization
db2 => quit
DB20000I The QUIT command completed successfully.
[db2inst1@dbmon ~]$ db2 "create unique index id_un on xpf(id):
> ^C
[db2inst1@dbmon ~]$ db2 "create unique index id_un on xpf(id)"
DB20000I The SQL command completed successfully.
[db2inst1@dbmon ~]$ db2 "call sysproc.admin_move_table(
'DB2INST1',
'XPF',
'',
'',
'',
'',
'',
'',
'ID INT',
'',
'MOVE')"
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST1
CLEANUP_END 2018-04-23-10.40.32.223403
CLEANUP_START 2018-04-23-10.40.32.107334
COPY_END 2018-04-23-10.40.31.981538
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
COPY_START 2018-04-23-10.40.31.803785
COPY_TOTAL_ROWS 2
INDEX_CREATION_TOTAL_TIME 0
INDEXNAME ID_UN
INDEXSCHEMA DB2INST1
INIT_END 2018-04-23-10.40.31.770811
INIT_START 2018-04-23-10.40.31.477718
ORIGINAL_TBLSIZE 1024
PAR_COLDEF ID INT
REPLAY_END 2018-04-23-10.40.32.070045
REPLAY_START 2018-04-23-10.40.31.982500
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2018-04-23-10.40.32.099560
SWAP_RETRIES 0
SWAP_START 2018-04-23-10.40.32.076474
UTILITY_INVOCATION_ID 01000000BD000000080000000000000000002018042310403177121800000000
VERSION 10.05.0008
24 record(s) selected.
Return Status = 0
[db2inst1@dbmon ~]$ db2 "select * from xpf"
ID
-----------
1
2
2 record(s) selected.
0