Recommended practices for using Cognos with Informix, Part 1: Deploy Informix with IBM Cognos Express 9

Install, configure, and tune

Connecting your IBM® Informix® databases to IBM 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 gets you started with using IBM Cognos Express V9 together with IBM Informix V11.5 as a content store and data source. In Part 2, you'll get 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.



30 June 2010

Also available in Chinese

Before you start

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

About this series

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

About this tutorial

This tutorial walks you through the steps to install and configure IBM Cognos Express and IBM Informix Server, how to test connectivity between the two, and then tune Informix for optimal performance.

Prerequisites

This tutorial is intended for system administrators and application administrators who are interested in deploying IBM Cognos Express version 9 with IBM Informix version 11.5 as 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 of 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 Express 9 software.

About IBM Cognos Express software

IBM Cognos Express is a business intelligence and planning solution that integrates the following IBM Cognos products with a central application used for installation and administration.

  • IBM Cognos Express Advisor - using the analysis features of IBM Cognos Executive Viewer, IBM Cognos Express Advisor enables you to quickly move from raw data to high-impact analysis and visualizations. Conduct powerful 'what if' scenario modeling using simple point-and-click.
  • IBM Cognos Express Xcelerator - using the performance management capabilities of IBM Cognos Advisor, IBM Cognos Express Xcelerator allows you to transform common spreadsheets for planning and analysis, and optimize large, highly complex data sets with real-time response. Gain more insight through multi-dimensional analysis while retaining a familiar Excel interface.
  • IBM Cognos Express Reporter - using the querying and reporting power of IBM Cognos 8, IBM Cognos Express Reporter empowers business users to access, modify and author reports with complete self-service reporting and ad hoc query capabilities. Access any type of data, including relational, analytical OLAP, or desktop files, and deliver your reports via the web, PDF, Microsoft Excel spreadsheets, email, or portal.
  • IBM Cognos Express Manager - provides the ability to install, configure and administer all IBM Cognos Express products. You can publish data sources, add users and secure business content in one central, easy-to-use application.

About IBM Informix software

IBM Informix software has unmatched high availability, is deeply embedded, has blazing performance, zero administration, and data warehousing characteristics, which 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 Cognos Express and Informix, proper planning is required to avoid any last minute surprises and delays in delivering the environment for the business users.

Planning for the hardware

If you have a blade server, you can create two nodes on the server, one for Cognos Express and the other one for Informix Server, 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: For optimal performance and ease of manageability, it is recommended to install Cognos Express and Informix Server software on two different servers or nodes.

Planning for the software

The following software stack is required. Make sure that you have access to all these software packages before you start the installation process.

For the server that is hosting Cognos Express:

  • Microsoft Windows Server 2003 for x86 64-bit
  • Service Pack 2 for Microsoft Windows Server 2003
  • Microsoft Office 2003 or Microsoft Office 2007
  • Internet Information Service (IIC) version 6
  • Cognos Express version 9.0
  • 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

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

Installing 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 that you also have access to the software installation media.

Note: 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. In this exercise, enter ce_remote as the server name.
  9. On the following screen, you can choose location and size of data dbspace and smart BLOB space. Type 1024 as the size of dbspace and sbspace, and then click Next.
  10. On the following screen, you are given the summary of what you are going to install. Click Next to begin the installation.
  11. 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 in the Informix command Window.

Setting up a sample database on the Informix server

You are now going to setup a sample gosales database called gs_db_ifx. Later on, you will use this database to test the connectivity between the database server and the Cognos Express server. To setup the gs_db_ifx database on the Informix Server, perform the following steps.

  1. Download the compressed dbexport file gs_db_ifx.exp.tar.gz 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 gs_db_ifx.exp, which contains the database dump.
  3. Open a command window for Informix instance (ce_remote in this example) by going to Start -> All Programs -> Informix Dynamic Server 11.50 -> < instance_name >.
  4. At the command prompt, change directory to C:\gosales where the gs_db_ifx.exp is located.
  5. At the command prompt, run the following commands to set the environment variables for CLIENT_LOCALE, DB_LOCALE, and DBDATE, as shown in Listing 1.
Listing 1: Setting environment variables
set CLIENT_LOCALE=en_us.utf8
set DB_LOCALE=en_us.utf8
set DBDATE=Y4MD/

Recommended practice: Any Informix database that Cognos Express can connect to has to be a UTF8 database. That is the reason you set CLIENT_LOCALE and DB_LOCALE to en_us.utf8 before you import the database.

  1. Run the following dbimport command at the C:\gosales folder to import gs_db_ifx database into the ce_remote DBSpace, as shown in Listing 2.
Listing 2: dbimport command
dbimport gs_db_ifx -d ce_remote
  1. Check the output file for any errors.

Installing and configuring Cognos Express software on the Cognos Server

Prerequisites

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

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

Instructions on installing IIS 6 on Windows Server 2003 can be found in the Resources section.

IBM Cognos Express Xcelerator comes with Cognos Express version 9 and has been tested for use with Microsoft Office 2003 and Microsoft Office 2007. If you install a different version of Microsoft Office, some features in Xcelerator may not function properly.

Installing Cognos Express software

  1. Insert the installation 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. Choose English, and then click OK.
  3. On the introduction screen, click Next.
  4. Read and accept the license agreement and click Next.
  5. Choose the default install folder and click Next.
  6. Choose the defaults for the shortcut folder and click Next.
  7. On the configuration screen, the express administrator account name will default to administrator. You can leave it as is, or shorten it to admin, as shown in Figure 1. You can also enter a password.
Figure 1. IBM Cognos Express configuration screen
The IBM Cognos Express configuration screen is where you can specify admin user information
  1. If you do not enter a password for the admin user, you will get a warning box to ensure that this is what you want. Click OK.
  2. On the next screen, as shown in Figure 2, you are presented with the pre-installation summary. Click Install.
Figure 2. Pre-installation summary screen
This screen capture shows the pre-installation summary screen, which allows you to review install information before clicking Install
  1. As shown in Figure, 3, the installation process will take a few minutes, with the installation bar denoting progress along the bottom.
Figure 3. Installation progress
Figure 3 shows the installation progress, including each step of the installation
  1. When the installation is done, click Done, as shown in Figure 4.
Figure 4. Completing the installation
This screen capture shows the Complete the installation screen.
  1. If you leave the Launch Express Manager Console check box selected, as shown previously in the figure 4, it will automatically open Cognos Express to complete the installation. If not, you will have to go to Start -> All Programs -> IBM Cognos Express -> Cognos Express Manager, as shown in Figure 5, to open the program to complete the installation.
Figure 5. Finishing the installation
Finishing the installation
  1. Once it is open, click Finish to complete the installation.
  2. The system will ask you to log into the Express Manager Console. Provide the User ID and Password for the admin user. You will see that Cognos Manager is installed. You can see the green check mark under the icon on the menu on the left hand side. The other components still need to be installed.
  3. You will begin with Reporter, which allows us to add data sources. Click Reporter in the left-hand menu, and then click the Install button on the main screen. When it is done installing, you will be asked to login again.
  4. After you login, you will see the green check mark now appearing under the Report icon on the left, and you will now be able to proceed with installing the other two services.
  5. Install Xcelerator. When it prompts you to choose a language - leave it on English, and then click Install.
  6. It will ask you to login once again after it is done installing Xcelerator.
  7. The final component will be Advisor. Click Install, and it will do the rest.
  8. Once you log back in, you will see green check marks under each icon on the left. The installation is complete.

Installing 32-bit Informix client software

It is required to install 32-bit Informix Client software on the machine that is running Cognos Express. 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 a license fee is associated with CSDK software. If you have the 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: Reboot the host after installing the IConnect software.

Configuring client/server connectivity on the Cognos Server

You can configure client/server communication between client products (such as Cognos Express) and the Informix Server using a program called Setnet32 on the client machine. The Setnet32 utility 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.

Recommended practice: Insert an entry for the database server host in the hosts file located at C:\WINDOWS\system32\drivers\etc on the Cognos Express Server.

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. From the Server Information tab, you see entries for cm_ids_svr_custom and cm_ids_svr_custom_drda. You are going to make a new entry for Informix server instance running on the Informix server. Enter the information as shown below, and shown in Figure 6.
  • IBM Informix Server: Name of the Informix instance. For example, ce_remote.
  • 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 6. New server entry in Setnet32
New server entry in setnet32
  1. Now click the Environment tab and set INFORMIXSERVER, as shown in Figure 7.
Figure 7. Environment variables in Setnet32
Environment variables in setnet32
  1. Click OK, and then click OK for any further prompts.

Recommended practice: Do not uninstall the CSDK or IConnect software once it is manually installed on the Cognos Express Server. Uninstalling will lead to some errors related to Informix connectivity.

Test the client/server connectivity on the Cognos Server

Informix client software comes with a 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 8, and then click OK.
Figure 8. Testing 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.

Adding an Informix data source

Now it is time to add a data source for the Informix database running on the remote Informix server. Enter the information below, also shown in Figure 9.

  1. Log in to Cognos Express Manager Console.
  2. From Administration, click Data.
  3. Click Add to add a data source.
  4. Choose Informix as the database source type, and enter the data source information for the remote Informix database running on the Informix database server.
  5. Click Retrieve Database Objects, as shown in Figure 9.
Figure 9. Adding an Informix data source
Adding an Informix data source
  1. It will take a few seconds to retrieve the database objects from the Informix database. You will see the name of one of the database objects displayed next to Database Object drop down as shown in figure 10. Click OK.
Figure 10. Informix data source
Informix data source
  1. When finished, it displays an entry for the data source you just added.

Installing and configuring Informix Server on the Cognos Express Server

Running Informix Server Software (for the data mart) on the same server as the Cognos Express Server is not a good practice. However, if you have no other option but to run both on the same server, then the following instructions will walk you through the process to set up Informix Server on the server that is running Cognos Express.

Prerequisites

Make sure that Cognos Express version 9 is installed on the Cognos Server / node2 before installing Informix Server software.

Installing Informix server software

  1. Launch the Informix software installer program by double-clicking the setup.exe file in IIF folder of the install media.
  2. Click Next on the welcome screen.
  3. Read and accept the terms in the license agreement, and click Next.
  4. As shown in Figure 11, you will see a message that says that the Informix is already installed on the computer. Choose to install the product in another directory and click Next.
Figure 11. Informix install location
Informix install location
  1. Choose Custom for setup type, and then click Next.
  2. Choose the default install directory, and then click Next.
  3. You are presented with available product components, select the features you want to install and click Next.
  4. On the next screen, as shown in Figure 12, select Start database server as Local System User, and Do not create user informix account, and then click Next.
Figure 12. Choosing not to create user Informix
Choosing not to create user Informix
  1. On the screen shown in Figure 13, 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 a server number other than 0 (between 1 and 255), and then click Next. For example, you can use ce_local for the server name and 2 for the server number.
Figure 13. Informix server details
Informix server details
  1. On the next screen, you are presented with data spaces configuration. Enter 1024 MB as the size for the dbspace and the sbspace, and then click Next.
  2. On the next screen, the installer will show the summary of what is getting installed. Click Next to begin the installation.
  3. When the installation is done, click Finish to exit the installer.

Installing 32-bit Informix client software

Download and install 32-bit Informix Connect (ICONNECT) software. If it is not already installed on the Cognos Express server, go to the mediafolder\ICONNECT folder and run the setup.exe program.

Creating a sample local database and a data source

You can follow the instructions in the following sections to create a sample database in the local Informix Server instance, and to also create a data source in Cognos Express for this local database.


Tuning 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 resources such as number of CPUs, amount of memory, storage, and others, 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.

Table 1 shows 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

You haven't fully utilized all the available memory because the size of the sample database that you 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. 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 14. The OpenAdmin Tool for Informix provides the ability to monitor and administer multiple Informix database server instances from a single location.

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 in the Resources section.

Figure 14. OpenAdmin Tool interface
Figure showing the OpenAdmin Tool interface

(View a larger version of Figure 14.)

Recommended practice: You should not install OAT on the Cognos Express Server. Instead, you should choose a different location such as the database server or another Windows client machine.


Conclusion

By following the steps outlined in this tutorial you can now successfully plan, install, configure, and deploy Cognos Express version 9 with Informix version 11.50 running on the same server, or a remote Microsoft Windows 2003 server. We hope it was helpful in learning about what it takes to successfully deploy Cognos Express software with Informix on Windows. Stay tuned for the second part of the tutorial series in which we cover the steps involved in successfully deploying Cognos BI Server software with Informix 11.50 as a content store and data source.

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=682959
ArticleTitle=Recommended practices for using Cognos with Informix, Part 1: Deploy Informix with IBM Cognos Express 9
publish-date=06302010