I am trying to get the admin_move_table command to work to move a
table from one tablespace to another. Got it to work on a simple dummy
table, but when using a complex table with range partitions, the
results have been less than stellar. Upon completion, my new
tablespace is -6 (negative six) which doesn't show up as a catalogued
tablespace. On subsequent attempts to move I frequently get a SQL1142
error where a temp file already exists.

The documentation is very sparse so it's hard to see what I might be
doing wrong. I'm running 9.7.3 on a partitioned Linux environment. My
command is below. Any insight would be appreciated.
call sysproc.admin_move_table (

'LRM',

'MIGRATE_TEST_4',

'TS_LRM_POS_HIST_D',

'TS_LRM_POS_HIST_D',

'L

'',

'',

'COPY_USE_LOAD',

'MOVE'

);

On Wed, 12 Oct 2011 07:33:25 -0700, esmith2112 wrote:

Unless you need to do this online while users are accessing the table, it
is almost as easy to just create a new table in the new tablespace,
export the data from old and load into new table, and then rename old and
new tables. You will have handle FK's and other dependent objects
manually.

Thanks for your input, Mark. We've looked at the re-create/export/
reload scenario, but online availability is important as are the
multiple dependencies such as hundreds of views that the application
creates for itself. That's why we were excited by the purported
benefits of this procedure. The manual move is much speedier than the
proc for the record. We'll keep experimenting and plugging along.


Evan

On Oct 13, 9:14*pm, esmith2112 <esmith2... (AT) gmail (DOT) com> wrote:
Or you can load from cursor - saves you time of export to file:
declare <curosre_name> cursor for
select * from table;
load from <cursor_name> of cursor warningcount 1
insert into <scheme.table>;

On Oct 12, 6:33*am, esmith2112 <esmith2... (AT) gmail (DOT) com> wrote:

I assume you are looking at the table space id in syscat.tables as
opposed to syscat.datapartitions? The table space id in the former is
a logical value and the physical table space id's can be found in the
syscat.datapartitions catalog view.


db2 "create table t1 (c1 int, c2 int) partition by range (c1)
(starting (1) ending (10) every (5))"

db2 "select TBSPACEID from syscat.tables where tabname='TEST'"

TBSPACEID
---------
-6

1 record(s) selected.

db2 "select TBSPACEID from syscat.datapartitions where tabname='TEST'"

TBSPACEID
-----------
2
2

2 record(s) selected.


Using admin_table_move should work with range partitioned tables, so
besides the -6 in the catalog do you have any other error or
indication that the table move did not happen?

- Naresh