Create IBM DB2 databases manually
Create IBM DB2 databases with SQL scripts instead of using the Connections database wizard.
Use this procedure if you do not want to use the database wizard to create the databases.
The SQL scripts are located in a compressed file called connections.sql.zip|tar, located in the INSTALL/Connections/connections.sql directory of the Connections setup directory or installation media. Extract this file before proceeding. When extracted, the SQL scripts are located in the Connections/connections.sql/application directory of the Connections set-up directory or installation media, where application is the directory containing the SQL scripts for each application.
For AIX , see the note in the Prepare the database wizard.about decompressing TAR files.
- For Linux on IBM System z with the DASD driver, the SQL scripts are located in the INSTALL_s390/Connections/connections.s390.sql directory.
- For Linux on IBM System z with the SCSI driver, back up the connections.s390.sql directory, and rename the connections.sql directory to connections.s390.sql.
If the database server and Connections are installed on different systems, copy the SQL scripts to the system hosting the database server.
AIX:Configure the AIX system hosting the DB2 databases to use the enhanced journaled file system (JFS2), which supports file sizes larger than 2 GB. To enable large files in the JFS system:
- In the SMIT tool, select System Storage Management>File System>Add/Change/Show/Delete File Systems
- Select the file system type to use and specify other characteristics as wanted. If we use a Journaled File System, set the Large File Enabled setting to true.
See the AIX documentation for more options.
When we are creating a database either with the database wizard or SQL scripts, we must log into the system where the database is hosted with the database administrator account. The default values for DB2 are db2admin on Windows, and db2inst1 on Linux and AIX. For Oracle, the default value on AIX and Linux is oracle, and system administrator on Windows. For SQL Server, the default value is the system administrator.
Perform this task for each Connections application that we are installing.
To capture the output of each command to a log file, append the following parameter to each command: >> /file_path/db_application.log
where file_path is the full path to the log file and application is the name of the log file. For example:
db2 -tvf createDb.sql >> /home/db2inst1/db_activities.log
Ensure you have write permissions for the directories and log files.
To create the application databases:
- (Only required if the database server and Connections are installed on different systems.) Copy the Connections SQL scripts to the DB2 database system. Authorize a user ID that can create the databases.
- Log in to the DB2 database system with the user ID of our owner of the database instance. The user ID must have privileges to create a database, a tablespace, tables, and indexes.
- If we created multiple database instances, specify the user ID for the first instance.
- The default administrative ID for Windows is db2admin.
- Start the DB2 command line processor in command mode and enter the following command:
db2start
- For Home page and Profiles, change to the directory where the SQL scripts for each application are stored, and then enter the following command to run the script:
db2 -tvf createDb.sql
- For Home page, run the following script:
db2 -tvf initData.sql
- For Activities, Communities, Blogs, Bookmarks, Files, Forums, Mobile, and Wikis, change to the directory where the SQL scripts for each application are stored, and then enter the following command to run the script:
db2 -td@ -vf createDb.sql
The SQL scripts for Bookmarks are stored in the dogear directory.
- For Files only: cd WP_PROFILEhe directory where the SQL scripts for each application are stored, and then enter the following commands to run the scripts for enabling Push notification:
db2 -td@ -vf pns-createDb.sql
db2 -td@ -vf pns-appGrants.sql
- Grant access privileges to the lcuser account for the Home page and Profiles databases:
db2 -tvf <application>/appGrants.sql
- Grant access privileges to the lcuser account for the Activities, Communities, Blogs, Bookmarks, Files, Forums, Mobile, and Wikis databases:
db2 -td@ -vf application/appGrants.sql
- Runs to generate statistics for the Home page database:
db2 -tvf application/reorg.sql
db2 -tvf application/updateStats.sql
- Runs to create Calendar tables in the Communities database:
db2 -td@ -vf communities/calendar-createDb.sql
db2 -td@ -vf communities/calendar-appGrants.sql
- To use the Metrics application to create the Metrics and Cognos databases:
db2 -td@ -vf metrics/createDb.sql
db2 -td@ -vf metrics/appGrants.sql
db2 -td@ -vf cognos/createDb.sql
db2 -td@ -vf cognos/appGrants.sql
The first two of these commands create the Metrics database, and the following two commands create the Cognos database. The Cognos database tables are created when we start the Cognos BI Server for the first time.
- To use Connections Content Manager (CCM) to create the CCM databases:
db2 -td@ -vf libraries.gcd/createDb.sql
db2 -td@ -vf libraries.gcd/appGrants.sql
db2 -td@ -vf libraries.os/createDb.sql
db2 -td@ -vf libraries.os/appGrants.sql
Two databases will be created to install Connections Content Manager databases.
- Close the DB2 command line processor.
- When you install Connections, the JDBC configuration page of the installation wizard asks you to provide a user ID and password for the Application User. The user ID specified on that page must have read and write access to the database. We can provide the user ID of an administrative user or create a dedicated user ID with fewer privileges. See the Create a dedicated DB2 user topic for more information.
What to do next
(DB2 for Linux on System z only.) To improve database performance, enable the NO FILE SYSTEM CACHING option. See Enable NO FILE SYSTEM CACHING for DB2 on System z topic.
Parent topic:
Create databases with SQL scripts
Related: