H2 Database Migration Steps

There are two ways user can do h2db migration:

1. Old InformixHQ (1.6.3 or lower) where old jar file should be available:

In this case, user needs to simply run one command for upgrade and data migration:
java -cp {NEWHQDIR}/informixhq-server.jar com.informix.hq.server.h2.upgrade.H2Upgrade {OLDHQDIR}/informixhq-server.jar
OR
java -cp {NEWHQDIR}/informixhq-server.jar com.informix.hq.server.h2.upgrade.H2Upgrade {OLDHQDIR}/informixhq-server.jar 
{PROPERTIES_DIR}/informixhq-server.properties
Note:
  • Path of the old InformixHQ server jar(version 1.6.3 or lower) should be known, and should be supplied where {OLDHQJAR} is referenced in the command above.
  • If {OLDHQJAR} jar path is not available, then this upgrade needs to be completed using few manual steps. Please refer section 2 below.
  • Optional second parameter -> Path of the properties file. If any H2 Database properties (encrypt.enable, encrypt.algorithm, encrypt.password) is set previously, then this should be supplied where {PROPERTIES_DIR} is referenced in the command above.
  • Current directory should contain old H2 Database file (h2db.mv.db).
  • {NEWHQJAR} referenced above is current InformixHQ (2.1.0 or higher) jar path. Default InformixHQ jar path is {INFORMIXDIR}\hq\
  • Ensure old h2db.mv.db has correct file permission for InformixHQ jar to access the same.
  • After running above command with required parameters successful output will be: 
    Backup of old h2db file is completed successfully, now old h2db file name is h2db_old.mv.db.
Alter process executed successfully.
Data export process executed successfully.
Data import process executed successfully.
Clean-up for import and export process completed successfully.

2. If Old InformixHQ (1.6.3 or lower) jar path NOT available:

In this case, manual steps given below need to be followed for H2 Database Upgrade and migration:

  • Prerequisites for manual upgrade:

    In order to migrate from H2 Database 1.4.192 to H2 Database 2.1.214, both versions of H2 Database are needed.

    Download the jars version 2.1.214 (h2-2.1.214.jar) and version 1.4.192(h2-1.4.192.jar) from H2 site or from maven repo https://mvnrepository.com/artifact/com.h2database/h2

    Ensure that the downloaded H2 jar files have appropriate file permissions for migration process. If any of the commands given below fails, migration can be started again with backup taken in step 1 below.

    Software Required Version
    h2-1.4.192.jar 1.4.192
    h2-2.1.214.jar 2.1.214
    Java 1.8
  • Procedure:
    Follow these steps in order to migrate the H2 Database:
    1. Backup existing database

      Find the existing h2 database (h2db.mv.db).

      For InformixHQ, database can be found at <installation_Path>/explore/server/h2db.mv.db

      By default, InformixHQ installation path is $INFORMIXDIR/hq

      H2 Database file name is h2db.mv.db.

      Make a copy of h2db.mv.db to some other directory for backup before starting the migration.

    2. Create a SQL File migrate.sql

      Create an SQL file migrate.sql with the following ALTER TABLE statements.

      Save the file in the same folder where h2db.mv.db file is present.

      ALTER TABLE IF EXISTS users ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS users ADD PRIMARY KEY (ID);
ALTER TABLE IF EXISTS informix_servers ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS informix_servers ADD PRIMARY KEY (ID); 
ALTER TABLE IF EXISTS informix_server_groups ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS informix_server_groups ADD PRIMARY KEY (ID);
ALTER TABLE IF EXISTS alerting_incidents ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS alerting_incidents ADD PRIMARY KEY (ID);
    3. Run the Script migrate.sql and Export data in Zip file
      Open a command line window in the path where H2 db file is present and run the java command. Replace <h2_path> with the path where h2-1.4.192.jar is present.
      • If h2db encryption is NOT enabled in InformixHQ properties file:
        java -cp <h2_path>/h2-1.4.192.jar org.h2.tools.RunScript -url jdbc:h2:./h2db -script migrate.sql
java -cp <h2_path>/h2-1.4.192.jar org.h2.tools.Script -url jdbc:h2:./h2db -script h2db.zip -options compression zip
      • If h2db encryption is Enabled in InformixHQ properties file:

        If h2.encrypt.enable, h2.encyrpt.password, and h2.encrypt.algorithm parameters are set in InformixHQ server properties file, then the commands will be modified as shown below.

        Example: If h2.encrypt.enable=true, h2.encyrpt.password=password123, and h2.encrypt.algorithm= AES, h2 database URLs should be modified as shown below:

        (Add a space after the password, since the format is <file_password> space <user_password>)

        java -cp <h2_path>/h2-1.4.192.jar org.h2.tools.RunScript -url "jdbc:h2:./h2db;CIPHER=AES" -password "password123 " 
-script migrate.sql
java -cp <h2_path>/h2-1.4.192.jar org.h2.tools.Script -url "jdbc:h2:./h2db;CIPHER=AES" -password "password123 " -script h2db.zip
 -options compression zip
    4. Rename altered H2db

      Rename the existing h2db.mv.db database. 

      mv h2db.mv.db h2db_old.mv.db
    5. Create new H2 Database (2.1.214) and restore data

      Run following java command to create new H2 Database file (h2db.mv.db) and to restore data from zip file created in step 3. Replace <h2_path> with the path where h2-2.1.214.jar is present.

      • If h2db encryption is NOT enabled in InformixHQ properties file:
        java -cp <h2_path>/h2-2.1.214.jar org.h2.tools.RunScript -url jdbc:h2:./h2db -script h2db.zip -options compression zip FROM_1X
      • If h2db encryption is Enabled in InformixHQ properties file:

        If h2.encrypt.enable, h2.encyrpt.password, and h2.encrypt.algorithm parameters are set in InformixHQ server properties file, then the commands will be modified as shown below.

        Example : If h2.encrypt.enable=true, h2.encyrpt.password=password123, and h2.encrypt.algorithm= AES, h2 database URLs should be modified as shown below:

        (Add a space after the password, since the format is <file_password> space <user_password>)

        java -cp <h2_path>/h2-2.1.214.jar org.h2.tools.RunScript -url "jdbc:h2:./h2db;CIPHER=AES" -password "password123 " -script h2db.zip 
-options compression zip FROM_1X

      Once all the above steps are completed without any error, data is successfully migrated to h2db.mv.db.

    6. Cleanup Migration files (Optional)

      At this point h2db.zip , h2db_old.mv.db, and migrate.sql files can be deleted.