Migrating your FEBDB (community surveys) over different Operating Systems

This post is relevant if you every want to migrate your Connections environment where you have Forms/Community Surveys installed, using a side-by-side migration and you go from one operating system to another (for example Windows to Linux) for your DB2 server (if I’m not mistaken, Community Surveys/Forms doesn’t support anything but DB2 so far). The normal way to migrate your FEBDB (the name of the database where Community Surveys stores it’s information) is by using the backup/restore procedure. However, you can’t use this procedure if your DB2 moves between operating systems (this is not entirely true. Check here for the details. For migrating from Windows to anything else, it is true though). These kind of migrations are pretty rare, so I don’t know if anyone will ever need this, but if you do and you find this post you’re going to be very grateful as this is hell to figure out.

To migrate your Connections databases across operating systems or different database software, you need to use the database transfer tool. This works pretty well (in another post I’ll post the exact details to migrate from Connections 5.5 to 6.5 using this tool). However, for your FEBDB this tool doesn’t work. Luckily a while back someone from IBM (now working for HCL), called Christopher Dawes wrote a tool to transfer the FEBDB which uses the Forms api to export all surveys from the old environment and import them in the new environment (if the tool is gone from the url, send me a message).

The procedure to use this tool is as follows:

1. Drop and Create the FEBDB in your new environment (assuming you already created some suveys to test your installation before the final side-by-side migration)
   As your database user (for Linux usually db2inst1):

   db2 "drop database febdb"
   db2 "CREATE DB FEBDB using codeset UTF-8 territory us PAGESIZE 32768"
   db2 CONNECT TO FEBDB
   db2 "CREATE BUFFERPOOL BUFFERFEB IMMEDIATE SIZE 250 PAGESIZE 32K"
   db2 "CREATE USER TEMPORARY TABLESPACE LARGE_USERTEMP PAGESIZE 32k MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 PREFETCHSIZE 16 BUFFERPOOL BUFFERFEB"
   db2 "GRANT DBADM ON DATABASE TO USER LCUSER"
   db2 "GRANT SECADM ON DATABASE TO USER LCUSER"
   db2 "GRANT ACCESSCTRL ON DATABASE TO USER LCUSER"
   db2 "GRANT DATAACCESS ON DATABASE TO USER LCUSER"

2. Create a list of all application IDs in your current FEBDB (so your old environment)
   To do this, connect to your old database and run this SQL command:

   SELECT UID from FREEDOM.APPLICATIONS

   Copy the output to a text file. One application ID per line

3. Export your surveys from current environment
   This step you need to perform after everyone is from your Connections environment when you do the side-by-side migration, but before you stop the system! One way to do this is to stop all applications except Forms/Community surveys.
   Run this command on a server with Java, for example your new DB2 server (assuming Linux. A WebSphere server should work as well):

   /opt/ibm/db2/V11.1/java/jdk64/jre/bin/java -jar <path-to>/ManageFEBAppsUtility.jar -o exportListToFS -ps https://connections.example.com/ -u <connections-admin-id> -p <connections-admin-password> -c surveys-basic -listPath <path-to/file with appIds.txt> -f <path-to/export-directory> -ss_ignoreSSL -debug

   assuming you installed db2 in the standard path. If you didn’t or used a WebSphere server, change the path accordingly. This step will create a binary file per Application ID in the selected directory.

4. Upgrade your FEBDB
   If you moved to a newer version of Community Surveys, you’ll have to upgrade your FEBDB. You do this by simply going to https://connections.example.com/surveys while logged in as your Connections admin user. Surveys will tell you if you need to upgrade the database.
5. Import your surveys in your new environment after you’ve finished the rest of the migration
   On the same server where you exported the surveys, run this command:

   /opt/ibm/db2/V11.1/java/jdk64/jre/bin/java -jar <oath-to>/ManageFEBAppsUtility.jar -o import -ps https://connections.example.com -u <connections-admin-id> -p <connections-admin-password> -c surveys-basic -f <path-to/export-directory> -deploy -data -ignoreSSL -ct TLSv1.2

   Assuming you moved your old Connections DNS url to your new environment. This process should take a while. You won’t see any messages, but you can check your FEBDB when you’re done

6. Run some extra Database commands to prepare your FEBDB for larger surveys:
   As your database user:

   db2 CONNECT TO FEBDB >> /data/migration/febdb.log
   db2 "CREATE BUFFERPOOL FEB4KBP IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 4 K"
   db2 "CREATE LARGE TABLESPACE USERSPACE4K PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL FEB4KBP"
   db2 "CREATE BUFFERPOOL FEB8KBP IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 8 K"
   db2 "CREATE LARGE TABLESPACE USERSPACE8K PAGESIZE 8 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL FEB8KBP"
   db2 "CREATE BUFFERPOOL FEB16KBP IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 16 K"
   db2 "CREATE LARGE TABLESPACE USERSPACE16K PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL FEB16KBP"

That’s it. This should give you a migrated FEBDB.

Thanks a lot to Matteo Bisi and Christoph Stöttner for helping me with this one!