# Jive Messenger Database Setup

Jive Messenger stores all data in a back-end database that you must setup and manage. This document provides setup instructions for all of the currently supported databases.

JDBC 2.0 drivers are required for Jive Messenger to communicate with your database. Suggested drivers for particular databases are noted below where applicable. However, the JDBC-ODBC bridge will not work with Jive Messenger under any circumstances.

For a full list of available JDBC drivers, please see: http://industry.java.sun.com/products/jdbc/drivers.

All database schemas can be found in the `resources/database` directory of the Jive Messenger installation.

Note that additional databases besides the ones in this list may be supported. Please check the distribution for a database schema for your database or consider creating a schema yourself if one isn't provided.

Choose your database from the list below for setup details:

- MySQL
- Oracle
- Microsoft SQLServer
- PostgreSQL
- IBM DB2
- HSQL db (Hypersonic)

## MySQL

JDBC Drivers

The JDBC driver for MySQL can be found at:

http://www.mysql.com/downloads/api-jdbc.html

If you still have the older mm.mysql JDBC drivers, you are encouraged to upgrade to the newest Connector/J drivers (3.0.2 or later).

In the Jive Messenger web-based setup tool, use the following values:

- driver: `org.gjt.mm.mysql.Driver`
- server: `jdbc:mysql://[YOUR_HOST]/[DATABASE_NAME]`

where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server. In many cases `localhost` is a suitable value for [YOUR_HOST] when your database is running on the same server as your webserver.

Setup Instructions

1. Make sure that you are using MySQL 3.23.2 or later and the MyISAM table type (default).
2. Create a database for the Jive tables:
   `mysqladmin create [databaseName]`
   (note: "databaseName" can be something like 'jivexmpp')
3. Import the schema file from the `resources/database` directory of the installation folder:
   Unix/Linux: `cat messenger_mysql.sql | mysql [databaseName];`
   Windows: `type messenger_mysql.sql | mysql [databaseName];`
4. Start the Jive Messenger setup tool, and use the appropriate JDBC connection settings.

Character Encoding Issues

MySQL does not have proper Unicode support, which makes supporting data in non-Western languages difficult. However, the MySQL JDBC driver has a workaround which can be enabled by adding `<mysql><useUnicode>true</useUnicode></mysql>` to the `<database>` section of your `jive_messenger.xml` file. When using this setting, you should also set the Jive character encoding to utf-8 in the admin tool.

Further Help

If you need help setting up MySQL, refer to the following sites:

http://www.mysql.com/doc.html
http://www.devshed.com/Server_Side/MySQL

## Oracle

JDBC Drivers

For most users, the best drivers for Oracle are the ones written by Oracle (which come with the database or can be downloaded from Oracle's website). You should use the Oracle 9i "thin" drivers (users have reported bugs with the 8i drivers) that are in the the `classes12.zip` file. Do not use the `classes11.zip` JDBC driver. Note: you may wish to rename the `classes12.zip` file to `classes12.jar` or `oracle.jar` since some application servers will not work correctly with zip files.

In the Jive Messenger web-based setup tool, use the following values:

- driver: `oracle.jdbc.driver.OracleDriver`
- server: `jdbc:oracle:thin:@[YOUR_HOST]:1521:[DATABASE_NAME]`

where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server. In many cases `localhost` is a suitable value for [YOUR_HOST] when your database is running on the same server as your webserver.

Setup Instructions

First, create a "Jive" user or some other user that will "own" the Jive tables. This isn't necessary, but doing so will allow your tables to exist in a seperate tablespace.

Next import the schema from the `resources/database` directory of the installation using sqlplus (or your favorite Oracle tool such as Toad). For sqlplus: copy the "messenger_oracle.sql" file to the same directory where sqlplus is installed (should be something like /Oracle/Ora81/bin/). Next, log into sqlplus and then execute the command:
`@ messenger_oracle`
That will import the schema file. Your database is now setup.

## SQL Server 2000

JDBC Drivers

Microsoft provides a free JDBC driver that works with Jive Messenger. After downloading and extracting the drivers, place `msbase.jar`, `mssqlserver.jar`, and `msutil.jar` in the classpath of your application server or the `WEB-INF/lib` directory of your Jive Messenger web application. Use the following values in the Jive Messenger web-based setup tool:

- driver -- `com.microsoft.jdbc.sqlserver.SQLServerDriver`
- server -- `jdbc:microsoft:sqlserver://[HOST_NAME]:[PORT_NUMBER];databasename=[DB_NAME]`

where [HOST_NAME] is the host name or IP address of your database server, and [PORT_NUMBER] is the port that SQLServer is listening on (normally 1433) and [DB_NAME] is the name of the database (this parameter is optional).

Setup Instructions

1. Create a new database using Enterprise Manager if you do not already have a database you want to use. You may wish to name the database "jivexmpp".
2. Create a user to access the database if you do not already have one you'd like to use. Consider making the default database for the user be the one you created in step 1.
3. Open the Query Analyser and connect to the server.
4. Select the database you want to use for Jive Messenger from the DB drop down (the one you created in step 1 if you're using a new database).
5. Open the messenger_sqlserver_2000.sql file.
6. Press F5 to run the script. The script will run and create the necessary tables.
7. Proceed to the Jive Messenger setup tool and use the appropriate JDBC settings when prompted.

## PostgreSQL

JDBC Drivers

JDBC Drivers for PostgreSQL can be found in various distribution packages at PostgreSQL's JDBC Home Page. This installation in particular refers to `"pgjdbc2.jar"` driver for accessing PostgreSQL 7.3. Refer to the JDBC documentation at that site for more information.

In the Jive Messenger web-based setup tool, use the following values:

- driver -- `org.postgresql.Driver`
- server -- `jdbc:postgresql://[HOST_NAME]:[PORT_NUMBER]/dbname`

If left out, host defaults to localhost (not 127.0.0.1) and port to 5432.

Setup Instructions

1. Create the database. For example, using the PostgreSQL `"createdb"` utility:
   `createdb -E UNICODE jivexmpp`
2. Import the schema from the `resources/database` directory of the installation. Use psql (or your favorite Postgres sql utility) to import the Jive Messenger database schema:
   `psql -d jivexmpp -f messenger_postgresql.sql`
3. Proceed to Jive Messenger setup and use the appropriate JDBC settings when prompted.

## IBM DB2 7

JDBC Drivers

Use the JDBC 1.2 compliant driver, db2java.zip (1293KB), located in the "%DB2PATH%\java2" directory.

Values for the config file are:

- driver: `COM.ibm.db2.jdbc.app.DB2Driver`
- server: `jdbc:db2:[DB NAME, e.g. JIVE]`

If you are using a remote database you need to use the following values for the config file are:

- driver: `COM.ibm.db2.jdbc.net.DB2Driver`
- server: `jdbc:db2:[DB NAME, e.g. JIVE]`

Setup Instructions

You have to create a new database (e.g. JIVE) or use an existing one. Connect to the database using any valid username allowed to create tables and indexes. Keep in mind that DB2 UDB applies the user name as the table schema. Use the same user when you configure Jive with the administration interface. Commands are as follows:

`DB2 CREATE DB JIVE ALIAS JIVE
DB2 CONNECT TO JIVE USER [DBUSER] USING [DBPASSWORD]`
(or you can use the Command Center GUI)

Start the DB2 command window (in Windows), or the user's shell in Unix and import the schema in the `resources/database` directory of the installation with:

`db2 -tvf messenger_db2.sql`

Or, use the DB2 Command Center and run the script through the "Replication Sources" folder in the Database tree.

The database is now setup.

## HSQL 1.7.1 (Hypersonic)

Special Note

Jive Messenger bundles HSQL as its embedded database. If you choose to use the embedded database, it can be configured via the Jive Messenger installer. If you have a stand-alone installation of HSQL, follow the instructions below to connect to it.

JDBC Drivers

Because HSQL db is embedded in Jive Messenger, there is no need to download the JDBC driver separately. Values for the config file are:

- driver: `org.hsqldb.jdbcDriver`
- server: `jdbc:hsqldb:[PATH_TO_DB_FILES/DB_NAME]`

For example, the server URL might be `jdbc:hsqldb:../database/defaultdb`

Setup Instructions

Create a database for the Jive Messenger tables. This assumes you have HSQL installed in its own directory and working correctly.

1. Run the HSQL Database Manager by navigating to the folder where hsqldb.jar is located and type the following

   `java -cp hsqldb.jar org.hsqldb.util.DatabaseManager`

   then hit enter. You will now see a GUI application. Note, you will not see a hsqldb.jar in the Jive Messenger distribution - this is referring to the hsqldb.jar from the HSQL distribution.
2. When prompted with the connect window, enter `org.hsqldb.jdbcDriver` as the Driver, and specify the URL of your database in the URL field. The default password is already entered.
3. Now copy and paste the contents of `messenger_hsql.sql` from the `resources/database` directory of the installation into the command window, and hit execute. If no errors are reported, then your database is setup properly.
4. Finally, you should change the username and password login for your database: just open the .script file in your database folder and change the appropriate values. Make sure that you don't use a blank password, as Jive Messenger will not accept one.