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.