Jive Live Assistant Database Setup

Jive Live Assistant automatically loads the database schema the first time it is installed in Wildfire. However, if for any reason this fails, please following the instructions below to properly install the Live Assistant database schema. This document provides setup instructions for all of the currently supported databases.

JDBC 2.0 drivers are required for Jive Wildfire and Live Assistant to communicate with your database. Suggested drivers for particular databases are noted in the Wildfire Database Setup instructions.

Wildfire bundles HSQLDB, so getting started with embedded database is great for evaluation.

Choose your database from the list below for setup details:

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

Note: All database scripts can be found in the database directory of the liveassistant.zip or liveassistant.tar.gz file.

MySQL

Character Encoding Issues

Until recently, MySQL did not have proper Unicode support, which made supporting data in non-Western languages difficult. However, it is possible to workaround this limitation, as follows.

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-wildfire.xml file.
MySQL 4.1 introduces better support for character encodings than previous versions. This new functionality will assign a default character encoding to the database and its tables and columns. It is best to set the default character encoding for your database before installing the Live Assistant schema so that you can be sure that you will not have encoding problems in the future. After creating your database, execute the following line in the MySQL console: ALTER DATABASE <database name> DEFAULT CHARACTER SET <character set>; For example, if you plan on using UTF-8 in Live Assistant, you should enable the JDBC driver workaround mentioned above and then execute this line in the MySQL console: ALTER DATABASE <database name> DEFAULT CHARACTER SET utf8;
Finally, in the JDBC URL for MySQL add "characterEncoding={character set}" to the end of the URL as a parameter. For example: jdbc:mysql://{host name}:{port}/{database name}?characterEncoding=utf8

There is a related KB Document on Jive Software's support site: 501.
If you need further help setting up MySQL, please refer to the following sites
http://www.mysql.com/doc.html
http://www.devshed.com/Server_Side/MySQL

Setup Instructions

Import the liveassistant_mysql.sql file into the MySql database you are using for Wildfire.

Ex. mysql -u root -p wildfire < liveassistant_mysql.sql

Oracle

Setup Instructions

Import the liveassistant_oracle.sql file from the database directory of the installation using sqlplus (or your favorite Oracle tool such as Toad) into the same database used for Wildfire. For sqlplus: copy the "liveassistant_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:
@ liveassistant_oracle
That will import the schema files. Your database is now setup.

SQL Server 2000

Setup Instructions

Open the Query Analyser and connect to the server.
Select the database you already created for Wildfire from the DB drop down.
Open the liveassistant_sqlserver.sql file.
Press F5 to run the script. The script will run and create the necessary Wildfire tables.

PostgreSQL

Setup Instructions

Import the liveassistant_postgressql.sql schema from the database directory of the installation. Use psql (or your favorite Postgres sql utility) to import the Jive Live Assistant database schema:
psql -d wildfire -f liveassistant_postgresql.sql

IBM DB2 7

Setup Instructions

Start the DB2 command window (in Windows), or the user's shell in Unix and import the liveassistant_db2.sql schema in the database directory of the installation into the database already created for Wildfire with:

db2 -tvf liveassistant_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.

HSQLDB 1.7.4

Special Note

Jive Live Assistant creates the database automatically when running with the embedded database. If you choose to use the embedded database, no configuration is needed.