Migrating Data to Oracle or PostgreSQL

Jive provides a migration utility that is designed to ease the process of migrating to a PostgreSQL or Oracle DBMS. The utility copies data from the old schema and DBMS to a new database. Along the way, the tool manages occasional imcompatibilities between DBMS systems.

Note: You must migrate your data before upgrading to version 4.

The migration tool supports the following source and destination DBMSes:

Source DBMSes	Destination DBMSes
Microsoft SQL Server, MySQL, IMB DB2, and HSQLDB	Supported Oracle or PosgreSQL

Note: If you're also migrating an existing jiveHome directory as part of upgrade, you might need to re-run the application setup tool. This might be necessary in order to specify connection parameters for the database you're migrating to. You'll know that you need to do this if, when you start the application after migrating both database and Jive home, you're not greeted by the setup tool. For information on running the setup tool, see Setting Up the Community.

You don't need to use this tool if you're upgrading from a DBMS that's supported for version 3. (See the System Requirements for more information.)

The easiest way to use the command-line migration utility is by specifying a properties file that contains the parameter values you'll need. You'll find the utility in the migration directory of your application distribution. You'll find the utility on the target computer at the following path:

/usr/local/jive/bin/migration/bin

Syntax

./migration path/to/filename.properties

Parameters

Except where noted, all of the parameters are required.

Parameter	Description
inputUrl	The JDBC URL used to connect to the source database.
inputDriver	The Java class name of the JDBC driver used to connect to source database.
inputUser	Source database username.
inputPassword	Source database password.
outputUrl	The JDBC URL to connect to the target database.
outputDriver	The JDBC driver used to connect to target database.
outputUser	Target database username.
outputPassword	Target database password.
workDir	Directory where the migration utility will store the binary data, logs, processing scripts during the migration.
Optional Parameters
customSchemas	Comma-separated list of files that contain definitions of tables that are not part of the Jive SBS schema. The table definitions must be in the schema format used by Jive SBS. See plugin documentation for additional information on how to write a schema file.
useSqlDump	Specifies that all of the data should be put into an XML file, then used later for importing. Values are `true` or `false`.
steps	A comma-separated list of steps to perform during migration. Use this property to change the default steps performed in a standard migration. Use one or more of the following values: exportBlobs: Export the blob data to the work directory. exportSql: Export the data to a set of XML files. createTargetSchema: Create the schema and custom tables in the target database. writeEtls: Write ETL (extract, transform and load) scripts to the working directory. runEtls: Execute ETL scripts from the working directory. validate: Validate the migrated data against the source database.
threadCount	Number of threads. Default is 1.

Migration Example

Here's an example of the properties you'll need as you might set them in a properties file:

inputUrl=jdbc:mysql://soul:3306/csc252
inputDriver=com.mysql.jdbc.Driver
inputUser=csc252
inputPassword=password
outputUrl=jdbc:oracle:thin:@oracle-utf8:1521:ORAUTF8 
outputDriver=oracle.jdbc.driver.OracleDriver
outputUser=KUATO
outputPassword=Kuato
workDir=/usr/local/jive/var/work/migrate
steps=exportBlobs, exportSql, writeEtls

The following sample demonstrates running the migration tool from the jive user’s home directory with a configuration file of migration.properties:

[1600][jive@targethost:~]$ ls -l
total 2408
drwxr-xr-x  4 jive jive    4096 Jan 22 14:05 applications
drwxr-xr-x  6 jive jive    4096 Jan 22 15:05 bin
-rw-r--r--  1 jive jive 2413509 Jan 22 11:01 csc.zip
drwxr-xr-x  5 jive jive    4096 Jan 22 14:05 etc
drwxr-xr-x 15 jive jive    4096 Jan 22 14:05 httpd
drwxr-xr-x  7 jive jive    4096 Jan 22 15:04 java
-rw-r--r--  1 jive jive     332 Jan 22 15:52 migration.properties
drwxr-xr-x  8 jive jive    4096 Jan 22 14:13 postgres
drwxr-xr-x  6 jive jive    4096 Jan 22 14:13 python
drwxr-xr-x  2 jive jive    4096 Jan 22 15:04 sbin
drwxr-xr-x 10 jive jive    4096 Jan 22 15:04 tomcat
drwxr-xr-x  7 jive jive    4096 Jan 22 14:05 var

[1600][jive@targethost:~]$ cat migration.properties 
inputUrl=>jdbc:mysql://soul:3306/csc252
inputDriver=com.mysql.jdbc.Driver
inputUser=csc252
inputPassword=password
outputUrl=jdbc:oracle:thin:@oracle-utf8:1521:ORAUTF8 
outputDriver=oracle.jdbc.driver.OracleDriver
outputUser=KUATO
outputPassword=Kuato
workDir=/usr/local/jive/var/work/migrate
steps=exportBlobs, exportSql, writeEtls

[1818][jive@targethost:~]$ ./bin/migration/bin/migration ./migration.properties 
log4j:WARN No appenders could be found for logger (com.jivesoftware.migration.schema.DbUtil).
log4j:WARN Please initialize the log4j system properly.
Number of open connections:
Connection URL:jdbc:mysql://10.61.130.77:3306/csc252, count:1
Number of open connections:
Connection URL:jdbc:mysql://10.61.130.77:3306/csc252, count:0
Number of open connections:
Connection URL:jdbc:mysql://10.61.130.77:3306/csc252, count:1
Number of open connections:
Connection URL:jdbc:mysql://10.61.130.77:3306/csc252, count:0
Number of open connections:
Connection URL:jdbc:mysql://10.61.130.77:3306/csc252, count:1
Wrote file:/usr/local/jive/var/work/migrate/jiveAttachData/1001.bin
Wrote file:/usr/local/jive/var/work/migrate/jiveAttachData/1002.bin
Wrote file:/usr/local/jive/var/work/migrate/jiveAttachData/1003.bin
Wrote file:/usr/local/jive/var/work/migrate/jiveAttachData/1004.bin
Wrote file:/usr/local/jive/var/work/migrate/jiveAttachData/1005.bin
…
jiveAnswer:2000
jiveDocTypeElement:2001
jiveDocBodyVersion:2002
jiveDraftImage:2003
jiveAttachVersion:2004
jiveImageVersion:2005
jiveWFCurrStepPrev:2006
jiveUsrRelGrApr:2007
jiveUsrRelGrNtf:2008
jiveUserRel:2009
jiveUsrRelListMap:2010
jiveCollaboration:2011
jiveSGroupMember:2012
jivePTracker:2013
jiveSectionElement:3000
Step: writeEtls completed in:0
Memory used: 18764656