System Configuration:
Lenovo Thinkpad T60 w/4Gb RAM, dual core 1.83GHz processor
Ubuntu Linux 10.04
DB2 9.7 Express C Edition
Cognos 8.4.1
Apache2
DB2 9.7 Express C Edition
This is an open source "like" version of DB2 that comes without formal support, but can be downloaded and installed for free and has support in open-source style community forums.
Install DB2 9.7 Express C Edition
1 - Install pre-requisite packages
user@host:$ sudo apt-get install libaio1 ksh libstdcc++6-4.4-dev libstdcc++6-4.4-pic [sudo] password for user: user@host:$
2 - Add Canonical Partners to Synaptic Package Sources List
user@host:$ sudo gedit /etc/apt/sources.list [sudo] password for user: user@host:$
3 - Uncomment the following lines, save file and close:
deb http://archive.canonical.com/ubuntu lucid partner deb-src http://archive.canonical.com/ubuntu lucid partner
4 - Update Synaptic Package Sources
user@host:$ sudo apt-get update
5 - Install DB2 9.7 Express C
user@host:$ sudo apt-get install db2exc
6 - Login as the default DB2 user to verify installation
user@host:$ sudo su - db2inst1 db2inst1@host:~$
7 - Create sample DB2 database (optional)
db2inst1@host:~$ db2sampl Creating database "SAMPLE"... Connecting to database "SAMPLE"... Creating tables and data in schema "DB2INST1"... Creating tables with XML columns and XML data in schema "DB2INST1"... 'db2sampl' processing complete.
8 - Connect to sample DB2 database
db2inst1@host:~$ db2 connect to sample Database Connection Information Database Server = DB2/Linux 9.7.2 SQL authorization ID = DB2INST1 Local database alias = SAMPLE db2inst1@host:~$
9 - Issue test queries to verify proper database creation
db2inst1@host:~$ db2 LIST TABLES
{...output...}
47 record(s) selected.
db2inst1@host:~$ db2 "SELECT * FROM EMP"
{...output...}
42 record(s) selected.
db2inst1@host:~$ db2stop
SQL1064N DB2STOP processing was successful.
db2inst1@host:~$ exit
user@host:$
Now that we successfully installed DB2 9.7 and verified that it's working properly by testing with the DB2 sample database, we'll move on to working with the Cognos 8.4.1 sample data set.
Restore Cognos 8.4.1 Sample Database in DB2
1 - Create cognos user and group, set password
user@host:$ sudo addgroup cognos [sudo] password for user: Adding group 'cognos' (GID 1001) ... Done. user@host:$ sudo adduser -m -s /bin/bash -g cognos cognos user@host:$ sudo passwd cognos Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully
2 - Create directory for DB2 repository, create "cognos" DB2 instance
user@host:$ cd /opt/ibm/db2/V9.7/instance user@host:$ sudo mkdir db2repo [sudo] password for user: user@host:$ sudo chown -R cognos:cognos db2repo user@host:$ sudo /opt/ibm/db2/V9.7/instance/db2icrt -u cognos cognos DBI1070I Program db2icrt completed successfully.
3 - Update port # and start DB2 instance.
user@host:$ sudo su - cognos [sudo] password for user: cognos@host:~$ db2 update dbm cfg using svcename 1999 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. cognos@host:~$ db2start SQL1063N DB2START processing was successful.
4 - Create Go Sales DB2 database for Cognos Samples and set code page
cognos@host:~$ db2 CREATE DATABASE GS_DB USING CODESET UTF-8 TERRITORY US DB20000I The CREATE DATABASE command completed successfully. cognos@host:~$ db2set DB2CODEPAGE=1208
5 - Open DB2 command window
cognos@host:~$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.2
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 =>6 - Attach to cognos DB2 instance
db2 => ATTACH TO cognos Instance Attachment Information Instance server = DB2/LINUX 9.7.2 Authorization ID = COGNOS Local instance alias = COGNOS db2 =>
7 - Connect to GS_DB database
db2 => CONNECT TO GS_DB Database Connection Information Database server = DB2/LINUX 9.7.2 SQL authorization ID = COGNOS Local database alias = GS_DB db2 =>
8 - Create bufferpool for GS_DB tablespace
db2 => CREATE BUFFERPOOL gs_buffer PAGESIZE 16K DB20000I The SQL command completed successfully. db2 =>
9 - Create gs_data tablespace using gs_buffer bufferpool
db2 => CREATE REGULAR TABLESPACE gs_data PAGESIZE 16K MANAGED BY DATABASE USING (file 'db2repo/GS_TBS' 163840) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.33 BUFFERPOOL gs_buffer DROPPED TABLE RECOVERY ON DB20000I The SQL command completed successfully. db2 =>
10 - Exit from DB2 command window
db2 => quit DB20000I The QUIT command completed successfully. cognos@host:~$ exit logout user@host:$
6 - Extract Cognos sample database export files for DB2
This assumes that you've already downloaded and extracted the Cognos sample data package within the Cognos main installation directory. If you haven't done this look at this post for the steps to complete first.
user@host:/opt/cognos/c8/webcontent/samples/datasources/db2$ tar -xzvf GS_DB.tar.gz
GS_DB/
GS_DB/db2move.lst
GS_DB/EXPORT.out
GS_DB/tab1.ixf
GS_DB/tab1.msg
{...output...}
7 - Change ownership and permissions on extracted files
user@host:$ sudo chown -R cognos:cognos GS_DB [sudo] password for user: user@host:$ sudo chmod -R 0775 GS_DB
8 - Login as cognos user and kick off the import, then go enjoy a beer... or maybe 2!
user@host:$ sudo su - cognos
cognos@host:~$ cd /opt/cognos/c8/webcontent/samples/datasources/db2/GS_DB
cognos@host:$ db2move GS_DB import -u cognos -p cognos
***** DB2MOVE *****
Action: IMPORT
Start time: Mon May 2 22:04:42 2011
Connecting to database GS_DB ... successful! Server : DB2 Common Server V9.7.2
Binding package automatically ... /home/cognos/sqllib/bnd/db2common.bnd ... successful!
Binding package automatically ... /home/cognos/sqllib/bnd/db2move.bnd ... successful!
* IMPORT: table "GOSALESHR"."RANKING_RESULTS"
-Rows read: 1898
-Inserted: 1898
-Rejected: 0
-Committed: 1898
* IMPORT: table "GOSALESHR"."RECRUITMENT"
-Rows read: 416
-Inserted: 416
-Rejected: 0
-Committed: 416
{...output...}
* IMPORT: table "GOSALESDW"."MRK_RTL_SURVEY_TARG_FACT"
-Rows read: 64
-Inserted: 64
-Rejected: 0
-Committed: 64
Disconnecting from database ... successful!
End time: Mon May 2 23:18:56 2011As you can see, it takes a while. The import took 1 hr and 14 min on my machine (which is a Lenovo Thinkpad T60P).
Create Views, Constraints, and Stored Procedures in GS_DB
1 - Edit SQL script that adds views, constraints, and stored procedures to GS_DB
user@host:$ sudo gedit /opt/cognos/c8/webcontent/samples/datasources/db2/gs_db_modify.sql [sudo] password for user:
Change the 1st line in the file to use the correct connection information for your database. In my case this was:
connect to GS_DB user cognos using cognos
Comment out the 1st SQL statement, because it is redundant (the cognos user already has all of this authority in the GS_DB database). If you forget or don't want to comment out this line, the resulting error is benign, the SQL script will still complete successfully.
--GRANT AUTHORITIES TO USER cognos /* GRANT BINDADD,CONNECT,LOAD ON DATABASE TO USER cognos @ */
Then change all the following GRANT statements that reference the GOSALES user to your database user name (in my case cognos).
--GRANT PRIVILEGES TO USER cognos GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES ON TABLE GOSALES.BRANCH TO USER cognos @ and so on...
Save and exit. Make certain to save the file with Unix/Linux line breaks, as it comes formatted as a Windows file and will not execute.
2 - Run SQL script to add views, constraints, and stored procedures
The included .bat file is provided to execute the SQL script on a Windows machine, however it is quite easy to run the same command on Linux.
First change ownership and permissions in the /opt/cognos/c8/webcontent/samples/datasources/db2 directory so that the cognos user can read/write.
user@host:/opt/cognos/c8/webcontent/samples/datasources$ sudo chown -R cognos:cognos db2 [sudo] password for user: user@host:/opt/cognos/c8/webcontent/samples/datasources$ sudo chmod -R 0775 db2
Now execute the SQL script using the DB2 command:
cognos@host:/opt/cognos/c8/webcontent/samples/datasources/db2$ db2 -td@ -f gs_db_modify.sql -z gs_db_modify.log
Database Connection Information
Database server = DB2/LINUX 9.7.2
SQL authorization ID = COGNOS
Local database alias = GS_DB
DB20000I The SQL command completed successfully.
{...output...}
You will see the following message in the command window and log file.
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0458N In a reference to routine "GOSALES.ASSIGNSTAFF" by signature, a matching routine could not be found. SQLSTATE=42883
It does not indicate an error, it is a result of the following SQL statement near the bottom of the SQL script, which outputs this warning if the stored procedure does not exist:
DROP PROCEDURE GOSALES.ASSIGNSTAFF (INTEGER, INTEGER)
Create Data Source Connections to Samples Databases
This took me a while to get right. Creating the data source connections is fairly straight forward, however when testing the data source connections within Cognos Administration I kept getting the following error:
QE-DEF-0285 The logon failed. QE-DEF-0321 The userID or password is either missing or invalid. RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in: testDataSourceConnection UDA-SQL-0031 Unable to access the "testDataSourceConnection" database. UDA-SQL-0129 Invalid login information was detected by the underlying database. [IBM][CLI Driver] SQL1013N The database alias name or database name "GS_DB" could not be found. SQLSTATE=42705
Unfortunately, the error codes and message did not immediately point to any resolution, so I tried a lot of different things. What it came down to was a missing environment variable that the Cognos services were looking for... DB2INSTANCE, which is supposed to be set and pointed at the DB2 instance that Cognos will use.
Since I start the Cognos Configuration Manager and services using my login, I put the following into my ~/.profile:
# Set JAVA_HOME for Cognos 8 JAVA_HOME="/usr/lib/jvm/java-6-sun/jre" export JAVA_HOME # Set current DB2 instance for DB2 # Required for Cognos 8 to connect to local DB2 database DB2INSTANCE="cognos" export DB2INSTANCE # Add Cognos 8 install location to path COGNOS_HOME="opt/cognos/c8" PATH=$PATH:COGNOS_HOME/bin export PATH
You can also just set these within the active command shell, but you'll then have to reset them every time you start Cognos. Once you have these set, shutdown the Cognos services and restart the Cognos Configuration Manager from the command line...
/opt/cognos/c8/bin/cogconfig.sh
Then start the Cognos services and open up Cognos in your web browser - http://localhost/cognos8
1 - Click on 'Administer IBM Cognos Content'
2 - Click on 'Configuration'
3 - Click on the New Data Source icon
4 - Enter 'great_outdoors_sales' as the data source name and click Next
5 - Select DB2 as the type and click Next
6 - Enter 'GS_DB' as the database name.
Leave everything else blank/default, except under Signon. Select Signons, Password, and 'Create a signon that the Everyone group can use.' Then enter the database user ID and password... in my case this is cognos / cognos. Then click the 'Test connection' link at the bottom.
7 - Click the 'Test' button on the next page.
8 - If you're test is successful, you'll see something like the following.
You can now start running reports against the sample data within Report Studio, Query Studio, etc.!
