How to migrate non-parititioned data to paritioned tables in the warehouse - Option 1
trwright 100000VPVF Visits (5541)
This blog concerns how to move data from non-partitioned warehouse tables to partitioned tables.
This issue comes up when warehousing has been done for some period of time without partitioning turned on in the warehouse proxy agent and summarization and pruning agent. Then the customer decides to use partitioning. If tables already exist in the WAREHOUS database without partitioning turned on then turning it on will not cause the data to start being partitioned. The data will continue to be inserted into the non-partitioned tables and there will likely be errors as it's trying to insert partitioned data into non-partitioned tables.
There are 2 options for changing the tables to use partitions.
Option 1 - all current data collected so far will be lost and the tables will be re-initialized
Remove all of the current non-partitioned tables and allow them to be re-created by the warehouse proxy and summarization and pruning agents.
Using this option you will lose all of the current data in the raw data tables as well as the summarized tables in the database.
Steps are as follows
1) stop the warehouse proxy agent and the summarization and pruning agent
./itmcmd agent stop hd
./itmcmd agent stop sy
bring up the MTEMS
stop the warehouse proxy agent
stop the summarization and pruning agent.
2) Verify that the following options are in the summarization and pruning and warehouse proxy configuration files:
Summarization and pruning configuration file:
filename: sy.ini (or ksyenv on Windows):
Warehouse proxy configuration file
filename: hd.ini (or khdenv on Windows):
3) Remove the current tables from the database. In this example we will use the KLZ_Disk tables, but all tables that are currently not partitioned will need to be removed.
Note: the raw data table as well as all summarized tables will need to be removed.
Assuming all summarization is turned on, the following tables would need to be removed:
4) Once all of the tables are removed start the summarization and pruning agent and the warehouse proxy agent and the tables should then be re-created using partitions. It may take a day or more to actually create the tables and start populating them.
./itmcmd agent start hd
./itmcmd agent start sy
bring up the MTEMS
start the warehouse proxy agent
start the summarization and pruning agent.
Option 2 concerns migrating the data from the non-partitioned tables to the partitioned tables (no data loss). It is covered in a separate blog with this title:
How to migrate non-parititioned data to paritioned tables in the warehouse - Option 2
Subscribe and follow us for all the latest information directly on your social feeds: