Create Oracle databases with SQL scripts instead of using
the IBM® Connections database
wizard.
Before you begin
Follow this procedure if you do not want to use the database
wizard to create your databases.
The SQL scripts are
located in a compressed file called connections.sql_*.zip, located in the
IBM_Connections_Install/IBMConnections/native directory of the IBM Connections setup directory or installation media. Extract this file before
proceeding. When extracted, the SQL scripts are located in the
IBM_Connections_Install/IBMConnections/native/connections.sql/application_subdirectory
directory of the IBM Connections set-up directory or
installation media, where application_subdirectory is the directory that contains
the SQL scripts for each application.
If the
database server and IBM Connections are installed on different
systems, copy the SQL scripts to the system that hosts the database server.
Notes:
- You must specify the Unicode AL32UTF8 character set when you create the Oracle database.
- The createDb.sql script for communities works only if the blocksize
is increased to 16K from the default blocksize of 8K. For more information,
refer to your Oracle documentation.
About this task
This task describes how to use SQL scripts to create Oracle
databases for IBM Connections
applications. Complete this task only if you do not want to use the
database wizard.
Note: To capture the output of each
command to a log file, run the following commands before starting
this task:
sql> spool on
sql>
spool output_file
where output_file is
the full path and name of the file where the output is captured.
When
you have completed this task, run the following command: sql>
spool off
To manually create the application database
tables, complete the following steps:
Procedure
- Log in with the same user ID that you used to install the
Oracle database system.
- Create an Oracle
user ID with system database administrator privileges that you can
use to manage the database tables. Alternatively, use an existing
ID that has administrative privileges, such as
SYS
.
- Set the ORACLE_SID.
If you created multiple
databases, specify the database on which to install the tables by
providing the SID for that database.
- Run SQL Plus by entering the following command:
- Log in as an administrator with the sysdba role by entering
the following command:
connect as sysdba
Note: If
not logged in as sysdba, the statistics gathering job for the Bookmarks
database is not created or correctly scheduled. As a result, database
performance is impacted.
- Enter the Oracle user ID and password.
- For each application, change to that application's SQL
scripts directory and enter the following command to create the application's
database tables:
@application_subdirectory/createDb.sql password
Notes:
- Repeat this step for each IBM Connections
application that you plan to install.
- Begin the command with the @ symbol.
- The createDB script creates a dedicated user
ID for the JDBC connector for an application database. Later, when
you run the IBM Connections
installation wizard, you must provide the user ID that you specify
in this step. You can specify one of the following default user IDs:
- Activities: OAUSER
- Blogs: BLOGSUSER
- Bookmarks: DOGEARUSER
- Cognos: COGNOS
- Communities: SNCOMMUSER
- Files: FILESUSER
- Forums: DFUSER
- Global Configuration Database: FNGCDUSER (Connections Content
Manager)
- Home page: HOMEPAGEUSER
- Metrics: METRICSUSER
- Mobile: MOBILEUSER
- Object Store: FNOSUSER (Connections Content Manager)
- Profiles: PROFUSER
- Push Notifications: PNSUSER
- Wikis: WIKISUSER
Notes:
- Each of these default user IDs has a narrower set of privileges
than an administrative user ID.
- You can change the passwords for these database users later in
Oracle Enterprise Manager Console. If you change the passwords there,
you must also change them in the J2C authentication alias settings
in the WebSphere® Application
Server Integrated Solutions Console.
- If you plan
to install the Metrics application, you can create the database now
but the tables are not created until you start the Cognos BI Server
for the first time.
- Optional: (Communities only.) Run the following
commands:
@application_subdirectory/calendar-createDb.sql
@application_subdirectory/calendar-appGrants.sql
- Optional: (Dogear only.) Run the following
command:
@application_subdirectory/createHistogramStatsJob.sql
Note:
- This script creates a job to collect histogram statistics.
- You must run this command while logged in with the SYS ID.
- For this command to run successfully, grant ANALYZE ANY DICTIONARY
and ANALYZE ANY system privileges to the DOGEAR account.
- (Home page only.) Run the following command:
@application_subdirectory/initData.sql
- Run the following command to grant access privileges for
each application:
@application_subdirectory/appGrants.sql
For Connections Content Manager, the appGrants.sql
scripts in
library.gcd and library.os require a password for
FNGCDUSER and FNOSUSER. Run the following commands:
library.gcd/oracle/appGrants.sql password
library.os/oracle/appGrants.sql password
- Close the SQL Plus window.