Data Migration Guide : H2 Database in InformixHQ 

This blog discusses the migration of H2 Database version 1.4.192 to the latest H2 Database version 2.1.210 in InformixHQ 2.0.0

For new users who are using InformixHQ for the first time (version 2.0.0 or higher), there is no impact and no upgrade is needed as InformixHQ will create new H2  database with the latest version

What is H2 Database & where to find it in InformixHQ ?

H2 database is a relational database management system written in Java. It can be embedded in Java applications or run in client-server mode.  H2 database is located in the directory from where users are running informixhq-server.jar. If users are running informixhq-server.jar for the first time, InformixHQ will create the h2db.mv.db file.

Default location of informixhq-server.jar and h2db.mv.db is $INFROMIXDIR/hq.

Understanding the impact of H2 database on InformixHQ

If users using InformixHQ 1.6.3 or lower version & wanted to move on to InformixHQ 2.0.0 or above. In this case, the user will not be able to start InformixHQ until the data is migrated to the newer version of the H2 Database. If users want to run InformixHQ 2.0.0 or above without this migration, users will be encountered the following error in the InformixHQ log file:

"org.h2.mvstore.MVStoreException: The write format 1 is smaller than the supported format 2 [2.1.210/5]"

Users will also get a console message providing steps of migration along with a documentation link for reference.

Note:

1. If existing users start InformixHQ (2.0.0 or higher) using java -jar command as shown below, InformixHQ will not be started and information from section 1 below is shown to the users on console.

java -jar informixhq-server.jar informixhq-server.properties

Sample screenshot:

2. If users start InformixHQ using start-up scripts, InformixHQ will not be started and there will be no information on the console. Users need to check the HQ server logs for details.

H2 Database Migration Steps 

To migrate the H2 database users need an old version of the InformixHQ server 1.6.3 (or lower), hence we recommend users to upgrade Informix xC8 (or higher) in a different installation directory than the old installation directory. 

Notes:
1. User MUST FIRST copy xC7 (or lower) h2db.mv.db and associated properties files manually into the xC8 (or higher) directory before starting the migration. The commands mentioned in instructions DO NOT copy existing h2db.mv.db file from old to new INFORMIXDIR/hq.

2. If user have the old informixhq-server.jar [version 1.6.3 or lower] available in another location, follow the steps from section 1 below.

3. If user install xC8 or higher over the existing INFORMIXDIR, and DO NOT have the old informixhq-server.jar [version 1.6.3 or lower] saved in another location, follow the steps from section 2 below.


There are two ways user can do h2 database 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} 

Note:

• Path of the old InformixHQ server jar(version 1.6.3 or lower) should be known, and should be supplied where {OLDHQDIR} is referenced in the command above.
• If {OLDHQDIR} 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).
• {NEWHQDIR} referenced above is current InformixHQ (2.0.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.

Sample screenshot:

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

Procedure:

Follow these steps in order to migrate the H2 Database:

1. Backup existing database

2. Create a SQL File migrate.sql

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:

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

mv h2db.mv.db h2db_old.mv.db

5. Create new H2 Database (2.1.210) 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.210.jar is present.

• If h2db encryption is NOT Enabled in InformixHQ properties file: 

java -cp <h2_path>/h2-2.1.210.jar org.h2.tools.RunScript -url jdbc:h2:./h2db -script h2db.zip -options compression zip

• If h2db encryption is Enabled in InformixHQ properties file:

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

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.
 

7. Run InformixHQ (2.0.0 or higher) Normally

Sample screenshot:

In this sample migration, all the necessary files H2 jars and h2db, are kept at the same location from where migration commands are executed.

FAQ:
Why is H2 database migration required?

Between H2 Database version 1.4.192 and the latest H2 Database version 2.1.210, there have been considerable changes and direct upgrade of H2 Database is not possible. For existing users, H2 Database (InformixHQ 1.6.3 or lower) is created with version 1.4.192, hence data needs to be migrated to 2.1.210 manually.

What is use of H2 database in InformixHQ?

InformixHQ uses H2 Database as an embedded database where it stores all the details related to InformixHQ users. E.g. Informix servers connections, connection properties, repository database name, alerting configurations, etc.