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?
Asked by Lennart - Slava Ukraini
(23862 rep)
Jun 2, 2022, 05:54 PM
Last activity: Jun 9, 2022, 08:54 AM
Last activity: Jun 9, 2022, 08:54 AM