Setting up the Cognos 8 Samples on DB2

April 23, 2008 · Filed Under Cognos 8 

Cognos 8 Business Intelligence ships with sample data and reports for a fictional company named “The Great Outdoors”. The samples are useful for testing the functionality of the product and for following the examples in the documentation. The Cognos 8 samples are similar to the samples shipped with Series 7 and ReportNet, but not exactly the same. It is recommended to use the sample data that shipped with the version of the product installed, or some of the sample reports will not work correctly. If you are using several versions of the Cognos software concurrently, this means restoring the data into different schemas or different databases. This tutorial will walk through restoring the sample data on DB2 and then setting up the sample reports. The five SQL scripts used to setup the database can be downloaded here: cognos8_sample_setup_db2.

Create the sample database

To start, connect to the DB2 database.

Create a database named cogsamp83 to use for the sample data by running this script:

db2 -tvf create_sample_db.sql

Create the Buffer Pools and Tablespaces. This step is not required if the buffer pools already exist. Prefix the names with ‘COG’ to indicate that they were created for the use of the Cognos databases.

db2 –tvf create_buffer_pools.sql

Create a tablespace called COGSAMPLE:

db2 –tvf create_tablespace.sql

Create the five database schemas. If you are using Data Manager, create the optional sixth schema to use as a target for data manager.

  • GOHR GO Human Resources
  • GOMR GO Multilingual Retailers
  • GORT GO Retailers
  • GOSL GO Sales
  • GOSLDW GO Sales Data Warehouse
  • GODMT GO Data Manager Target

db2 –tvf create_schemas.sql

Some basic tuning should be applied to this database.

db2 –tvf tune_sampledb.sql

Catalogue the database with these commands on every server where Cognos is running:

CATALOG TCPIP NODE dbnode

REMOTE dbhost SERVER 50000

CATALOG DATABASE cogsam83

AT NODE dbnode

WITH ‘Sample Data for Cognos 8′

Restore the sample data

The sample files for DB2 are located here: <InstallationLocation>\c8\webcontent\samples\datasources\db2. If you only installed the Cognos 8 BI Server, then the samples schemas will have not been installed. You must retrieve the files from a separate Cognos installation package.

Extract the files in the GS_DB.tar archive. If you are using WinZip, you must disable CR/LF conversion; otherwise DB2MOVE will not read the files correctly. This done under the WinZip options menu, configuration, miscellaneous.

Be sure to extract the files to a location where you have full permissions. You might have to copy the GS_DB.tar.gz file to another location if you are using Windows Vista or a Windows Server edition.

Use the DB2MOVE command to restore the sample tables:

DB2MOVE cogsam83 IMPORT -U cognos -P admin1234

The command will write to a log file named import.out in the same directory.

Create the views and stored procedures. Edit the first line of the gs_db_modify.sql file to connect to your sample database with the correct user id and password. Then execute gs_db_modify.bat, which will run the SQL, creating the views and stored procedures required for the Cognos samples. This will create a log file called gs_db_modify.log.

Connect from the Cognos Connection portal

First test that the database is in the DB2 client catalogue on the Cognos server by running the following two commands:

DB2 LIST NODE DIRECTORY

DB2 LIST DATABASE DIRECTORY

Create the connection strings in Cognos Connection. From the Launch menu on the upper right hand side, select Cognos Administration.

Select the Configuration tab.

Click the New Datasource icon to bring up this page.

Enter a name and description and click Next. The sample package uses the name ‘great_outdoors_sales’, so keep this same name.

Select DB2 as the datasource type from the dropdown list and click next.

Enter the database name, and a user id and password that can be saved with the datasource.

Scroll down to the bottom of the page and click the ‘Test the Connection’ link.

The connection string is displayed. Click the Test button.

If the connection is successful, this message will appear. If not, than an error will appear in the Message column. Click Close to continue.

Click Close again, then Next. The command page will appear to set the commands to run when a datasource is accessed. We do not need any for this connection, so click Finish.

The great_outdoors_sales datasource will appear in the list.


Create another datasource called ‘great_outdoors_warehouse’. Use the exact same steps, connecting to the same database, with the additional step of adding a session command. We need to point the connection explicitly to the gosldw schema.

At this page, click the set link that corresponds to the Open Session Commands row.

Enter the following xml code and click OK.

<commandBlock>

<commands>

<sqlCommand>

<sql>SET CURRENT SCHEMA = GOSLDW</sql>

</sqlCommand>

</commands>

</commandBlock>

An excerpt of the command will appear under the value column. Click Finish to complete creating the datasource.

The datasource will be added to the list.

Restore the sample packages and reports

With the database connections created, the next step is to restore the sample packages and reports. The packages are located in <install directory>\webcontent\samples\content. Depending on what Cognos software is installed, the number of files here will vary. The reporting samples are in Cognos_Samples.zip.

Copy over the Cognos_Samples.zip file to the deployment folder. If this is a multi-server installation, copy over the zip file to the <install>\cognos\c8\deployment folder on the server where content manager is running.

In Cognos Administration, click the link on the left hand side for content administration.

The Content Administration page will appear.

Click the New Import icon.

Select Cognos Samples and click the next button.

Accept the defaults or specify an alternate name and location for the deployment specification.

Select the folders and packages to import, or select the top checkbox to select them all. Click Next.

Change any options from the default if desired. Click Next to continue.

A summary will appear. Click next to continue.

Select the last option, to save and run the deployment once. Click Finish to finalize the deployment import.

Select the time to run the deployment, or accept the default ‘Now’. Click Run to begin.

Click OK on this page to really run it. This screen is the last chance to back out of the import. Select to view the details immediately.

The status screen will appear.

Click the refresh button to see the latest status. Click Close when the import has finished.

The saved deployment will appear in the list.

Click the Home icon to exit Cognos administration and to go to Public Folders. The sample reports will be here.


Drill into a folder and select a report to run to test the samples setup.

This is the report called’Order Invoices - Donald Chow, Sales Person’, found under ‘GO Sales (query)’, ‘Report Studio Report Samples’.

Links to social bookmarking sites.
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Reddit
  • TwitThis
  • Live

Comments

One Response to “Setting up the Cognos 8 Samples on DB2”

  1. Free Database Roundup : Infotech Reflections on May 22nd, 2008 10:49 am

    […] IBM Cognos 8 Business Intelligence. I have posted an install guide, and a guide to setting up the Cognos 8 samples on […]

Leave a Reply