Using DB2’s ADMIN_MOVE_TABLE to Move Tables from One Tablespace to Another

What is ADMIN_MOVE_TABLE

ADMIN_MOVE_TABLE is an administrative stored procedure introduced in DB2 9.7. The intent is to provide a tool that can be used to perform an online move of a table, while transactions are still occurring against the table. Moves can be used to change what tablespace a table is in, convert a table to MDC, change the table name, perform several changes that would normally require reorgs, reduce the size of a column, and perform other changes. In the early fixpacks, it was not much more than any DB2 DBA could have written themselves. It uses triggers against the source table to track changes in a staging table while moving the data to a new table. Once the new table is populated, then the data tracked by the triggers is replayed against the target table and a table-level exclusive lock is obtained on the source table for a short period to make the switch (rename).

Recent Scenario

In this scenario, I created a new tablespace on a different disk, to work on moving some data from an existing filling disk and also move tables into tablespaces with reclaimable storage enabled. This database had been upgraded from 9.5 about six months ago. I am using ADMIN_MOVE_TABLE to move tables out of the old tablespace and into the new one.

Since this datbase is on 9.7, there are some limitations on ADMIN_MOVE_TABLE:

  1. A table being moved should have a primary key or unique index
  2. If a table being moved includes LOBs, it must have a primary key or unique index
  3. Tables with foreign key constraints referencing them cannot be moved online
  4. Tables with MQTs referencing them cannot be moved online
  5. If tables with check constraints or generated columns were created prior to 9.7, they cannot be moved until the integrity for that is refreshed, which requires at least a short time offline. You will see SQL0668N, RC 10 for this.

Some of these restrictions are lifted in later fixpacks of 10.1 or later.

With all of these restrictions, I wrote a query to give me details on each table within a tablespace so I could see what tables I will run into problems with.

with factors as (select char(TABSCHEMA,12) as tabschema
        , char(TABNAME,30) as tabname
        , char(TBSPACE,16) as tbspace
        , case when exists (select 1 from SYSCAT.TABCONST cc where cc.TABSCHEMA=st.TABSCHEMA
                        and cc.TABNAME= st.TABNAME and TYPE in ('P','U'))
                then 'YES' else 'NO' end as UNIQ
        , case when exists (select 1 from SYSCAT.REFERENCES sr
                        where sr.TABSCHEMA=st.TABSCHEMA and sr.TABNAME=st.TABNAME)
                then 'YES' else 'NO' end as FK_CHILD
        , case when exists (select 1 from syscat.references sr1
                        where sr1.REFTABSCHEMA=st.TABSCHEMA and sr1.REFTABNAME= st.TABNAME)
                then 'YES' else 'NO' end as FK_PARENT
        , case when exists (select 1 from syscat.columns sc where sc.TABSCHEMA=st.TABSCHEMA
                        and sc.TABNAME=st.TABNAME and GENERATED in ('A','D'))
                        and st.CREATE_TIME < current timestamp - 6 months
                then 'YES' else 'NO' end as GENERATED
        , case when exists (select 1 from syscat.columns sc where sc.TABSCHEMA=st.TABSCHEMA
                        and sc.TABNAME=st.TABNAME and TYPE like '%LOB' or TYPENAME = 'LONG VARCHAR')
                then 'YES' else 'NO' end as LOBS
        , case when exists (select 1 from SYSCAT.TABCONST cc where cc.TABSCHEMA=st.TABSCHEMA
                        and cc.TABNAME= st.TABNAME and TYPE = 'K')
                then 'YES' else 'NO' end as CHECK_CONST
        , case when exists (select 1 from SYSCAT.TABDEP td where td.BSCHEMA=st.TABSCHEMA
                        and td.BNAME=st.TABNAME)
                then 'YES' else 'NO' end as TABDEP
    from SYSCAT.TABLES st)

select f.*
        , case  when UNIQ = 'YES' and FK_CHILD = 'NO' and FK_PARENT = 'NO' and GENERATED = 'NO' and CHECK_CONST = 'NO' and TABDEP = 'NO'
                then 'ALL_CLEAR'
                when UNIQ = 'NO' and LOBS = 'NO' and FK_CHILD = 'NO' and FK_PARENT = 'NO' and GENERATED = 'NO' and CHECK_CONST = 'NO' and TABDEP = 'NO'
                then 'SLOW_NO_UNIQ'
                else 'NO_GO' end as CAN_MOVE
from FACTORS f
where
        TBSPACE='SOME_TAB_SP'
--      TABSCHEMA='DB2'
--      and TABNAME='SALES'
order by TABSCHEMA, TABNAME
with ur;

The things you'd have to change in the above:

  • Creation time of tables with check constraints/generated columns: In this SQL, my database was upgraded 6 months ago, so I'm checking for tables created before the upgrade. This would be different in different environments.
  • Tablespace name: To query by tablepsace, change the tablespace name to match the one you're looking for
  • Table/Schema name: Get data only for a specific table by commenting out tbspace and un-commenting the tabschema/tabname lines and completing them/
  • Function used to make output more readable: I use the CHAR function above because it works better for me at a powerShell prompt, which I was using for this scenario. On Linux/UNIX, I much prefer substr.

This query gives output like the following:

TABSCHEMA    TABNAME                        TBSPACE          UNIQ FK_CHILD FK_PARENT GENERATED LOBS CHECK_CONST TABDEP CAN_MOVE
------------ ------------------------------ ---------------- ---- -------- --------- --------- ---- ----------- ------ ------------
ACCESSORIAL  REDACTED00                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCESSORIAL  REDACTED01                     SOME_TAB_SP      NO   NO       NO        YES       YES  NO          NO     NO_GO
ACCESSORIAL  REDACTED02                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCESSORIAL  REDACTED03                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCESSORIAL  REDACTED04                     SOME_TAB_SP      NO   NO       NO        NO        YES  NO          NO     NO_GO
ACCESSORIAL  REDACTED05                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCESSORIAL  REDACTED06                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCESSORIAL  REDACTED07                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED00                     SOME_TAB_SP      YES  NO       NO        NO        YES  YES         NO     NO_GO
ACCOUNTING   REDACTED01                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED02                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED03                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED04                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED05                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
ACCOUNTING   REDACTED06                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED07                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
ACCOUNTING   REDACTED08                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
BONUS        REDACTED00                     SOME_TAB_SP      NO   NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED01                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED02                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
BONUS        REDACTED03                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED04                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED05                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED06                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR
BONUS        REDACTED07                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED08                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BONUS        REDACTED09                     SOME_TAB_SP      YES  NO       NO        YES       YES  NO          NO     NO_GO
BROWNSHOE    REDACTED00                     SOME_TAB_SP      YES  NO       NO        NO        YES  NO          NO     ALL_CLEAR

ADMIN_MOVE_TABLE

When running the ADMIN_MOVE_TABLE function, it is important to understand the various phases. Every ADMIN_MOVE_TABLE moves through these phases, in order, even if you use the simplified syntax to do it all in one execution of the command. The phases are:

  1. INIT - DB2 creates the target table, the staging table, and triggers to track activity on the table during the move process.
  2. COPY - DB2 moves the bulk of the data from the old table to the new table.
  3. REPLAY - DB2 replays changes that occurred on the old table during the COPY phase on the new table.
  4. SWAP - DB2 does a final replay of changes, then acquires an exclusive lock on the table, and makes the switch to the new table.
  5. CLEANUP - DB2 removes the interim objects and the original table.

If an error occurs, you may have to re-run ADMIN_MOVE_TABLE from the appropriate phase. You will need to know what phase DB2 was in to troubleshoot any issues.

You can run each phase to control exactly when that exclusive lock happens. On very busy tables, DB2 may not be able to perform the swap with high activity on the table.

The full syntax of the ADMIN_MOVE_TABLE procedure is in the IBM DB2 Knowledge Center.

Using SQL to write the ADMIN_MOVE_TABLE procedure calls can be really useful.

For the most part, you cannot execute two instances of ADMIN_MOVE_TABLE at the same time, at least on the 9.7 box I was working on.

Tracking Progress of ADMIN_MOVE_TABLE

ADMIN_MOVE_TABLE may run for a while depending on the table and the options you're using. developerWorks has an excellent article on improving ADMIN_MOVE_TABLE performance.

To track the progress of tables that are in the progress of being moved, you can use SQL like this:

with t1 as (select char (tabschema,10) as schema, char(tabname,30) as tabname,
       key, char(value,20) as value
       from systools.admin_move_table
       where key='STATUS')
select schema, tabname, value as status
    , case when value='COPY'
        then (select char(value,20) from systools.admin_move_table amt2 where t1.schema=amt2.tabschema and t1.tabname=amt2.tabname and key='COPY_TOTAL_ROWS')
        when value='REPLAY'
        then (select char(value,20) from systools.admin_move_table amt2 where t1.schema=amt2.tabschema and t1.tabname=amt2.tabname and key='REPLAY_TOTAL_ROWS')
    end as tot_rows
    from t1
with ur
;

This produces output that looks like this:

SCHEMA     TABNAME                        STATUS               TOT_ROWS
---------- ------------------------------ -------------------- --------------------
DB2        REDACTED00                     COMPLETE             -
DB2        REDACTED01                     COMPLETE             -
DB2        REDACTED02                     COPY                 1200000
DB2        REDACTED03                     COMPLETE             -
DB2        REDACTED04                     COMPLETE             -
DB2        REDACTED01                     COMPLETE             -
ONESYS     REDACTED00                     COMPLETE             -
SCANTRAK   REDACTED00                     COMPLETE             -

  8 record(s) selected.

The TOT_ROWS column covers total rows for the current phase, so if it's in the COPY phase, then it's total rows copied so far. If it's in the REPLAY phase, then it'll note the number of rows processed during the REPLAY phase.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 549

18 Comments

  1. Nice article Ember and thanks for sharing some great example. Just wanted to add couple of points to the above, as many of us use this ADMIN table option unknowingly.

    This utility is indeed useful in Db2 V10.5 aka BLU as well. While you are required to convert a Row Organized table into a Column Organized table in BLU, we use the db2convert utility in 10.5 and that tool itself calls ADMIN_MOVE_TABLE in the background.

  2. Hi Ember, nice reference, this helped me resolve an issue quickly.

    Found out that SQL may need correction here.

    TYPENAME like ‘%LOB’

    Cheers!!

  3. When we perform Rename activity on a table that has 10000 rows.in DB2
    1. Do we have to manually exp/imp data to the newly renamed table?or the data gets reflected automatically in the newly renamed table
    2.Do we have to manually create the structure of new table before renaming activity ? or not required.
    3. Is Reorg/runstats required post renaming activity..

    Gurus, please answer my questions.
    Thanks in Advance..

    • All the rename command does is change the name of the table. The structure of the table is not changed or even copied, only the name of the table, which is not included in any of the table-related structures. You cannot change the schema the table is. You may want to consider renaming indexes, which may include some part of the table name depending on your naming standards. All authorizations are automatically updated. I would probably do runstats to be safe, but I don’t believe it would be required.

  4. We were facing the below issue on HADR Standby server at time of every online reorg activity done,
    SQL1776N The command cannot be issued on an HADR database. Reason code =”4″.
    Reason we enabled ROS on standby, As back to back SAP jobs running,

    Do if i use admin move table method used, same issue will occur ?

    • I’m not sure I understand the question. You cannot use ADMIN_MOVE_TABLE on an HADR Standby, nor can you do online reorgs on an HADR standby.

      • Will clear my issue, We were in 24 hrs support environment,

        At every end time of reorg activity in primary, we use to face issue on standby as
        SQL1776N The command cannot be issued on an HADR database. Reason code =”4″. For some time period, do ADMIN_MOVE_TABLE will help to over come this issue

      • As we are supporting 24/7, As we enabled ROS on standby, Customer enabled batch jobs at stand-by,
        At every end time of reorg activity, Facing issue in standby as below
        SQL1776N The command cannot be issued on an HADR database. Reason code =”4″.

        Do admin move table help here ? If not let me get good solution ?

        • No, admin_move_table will not help. ROS has a number of things that make the reads not work for a period of time. I’ve actually never met someone who used it and liked it. See this page in the IBM KC for all the restrictions on the standby.

  5. Hi,

    Admin table move completed with error and status is showing REPLAY. I tried re-running the admin move with REPLAY/SWAP/VERIFY/MOVE again, but ll failed with SQL2105N Reason Code 10 (Index info cannot be found in Protocol table). Any suggestion to complete the move?

    CALL SYSPROC.ADMIN_MOVE_TABLE(‘SCHEMA’, ‘TABLE’,’TBSPACE_NEW’,’TBSPACE_NEW’,’TBSPACE_NEW’,”, ”, ”,”,”,’MOVE’)

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

    Error when retied with options REPLAY,VERIFY, SWAP, MOVE.
    SQL2105N The ADMIN_MOVE_TABLE procedure could not be completed because a
    prerequisite for running the procedure was not satisfied. Reason code: “10”.
    SQLSTATE=5UA0M

    Table State:
    SCHEMA TABNAME KEY VALUE
    ———- ———————————– ——————————– —————————————————————————————————-
    SCHEMA TABLE AGENT_ID 26169
    SCHEMA TABLE APPLICATION_ID *N0.db2inst1.190911014641
    SCHEMA TABLE AUTHID DB2INST1
    SCHEMA TABLE COPY_END 2019-09-10-21.43.30.184965
    SCHEMA TABLE COPY_OPTS ARRAY_INSERT,NON_CLUSTER
    SCHEMA TABLE COPY_START 2019-09-09-23.07.30.566388
    SCHEMA TABLE COPY_TOTAL_ROWS 856669404
    SCHEMA TABLE DICTIONARY_CREATION_TOTAL_TIME 3
    SCHEMA TABLE INDEXNAME RESRTRT1X1
    SCHEMA TABLE INDEXSCHEMA SCHEMA
    SCHEMA TABLE INIT_END 2019-09-09-23.07.21.582183
    SCHEMA TABLE INIT_START 2019-09-09-23.07.20.196404
    SCHEMA TABLE REPLAY_START 2019-09-10-21.43.30.190792
    SCHEMA TABLE REPLAY_TOTAL_ROWS 0
    SCHEMA TABLE REPLAY_TOTAL_TIME 5
    SCHEMA TABLE STAGING TABLEAELP3Bs
    SCHEMA TABLE STATUS REPLAY
    SCHEMA TABLE TABNAME_IN_CATALOG TABLE
    SCHEMA TABLE TARGET TABLEAELP3Bt
    SCHEMA TABLE VERSION 09.07.0011

    Regds,
    Kranthi

  6. I use DB2 LUW 11.1.4.4 on Linux EL 7
    I was able to move tables with
    1 ) identity columns
    2 ) FK constraints

    I moved very critical 5 tables. However when I moved one small tables I dropped one index which after which I was able to move. I’m not sure why that index was blocking.

    Also what I learnt is if you move and if fails for some reason you may have use the word CANCEL to cancel the operation else it won’t allow you to move further.

    CALL SYSPROC.ADMIN_MOVE_TABLE(‘SCHEMA’, ‘TABLE’,’TBSPACE_NEW’,’TBSPACE_NEW’,’TBSPACE_NEW’,”, ”, ”,”,”,’MOVE’)

    CALL SYSPROC.ADMIN_MOVE_TABLE(‘SCHEMA’, ‘TABLE’,’TBSPACE_NEW’,’TBSPACE_NEW’,’TBSPACE_NEW’,”, ”, ”,”,”,’CANCEL’)

  7. Hello Ember,

    Can we use AMT in place of reorg for maintenance purpose to minimize the database downtime.
    Does it make sense to use AMT along with its limitations, restrictions and resource consuming nature in place of online or offline reorgs.
    Is reorg mandatory for a database on specific intervals (I do monthly offline reorg and weekly online reorg) or can we just do it when feel its needed. (checking from reorgchk utility)
    Big follower of your articles 🙂

    • ADMIN_MOVE_TABLE can absolutely be used in place of reorg, though there is a last-minute lock drain that occurs with it, so it is up to you to determine which is lower impact. I’ve also seen a couple of bugs with AMT over the years, so would consider it a bit more risky than a reorg.

      I’m a fan of monthy or weekly online reorgs only of tables needing it. The only scenario I do regular offline reorgs for is to hit longlobdata – needed if you delete a lot of data from tables with lob columns.

      Generally if you don’t do reorgs, you’ll see worse performance and less efficient use of space over time. I could see arguments for reorgs as infrequently as quarterly, and I also tailor reorgs to avoid tables (usually very small ones) that always show up on reorgchk due to their geometry. I do NOT think every table needs reorging, and do believe that reorgs should only be done on tables when they need it.

  8. Hi Ember,
    as dependencies are fixed since Db2 10.1FP2, I have updated a bit your query with expression-base indexes and checking for table type and status. I emit a message for dependencies, although they should work without issues.
    I have asked Db2 Development, if the order of dependent tables are important. No, it is not.

    with factors as (select substr(TABSCHEMA,1,12) as tabschema
    , substr(TABNAME,1,30) as tabname
    , substr(TBSPACE,1,16) as tbspace
    , case when TYPE=’T’ and STATUS = ‘N’ then ‘YES’ else ‘NO’ end as TTYPE
    , case when exists (select 1 from SYSCAT.TABCONST cc where cc.TABSCHEMA=st.TABSCHEMA
    and cc.TABNAME= st.TABNAME and TYPE in (‘P’,’U’))
    then ‘YES’ else ‘NO’ end as UNIQ
    , case when exists (select 1 from SYSCAT.INDEXES si
    where si.TABSCHEMA=st.TABSCHEMA and si.TABNAME=st.TABNAME and si.VIEWNAME is not null)
    then ‘YES’ else ‘NO’ end as IDXEXPR
    , case when exists (select 1 from SYSCAT.REFERENCES sr
    where sr.TABSCHEMA=st.TABSCHEMA and sr.TABNAME=st.TABNAME)
    then ‘YES’ else ‘NO’ end as FK_CHILD
    , case when exists (select 1 from syscat.references sr1
    where sr1.REFTABSCHEMA=st.TABSCHEMA and sr1.REFTABNAME= st.TABNAME)
    then ‘YES’ else ‘NO’ end as FK_PARENT
    , case when exists (select 1 from syscat.columns sc where sc.TABSCHEMA=st.TABSCHEMA
    and sc.TABNAME=st.TABNAME and GENERATED in (‘A’,’D’))
    and st.CREATE_TIME < current timestamp – 6 months
    then 'YES' else 'NO' end as GENERATED
    , case when exists (select 1 from syscat.columns sc where sc.TABSCHEMA=st.TABSCHEMA
    and sc.TABNAME=st.TABNAME and TYPE like '%LOB' or TYPENAME = 'LONG VARCHAR')
    then 'YES' else 'NO' end as LOBS
    , case when exists (select 1 from SYSCAT.TABCONST cc where cc.TABSCHEMA=st.TABSCHEMA
    and cc.TABNAME= st.TABNAME and TYPE = 'K')
    then 'YES' else 'NO' end as CHECK_CONST
    , case when exists (select 1 from SYSCAT.TABDEP td where td.BSCHEMA=st.TABSCHEMA
    and td.BNAME=st.TABNAME)
    then 'YES' else 'NO' end as TABDEP
    from SYSCAT.TABLES st)

    select f.*
    , case when TTYPE = 'YES' and UNIQ = 'YES' and IDXEXPR = 'NO' and FK_CHILD = 'NO' and FK_PARENT = 'NO' and GENERATED = 'NO' and CHECK_CONST = 'NO' and TABDEP = 'NO'
    then 'ALL_CLEAR'
    when TTYPE = 'YES' and UNIQ = 'YES' and (FK_CHILD = 'YES' or FK_PARENT = 'YES' or TABDEP = 'YES') and IDXEXPR = 'NO' and (GENERATED = 'YES' or CHECK_CONST = 'YES')
    then 'LONG_OFFLINE_SWAP'
    when TTYPE = 'YES' and UNIQ = 'NO' and LOBS = 'NO'
    then 'SLOW_NO_UNIQ'
    when TTYPE = 'YES' and UNIQ = 'YES' and IDXEXPR = 'NO'
    then 'DEPENDENCIES are OK'
    else 'NO_GO' end as CAN_MOVE
    from FACTORS f
    where
    TBSPACE='USERSPACE1'
    AND TABSCHEMA = 'DAILYPROD'
    — and TABNAME='SALES'
    order by TABSCHEMA, TABNAME
    with ur;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.