1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Jive Messenger Database Documentation</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1><a name="introduction">Jive Messenger Database Setup</a></h1>
<p>Jive Messenger stores all data in a back-end database. If you choose to not use the embedded
database, you must setup and manage an external database for the application.
This document provides setup instructions for all of the currently supported databases.
</p>
<p>
JDBC 2.0 drivers are required for Jive Messenger to communicate with your database. Suggested
drivers for particular databases are noted below where applicable. Jive Messenger bundles JDBC
drivers for MySQL, Postgres, and HSQLDB.
</p>
<p>
For a full list of available JDBC drivers, please see:
<a href="http://industry.java.sun.com/products/jdbc/drivers">
http://industry.java.sun.com/products/jdbc/drivers</a>.</p>
<p>
All database schemas can be found in the <tt>resources/database</tt> directory of the
Jive Messenger installation.
</p>
<p>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.
</p>
<p>
Choose your database from the list below for setup details:
</p>
<ul>
<li> <a href="#mysql">MySQL</a>
<li> <a href="#oracle">Oracle</a>
<li> <a href="#sqlserver">Microsoft SQLServer</a>
<li> <a href="#postgres">PostgreSQL</a>
<li> <a href="#db2">IBM DB2</a>
<li> <a href="#hsql">HSQL db (Hypersonic)</a>
<!--
<li> <a href="#interbase">Interbase 6</a>
<li> <a href="#cloudscape">Cloudscape</a>
-->
</ul>
<h2><a name="mysql">MySQL</a></h2>
<ul>
<b>JDBC Drivers</b><p>
The JDBC driver for MySQL is bundled with Jive Messenger, so you do not need
to download and install a separate driver.
<p>
In the Jive Messenger setup tool, use the following values:<p><ul>
<li>driver: <tt>com.mysql.jdbc.Driver</tt>
<li>server: <tt>jdbc:mysql://[YOUR_HOST]/[DATABASE_NAME]</tt>
</ul><p>
where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server.
In many cases <tt>localhost</tt> is a suitable value for [YOUR_HOST] when
your database is running on the same server as your webserver.
<p><b>Setup Instructions</b><p>
<ol>
<li>Make sure that you are using MySQL 3.23.2 or later (4.x recommended).
<li>Create a database for the Jive Messenger tables:<br>
<code>mysqladmin create [databaseName]</code><br>
(note: "databaseName" can be something like 'messenger')
<li>Import the schema file from the <tt>resources/database</tt> directory of the installation folder:<br>
Unix/Linux: <code>cat messenger_mysql.sql | mysql [databaseName];</code>
<br>
Windows: <code>type messenger_mysql.sql | mysql [databaseName];</code>
<li>Start the Jive Messenger setup tool, and use the appropriate JDBC connection
settings.
</ol>
<p><b>Character Encoding Issues</b><p>
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
<tt><mysql><useUnicode>true</useUnicode></mysql></tt>
to the <tt><database></tt> section of your <tt>jive_messenger.xml</tt> file.
When using this setting, you should also set the Jive character encoding
to utf-8 in the admin tool.
<p><b>Further Help</b><p>
If you need help setting up MySQL, refer to the
following sites:<p>
<a href="http://www.mysql.com/doc.html">http://www.mysql.com/doc.html</a><br>
<a href="http://www.devshed.com/Server_Side/MySQL">http://www.devshed.com/Server_Side/MySQL</a>
</ul>
<p><h2><a name="oracle">Oracle</a></h2><p><ul>
<b>JDBC Drivers</b><p>
The Oracle 9i or 10g "thin" drivers are recommended and can be downloaded from
<a href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html">Oracle's website</a>.
Various versions of the thin drivers are avaialble, but the "ojdbc14.jar" driver is recommended for use with Jive Messenger.
Copy the JDBC driver to the <tt>lib/</tt> directory of your Jive Messenger installation.
<p>
In the Jive Messenger web-based setup tool, use the following values:<p>
<ul>
<li>driver: <tt>oracle.jdbc.driver.OracleDriver</tt>
<li>server: <tt>jdbc:oracle:thin:@[YOUR_HOST]:1521:[DATABASE_NAME]</tt>
</ul><p>
where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server.
In many cases <code>localhost</code> is a suitable value for [YOUR_HOST] when
your database is running on the same server as your webserver.
<p><b>Setup Instructions</b><p>
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.
<p>
Next import the schema from the <tt>resources/database</tt> 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:<br>
<code>@ messenger_oracle</code><br>
That will import the schema file. Your database is now setup.
</ul>
<p><h2><a name="sqlserver">SQL Server</a></h2><p>
<ul>
<p><b>SQL Server 2000</b></p>
<b>JDBC Drivers</b><p>
Microsoft provides a <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=07287b11-0502-461a-b138-2aa54bfdc03a&DisplayLang=en">free JDBC driver</a> that works with Jive Messenger.
After downloading and extracting the drivers, place <tt>msbase.jar</tt>,
<tt>mssqlserver.jar</tt>, and <tt>msutil.jar</tt> in the <tt>lib/</tt>
directory of your Jive Messenger installation. Use the following values in the Jive Messenger
web-based setup tool:<p>
<ul>
<li>driver -- <tt>com.microsoft.jdbc.sqlserver.SQLServerDriver</tt>
<li>server -- <tt>jdbc:microsoft:sqlserver://[HOST_NAME]:[PORT_NUMBER];databasename=[DB_NAME]</tt>
</ul>
<p>
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).
<p><b>Setup Instructions</b><p>
<ol>
<li>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 "messenger".
<li>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.
<li>Open the Query Analyser and connect to the server.
<li>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).
<li>Open the messenger_sqlserver.sql file.
<li>Press F5 to run the script. The script will run and create the
necessary tables.
<li>Proceed to the Jive Messenger setup tool and use the appropriate JDBC settings
when prompted.
</ol>
<p><b>SQL Server 2005</b></p>
A JDBC driver for SQL Server 2005 is <a href="http://www.microsoft.com/sql/downloads/2005/jdbc.mspx">available
from Microsoft</a>. After downloading and extracting the drivers, place <tt>sqljdbc.jar</tt>
in the <tt>lib/</tt> directory of your Jive Messenger installation. Use the following values
in the Jive Messenger web-based setup tool:<p>
<ul>
<li>driver -- <tt>com.microsoft.sqlserver.jdbc.SQLServerDriver</tt>
<li>server -- <tt>jdbc:sqlserver://[HOST_NAME]:[PORT_NUMBER]/database=[DB_NAME]</tt>
</ul>
<p>
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).
<p><b>SQL Server 7</b></p>
<p>Note: if you are using SQL Server 7, the Open Source jTDS drivers should
be used instead of the Microsoft JDBC drivers. Download the jTDS driver from
<a href="http://jtds.sourceforge.net/">http://jtds.sourceforge.net/</a>
and place the jtds-1.0.2.jar (name may be different depending on version
downloaded) in the lib/ directory of your Jive Messenger installation. Use
the following values in the Jive Messenger setup tool:</p>
<ul>
<li>driver -- <tt>net.sourceforge.jtds.jdbc.Driver</tt>
<li>server -- <tt>jdbc:jtds:sqlserver://[HOST_NAME]:[PORT]/[DATABASE]</tt>
</ul>
</ul>
<p><h2><a name="postgres">PostgreSQL</a></h2><p>
<ul>
<b>JDBC Drivers</b>
<p>
The JDBC driver for Postgres is bundled with Jive Messenger, so you do not need
to download and install a separate driver.
<p>
In the Jive Messenger web-based setup tool, use the following values:<p><ul>
<li>driver -- <tt>org.postgresql.Driver</tt>
<li>server -- <tt>jdbc:postgresql://[HOST_NAME]:[PORT_NUMBER]/dbname</tt>
</ul><p>
If left out, host defaults to localhost (not 127.0.0.1) and port to 5432.
<p>
<b>Setup Instructions</b>
<p>
<ol>
<li>Create the database. For example, using the
PostgreSQL <code>"createdb"</code> utility:
<br>
<code>createdb -E UNICODE messenger</code>
<li>Import the schema from the <tt>resources/database</tt> directory of the installation.
Use psql (or your favorite Postgres sql
utility) to import the Jive Messenger database schema:
<br>
<code>psql -d messenger -f messenger_postgresql.sql</code>
<li>Proceed to Jive Messenger setup and use the appropriate JDBC settings when
prompted.
</ol>
</ul>
<p><h2><a name="db2">IBM DB2 7</a></h2><p><ul>
<b>JDBC Drivers</b>
<p>
Use the JDBC 1.2 compliant driver, db2java.zip (1293KB),
located in the <nobr>"%DB2PATH%\java2"</nobr> directory. Copy the JDBC driver to the <tt>lib/</tt>
directory of your Jive Messenger installation.
<p>
Values for the config file are:
<ul>
<li>driver: <code>COM.ibm.db2.jdbc.app.DB2Driver</code>
<li>server: <code>jdbc:db2:[DB NAME, e.g. JIVE]</code>
</ul>
If you are using a remote database you need to use the following values for the config file are:
<ul>
<li>driver: <code>COM.ibm.db2.jdbc.net.DB2Driver</code>
<li>server: <code>jdbc:db2:[DB NAME, e.g. JIVE]</code>
</ul>
<p>
<b>Setup Instructions</b>
<p>
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:
<p>
<code>DB2 CREATE DB JIVE ALIAS JIVE<br>
DB2 CONNECT TO JIVE USER [DBUSER] USING [DBPASSWORD]</code><br>
(or you can use the Command Center GUI)
<p>
Start the DB2 command window (in Windows), or the user's shell in
Unix and import the schema in the <tt>resources/database</tt> directory of the
installation with:<p>
<code>db2 -tvf messenger_db2.sql</code>
<p>
Or, use the DB2 Command Center and run the script
through the "Replication Sources" folder in the Database tree.
<p>
The database is now setup.
</ul>
<p><h2><a name="hsql">HSQLDB 1.7.4</a></h2><p><ul>
<b>Special Note</b>
<p>
Jive Messenger bundles hsqldb 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 hsqlDB, follow the instructions below to connect to it.
</p>
<b>JDBC Drivers</b>
<p>
Because hsqldb is embedded in Jive Messenger, there is no need to download the
JDBC driver separately. Values for the config file are:
<ul>
<li>driver: <code>org.hsqldb.jdbcDriver</code>
<li>server: <code>jdbc:hsqldb:[PATH_TO_DB_FILES/DB_NAME]</code>
</ul>
<p>
For example, the server URL might be <tt>jdbc:hsqldb:../database/defaultdb</tt>
<p>
<b>Setup Instructions</b>
<p>
Create a database for the Jive Messenger
tables. This assumes you have HSQL installed in its own
directory and working correctly.
<ol>
<li>
Run the HSQL Database Manager by navigating to the folder where
hsqldb.jar is located and type the following
<ul>
<p>
<code>
java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
</code>
<p>
</ul>
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.
<li>
When prompted with the connect window, enter <tt>org.hsqldb.jdbcDriver</tt>
as the Driver, and specify the URL of your database in
the URL field. The default password is already entered.
<li>Now copy and paste the contents of <tt>messenger_hsql.sql</tt> from
the <tt>resources/database</tt> directory of the installation into the
command window, and hit execute. If no errors are
reported, then your database is setup properly.
<li>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.
</ol>
</ul>
<!--
<p><h2><a name="interbase">Interbase 6.0</a></h2><p><ul>
<font color="red"><b>Warning:</b></font> some users have
experienced problems trying to make Interbase work with Jive. You may
wish to try a different database such as MySQL or Postgres if possible.<p>
<b>JDBC Drivers</b>
<p>
JDBC drivers and other files for Interbase can be downloaded at
<a href="http://www.borland.com/devsupport/interbase/opensource/" target="_blank">
http://www.borland.com/devsupport/interbase/opensource/</a>. The JDBC
JAR file is called interclient.jar, and should be put in the classpath of
your application server.
<p>
Values for the config file are:
<ul>
<li>driver: <code>interbase.interclient.Driver</code>
<li>server: <code>jdbc:interbase://host/fullpath</code>
</ul>
<p>
<b>Setup Instructions</b>
<p>
<ol>
<li>Create a new database by connecting to your Interbase server with
IBConsole then use the Database/Create Database menu option.
<li>Execute the Jive SQL script using Interactive SQL. This can be
started from the Tools/Interactive SQL menu. Within Interactive SQL
load the jive_xmpp_interbase.sql script (Query/Load Script) and execute
it (Query/Execute). The database will now have been constructed.
</ol>
<p>
Notes: It would probably be a good idea to create a new user for Jive or
at least to change the password.
<p>
To use JDBC with interbase you have to have install the Interbase
InterServer which enables the InterClient JDBC driver to access the
database.
</ul>
<p><h2><a name="cloudscape">Cloudscape 3.6.4</a></h2><p><ul>
Note: The following procedure will use the embedded version of Cloudscape,
but a similar process can be used to setup the client/server version. The
downloaded zip file supports both embedded and client/server modes.
<p>
<p>
<b>Setup Instructions</b>
<p>
Cloudscape and the JDBC drivers can be downloaded at
<a href="http://www.informix.com/cloudscape/" target="_blank">
http://www.informix.com/cloudscape/</a>, the zip file is cloudscape364.zip.
After downloading, unzip the file into \cloudscape directory and add the
following JARs to the CLASSPATH of your system, the lib directory of Jive Messenger,
your application server,
or put them in the WEB-INF/lib directory created for the Jive web application.
<p>
For Windows:
<pre>
md \cloudscape
cd \cloudscape
jar xvf cloudscape364.zip
set CLASSPATH=%CLASSPATH%;\cloudscape\lib\cloudscape.jar;\cloudscape\lib\tools.jar
</pre>
<ol>
<li> Change into your Cloudscape directory, i.e. cd \cloudscape
<li> Copy in the file jive_cloudscape_364.sql.
<li> Using the Cloudscape Interactive JDBC scripting tool (ij), create the
database:
<pre>
java COM.cloudscape.tools.ij
ij version 3.6 (c) 1997-2001 Informix Software, Inc.
ij> connect 'jdbc:cloudscape:jiveDB;create=true';
ij> run 'jive_cloudscape_364.sql';
ij> select * from jiveuser;
ij> exit;
</pre>
</ol>
<p>
<b>JDBC Drivers</b>
<p>
Values for the admin tool are:<ul>
<li>driver: <code>COM.cloudscape.JDBCDriver</code>
<li>server: <code>jdbc:cloudscape:/cloudscape/jiveDB</code>
<li>username: <code>APP</code>
<li>password: <code>APP</code>
</ul>
</ul>
-->
</body>
</html>