(Optional) Creating Metrics databases on AIX manually with SQL scripts for Connections on IBM i

Optionally create Metrics and Cognos® databases on AIX® manually using the SQL scripts that are provided on the installation media for Connections on IBM® i.

Before you begin

Use this procedure to create your databases for Metrics and Cognos on AIX manually.

Make sure the DB2® database instance has been created, a dedicated IBM DB2 database user named lcuser with restricted privileges has been created, and the DB2 instance is started. You must configure each database used in the IBM Connections deployment for Unicode. For more details please refer to Creating database schemas on IBM i for more information.

The SQL scripts are located in a packaged file called connections.OS400.sql.tar, located in the IBM_Connections_Install_OS400/IBMConnections/connections.OS400.sql. To create database manually, copy the connections.OS400.sql.tar file to the target AIX system, and use chmod to authorize a user ID that can create the databases and extend access control lists (ACLs) for the tar file.

To unzip the tar file, run the following command: tar -xvf connections.OS400.sql.tar

Then the sql folder for all applications are created there. You only need to create the Cognos and Metrics's database on AIX.

Note: Configure the AIX system that hosts 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, complete the following steps:
  1. In the SMIT tool, select System Storage Management > File System > Add/Change/Show/Delete File Systems .
  2. Select the file system type you want to use and specify other characteristics as wanted. If you use a Journaled File System, set the Large File Enabled setting to true. See the AIX documentation for more options.
Note: When you are creating a database with the SQL scripts, you must log into the system where the database is hosted with the database administrator account.

About this task

You must perform this task for the Cognos and Metrics applications that you are installing.
Note: 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 db_application is the name of the log file. For example:
db2 -tvf createDb.sql >> /home/db2inst1/db_cognos.log
Ensure that you have write permissions for the directories and log files. To create the application databases, complete the following steps:

Procedure

  1. Log into the DB2 database system with the user ID of the owner of the database instance. The user ID must have privileges to create a database, a tablespace, tables, and indexes.
  2. Start the DB2 command line processor in command mode and enter the following command: db2start.
  3. To use the Metrics application, run the following commands 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
    Note: The first two of these commands create the Metrics database and the next two commands create the Cognos database. The Cognos database tables are created when you start the Cognos BI Server for the first time.
  4. Close the DB2 command line processor.
  5. Optional: When you install IBM Connections on IBM i, the JDBC configuration page of the installation manager asks you to provide a user ID and password for the Application User. The user ID that you specify on that page must have read and write access to the database. You can provide the user ID of an administrative user or you can create a dedicated user ID with fewer privileges. Refer to Creating a dedicated DB2 user profile on IBM i for more information.