Migration pack fails with There is already an object named 'dbo.SQ_MQ2DWP' in the database.

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When running the initial migration pack mode to migrate to a UTF8 database, the following error occurs:

 U00020274 Wrong SQL statement in 'PCK.AUTOMIC_UTF-8.MIG.SQLI.STEP08.CREATE.SEQUENCES'. DB error: 'U00003754 Database-error in SQLI-variable: 'U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42S02', 'Invalid object name 'dbo.ucmig_sequences'.''

The Connection objects for source and destination can be tested just fine

Cause

This is caused by the source database NOT being the currently active system database where the workflow is running

Resolution

The migration workflow documentation includes the following:

"Your source database used for the Migration Action Pack MUST BE the actual AE database that your system uses and cannot be a clone or copy of it. If you use a clone or copy, the Migration Action Pack fails during the Initial step because it uses the SQLI variables to get all the information required and to create supporting data within the source database."

The biggest concern that customers have is that the migration workflow will have an impact on the performance of the live system.  We have not had any reports of this actually happening.  In the end, the workflow mode 0 will:

Mode 0 - initial - of the workflow ill

• Create a handful of functions and views that are used later to help speed up select and transfer or data
• Creates the uc_migration table which holds information on the data that has already been migrated
• Selects all tables and definitions from the source database and inserts them into the destination database
• selects all index definitions from the source database and inserts them into the destination database
• Select all AH (statistics), RH (reports), and MELD (messages) records that fit specific criteria (lower than the lowest runid in the activites window/process monitoring in all clients and earlier than the CHANGE_LOGGING_DAYS in UC_SYSTEM_SETTINGS) and insert these into the target database

Mode 1 - refresh - of the workflow will:

• Select all AH (statistics), RH (reports), and MELD (messages) records that fit specific criteria (lower than the lowest runid in the activites window/process monitoring in all clients and earlier than the CHANGE_LOGGING_DAYS in UC_SYSTEM_SETTINGS and has not been recently moved) and insert these into the target database

Mode 2 - final - of the workflow will

• Do the same as Mode 1
• Create SQL statements to finalize the migration (moves all other data that has not already been moves, puts additional index constraints in place)

 

To resolve this:

Update the source database to be the live source database for the Automic system that runs the workflow
If needed - Update the PCK.AUTOMIC_UTF-8.MIG.VARA.USED.SETTINGS variable in the client where the workflow runs so that it had "Mode" set to 0.  This allows for the migration workflow to run with an empty target database