Shulou(Shulou.com)06/01 Report--

Admin_move_table online add delete field .txt

IBM Knowledge Center-move tables online. pdf by using the ADMIN_MOVE_TABLE procedure

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 Connection Information

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 (1memorialjames`), (2recractionkobe')"

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 and 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 keywords 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 detailed 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 an 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:

ten

Index information cannot be found in the protocol table.

eleven

Expected data cannot be found in the protocol table.

twenty-three

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.

twenty-four

A table space for regular data, large object (LOB) data, or

Indexes was specified but not all three of these table spaces

Were specified.

twenty-five

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.

thirty

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:

ten

Start the online move table operation again, which should fix

The error in the protocol table.

eleven

Start the online move table operation again, which should fix

The error in the protocol table.

twenty-three

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.

twenty-four

Specify all three table space parameters (regular data, large

Object data, and index) or none of these parameters.

twenty-five

Create a unique index on the table, or do not attempt to move

The table.

thirty

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

-

one

two

2 record (s) selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.