Migrating DB2 Databases to UTF-8

As a system and/or database administrator, you need to migrate your DB2 database to a new UTF-8 one before upgrading your system to v24.

Important! Before migrating your database, make sure that you are familiar with all considerations and prerequisites for the migration. For more information, see Migrating AE Databases to UTF-8.

This page includes the following:

Prerequisites to Migrate DB2 Databases Using the Migration Action Pack

As a system/database administration, you need to make sure that your system meets the requirements needed to use the UTF-8 DB Migration Action Pack.

  • 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. DB2 databases use the UTF-8 character set.

  • 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.

  • You must have a database link configured from the destination to the source database. You need to create it manually as described below.

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

For more information, see:

Configuring the Database Link for DB2 Databases

As a system/database administration, you must create the database link for migration to UTF-8 manually, where:

  • db_name = source db2

  • user = source db user

  • hostname = source hostname

In the source database, make sure that the destination user has the following rights granted:

  • The database should be a federated one set directly on the database instance, see IBM's The Federated Server documentation.

  • The database user with the given rights must create the wrapper for the database manually, see IBM's Wrappers and Wrapper Modules documentation.

Execute the following commands on the destination instance with a connection to the destination database:

DB2 => catalog tcpip node <nodename> remote <hostname> server <port>

DB2 => catalog database <db_name> as <db_name_alias> at node <nodename>

DB2 => create wrapper <wrapper_name>

DB2 => create server <db_name_alias> type db2/udb version 10.5 wrapper <wrapper_name> authorization "<user>" password "<pw>" options (dbname '<db_name_alias>')

Migrating the DB2 Database with the Migration Action Pack

Before starting, make sure that you meet all the prerequisites listed above and that you have read all the considerations and recommendations, see:

Follow the steps below to use the Migration Action Pack to migrate your AE database to UTF-8.

  1. Download the Migration Action Pack from https://marketplace.automic.com/. You can either search for Package.UTF8.DB.Migration, or simply type UTF8 to find it.

    It contains the Workflow and Connection (CONN) objects that you need for the migration.

  2. In a Client other than Client 0, go to the Administration perspective. You cannot execute the Workflow included in the Action Pack in Client 0.

  3. Click the Packs tab at the bottom of the Navigation pane on the left, see Working with Packs and Plug-ins.

  4. Click Install from File and select your downloaded package. Once selected, click Install.

  5. Once your Pack has been installed successfully, right-click it and select Jump to Source to open it.

  6. Open the SOURCE - MOVE.UC4.DB.TO.UTF8 - SETUP.OBJECTS folder, which contains two Connection (CONN) objects.

  7. Define the Connection (CONN) objects with the required connection data for the source and the destination databases:

    In the Connection sections:

    • Database Type
      The type of your source/destination database.

    • Server:Port
      Name of the source/destination computer on which the database is available and the port number.

    • Database Name
      Name of the source/destination database that should be used.

  8. Test the database connections using the Test button in the Connection Test section of each Connection object.

  9. Execute the PCK.AUTOMIC_UTF-8-MIG.START Workflow. It guides you through a documented migration process, where you can input all the relevant parameters using a prompt with various fields.

    Important!

    • Upon executing the AE database Migration Action Pack, it checks the encoding of the source (not UTF-8) and destination (UTF-8) AE databases. If one of the two checks is positive, the migration proceeds; otherwise, it aborts.

    • You must execute the Workflow running each Run Mode at least once.

    1. Define the SQL Agent for the source and destination database.

    2. Define the Run Mode of the Workflow. Each run mode uses data from the various database areas with different requirements. Therefore, you must run each mode at least once.

      • 0 - Initial (default)

      • 1 - Refresh

      • 2 - Final

      For more information, see First Migration Part - System Online.

    3. Enter the email address of the person or group that should receive the SQL queries required to complete the migration process during system downtime after the Workflow has finished.

  10. Submit your entries.

  11. Once done and depending on the defined Run Mode, you can do the following:

    • Initial: Once the Workflow finishes successfully, you can start Workflow in Refresh mode.

    • Refresh: Once the Workflow finishes successfully, you can start, you can start the Workflow again in Refresh mode until you are ready to start the offline migration part, or start it again in Final mode.

    • Final: Once the Workflow finishes and generates the required SQL queries, it sends an email to the defined address, provided that SMTP is correctly configured and works. The SQL statements 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 in case sending the email is not possible for any reason.

    Important!

    • If the email cannot be sent successfully in this final step, and the corresponding task fails with an error status, you can ignore this task and resume the Workflow.

    • No rollback function is required because the Workflow does not change anything in your source database. It is copied to the destination database and a temp table (uc_migration) that controls the data for the migration process is created. If something goes wrong, you can easily delete the destination database and the temp table and restart the migration process. If you want to perform a test run of the Action Pack, you can use a copy of your existing AE database with actual life data.

  12. Shut down your system.

  13. Manually execute the SQL queries generated in the Final mode.

    After executing the queries, the structure and data of the source AE database are completely copied to the destination AE database, which is a clone of the source AE database, but UTF-8 is configured, including the data that was converted to UTF-8. The source and destination databases have the same version.

Next Steps

Now that the database has been migrated, you have a UTF-8 AE database that has not been upgraded to v24 yet. You can now proceed with the system upgrade.

During the upgrade, make sure that the connection strings in all INI files are adapted so that they point to the new UTF-8 AE database and have the encoding set correctly to UTF-8. For more information, see Upgrading an Automation Engine System Manually.

See also: