admin_move_table, tables stuck in CLEANUP

All we need is an easy explanation of the problem, so here it is.

I’m looking into converting a number of log tables to range partitioning, main purpose is to reduce the size of the database by detaching and archiving historical data. Process looks like:

CALL SYSPROC.ADMIN_MOVE_TABLE('S','T1','', '', '', '', '',
'(ACTION_TIME) (STARTING FROM (''2000-01-01-00.00.00.000000'') ENDING AT (''2029-12-31-23.59.59.999999'') EVERY 1 YEAR)', '', 'COPY_USE_LOAD', 'MOVE' );

...

CALL SYSPROC.ADMIN_MOVE_TABLE('S','T120','', '', '', '', '',
'(ACTION_TIME) (STARTING FROM (''2000-01-01-00.00.00.000000'') ENDING AT (''2029-12-31-23.59.59.999999'') EVERY 1 YEAR)', '', 'COPY_USE_LOAD', 'MOVE' );

For simplicity reasons during this test, I did not do anything about the index on each table (p.k. + ts), they remain in the same tablespace as before. All tables use this tablespace for their indexes

Seems to work alright, although I get a number of mysterious warnings. For every table I get:

SQL0206N "STATSPROFTYPE" is not valid in the context where it is used.

and for some of the temporary tables (which seemed to be tables with quoted names ending with lowercase letters), I got other complaints about runstats.

Did some tests and the tables seemed accessible.

Then I detached old partitions like:

for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from SYSCAT.DATAPARTITIONS where tabname like '%_LOG' group by tabschema, tabname having COUNT(datapartitionname) > 1"); do
    for p in part{0..18}; do 
        db2 "alter table $t detach partition $p into ${t}_$p"; 
        db2 "drop table ${t}_$p"; 
    done 
    # db2 "reorg table $t";
    db2 "runstats on table $t with distribution and sampled detailed indexes all"
done

Still all tables are accessible, but if I query:

db2 "SELECT substr(tabschema,1,20), substr(tabname,1,60), substr(VALUE,1,10) FROM SYSTOOLS.ADMIN_MOVE_TABLE WHERE KEY='STATUS'"

All tables have a value of CLEANUP. I left it for a couple of hours, there are no activity in the database, but the tables remain the same. On average I probably removed 50% of the rows in these tables, but the size according to:

db2 "CALL GET_DBSIZE_INFO(?, ?, ?, -1)"
...
Parameter Name  : DATABASESIZE
Parameter Value : 769891516416 

is not reduced.

Due to a lack of ideas, I looped over the tables and reorged each table and all indexes, but it does not seem to change anything.

Is it normal that tables stay in this state for a long period of time? Would it help to wait for CLEANUP, before migrating the next table?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It turned out that the database where the backup origins from had not been db2updv115 properly. That in turn lead to that statsproftype in sysibmadm.ADMINTABINFO did not exist, and the process halted when ADMIN_MOVE_TABLE tried to access that.

After restoring the database, db2updv115 and rebind, ADMIN_MOVE_TABLE seems to work as expected.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply