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:
- A table being moved should have a primary key or unique index
- If a table being moved includes LOBs, it must have a primary key or unique index
- Tables with foreign key constraints referencing them cannot be moved online
- Tables with MQTs referencing them cannot be moved online
- 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:
- INIT - DB2 creates the target table, the staging table, and triggers to track activity on the table during the move process.
- COPY - DB2 moves the bulk of the data from the old table to the new table.
- REPLAY - DB2 replays changes that occurred on the old table during the COPY phase on the new table.
- SWAP - DB2 does a final replay of changes, then acquires an exclusive lock on the table, and makes the switch to the new table.
- 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.
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.
Hi Ember, nice reference, this helped me resolve an issue quickly.
Found out that SQL may need correction here.
TYPENAME like ‘%LOB’
Cheers!!
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.
In addition to my previous questions.. what are all the restrictions that we have for Renaming table
The IBM Knowledge Center has a pretty good set of details on this. I’d still try it in a non-production environment with a similarly-structured table to verify it works before doing it in production.
thanks So much.. it really helped me clear questions on Renaming..
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.
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
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’)
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.
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;
Awesome, thank you Roland!