Using DB2 as Content Manager for Cognos
Abstract:
Cognos8.3 installs Derby10.1.2.1 as the default content manager. This article will help you, if you want to use DB2 as the content manager for Cognos.
This article will guide you to setup Cognos with DB2. It also covers how to setup IIS or IBM Http Server with Cognos.
This article is tested on the following product version.
- IBM DB2 9.1
- Cognos 8.3
- IIS 5.1
Steps
1. Install DB2
For getting information on how to install DB2 please visit.
This article expecting DB2 is installed into the default location
i.e. : “C:\Program files\IBM\SQLLIB”. If DB2 is installed in other location, then please change the path later in this article.
2. Install Cognos without Embedded Derby database.
Make sure the “Cognos Content Database” is deselected. Assume Cognos is installed to its default path i.e. ”C:\Program Files\cognos\c8”.
3. Create a Database in DB2, which Cognos will use as Content Manager.
Cognos stores its contents in a Database. So we need to create a database in DB2. As discussed in the “INSTALLATION AND CONFIGURATION GUIDE”, the database should have specific buffer pools and table spaces. Script “CognosOnDB2.db2” is attached with this article, to avoid the tedious process of setting those buffer pools and table spaces. Save the attached script (say at C:\Documents and Settings\Administrator\Desktop”).
To execute the script follow these steps.
· On a command widow type “db2cmd”. You will get a new command prompt.
· On this new command prompt type ‘db2 –tf “<full path of the script>\CognosOnDB2.db2” ‘. This will cerate a database named “COGNOS”, required buffer pools, table spaces and set all the required authentications.
4. Copies the required DB2 jar files into Cognos Environment.
Copy db2java.zip, db2jcc.jar file from “C:\Program files\IBM\SQLLIB\java” to “C:\Program Files\cognos\c8\webapps\p2pd\WEB-INF\lib”
5. Set up Java.
On Windows platform, by default java will be installed with Cognos8.3. This java will be located at “C:\Program Files\cognos\c8\bin\jre\1.5.0”. You can set this Java as the default java for Cognos. For that, you need to create a new Environment variable named “JAVA_HOME”.
Create a new Environment variable
If you are using any other java (not the default Cognos java) then you need to copy this jar file.
Copy C:\Program Files\cognos\c8\bin\jre\1.5.0\lib\ext\bcprov-jdk14-134.jar to “<your java Home dir>\lib\ext”.
If you have not copied this jar file, you will get security exception, while starting Cognos service.
Note: Before using other Java, check the compatibility at Cognos Support Site, http://support.cognos.com/.
6. Install IIS server and setup Virtual Host.
If IIS is not installed on your system, then install it by following this.
Go to Control Panel -> Add remove Programs -> Add Remove Windows Components -> IIS
Cognos need two Virtual directories in IIS.
Visit http://support.microsoft.com/kb/172138 for the steps to create Virtual Directory in IIS.
Note: the “cgi-bin” virtual directory should be created inside virtual directory “cognos8”.
7. If you are using IBM HTTP Server
If you are using IBM HTTP Server, then need to modify the httpd.conf file for adding virtual directories.
Add the following lines in httpd.conf in appropriate sections.
ScriptAlias /cognos8/cgi-bin/ "C:/Program Files/cognos/c8/cgi-bin/"
Alias /cognos8 “C:/Program Files/cognos/c8/webcontent"
<Directory " C:/Program Files/cognos/c8/webcontent">
Options Indexes MultiViews
</Directory>
<Directory "C:/Program Files/cognos/c8/cgi-bin">
AllowOverride None
AddOutputFilter Includes html
Options ExecCGI
AddHandler type-map var
Order allow,deny
Allow from all
LanguagePriority en de es fr it nl sv
ForceLanguagePriority Prefer Fallback
</Directory>
8. Changes for the “Cognos Configuration” to set the recently created Database as the content store.
· Start the Cognos Configuration.
· Delete the existing (default) content Manager.
· Create a new Content manager.
· Select “DB2 database” as type.
· Set the user ID/Password for DB2 and specify the database name as “COGNOS”.
· Save the Cognos configuration by “File->Save” on Cognos Configuration.
Now your Cognos is ready to run with DB2.
· Start Cognos by “Actions -> Start” on Cognos Configuration.
Verification:
If every thing goes fine, while starting Cognos service, it will give the following message at Details window.
“CM-SYS-2057 Creating content store tables (schema version 4.0066).”
You will see around 115 new tables created on COGNOS database.
Script for the Congnos DB2 DB. (Please save the below script in .sql file and execute in db2cmd prompt).
--------------------------------------------------------------------
-- This file will setup the database for Cognos---------------------
-- Developed by Gowreenath Boina for WebSphere Knowledge Community--
--------------------------------------------------------------------
CREATE DATABASE COGNOS AUTOMATIC STORAGE YES ON 'C:\' ALIAS COGNOS USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768 WITH 'Cognos DB';
CONNECT TO COGNOS;
CREATE SCHEMA "DB2COGNOS" AUTHORIZATION DB2INST1;
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL "COGNOS_04KBP" SIZE 250 PAGESIZE 4096;
CREATE BUFFERPOOL "COGNOS_08KBP" SIZE 250 PAGESIZE 8192;
CREATE BUFFERPOOL "COGNOS_16KBP" SIZE 250 PAGESIZE 16384;
CREATE BUFFERPOOL "COGNOS_32KPB" SIZE 250 PAGESIZE 32768;
CONNECT RESET;
CONNECT TO COGNOS;
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE TEMPORARY TABLESPACE TSN_SYS_COGNOS IN DATABASE PARTITION GROUP IBMTEMPGROUP
PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 16
PREFETCHSIZE 16
BUFFERPOOL COGNOS_32KPB
OVERHEAD 12.670000
TRANSFERRATE 0.180000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY OFF;
CREATE USER TEMPORARY TABLESPACE TSN_USR_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 16384 MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 16
PREFETCHSIZE 16
BUFFERPOOL COGNOS_16KBP
OVERHEAD 10.500000
TRANSFERRATE 0.140000
FILE SYSTEM CACHING
DROPPED TABLE RECOVERY OFF;
CREATE REGULAR TABLESPACE TSN_REG_COGNOS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 16384 MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 16
PREFETCHSIZE 16
BUFFERPOOL COGNOS_16KBP
OVERHEAD 10.500000
TRANSFERRATE 0.140000
DROPPED TABLE RECOVERY ON;
---------------------------------------
-- Authorization Statements on Schemas
---------------------------------------
GRANT CREATEIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;
GRANT DROPIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;
GRANT ALTERIN ON SCHEMA "DB2COGNOS" TO USER "DB2USER" WITH GRANT OPTION;
----------------------------------------
-- Authorization Statements on Database
----------------------------------------
GRANT CREATETAB ON DATABASE TO USER "DB2USER" ;
GRANT BINDADD ON DATABASE TO USER "DB2USER" ;
GRANT CONNECT ON DATABASE TO USER "DB2USER" ;
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER "DB2USER" ;
---------------------------------------
-- Authorization statement on table space
---------------------------------------
GRANT USE OF TABLESPACE "TSN_USR_COGNOS" TO USER "DB2USER" ;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
;
No comments:
Post a Comment