Recommended practices for using Cognos with Informix, Part 2: Deploy Informix with IBM Cognos BI Server 10

Install and get up and running

Connecting your Informix® databases to Cognos® Business Intelligence software gives you a way to unleash the power of your data with expanded query, reporting, and analysis capabilities. If you're ready to take that step, this two-part tutorial series gives you the information you need to install, configure, and deploy the necessary components to achieve the best results. Part 1 showed how to get started with using IBM Cognos Express V9 together with IBM® Informix V11.5 as a content store and data source. Now in Part 2, you'll find the same level of detail for deploying Informix with IBM Cognos BI Server V10. The tutorials include recommended practices for each step along the way, based on lessons learned from real-world deployments on the Microsoft® Windows® operating system.

Share:

Sreeni Paidi, Enablement Consultant, IBM

Sreeni Paidi photoSreeni Paidi is an Enablement Consultant with the IBM Information Management Technology Ecosystem organization. Sreeni has about 15 years of experience working on DB2, Informix, Oracle, PostgreSQL, and SQL Server database servers as a Database Programmer, DBA and an Integration Architect. Sreeni is part of a worldwide team that provides technical skills and consulting services to help business partners and customers accelerate the integration of IBM data management software with their applications.



Jeffrey Williamson (jeffrey.williamson@jcbpartners.com), Senior Consultant, JCB Partners

A picture of the author Jeffrey WilliamsonJeffrey Williamson is a Senior Consultant and Southern California Market Lead for JCB Partners. Jeffrey has 4 years of experience implementing IBM Cognos Analyst, IBM Cognos Contributor and IBM Cognos TM1 as both a builder and a design architect. He has worked with clients in a wide range of industries including pharmaceutics, gaming and hospitality, retail, and restaurants. He is part of a team that helps organizations optimize the resources they have and better align their time and skills with the value of the organization.



07 July 2011

Also available in Chinese Portuguese

Before you start

Before you start, read about what is covered in this tutorial, prerequisites, and system requirements.

About this series

This series has two parts. The first part covered the recommended practices for deploying Cognos Express software with Informix. This second part covers the recommended practices for deploying Cognos BI Server software with Informix as a content store and a data source.

About this tutorial

This tutorial uses a two-server approach, where one server is used for hosting the Informix data sources and the other server is used for hosting Cognos BI software and the content store database.

The following topics are covered in this tutorial:

  • Planning for hardware and software
  • Setting up the server that is planned for hosing Informix data sources by:
    • Installing Informix server software
    • Creating a sample Informix database as a Cognos data source
    • Tuning Informix database instance
  • Setting up the server that is planned for hosting Cognos BI components by doing the following:
    • Installing Cognos BI Server software
    • Installing Informix server software for the content store
    • Creating a content store database
    • Installing 32-bit Informix client software
    • Installing IBM Data Server Driver for JDBC and SQLJ software
    • Configuring client/server connectivity between Cognos BI and Informix server
    • Cognos BI Server configuration
    • Creating an Informix data source
    • Using Informix data source in Cognos Framework Manager

At the time when this tutorial was written, IBM Cognos BI version 10.1 wasn't certified on IBM Informix version 11.70. However you shouldn't have any problems using Informix 11.70 database as a data source or a content store database as long as you install 32-bit Informix-Connect version 3.50 on the Cognos BI Server. More details to follow.

Prerequisites

This tutorial is intended for system administrators and application administrators who are interested in deploying IBM Cognos BI Server version 10.1 with IBM Informix version 11.5 as a content store and an operational data mart on Windows Server 2003. You should have a general familiarity with installing software on Microsoft Windows.

System requirements

To run the examples in this tutorial, you need a Windows 2003 server with at least 2 GB memory and local Administrator user access on the Windows box. You will also need access to Informix 11.5 server software, Informix Connect software, and Cognos BI Server 10.1 software.

About IBM Cognos BI software

IBM Cognos Business Intelligence is a Web-based business intelligence solution with integrated reporting, analysis, score-carding, and event management features. IBM Cognos BI consists of server and modeling components, which are described in the following section.

  • Server components provide the user interfaces for reporting, analysis, score-carding, event management, as well as the server functionality for routing and processing user requests. Server components are further classified into the following categories:
    • Gateway is an extension of a Web server program that transfers information from the Web server to another server. It provides Web Communication in Cognos BI. Gateways are often CGI programs but may follow other standards such as ISAPI, Apache Module etc.
    • Application Tier Components that include IBM Cognos Administration, Cognos Viewer, Report Studio, Query Studio, Analysis Studio, Event Studio, Metric Studio, IBM Cognos Business Insight and IBM Cognos for Microsoft Office.
    • Content Manager manages the storage of data related to security, configuration, models, metrics, report specifications, and report output.
  • Modeling components are used to model data within data sources to structure and present data in a way that is meaningful to end users. Modeling components include the following tools:
    • Framework Manager for creating a business view of the data.
    • Metric Designer for extracting data for score-carding.
    • IBM Cognos Transformer for multidimensional modeling.
    • Series 7 IQD Bridge that contains the connection information that IBM Cognos BI requires to use IBM Cognos Series 7 Impromptu IQD data sources.
    • Map Manager for importing and managing maps.

Please see the Resources section for a link to visit the IBM Cognos landing page for more information about Cognos BI solution offerings.

About IBM Informix software

IBM Informix software's unmatched high availability, deep embed, blazing performance, zero administration, and data warehousing characteristics makes it a perfect relational database offering for any size business. Please see the Resources section to read the article by Carlton Doe for more information on IBM Informix 11 software editions.


Planning

Before you start setting up the servers with the Cognos BI Components and Informix, proper planning is required to avoid any last minute surprises and delays in delivering the environment to the business users.

Verify system requirements

Check the minimum hardware and software requirements to run the Cognos BI Server and the Informix Server.

Planning for the hardware

If you have a blade server, you can create two nodes on the server, one for Cognos BI and the other one for Informix Server (which is used as a data source), with hardware resources divided between the two nodes. Storage requirements are unique to every database environment. Make sure that you have adequate storage available for the database server.

Recommended practice: If you are planning to use Informix as a data source, it is recommended to install Cognos BI components and Informix Server software on two different servers or nodes. If you are planning to use Informix database as a content store, it is recommended to install Informix server software locally on the Cognos BI server for the content store. In other words, content store database has to be local to Cognos BI server where as data source database can be a remote database running on a different server or node.

Planning for the software

Cognos BI consists of a lot of software components. Requirements of each business are different. Make sure that you have access to all the required software packages and components before you start the installation process.

IBM Cognos BI includes server and modeling components. For information about installing and configuring these components, see the IBM Cognos BI Installation and Configuration Guide. The objective of this tutorial is to show you how to configure Cognos BI and Informix to use Informix as a content store as well as a data source. This tutorial will not show you how to install all of the Cognos BI components.

Recommended practice: Read about software environments and platforms supported by Cognos BI version that you are going to install.

For the server that is hosting Cognos BI:

  • Microsoft Windows Server 2003 for x86 64-bit
  • Service Pack 2 for Microsoft Windows Server 2003
  • Internet Information Service (IIC) version 6
  • IBM Cognos Business Intelligence Server version 10.1.0 or higher
  • 32-bit Informix Server software version 11.50.xC7 or higher, but only if you are planning to configure Informix content store. You can use Informix Innovator-C edition for this.
  • 32-bit Informix Connect (I-Connect) 3.50

For the server that is hosting Informix database instance:

  • Microsoft Windows Server 2003 for x86 64-bit
  • Service Pack 2 for Microsoft Windows Server 2003
  • 64-bit Informix Server Software version 11.50.xC7 or higher

Recommended practice: Every environment's database requirements are different. Before you decide on the Informix edition that you would need to install, please see the Resources section to read the article by Carlton Doe for details on the differences among Informix editions.


Installing and configuring Informix software on the Informix server

In this section we walk you through the procedure to install and configure Informix software on the remote host which will be hosting the Informix datamarts / data sources.

Prerequisites

Make sure that the following software is installed on the Informix server / node1 before installing Informix server software:

  • Microsoft Windows Server 2003 for x86 64-bit
  • Service Pack 2 for Microsoft Windows Server 2003

Install Informix server software

This tutorial assumes that you have decided on the edition and version of Informix Server software that you will be installing on the database server and you also have access to the software installation media.

Read the release notes, machine notes, and fixed and known defects list, which are on your product media and in the information center.

  1. Launch the IDS installer program by double-clicking the setup.exe file in the IIF folder of the installation media.
  2. From the welcome screen, click Next.
  3. Read and accept the license terms and conditions, and then click Next.
  4. Choose Custom installation, and then click Next.
  5. Choose the default destination path for IDS installation, and then click Next.
  6. On the following screen you are given options to select features that you want to install, as well as the option to clear the features that you don't want to install. Select the features that you want to install and then click Next.
  7. On the following screen you are asked to enter and confirm the password of user Informix. Installer creates the Informix user with the given password. Enter the password and click Next.
  8. On the following screen, you are asked to enter the Informix server details such as server name, server number, service name, port numbers, and others. Enter the server name of your choice and click Next. The following values are used in this tutorial, and are also shown in Figure 1.
    • Server Name: cbiwin
    • Service Name for SQLI connections: cbiwin
    • Port number for SQLI: 9088
    • Server number: 0
    • Server Alias for DRDA support: cbiwin_drda
    • Port number for DRDA support: 9089
Figure 1. Informix server information
Informix server information

Recommended practice: Make sure that Initialize Server and Enable DRDA Support check boxes are selected. Make a note of the values entered on this screen. The port numbers do not have to be 9088 and 9089 but be sure to make a note of them.

  1. On the next screen, you can choose location and size of data dbspace and smart BLOB space. Enter 1024 as the size of dbspace and sbspace and click Next.
  2. On the next screen, you are given the summary of what you are going to install. Click Next to begin installation.
  3. Click Finish once the installation is completed.

Recommended practice: After the installation is done, verify that the Informix Server instance is running by checking the Windows services. You can also verify this by running the onstat - and onstat -d Informix commands from the Informix command Window.

Any Informix database that Cognos BI Server can connect has to be a UTF8 database. Before creating the Informix database that you are going to use as a data source with Cognos BI, make sure that the DB_LOCALE and CLIENT_LOCALE environment variables are set to en_us.UTF8. In addition to this, set DB_LOCALE and CLIENT_LOCALE environment variables to en_us.UTF8 using the setnet32 program. The Setnet32 program is part of Informix Client SDK (CSDK) and Informix Connect (IConnect) software. Install either CSDK or IConnect software in addition to Informix server software on the Informix host. CSDK and IConnect are part of the Informix Server install media. Refer to the instructions in the section titled Installing 32-bit Informix client software for more information on installing IConnect software. After the client software is installed, follow the procedure below to set some environment variables for UTF8 databases.

  1. Start Setnet32 by clicking Start -> All Programs -> IBM Informix Connect 3.50 -> Setnet32.
  2. From the Server Information tab, select the IBM Informix server cbiwin.
  3. Click the Environment tab and set the following environment variables:
    • CLIENT_LOCALE=en_us.utf8
    • DB_LOCALE=en_us.utf8
    • INFORMIXSERVER=cbiwin
  4. Click OK.

Create a sample Informix data source database

Now we are going to setup a sample gosales database called gsdb. Later on, you will configure this database as a data source for the Cognos BI Server and connect to it from the Cognos Framework Manager. To setup a gsdb database on the Informix Server, do the following steps:

  1. Download the compressed dbexport file GSDBv1.2_ids.zip to the C:\gosales folder on the Informix database server.
  2. Uncompress the dbexport file at C:\gosales. You will see a directory created with the name GSDBv1.2_ids, which contains the database dump.
  3. Open a command window for informix instance (cbiwin in our example) by clicking Start -> All Programs -> Informix Dynamic Server 11.50 -> < instance_name >.
  4. At the command prompt, change the directory to C:\gosales\GSDBv1.2_ids
  5. At the command prompt, run the following commands shown in Listing 1 to set the environment variables for CLIENT_LOCALE, DB_LOCALE, and DBDATE.
Listing 1: Setting environment variables
set CLIENT_LOCALE=en_us.utf8
set DB_LOCALE=en_us.utf8
set DBDATE=Y4MD
  1. Make sure that the SBSPACENAME parameter in the ONCONFIG.cbiwin parameter file (%INFORMIXDIR%\etc\%ONCONFIG%) is set to sbspace. If you followed the instructions in this tutorial and created the Informix instance at the time of Informix software installation, it will be automatically set to sbspace. If it is not already set, set the SBSPACENAME parameter to sbspace. Please note that this is not a requirement of Cognos BI but the sample gosales database uses an SBSPACE with the name sbspace.
  2. Recycle (stop and restart) the Informix instance service (Informix IDS - cbiwin) in the windows services.
  3. Execute createGSDBv1.2.bat batch command at the cbiwin command window that was previously opened. This batch command will create the gsdb database, create all the tables, and load the sample data into the tables.

Installing and configuring Cognos BI Server software on the Cognos server

Prerequisites

Make sure that the following software is installed on the Cognos server / node2 before installing Cognos BI software:

  • Microsoft Windows Server 2003 for x86 64-bit
  • Service Pack 2 for Microsoft Windows Server 2003
  • Internet Information Service (IIC) version 6

Instructions on how to install IIS 6 on Windows Server 2003 can be found in the Resources section.

Install Cognos BI software

Recommended practice: You can install Cognos BI components on just one computer or distribute them across multiple computers in the network. Before starting the installation, read IBM Cognos Business Intelligence version 10.1.0 Installation and Configuration Guide. Verify the system requirements and review the default port settings for Cognos BI by following the instructions in the "Planning your installation" section in the Installation Guide.

Do the following steps to install the Cognos BI software:

  1. Insert the Cognos BI server install media disk into a drive to start the install program. If you downloaded the installation media, extract the files from the package, maintaining the downloaded folder structure delivered in the media and double-click the install.exe file.
  2. Select the language you want to use to run the installer and Click Next.
  3. Read and accept the license agreement and click Next.
  4. Choose the default install folder and click Next.
  5. Choose the default components when prompted to choose the components to install and click Next.
  6. On the configuration screen, the express administrator account name will default to administrator. You can leave it as is, or shorten it to admin. You can also enter a password.

Best Practice: As shown in Figure 2, the Cognos Content Database is not installed by default, and we want to keep it that way. Cognos recommends not installing the default Content Database (Derby DB) for production. Later on, you will learn how to configure an Informix database as a Cognos Content Database.

Figure 2. Selecting the components to install
Selecting the components to install
  1. Choose the default Shortcut folder and click Next.
  2. On the following screen, you are presented with the pre-installation summary. Click Install. The installation process will begin. It will take a few minutes, with the installation bar denoting the progress along the bottom.
  3. Click OK when prompted about non-English documentation installation.
  4. When the installation is done, click Finish.

Create a content store database in Informix

Cognos Content Manager uses the content store database to store global configuration data, global settings, connections to data sources, and product-specific content. You must use one of the supported enterprise-level databases as the content store in a production environment.

Recommended practice: It is recommended to have a local Informix content store database on the Cognos BI Server. For this reason, install Informix server software and create an Informix instance on the host that is running the Cognos BI Server software. You can install free-of-charge Informix Innovator-C Edition for the content store. Refer to the section titled Installing Informix server software for more information on installing Informix software. Make sure that the Informix server name (instance name), and the Informix server alias are different from those created on the database server.

The following values are used in this tutorial:

  • Server Name: cbilocal
  • Service Name for SQLI connections: cbilocal
  • Port number for SQLI: 9088
  • Server number: 0
  • Server Alias for DRDA support: cbilocal_drda
  • Port number for DRDA support: 9089

After installing Informix and creating the instance, do the following steps to create an Informix database for the Cognos Content Store:

  1. Login to the Cognos BI host as a local administrator user or Informix.
  2. Bring up the command window for the Informix instance by clicking Start -> All Programs -> Informix Dynamic Server 11.50 -> (server_name).
  3. At the command prompt, run the following command to set the environment variable DB_LOCALE:
    • set DB_LOCALE=en_us.utf8
  4. As per Cognos recommendations, in the ONCONFIG.cbiwin file (%INFORMIXDIR%\etc\%ONCONFIG%), set the property SHMBASE to at least 0x14000000L.
  5. Recycle (stop and restart) the Informix instance service (Informix IDS - server_name) in the windows services.
  6. As per Cognos recommendations, create two sbspaces namely CMDATASPACE and CMOBJPROPS7SPACE, with the logging turned on, by running the commands shown in Listing 2 at the cbiwin command window:
Listing 2: Commands to create sbspaces for the content store database
type NUL > C:\IFMXDATA\cbilocal\CMDATASPACE.000
type NUL > C:\IFMXDATA\cbilocal\CMOBJPROPS7SPACE.000
onspaces -c -S CMDATASPACE -p C:\IFMXDATA\cbilocal\CMDATASPACE.000 
	-o 0 -s 300000 -Df "LOGGING=ON"
onspaces -c -S CMOBJPROPS7SPACE -p C:\IFMXDATA\cbilocal\CMOBJPROPS7SPACE.000 
	-o 0 -s 300000 -Df "LOGGING=ON"
  1. Now you are ready to create a database for the content store. The Content Store database has to be created with log mode ANSI as shown in Listing 3.
  2. Save the following "CREATE DATABASE" SQL statement into a file called create_csdb.sql at %INFORMIXDIR%.
Listing 3: Command to create the content store database
CREATE DATABASE csdb WITH LOG MODE ANSI;
  1. Run the create_csdb.sql script with the command dbaccess - create_csdb.sql at the cbiwin command prompt.

Recommended practice: Set the system environment variables GL_USEGLU and DB_LOCALE with the following values:

  • GL_USEGLU=1
  • DB_LOCALE=en_us.utf8

Install 32-bit Informix client software

It is required to install 32-bit Informix Client software on the machine that is running Cognos BI Server. You can install either Informix Client Software Development Kit (CSDK) version 3.50, or Informix Connect (ICONNECT) version 3.50, which is runtime equivalent of CSDK.

IConnect software is free to download and install, whereas there a license fee is associated with CSDK software. If you have the 32-bit Informix Server software media, you can find the IConnect software under the IConnect folder. Please see the Resources section for a link to download the IConnect software.

Recommended practice: Install 32-bit Informix-Connect software version 3.50 on the Cognos Server by running ICONNECT\setup.exe program or installiconnect.exe program from the product directory. Reboot the host after installing the ICONNECT software. As noted at the beginning of the tutorial, Cognos BI is certified on Informix 11.50 at the time this tutorial was written. You can still connect to Informix 11.70 databases as long as you install 32-bit Informix-Connect version 3.50 on the Cognos BI Server. Do not install Informix-Connect version 3.70 that comes with Informix version 11.70.

Install IBM Data Server Driver for JDBC and SQLJ

You would need to install IBM Data Server Driver for JDBC and SQLJ in order to connect to the content store database. Please see the Resources section for a link to a copy of the IBM Data Server Driver for JDBC and SQLJ. You can also find a copy of the Data Server Driver bundled with the CSDK software. Download and install the driver software.

Configuring client/server connectivity on the Cognos Server

As a next step, you need to configure client/server communication between client products (such as Cognos BI) and the Informix Server using a program called Setnet32 on the client machine. The Setnet32 utility is part of the IConnect / CSDK software. It has the following four tabs:

  • Environment: allows you to set environment variables
  • Server Information: allows you to set database server network information
  • Host Information: allows you to set your host computer and login information
  • About Setnet32: provides information about the Setnet32 utility

Please see the Resources section for a link to the Informix 11.5 Information Center where you can find more information on Setnet32.

Configure SQLHOSTS information on the Cognos Server

The following steps walk you through the procedure to configure client products on the Cognos Server to connect to Informix Server running on the database server.

  1. Start Setnet32 by clicking Start -> All Programs -> IBM Informix Connect 3.50 -> Setnet32.
  2. If you have installed Informix Server software on the Cognos BI server and created the content store database, you would see an entry for the local Informix instance on the Server Information tab. You are going to make a new entry for Informix server instance running on the Informix server. Enter the following information, as shown in Figure 3.
    • IBM Informix Server: Name of the Informix instance. For example, cbiwin.
    • Host name: hostname or IP address of the database server which is hosting the Informix Server instance.
    • Protocolname: olsoctcp.
    • Servicename: Name of the service or port number for olsoctcp protocol.
Figure 3. New server entry in Setnet32
New server entry in setnet32
  1. Now click the Environment tab and set the following environment variables, as shown in Figure 4:
    • CLIENT_LOCALE=en_us.utf8
    • DB_LOCALE=en_us.utf8
  2. Click OK, and then click OK for any other prompts.
Figure 4. Environment variables in setnet32
Environment variables in setnet32

Test the client/server connectivity on the Cognos Server

Informix client software comes with a simple program called ILogin Demo that can be used to test the connectivity to an Informix Server.

  1. Start ILogin Demo by clicking Start -> All Programs -> IBM Informix Connect 3.50 -> ILogin Demo.
  2. Click File -> Run.
  3. Enter the connectivity information for the Informix database running on the database server, as shown in Figure 5, and then click OK.
Figure 5. Testing the connectivity from ILogin Demo
Testing connectivity from ILogin Demo
  1. You should see a screen with no results from the customer table. If you see errors that the client/server connectivity test was unsuccessful, try the test again after resolving the issues.

Setup the connectivity for the content store database

Set up Client/Server connectivity for the Informix content store database using the following two steps.

  1. Copy IBM data server driver files. You have already installed IBM Data Server Driver for JDBC and SQLJ on the Cognos server. Just copy the db2jcc.jar file from DataServerDriver_location\java to the c10_location\webapps\p2pd\WEB-INF\lib folder.
  2. Set up the SQLHOSTS information as follows:
  • Start setnet32 by clicking Start -> All Programs -> IBM Informix Connect 3.50 -> Setnet32.
  • On the Server Information tab, enter the information as shown below:
    • IBM Informix Server: This is same as the SERVER ALIAS defined for the DRDA support at the time of Informix installation on the Cognos BI Server. This is also the value of DBSERVERALIASES parameter in the ONCONFIG.servername file. For example cbilocal_drda.
    • Host name: hostname or the IP address of the Cognos BI server where the content store database is created.
    • Protocolname: drsoctcp.
    • Service name: service name or port number for the DRDA protocol, for example 9089.
  • Now click the Environment tab. Click OK when prompted for confirmation to define new IBM Informix Server.
  • Make sure that the environment variables CLIENT_LOCALE and DB_LOCALE are set to en_us.utf8 and click OK.

This concludes setting up client/server connectivity for an Informix content store database. You will be using this content store database when you configure and start the Cognos BI Server.

Cognos BI Server configuration

We are now ready to configure Cognos BI Server and start the service. Follow the steps given below to configure the Cognos BI server:

  1. If it is not already set, set the following system environment variables:
  • DB_LOCALE to en_us.utf8
  • CLIENT_LOCALE to en_us.utf8
  • GL_USEGLU to 1

Recommended practice: Unset or delete the JAVA_HOME environment variable if it is already set. It is better to use the Java that came with Cognos installation, so if you unset the JAVA_HOME environment variable now, Cognos BI will automatically use the Java that came with the Cognos installation.

  1. Start the Cognos BI Configuration by clicking Start -> All Programs -> IBM Cognos 10 -> IBM Cognos Configuration.
  2. Set the database connection properties for the Content Store.

The following instructions will walk you through the process to define an Informix database (csdb) that was previously created on the Cognos Server as a content store database. Defining any other type of the database as a content store is outside the scope of this tutorial.

  1. In the Explorer window, as shown in Figure 6, under Data Access -> Content Manager, right-click Content Store and select Delete.
Figure 6. Deleting the default content store
Deleting the default content store
  1. As shown in Figure 7, right-click Content Manager, click New resource, and then select Database.
Figure 7. Creating a new source for the content manager
Creating a new source for the content manager
  1. As shown in Figure 8, type csdb as the name, select Informix Dynamic Server as the type, and then click OK.
Figure 8. Entering csdb information
Entering csdb information
  1. You will see the Database Resource Properties displayed on the right hand side. Remember that the content store database was created in the Informix instance running on the Cognos BI Server. Enter the following database properties, also shown in figure 9, and click OK.
    • Type: Informix Dynamic Server Database.
    • Database server and port number: hostname or IP address of Cognos BI server: drda_port_number. For example: panther:9089.
    • User ID and password: Type Informix as the username and password of the Informix user on the Cognos BI Server.
    • Database name: Name of the content store database that was previously created. For example, csdb.
Figure 9. Entering database properties
Entering database properties
  1. As shown in Figure 10, from the Explorer window, under Data Access, Content Manager, right-click csdb, and then click Test.
Figure 10. Test the content store connection
Test the content store connection
  1. Click Close if the csdb database connectivity test passes, as shown in Figure 11. If the connectivity test fails, click Details to see the reason for the failure and correct the problem.
Figure 11. Connection test results
Connection test results

Note: Defining other properties such as a connection to the mail server account is outside the scope of this tutorial.

  1. Save Cognos Configuration by clicking Save from the File menu, or clicking the Save button.
  2. As shown in Figure 12, start IBM Cognos Services by clicking Actions -> Start, or by clicking the Start button.
Figure 12. Starting Cognos service
Starting Cognos service
  1. Click OK when a warning window is displayed about warnings during test phase. This was mainly because of not having a mail server defined.
  2. After Cognos Services start successfully, click Continue, as shown in Figure 13.
  3. Click Close.
Figure 13. Warnings during Cognos service startup
Warnings during Cognos service startup

Note: There are many reasons why Cognos service will not start successfully. Some of the common reasons are insufficient memory, incorrect JAVA_HOME, and Microsoft Data Execution Prevention (DEP).

Please see the Resources section for more general information about DEP, as well as errors related to DEP.

Configure the web server

You must configure the web server in order to access any web pages generated by the Cognos BI server. In this tutorial you are going to use the Internet Information Server (IIS) version 6. Installation of IIS is outside the scope of this tutorial. Please see the Resources section for a link to instructions on installing IIS 6 on Windows Server 2003.

Note: If you are configuring IIS v7.x then see the Resources section for a link on how to configure it. Though the instructions at the given link are for Cognos BI version 8.x, they will give you an idea about configuring IIS v7.x.

Add virtual directories

Let us continue with our instructions for configuring IIS v6. Once the IIS Web Server version 6 is installed, you have to create a couple of Virtual Directories and add a new Web Service Extension by doing the following steps.

  1. As shown in Figure 14, start the IIS console by clicking Start -> Run -> inetmgr.
  2. Expand localcomputer -> Web Sites, right-click Default Web Site, choose New -> Virtual Directory.
Figure 14. Creating virtual directory
Creating virtual directory
  1. Click Next on the welcome screen.
  2. As shown in Figure 15, type ibmcognos as Alias, and click Next.
Figure 15. Virtual directory alias
virtual directory alias
  1. As shown in Figure 16, type c10_location\webcontent as PATH, and click Next.
Figure 16. Virtual directory path
virtual directory path
  1. As shown in Figure 17, leave the defaults for access permissions (Read) and click Next.
Figure 17. Access permissions
access permissions
  1. Click Finish to create the virtual directory ibmcognos.
  2. Now you have to create a virtual directory under ibmcognos. Right-click the ibmcognos virtual directory that you just created and choose New -> Virtual Directory.
  3. Click Next on the welcome screen.
  4. Type cgi-bin as Alias, and click Next.
  5. As shown in Figure 18, choose c10_location\cgi-bin as the PATH, and click Next.
Figure 18. Directory path
directory path
  1. Choose Read and Execute as the Access Permissions, and click Next.
  2. Click Finish to create the virtual directory cgi-bin under ibmcognos.

Create a Web Service extension

Perform the following steps to create a Web Service extension.

  1. As shown in Figure 19, right-click Web Service Extensions and click Add a new Web server extension.
Figure 19. Adding a new web server extension
Adding a new web server extension
  1. As shown in Figure 20, type Cognos as the extension name, and click Add.
Figure 20. Entering Web Server extension name
Entering Web Server extension name
  1. Browse and choose c10_location\cgi-bin\cognos.cgi as the file and click OK.
  2. Click OK again to create the Web Service extension.
  3. As shown in Figure 21, click the Allow button to allow the newly created web service extension and exit IIS.
Figure 21. Changing WSE permissions
Changing WSE permissions

Test the Web Server connectivity

If they are not already running, start the Cognos BI Server (by going to IBM Cognos Configuration Console), and the Web Server (by going to Windows Services). You can now test the Web Server connectivity by typing the Gateway URI in a Web browser. The Gateway URI can be found in the IBM Cognos Configuration Console on the Environment page.

As shown in Figure 22, the default Gateway Welcome URI is: http://localhost:80/ibmcognos/cgi-bin/cognos.cgi?b_action=xts.run&m=portal/welcome/welcome.xts

It may take a few minutes for the Web page to open. If you see the Welcome page in the IBM Cognos BI portal, as shown in Figure 22, then your installation is working.

Figure 22. Displaying Gateway welcome page
Displaying Gateway welcome page

Working with an Informix data source in Cognos BI

In the previous section, you have installed, configured, and started the Cognos BI Server. It is now time to create an Informix data source and see it in action.

Creating an Informix data source in Cognos

Prerequisite:

If it is not already done, set up the client/server connectivity for the Informix data source by following the instructions given in the section titled Configuring client/server connectivity on the Cognos Server

Procedure:

  1. As shown in Figure 23, bring up the Cognos BI Administrator home page in a web browser and click the Administer IBM Cognos content link.
Figure 23. Administer IBM Cognos content
Administer IBM Cognos content
  1. As shown in Figure 24, click Configuration.
Figure 24. Data source configuration
Data Source configuration
  1. Click the New Data Source icon (cylinder icon) on the top right corner, as shown in Figure 25.
Figure 25. New data source selection
New data Source selection
  1. You will see the new data source wizard. Specify the name and description of the new data source, and then click Next.
  2. Choose IBM Informix as the type, and specify an isolation level (for example, cursor stability), and then click Next.
  3. You are asked to specify the following connection string related parameter values, also shown in Figure 26.
    • Informix database name: Name of your Informix database, for example gsdb.
    • Host name: Name or IP address of the database server host computer.
    • Server name: Name of your Informix server instance on the Informix server, for example cbiwin.
    • Collation sequence: Leave it blank
    • Service: Port number or service name of SQLI protocol (olsoctcp protocol) port for the database instance on the database server.
Figure 26. Connection string properties
Connection string properties
  1. As shown in figure 27, select the User ID and Password check boxes. If you prefer, also check Create a signon that Everyone group can use.
  2. Enter the database User ID and Password that you would like to use to connect to your Informix data source.
  3. Click Test the connection.
  4. Click Test again.
Figure 27. Testing the database connectivity
Testing the database connectivity

Recommended practice: It is not a good practice to use Informix user for this. You can create a separate account such as gsdbusr on the database server, grant necessary database and table-level privileges (such as connect, resource privileges), and use that database user account.

  1. As shown in Figure 28, if the database connection string parameters are valid, you should see that the database connection test was successful. Click Close.
Figure 28. Verify the connection test results
Verify the connection test results
  1. Click Close again, and then click Finish.
  2. You have successfully created an Informix data source connection. You can now use this data source connection to connect to the Informix database data mart.

The following are common reasons why the connection test would fail.

  • Not able to connect to the server (i.e. host) where the database is hosted. Check the connectivity with a ping command at the command prompt.
  • Source database is not a UTF8 database.
  • Not setting DB_LOCALE to en_us.utf8 while defining the SQLHOSTS information.
  • Incorrect database connection properties such as incorrect dbname, server, portnumber, and others.
  • Incorrect db username and/or password.
  • Dbuser doesn't have privileges to connect to the database
  • You have installed 64-bit CSDK or IConnect software on the Cognos BI Server. Client software has to be 32-bit.
  • You have installed 3.70 version of the CSDK / IConnect software. As of the date this tutorial was published version of CSDK / IConnect has to be 3.50.

Take the right action to correct the problem and try again.

Informix data source: See it in action

It is now time to see how an Informix data source can be used in Cognos BI. To demonstrate this, from Cognos Framework Manager, you are going to connect to the Informix data source that was created in the previous section. Cognos Framework Manager allows you to model data and publish packages.

Prerequisite:

Install IBM Cognos Framework Manager on the Cognos BI server. Installation of Cognos Framework Manager is very simple and it isn't covered in this tutorial.

See it in action:

  1. Bring up the Cognos Framework Manager user interface by clicking All Programs -> IBM Cognos 10 -> IBM Cognos Framework Manager.
  2. Click Create a new project using Model Design Accelerator.
  3. As shown in Figure 29, type the name of the new project and click OK.
Figure 29. Creating a new project
Creating a new project
  1. Select the design language for the project and click OK.
  2. Select the Informix data source that was previously created (gsdb) and click Next.
  3. As shown in Figure 30, select all the tables in the gosales schema and click Continue.
Figure 30. Selecting the tables
Selecting the tables
  1. The Framework Manager will import all the selected tables. You are presented with the Model Design Accelerator Info screen. Click Close.
  2. As shown in Figure 31, you will now see all the tables imported into the Model Design Accelerator. Save and close to exit.
Figure 31. List of tables
List of tables

Tune the Informix instance for performance

It is very important to tune the configuration of the database system for optimal performance. Gather the information about your system resource availability such as number of CPUs, amount of memory, storage, and then tune your database system accordingly.

On a test system (for both remote and local database instances) there was at least a 100% improvement in the performance by just tuning some of the crucial parameters in the ONCONFIG parameter file.

The following table outlines the default and changed values of these crucial parameters on the test system with 4 CPUs and 8 GB memory:

Table 1. Sample ONCONFIG parameters
Default value in 11.5Tuned value
CPU-related configuration parameters
MULTIPROCESSOR 0MULTIPROCESSOR 1
VPCLASS cpu,num=1,noageVPCLASS cpu,num=4,noage
VP_MEMORY_CACHE_KB 0VP_MEMORY_CACHE_KB 4096
AIO-related configuration parameter
DIRECT_IO 0DIRECT_IO 1
Shared memory configuration parameters
RESIDENT 0RESIDENT 1
SHMBASE 0x80000000LSHMBASE 0x11000000L
SHMVIRTSIZE 32656SHMVIRTSIZE 614400
SHMADD 8192SHMADD 131072
SQL statement cache configuration parameters
STMT_CACHE 0STMT_CACHE 2
STMT_CACHE_HITS 0STMT_CACHE_HITS 1
STMT_CACHE_SIZE 512STMT_CACHE_SIZE 20000
Parallel database query (PDQ) configuration parameters
DS_MAX_QUERIESDS_MAX_QUERIES 8
DS_TOTAL_MEMORYDS_TOTAL_MEMORY 491520
DS_NONPDQ_QUERY_MEM 128DS_NONPDQ_QUERY_MEM 122880
BUFFERPOOL and LRU configuration parameters
BUFFERPOOL default, buffers=10000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000BUFFERPOOL default, buffers=200000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000
BUFFERPOOL size=4K, buffers=10000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000BUFFERPOOL size=4K, buffers=200000, lrus=8, lru_min_dirty=50.000000, lru_max_dirty=60.500000

We haven't fully utilized all the available memory because the size of the sample database that we were working with was small. In a production environment you can definitely utilize more memory for the configuration of BUFFERPOOL and other memory related parameters. The ONCONFIG.%INFORMIXSERVER% file is located at %INFORMIXDIR%\etc directory.

You would have to restart the database instance (by stopping and starting the database service in the Windows services) after making these changes in the ONCONFIG file.

Apart from tuning the ONCONFIG file, the following best practices should be considered:

  • Have enough free-space in the root DBSpace.
  • Do not create any user databases in the root DBSpace.
  • Move logical and physical logs out of the root DBSpace and create them in a separate user DBSpace.
  • Tune your logical and physical log size as per your production requirements.
  • Take regular backups of your production database instances.

OpenAdmin Tool for monitoring and administration

OpenAdmin Tool (OAT) is a PHP-based web browser administration tool for managing one or more Informix database servers. As shown in Figure OAT, the OpenAdmin Tool for Informix provides the ability to monitor and administer multiple Informix database server instances from a single location.

Figure OAT. OpenAdmin Tool interface
OAT interface

(View a larger version of Figure OAT.)

You can download the OpenAdmin Tool one-click installer bundle which comes with everything that you need to get OAT up and running. You can install OAT either on the database server or on a client Windows machine.

You can find more information about capabilities of OAT and download a free copy of the latest version the Resources section.

Recommended practice: It is not a good idea to install OAT on the Cognos BI Server. Choose a different location such as the database server or another Windows client machine.


Conclusion

As you can see, we have covered a great deal of material in this tutorial series, walking you through the steps to install and configure Informix, Cognos Express, and Cognos BI Server to successfully deploy Cognos BI products to connect to Informix data sources. We hope that you have enjoyed reading through the material and practicing the steps as much as we have enjoyed writing these tutorials. Don't forget to leave your valuable feedback telling us what you think about the tutorials.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=696949
ArticleTitle=Recommended practices for using Cognos with Informix, Part 2: Deploy Informix with IBM Cognos BI Server 10
publish-date=07072011