Migrating AE Databases to UTF-8

As of v24, Automic Automation requires you to migrate the data in your existing (source) AE database to a new (destination) database configured for UTF-8 before you can start the regular upgrade process to v24.

Broadcom provides the UTF-8 DB Migration Action Pack on the Marketplace which you can use to perform the database migration. However, the Action Pack only allows a database migration to UTF-8 and it is not an upgrade. The destination database after the migration has the same version as the source database.

Important!

You have to migrate the database to a UTF-8 one first and then upgrade your system. This is necessary because AE databases used single-byte encoding in previous versions, providing character sets similar to basic (0-127) + extended (128-255) ASCII. The basic ASCII characters have the exact binary representation in UTF-8 and can be mapped directly to UTF-8. However, the extended ASCII ones have a 2-byte representation in UTF-8; therefore, you cannot just change the encoding of an existing AE DB to enable it for UTF-8. To learn more about UTF-8 changes in Automic Automation, see Universal Language Support (Unicode).
You can import the Action Pack to Automic Automation systems v12.3.9 HF2 (only) and from v21.0 4 onward.
If you use the Migration Action Pack, be aware that the source and the target databases MUST BE of the same type. For example, it is not possible to migrate a PostgreSQL database to an MS SQL one.
Alternatively, you also have the option to use a third-party vendor or tool, as well as the DB Unload and DB Load Utilities (if you only need to migrate objects). For more information, see AE DB Load and AE DB Unload.

Warning!

Do not run database maintenance that removes records (DB Unload in maintenance mode) at the same time that the migration workflow is running - that is, during the Initial or the Refresh mode - as it can lead to orphaned records.

This section provides an overview of the prerequisites and all relevant aspects to be considered before the migration. It also outlines how the UTF-8 DB Migration Action Pack works.

For database-specific information and migration instructions, see:

This page includes the following:

Considerations about Migrating the AE Database to UTF-8

Before migrating your database to UTF-8 and upgrading your system to v24, make sure you consider all the aspects listed in this section.

Is your database already UTF-8 encoded or not?

How to proceed varies depending on the database that you are using:
- PostgreSQL: Is already UTF-8 encoded; therefore, you do not need to migrate the database. You can proceed with the upgrade. You do not need any additional configuration after the upgrade.
- DB 2 and MS SQL: You need to migrate the database to UTF-8. When you upgrade your system, make sure your system can connect to the new database.
- Oracle DB already UTF-8 encoded: Since it is already UTF-8 encoded, you do not need to migrate the database.
- Oracle DB not UTF-8 encoded: You need to migrate the database to UTF-8. When you upgrade your system, make sure your system can connect to the new database.
Which MS SQL database versions are relevant?

Make sure that the source AE database that you want to migrate to UTF-8 is on version MS SQL Server 2017 or higher before migrating it. The AE DB Migration Action Pack does not support MSSQL Server versions lower than 2017 which will result in an error such as STRING_AGG is not recognized. The destination AE database must be on version MS SQL 2019 or higher to support UTF-8.

To use a UTF-8 AE database you may need to enable a Windows beta feature. For more information, see Preparing the AE Database - MS SQL.
How will migration affect the database data?

The majority of the converted characters will stay single-byte (basic ASCII), and some will be converted into 2-byte characters (extended ASCII such as Umlauts, ß, €, and so on). Therefore, you may expect a slight increase in size depending on the actual data to be converted.
How will migration affect the database field data types?

As the data types of the AE database fields have not changed, there will be no increase in this context.
How will migration affect the database field length?

The length of some database fields was extended, but as the data to be migrated only eats up the size it needs (but not the max field size), there will be no increase in this context.

Prerequisites for the UTF-8 DB Migration Action Pack

As a system/database administration, you make sure that your system meets the requirements to use the UTF-8 DB Migration Action Pack. The prerequisites that all systems must meet are the following:

In Client 0, set the following keys of the UC_SYSTEM_SETTINGS variable:
- SQLVAR_INTERNAL key to YES
- SQLVAR_MAX_ROWS key to 1000 lines or higher
- VAR_SECURITY_LEVEL to 3 (allow all)
Create a new, empty UTF-8 enabled AE database with the following character sets:
- MS SQL: Latin1_General_100_CI_AS_SC_UTF8
- DB2: UTF-8
- Oracle: AL32UTF8
You must have an Agent for Database Variables installed
You must have an Agent for Database Jobs and Events (SQL Agent) installed and able to reach the source and destination databases
You must set both Connection (CONN) objects for the source and destination databases respectively before executing the Workflow.
If you use an alternative MS SQL database schema, you must configure two additional Connection (CONN) objects in the MSSQL MQ sub-folder.
You must have a database link configured from the destination to the source database.
For Oracle databases, make sure that the database user of the destination database has the privilege to create DB links (GRANT CREATE PUBLIC DATABASE LINK TO <destination DB user>) before executing the Action Pack.

As before any migration or upgrade, make sure you also create a backup of your existing database.

To have this information readily accessible while working with a specific database, these prerequisites are also listed in the database-specific documentation pages, see:

Issues that Influence the Database Migration

Make sure you consider the following issues, as they will affect your migration strategy:

Are stats, reports, messages and object audit needed? If not, you could consider using the Transport Case to migrate your data.

Important! When you use the Transport Case, your target system must already be set up with the necessary Agents and relevant authorizations to move objects. For more information, see Transport Case.
Database size: The database size directly affects the time that the Initial, Refresh or Final modes may take. It could take hours or days to run them.
CHANGE_LOGGING_DAYS: If a lot of the data you need to migrate falls within the period defined in the CHANGE_LOGGING_DAYS key of the UC_SYSTEM_SETTINGS, the system downtime required may be long. For more information, see CHANGE_LOGGING Parameters.

Important! In scenarios with non-ILM databases, the UTF-8 DB Migration Action Pack automatically adds a 2-day buffer to the value specified in CHANGE_LOGGING_DAYS. This ensures that, no matter what you define in the variable, enough time has elapsed so that all tasks are finished and deactivated before starting to transfer the records.
Reorg (UC_UTILITY_REORG): If the database size is mainly related to long data retention periods, the Initial mode of the migration may take longer. For more information, see AE DB Reorg.
Maximum acceptable downtime: The system downtime depends on how long the offline data transfer takes. Therefore, it is recommended leaving as little data as possible to be migrated at this stage.
Network between databases: The faster the network, the better. The Initial, Refresh or Final modes will be faster.
Do you have experience with and knowledge about database migrations and vendor tools? If so, depending on all parameters, you could consider using vendor tools.

Important Recommendations

Make sure you also consider the following issues, as they crucial for the cutoff date between the Initial and the Final steps of the migration:

Check the setting of the CHANGE_LOGGING_DAYS key of the UC_SYSTEM_SETTINGS variable and consider adjusting it to a lower value, see CHANGE_LOGGING Parameters.
Clean up your Process Monitoring perspective before you start, see First Migration Part - System Online. This means that you must deactivate all tasks in the Process Monitoring perspective; otherwise, the Initial mode only gets data older than the oldest active task in the Process Monitoring perspective. For more information about deactivating tasks, see Understanding the Task List.

Note: C_PERIOD, EVNT and JSCH tasks change their execution record on a daily basis so you can ignore them.

How Does the UTF-8 DB Migration Action Pack Work?

The main component of the Migration Action Pack is a large Workflow that is designed to keep the AE downtime (offline) short.

The larger portion of the data migration happens while the system is in operation (online) and is divided in three modes: Initial, Refresh, and Final. The second portion of the data migration requires system downtime. How long does this take depends on the amount of data that was migrated during the first part. Therefore, it is recommended migrating as much data as possible during the first part.

The Migration Action Pack can be used to migrate databases with or without ILM data. If your database is partitioned with ILM, the Action Pack only migrates closed partitions. During the Refresh mode, which can be carried out multiple times, you can migrate partitions that were not closed at the Initial mode or during previous runs of the Refresh mode.

If you use MS SQL with ILM and your system drops partitions during the AE database migration, there might be partitions copied to the destination AE database that were already dropped in the source AE database.

For more information about ILM, see ILM - Information Lifecycle Management.

The performance metrics of an AE database migration will vary significantly depending on the particular environment such as AE server environment, database server environment, source AE database activities due to regular workload, and so on.

First Migration Part - System Online

Note: The first migration part described here applies to non-ILM databases only.

This first part of the database migration process happens while the system is in operation (online) and runs in parallel to your usual workload activities. It is a task focusing on the larger set of historical data in the AE database that no longer changes. The data is copied to the new UTF-8 AE database via a database link.

The Workflow provides three modes that copy data from the source to the destination database: Initial, Refresh, and Final mode.

Initial Mode

The Initial mode runs only once while your system runs normally in parallel to your regular workload processing.

The Workflow creates a temp (uc_migration) table in the source database to store metadata about the migration activities. In the background, it copies all historical data (execution data in AH tables) to the destination database, assuming that they:

do not have any active tasks in Process Monitoring (executions in EH tables)
are older than today minus the CHANGE_LOGGING_DAYS definition considering the additional 2 days applied to this variable by the Migration Action Pack.
are older than the oldest active tasks in the Process Monitoring

For messages (MELD tables), and object audits (XAO tables), everything that is older than 1 day is migrated.

Examples

Lets assume that the Process Monitoring perspective has not been cleaned (all tasks except C_PERIOD, EVNT JSCH are deactivated), that it still contains active tasks and that the parameters listed below are true.

Click the image to expand it.

Today: January 20
CHANGE_LOGGING_DAYS: 14 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 20 - 14 days - 2 days= January 4
Oldest active task in Process Monitoring: January 3

In this case, the Migration Action Pack migrates all data up to the latest entry in the Process Monitoring, that is, all data up to January 3. The data stored between January 3 (oldest activity) and January 20 (CHANGE_LOGGING_DAYS + 2 additional days) is not migrated in this Initial mode.

However, if the parameter change and the parameters below are true:

Today: January 20
CHANGE_LOGGING_DAYS: 7 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 20 - 7 days - 2 days= January 11
Oldest active task in Process Monitoring: January 15

Click the image to expand it.

In this case, the Migration Action Pack migrates all data up to the day designated by CHANGE_LOGGING_DAYS (plus the 2 additional days applied by the Action Pack), that is, all data up to January 11.

Important! To avoid misunderstandings about the cut-off date for the data migrated in this step, you must deactivate tasks in the Process Monitoring perspective.

Depending on your database type, the database schema and index are created, for example:

The following query determines AH_IDNR_START. It is either the highest AH_IDNR that is older than or equal to 10000 days ago (about 27 years - rather hypothetical case) or simply the lowest AH_IDNR in the system.

select coalesce( (select max(AH_IDNR) as AH_IDNR_START from AH where ah_timestamp4 <= SYSDATE-10000),

(select min(ah_idnr) from ah)

) from dual

The query below determines AH_IDNR_END. It is the highest AH_IDNR, older than CHANGE_LOGGING_DAYS+2, and not in Process Monitoring anymore.

select max(ah_idnr) as AH_IDNR_END from ah, ovw

where ah_timestamp1 < sysdate-to_number(ovw_value1)-2 and

ovw_oh_idnr=508 and ovw_vvalue = 'CHANGE_LOGGING_DAYS' and

ah_idnr < (select min(eh_ah_idnr) from eh)

Once this mode finishes, you can start the Workflow again in Refresh mode. Use the Workflow monitor to see what is happening and check the report, which includes all steps.

Refresh Mode

The Refresh mode copies the delta data, that is, all new historical data (AH) since the Initial or last Refresh mode and all the messages (MELD), and object audits (XAO) that are older than 1 day. You can run this mode as often as required while your system is online until you are ready to continue with the next step.

Click the image to expand it.

As during the Initial mode, the Refresh mode copies all historical data (statistics in AH tables), messages (MELD tables), and object audits (XAO tables) to the destination database, assuming that they:

were not transferred during the Initial run
do not have any activities in Process Monitoring
are older than today minus the CHANGE_LOGGING_DAYS + 2 definition
are older than the oldest active tasks in the Process Monitoring

Examples

Lets assume that the Initial mode took five (5) days to finish and run successfully. The Process Monitoring had not been cleaned (tasks deactivated) prior to running the Initial mode, which run with the following parameters :

Today: January 20
CHANGE_LOGGING_DAYS: 14 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 20 - 14 days - 2 days= January 4
Oldest active task in Process Monitoring: January 3

That means that today is January 25 and we have successfully migrated all data up to January 3.

Now, lets assume that we want to start the Refresh mode, but there are still active tasks in the Process Monitoring and the following parameters are true:

Today: January 25
CHANGE_LOGGING_DAYS: 14 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 25 - 14 days - 2 days= January 9
Oldest active task in Process Monitoring: January 5

In this case, this run of the Migration Action Pack in Refresh mode migrates only the data between the cut off date in the Initial step and the latest entry in the Process Monitoring, that is, all data between January 3 and January 5. The data stored between January 5 (oldest activity) and January 9 is not migrated in this Refresh mode run.

However, if we assume that the Initial mode took five (5) days to finish and run successfully, that the Process Monitoring had been cleaned before running it on January 20, and that it run with the following parameters:

Today: January 25
CHANGE_LOGGING_DAYS: 14 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 25 - 14 days - 2 days= January 9
Oldest active task in Process Monitoring: January 20

That means that today is January 25 and we have successfully migrated all data up to January 20.

Now, lets assume that we want to start the Refresh mode and the following parameters are true:

Today: January 25
CHANGE_LOGGING_DAYS: 14 days

Calculation: Today - CHANGE_LOGGING_DAYS definition - additional 2 days applied by the Migration Action Pack:

January 25 - 14 days - 2 days= January 9
Oldest active task in Process Monitoring: January 24

In this case , this run of the Refresh mode migrates all data between the cut off date in the Initial step and the and the day defined in CHANGE_LOGGING_DAYS + 2, that is, all data between January 18 and January 24.

During the Initial and Refresh modes, the SQL agent generates and executes the relevant SQL statements automatically so that the data from the source is copied to the destination database using the database link.

Final Mode

The Final mode runs only once while the system is online.

Click the image to expand it.

This step generates a final set of SQL queries that you must execute to complete the AE database migration. This step sends the queries to you by email, if you have configured it so. The queries are also stored in the PCK.AUTOMIC_UTF-8.EMAIL.CONTENT.FINAL.STEPS VARA object that is provided in the SOURCE - MOVE.NON-UTF8.DB.TO.UTF8 - EMAIL folder.

Click the image to expand it.

As a system/database administration, you must execute the SQL queries manually on the destination database while the system is shut down (system downtime) to transfer the remaining historical data (AH), messages (MELD), object audits (XAO), all Executions (Process Monitoring), all object definitions (Process Assembly), and the System Tables (UC_*) to the new UTF-8 database.

For details on database areas, see also Database Areas.

Important! To execute the SQL queries you must use an SQL tool. Broadcom has tested the following tools:

Oracle: SQL Developer
DB2: IBM Data Studio
MS SQL: SQL Server Management Studio (SSMS)

Important! If you use a different third-party tool to execute these queries, you may encounter syntax errors in the SQL statements. To avoid them, you must adjust the queries..

Second Migration Part - System Downtime

The second part of the database migration process requires system downtime.

Click the image to expand it.

The Automic Automation system is shut down, and the AE data no longer changes. During the system downtime, the remaining data (not migrated during the first part) is copied from the source to the destination database.

You need to manually execute the final SQL statements that were generated in the Final mode of the first migration part on the destination database to complete the AE database migration. If you have not configure the Action Pack to send you the SQL queries per email, you can find them in the PCK.AUTOMIC_UTF-8.EMAIL.CONTENT.FINAL.STEPS VARA object that is provided in the SOURCE - MOVE.NON-UTF8.DB.TO.UTF8 - EMAIL folder.

Migration and Upgrade Scenarios

Depending on your system and requirements, you might not want to migrate your database to UTF-8 and upgrade your system to v24. This section provides an overview of the following three scenarios:

You want to migrate your database to UTF-8 and upgrade your system to v24.
You already have a UTF-8 enabled database and want to upgrade your system to v24.
(Oracle only) You only want to migrate your database to UTF-8 but keep it on v21 and not upgrade your system.

Scenario 1

In this scenario, you want to migrate a non-UTF-8 AE database to a UTF-8 one using the UTF-8 DB Migration Action Pack. Once the database has been migrated, you want to upgrade your system from v21 to v24. Make sure that you meet all prerequisites, see Prerequisites for the UTF-8 DB Migration Action Pack.

This scenario requires the following process:

Import the UTF-8 DB Migration Action Pack from our marketplace at https://marketplace.automic.com/. You can import the Action Pack to Automic Automation systems v12.3.9 HF2 (only) and v21.0.4.
Execute the Action Pack. To do so, start the Action Pack's Workflow. The definition of the PromptSet is database-specific considering also if you use ILM or not. Once done, the Action Pack generates the final SQL queries you need to complete the AE database migration.

Important! The more time you take before continuing with the next step of the migration, the longer the system downtime will be, as data keeps gathering.
Shut down your Automic Automation system thus making sure that the data in the source AE database does not change anymore.
Execute the SQL queries in the destination AE database to copy the remaining data to the destination database.

Once done, the destination AE database is a clone of the source AE database but UTF-8 ready. Again, this does not represent an upgrade as the destination database has the same version as the source one.
Manually upgrade your system to v24. While upgrading, make sure that the configuration points to the new UTF-8 AE database and has the encoding set correctly to UTF-8. For more information, see Upgrading an Automation Engine System Manually.

Scenario 2 (PostgreSQL and Oracle UTF-8)

In this scenario, you already have a UTF-8 AE database and only want to upgrade your existing system from v21 to v24. You have to do so manually. For more information, see Upgrading an Automation Engine System Manually.

If you need to check whether your Oracle database is UTF-8, go to your current WP and search for the following NLS_CHARACTERSET under U00029111 UCUDB - Database parameters:

20250217/100606.754 - U00003535 DB INFO: 'NLS_CHARACTERSET               = AL32UTF8'

Notes:

This scenario applies only to PostgreSQL databases as well as to Oracle databases already UTF-8 encoded.
For Oracle UTF-8 databases, make sure to adapt the encoding of the connection strings in the INI files to UTF-8. In previous versions, it was set to a single-byte encoding such as latin1, latin9, and so on because the AE did not support UTF-8.
PostgreSQL databases do not require any changes in the configuration.

Scenario 3 (Oracle Only)

In this scenario, you only want to migrate your Oracle AE database to UTF-8 and continue using your v21 Automic Automation because you want to upgrade your system later on. This helps you validate the new UTF-8 AE database with your current v21 system before migrating it to v24.

Migrate your source AE database to a destination UTF-8 database as described in scenario 1. Once done, both database and system are still on v21 (only the data got converted to UTF-8). You must only update the connection string to point to the new (target) database but not the encoding in the INI file, as the v21 system expects the same encoding as in the past. The data is processed/handled in v21 Automic Automation context in legacy encoding (such as latin9) but stored as UTF-8 in the AE database.

If you need to check whether your Oracle database is Latin-1 or Latin-9, go to your current WP and search for the following NLS_CHARACTERSET under U00029111 UCUDB - Database parameters:

U00003535 DB INFO: 'NLS_CHARACTERSET =  WE8??????????

Now that you have a v21 UTF-8 AE database, you can upgrade your system from v21 to v24. You have to do so manually. For more information, see Upgrading an Automation Engine System Manually.