Final step V24 UTF8 Migration workflow SQL statements take hours
Article ID: 391203
Updated On:
Products
Issue/Introduction
The "Final" step of the UTF8 migration workflow, running SQL statements manually, takes hours when it's expected to be shorter.
Environment
Source AE version: 21.0.x or earlier
Resolution
After the initial and refresh runs of the workflow, check the uc_migration table on the source database to see what the end_time and ah_idnr_end are. Compare these with the highest ah_idnr in the source database ah table. If there is a large gap between these, additional investigations will be needed.
If there is a large gap between the highest ah_idnr_end in the uc_migration table and the highest ah_idnr in the ah table, it could be that there are too many items "active" in Process Monitoring still. There were older versions of Automic that kept EH data even when a client was deleted, so it may be necessary to check for that. The following example SQL will give information on what the oldest EH entries are:
select min(eh_aH_idnr), eh_client from eh group by eh_client
select min(eh_rdate), eh_client from eh group by eh_client
If the eh_client shows up as something that is actively used and there is still a large gap between it and the max ah_idnr in ah, then it may be necessary (and is highly encouraged) to deactivate some objects from Process Monitoring in those clients.
Another way to speed up the migration in general is to ensure that your database maintenance - archive (if necessary), reorg, and unload utilities should be run regularly to keep A* and R* tables smaller.
More information:
The initial migration and refresh migration are the key to being sure that only a small amount of data is necessary for the final migration workflow run. The uc_migration table is created during the initial migration workflow run and includes the highest run_id that will be moved prior to the next run of the workflow. This number in the uc_migration table is based on the lowest run_id in the EH table (process monitoring) or the oldest record in AH (statistics) that is as many days old as the CHANGE_LOGGING_DAYS setting in UC_SYSTEM_SETTINGS. The only way to speed things up for the final migration is to ensure that the EH table does not have old records in it and to lower the CHANGE_LOGGING_DAYS setting in UC_SYSTEM_SETTINGS to something like 1.
More info on this can be found in the documentation here: https://docs.automic.com/documentation/webhelp/english/ALL/components/DOCU/latest/Automic%20Automation%20Guides/Content/Installation_Upgrade/install_upgrade_MigratingAEDB.htm?Highlight=migration#link7
So if after the initial run of the workflow, you run the select statement against the source database:
select * from uc_migration
You should see a row that has a start_time of 1997-12-19 00:00:00 and includes an end_time that should not be too far in the past. You should also see an AH_IDNR_START of 1000001 and then an AH_IDNR_END. You can see how many additional records need to moved in future runs of the migration workflow by running the following query:
select count(*) from ah where ah_idnr > [insert the AH_IDNR_END number here];
This will show the amount of records that still have to be moved. If it seems like too high a number, it's likely that there is an old EH record that is stopping newer records from being migrated. You can find this with a query like:
SELECT eh_client, COUNT(eh_client) AS record_count, MIN(eh_starttime) AS oldest_starttime FROM EH GROUP BY eh_client;
If the start time returned for a client or multiple clients seems like it's too old, then you can log into that client and find the oldest record(s) in Process Monitoring and take action to remove them from Process Monitoring (restart C_PERIOD objects, events, etc...). Please note that the query above was created for SQL Server and might not work on all types of backend databases - a DBA should be able to assist in syntax for other database types.
If the number of records that still need to be migrated after the above is very low and the final offline piece of the workflow still take too long, you may want to talk to a DBA about database tools to migrate the data to UTF-8. A lot of customers with large databases have had success with something like Oracle Data Pump; tools like these require the DBA's assistance and are not something that Broadcom Support would be able to help with.
Feedback
