DB2 to MySQL Database Migration

db migrate [url=dburl] [path=libPath] [user=username] [pw=password] [drop]
                        [check] [imported] [verify]

jdbc:mysql://mysqlserver.example.com:3306/TRAVELER

• The Traveler HA pool should be configured for V12.0.1 or higher and running smoothly before attempting migration.
• All Traveler Servers in the HA pool must be down during the entire migration process.
• The destination database exists and is not populated. If the database is populated and the user has permission to drop tables drop=true may be used to clear the existing data.
• The destination database is in the same timezone as the source database.
• The database user being used for the migration has the requisite permissions to insert the data and create the schema if applicable.
• If NTS_AUTO_DBSCHEMA is set to false the schema must be manually created before migration can begin.
• We strongly recommend tuning the innodb_buffer_pool_size before starting the migration. For more information, see MySQL's documentation.
• We recommend migrating a test environment before migrating the production database.

./travelerUtil db migrate url=jdbc:mysql://mysqlserver.example.com:3306/TRAVELER 
path=/opt/hcl/domino/notes/latest/linux/mysql-connector-java-8.0.21.jar 
user=databaseAdmin pw=samplePassword

• The Traveler HA pool should be configured for V12.0.1 or higher and running smoothly before attempting migration.
• All Traveler Servers in the HA pool must be down during the entire migration process.
• The destination database must be in the same timezone as the source database.
• The database user being used for the migration must have the requisite permissions to insert the data and the file permission to import the data into MySQL.
• We strongly recommend migrating a test environment before migrating the production database.
• We strongly recommend tuning the innodb_buffer_pool_size before starting the migration. For more information, see MySQL's documentation.

1. Create the MySQL DB Schema.
2. Determine the upload directory on the MySQL Server.
3. Prepare for export from DB2.
4. Stop Traveler on each Traveler server in the HA Pool.
5. Export the data from the DB2 database.
6. Fix files exported from DB2.
7. Validate exported files.
8. Move the DB2 .csv files onto the MySQL Database server.
9. Prepare for import to MySQL.
10. Import export data into the MySQL database.
11. Issue migration with travelerUtil.
12. Configure Traveler Server(s) to the MySQL database.
13. Start Traveler.

1. Create the MySQL DB SchemaCreate the MySQL schema.

   Use the method to create the schema described in Deploying and configuring the enterprise database. If you are using NTS_AUTO_DBSCHEMA=true, you should use the default values in the createDB.sql and appsGrant.sql file and do not need to add NTS_AUTO_SCHEMA to the notes.ini. If you want to use NTS_AUTO_DBSCHEMA=false, you should update the .sql files for your configuration. If the schema already exists and has data it needs to be dropped.

2. Determine the upload directory on the MySQL Server.
   Use the MySQL client and issue the following query to determine the directory used in Step 8) and 9):

   mysql> show variables like 'secure_file_priv';

   Note: MySQL may not recognize file paths with "\", instead change "\" to "/" in order for MYSQL to recognize that the directory matches the secure_file_priv.
3. Prepare for export from DB2.
   Extract exportDB2.sql from the DB2 folder for the <domino install datadir>\traveler\cfg\db\TravelerSQL.zip or TravelerSQL.tar.gz> and copy it to your DB2 server. Review the comments in exportDB2.sql for instructions. You may need to update the connect to database statement to run under the correct DB2 user. The export file is generated with the default schema LNT and default database TRAVELER. If your configuration uses a different database name or schema name please update them accordingly. The db2 schema and database name can be found in the database configuration information section of the system dump. Please set <export_directory> to the directory where you would like the .csv files to be generated.

   Note: You can test the export while Traveler is running to validate the export commands process. However, during the actual migration process, you must export the data when Traveler is down otherwise the data will not be consistent.
4. Stop Traveler on each Traveler server in the HA Pool.

   Go through each server in the Traveler HA Pool and stop Traveler. We recommend stopping Domino too.

5. Export the data from the DB2 database.

   Run the exportDB2.sql file to export the data. Verify each table export ran without errors and 22 files were generated. If there are errors, check the Troubleshooting section below. If the problem can be fixed, the export command can be run again. Do not proceed if errors can not be resolved.

   Windows:

   db2cmd -c -w -i db2 -tvf exportDB2.sql

   Linux® or AIX®:

   su - db2inst1
   db2 -tvf exportDB2.sql 

6. Fix file formats.

   DB2 exports columns with with null values as ",,". MySQL needs them to be ",NULL," in order for the import to work. MySQL's default escape character is \. In order to properly escape this data '\' needs to be converted into "\\". See below for a sample script to accomplish these tasks:

   Windows:

   echo "Usage: exportDataToFileFromDB2.bat <output directory>"
   set "list=account adaptive_filter_sync approvedapps banneddoc bind device graduated_sync guidmap inv_map logsettings replicas sec_records sendmail travelerreplicas ts_field_name ts_global ts_global_id ts_sync_timestamp ts_sync_timestamp_folder users ts_gud_timestamp ts_record_mapping"
   
   for %%a in (%list%) do (
      echo "Fixing format for import for table %%a"
      @PowerShell "(GC %1/%%a.csv)| %%{$_ -Replace ',,',',NULL,'}|SC %1/%%a.csv"
      @PowerShell "(GC %1/%%a.csv)| %%{$_ -Replace ',,',',NULL,'}|SC %1/%%a.csv"
      @PowerShell "(GC %1/%%a.csv)| %%{$_ -Replace '\\','\\'}|SC %1/%%a.csv"
   )

   Linux:

   echo "Usage: exportDataToFileFromDB2.sh <output directory>"
   list="account adaptive_filter_sync approvedapps banneddoc bind device graduated_sync guidmap inv_map logsettings replicas sec_records sendmail travelerreplicas ts_field_name ts_global ts_global_id ts_sync_timestamp ts_sync_timestamp_folder users ts_record_mapping ts_gud_timestamp"
   
   for table in $list; do
     sed -i s/,,/,NULL,/g "${1}/${table}.csv"
     sed -i s/,,/,NULL,/g "${1}/${table}.csv"
     sed -i s/'\\'/'\\\\'/g "${1}/${table}.csv"
   done

7. Validate exported files.
   22 .csv files should be generated. Verify that the contents of these files are encoded in UTF-8. Verify that any '\' have been properly converted to "\\". Verify that any ",," have been properly converted to ",NULL,". Some files may empty (these files would likely be adaptive_filter_sync.csv, approvedapps.csv and banneddocs.csv).

   Note: If you run export script again it overwrites the .csv files. These files need to have their formats fixed again.
8. Move the DB2 .csv files onto the MySQL Database server.

   If the files are large, consider zipping them up into one zip file before transfering. Be sure to place them in the directory identified in step 2. If this directory is not defined then the files can be uploaded from anywhere.

9. Prepare import into MySQL.

   Extract importMySQL.sql from the MYSQL folder for the <domino install datadir>\traveler\cfg\db\TravelerSQL.zip or TravelerSQL.tar.gz> and copy it to your MySQL server. Review the comments in importMySQL.sql for instructions. Update the <MySQL Upload directory> to point to the directory determined in step 2. Finally, update the database name if not using the default TRAVELER database.

   Note: MySQL may not recognize file paths with "\", instead change "\" to "/" in order for MYSQL to recognize that the directory matches the secure_file_priv.
10. Run importMySQL.sql.
    Make sure that the user running the .sql file has the File permission on the MySQL Database. Verify that each table import statement runs without error.

    mysql -u <admin_user> -p < importMySQL.sql

    Run the importMy.sql file to import the data. Make sure that the user running the .sql file has the File permission on the MySQL Database. Verify that each table import statement runs successfully without error. If there are errors check the Troubleshooting section below.

    If the problem can be fixed, the import command can be run again. However if any data was imported to database, you should start with a clean database, or re-run the import steps that failed. Do not proceed if errors can not be resolved.

11. Issue migration with travelerUtil.

    It is important that the travelerUtil migration command is only run on one server. This step finalizes the migration and verifies that it completed successfully.

    EXAMPLE:

    travelerUtil db migrate url=jdbc:mysql://<Server Name>:3306<database name> path=<path to MySQL-Connector.jar> 
    user=<db login user> pw=<db user pwd> imported

    Note: If the imported option is not specified then travelerUtil will try to move all the table data and will fail because data already exists from MySQL import step.

    If the migration fails do not proceed. If the error can be resolved, migration can be attempted again. However, if the database is now populated it must be cleared before re-attempting migration. If the error cannot be resolved, Traveler can be started, but it still uses the old database configuration. Please refer to the troubleshooting section below for help resolving common migration problems.

12. Configure Traveler Server(s) to the MySQL database.

    After a successful migration, we recommend rebuilding indexes on the new database before modifying the configuration. In order to start using the new database, the servers must be re-configured using travelerUtil db set command to point to the new database server before start-up. This must be done manually on each server in the HA pool. For more information on configuring the Traveler server for Enterprise database, see Configuring HCL Traveler for enterprise database.

    Note: This must be done on each traveler server in the HA pool.
13. Start Traveler.

    Start Traveler and Domino on each traveler server in the HA pool.

• SQL3022 error. This is likely caused by selecting from the wrong schema or not using the correct user. Confirm that the correct user and schema are being used in the exportDB2.sql file and try exporting again.

• SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL3022",
    was encountered during the execution. More information is available. SQLSTATE=01H52

• SQL3001 error. This is likely caused by exporting to a directory that does not exist or the user not having the correct permissions to export to this directory. Make sure the directory exists and the user has the proper permissions and try exporting again.

• SQL20397W Routine "SYSPROC.ADMIN_CMD" execution has completed, but at least one error, "SQL3022", 
  was encountered during the execution. More information is available. SQLSTATE=01H52

• If a file is not properly UTF-8 encoded, the following error occurs. Resolve the error by converting the file to UTF-8.

   Error 1300 - Invalid utf8mb4 character string

• If column data for nulls ",," were not correctly changed to ",NULL," the following error occurs. Resolve the error by converting ",," to ",NULL,".

  Error Code: 1366. Incorrect integer value: '' for column 'TS_TAGGED_FOR_SLOW_SYNC' 
  at row 1 0.046 sec