Skip to main content

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

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

All information submitted is secure.

  • Close [x]

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.

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

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

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

Install and get up and running

Sreeni Paidi, Enablement Consultant, IBM
Sreeni Paidi photo
Sreeni 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 Williamson
Jeffrey 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.

Summary:  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.

View more content in this series

Date:  07 Jul 2011
Level:  Intermediate PDF:  A4 and Letter (2044 KB | 40 pages)Get Adobe® Reader®

Activity:  20739 views
Comments:  

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.

6 of 10 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=696949
TutorialTitle=Recommended practices for using Cognos with Informix, Part 2: Deploy Informix with IBM Cognos BI Server 10
publish-date=07072011
author1-email=spaidi@us.ibm.com
author1-email-cc=
author2-email=jeffrey.williamson@jcbpartners.com
author2-email-cc=