0

Iam using DB2 V9.7. The question is regarding SYSPROC.ADMIN_MOVE_TABLE.

I tried to move a table between tablespaces, and its throwing an error.

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE ('SCHEMA1','ADVISE_INSTANCE','SYSTOOLSPACE','','','','','','','','MOVE')"

SQL2103N  The ADMIN_MOVE_TABLE procedure could not be completed because some
aspect of either the source table or target table is not supported by the
ADMIN_MOVE_TABLE procedure.  Reason code: "6".  SQLSTATE=5UA0M

This message is returned when the ADMIN_MOVE_TABLE procedure cannot operate on the source table or the target table for the reason indicated by the reason code:
6   The source table type is not supported or the table includes features that are not supported with the ADMIN_MOVE_TABLE procedure.

User response
6   Change the type of the table to one that is supported. Remove any table features that are not supported with the ADMIN_MOVE_TABLE stored procedure.

I could see that there are no Referential integrity (RI) constraints on the table and the table type is ‘T’.

Also, on another database, I tried and got successful – as per the below steps.

db2 "select substr(TABSCHEMA,1,20) as schema, substr(TABNAME,1,20) as table, substr(TBSPACE,1,20) as tablespace, substr(INDEX_TBSPACE,1,20) as index_tablespace, substr(LONG_TBSPACE,1,20) as long_tablespace from SYSCAT.TABLES where TABNAME = 'SIMLATS'"

SCHEMA               TABLE                TABLESPACE           INDEX_TABLESPACE     LONG_TABLESPACE
-------------------- -------------------- -------------------- -------------------- --------------------
DB2INST1             SIMLATS              TABS               -                    -

  1 record(s) selected.

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1','SIMLATS','SYSTOOLSPACE','','','','','','','','MOVE')"

SQL2105N  The ADMIN_MOVE_TABLE procedure could not be completed because a
prerequisite for running the procedure was not satisfied.  Reason code: "24".
SQLSTATE=5UA0M

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.

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1','SIMLATS','SYSTOOLSPACE','SYSTOOLSPACE','SYSTOOLSPACE','','','','','','MOVE')"

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

  KEY                              VALUE
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1
  CLEANUP_END                      2014-06-11-14.28.53.696520
  CLEANUP_START                    2014-06-11-14.28.53.592276
  COPY_END                         2014-06-11-14.28.52.120725
  COPY_OPTS                        ARRAY_INSERT,NON_CLUSTER
  COPY_START                       2014-06-11-14.28.51.993282
  COPY_TOTAL_ROWS                  0
  INDEXNAME
  INDEXSCHEMA
  INIT_END                         2014-06-11-14.28.51.902765
  INIT_START                       2014-06-11-14.28.51.214971
  REPLAY_END                       2014-06-11-14.28.53.476923
  REPLAY_START                     2014-06-11-14.28.52.121262
  REPLAY_TOTAL_ROWS                0
  REPLAY_TOTAL_TIME                0
  STATUS                           COMPLETE
  SWAP_END                         2014-06-11-14.28.53.558219
  SWAP_RETRIES                     0
  SWAP_START                       2014-06-11-14.28.53.500371
  VERSION                          09.07.0002

  20 record(s) selected.

  Return Status = 0

db2 "select substr(TABSCHEMA,1,20) as schema, substr(TABNAME,1,20) as table, substr(TBSPACE,1,20) as tablespace, substr(INDEX_TBSPACE,1,20) as index_tablespace, substr(LONG_TBSPACE,1,20) as long_tablespace from SYSCAT.TABLES where TABNAME = 'SIMLATS'"

SCHEMA               TABLE                TABLESPACE           INDEX_TABLESPACE     LONG_TABLESPACE
-------------------- -------------------- -------------------- -------------------- --------------------
DB2INST1             SIMLATS              SYSTOOLSPACE         -                    -

  1 record(s) selected.

Any thoughts why I didn’t receive the Reason code: "24" at the first case even though there was no INDEX_TABLESPACE and LONG_TABLESPACE mentioned. Also, the 'ADVISE_INSTANCE' or the SIMLATS table’s does not have the INDEX_TABLESPACE and LONG_TABLESPACE defined.

So why do we still need to mention them during the admin_move_table procedure.

closed as off-topic by mustaccio, Marcello Miorelli, Marco, John Eisbrener, Paul White Sep 4 at 7:53

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marcello Miorelli, Marco, John Eisbrener, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

migrated from serverfault.com Jun 11 '14 at 14:23

This question came from our site for system and network administrators.

  • Generally it is a good idea to define each table's data, indexes, and LOBs in separate tablespaces. This assists with both REORGs as well as recovery of data. If you do not define them, whatever is not defined defaults to wherever the table defaults to (or is defined to for data). – Chris Aldrich Jun 20 '14 at 13:08
  • It should not be a problem to move ADVISE_INSTANCE, have you checked diaglog for any clues on why it failed with 6? Also, can't you just recreate the advise tables in a new tablespace? – Lennart Jul 30 at 12:54
-1

On Db2 v 11 try adding the ALLOW_READ_ACCESS option:

db2 "CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1','SIMLATS','SYSTOOLSPACE','SYSTOOLSPACE','SYSTOOLSPACE','','','','','ALLOW_READ_ACCESS','MOVE')"
  • 1
    Not supported in 9.7 as far as I know – Lennart Jul 30 at 12:41
  • I've mentioned that on DB2v11 so you need to upgrade you version bro – Hamed Ghanbari Aug 1 at 12:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.