Problem Cognos 10 Connection To Netezza in AIX 6.1 Server

Cognos 10 Business Intelligence Cognos Connection
1

Hi guys,

I have installed Cognos 10 in AIX 6.1 Server.
I have an unsolved trouble with Cognos Connection using ODBC.

My Error Code : (Problem with Compatible Type Connection)

QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
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-0107 A general exception has occurred during the operation “attach”.
BME-EX-0144 Trace back:
BmtPlayTransaction.cpp(757): QFException: CCL_RETHROW: BmtPlayTransaction::DoAction
BmtActionsHelper.cpp(488): QFException: CCL_RETHROW: BmtActionsHelper::DoAction
QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(2028): QFException: CCL_RETHROW: QFSSession::SessionProcessTestConnection()
Source/QE_RsApi.cpp(2070): QFException: CCL_RETHROW: QE
Source/QEI_ConnectionFault.cpp(814): QFException: CCL_THROW: QE

NB :
~ I have tested directly using NZODBC Driver to connect from AIX to Netezza and the connection is ok. (Select syntax can be used)
~ I also have tested directly using ODBC Driver to connect from AIX to Netezza and the connection is ok too.
~ But, when I tested using iodbctest in Cognos Bin, that’s not OK. the following result like this :

$ ./iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list): NZSQL
1: SQLDriverConnect = Þ­¾ï (33) SQLSTATE=Ð@Î
1: ODBC_Connect = (33) SQLSTATE=

** If The problem are related to the environment variable, I have already checked it too…
Here my environment setting :

#set java environment
JAVA_HOME=/usr/java5_64
JRE_HOME=/usr/java5_64/jre
HOME=/home/cognos
PATH=/usr/local/nz/bin:/usr/local/nz/lib:/usr/ibm/cognos/c10/bin:/usr/ibm/cognos/c10/lib:/opt/odbc32v51:/opt/odbc32v51/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/lpp/xlC/exe:
#:/usr/ibm/cognos/c10/bin.
AIXTHREAD_SCOPE=S

export AIXTHREAD_SCOPE

export JAVA_HOME
export JRE_HOME
export HOME
export DISPLAY=192.168.1.200:0.0

export ODBCINI=/opt/odbc32v51/odbc.ini
export ODBCHOME=/opt/odbc32v51
export NZ_ODBC_INI_PATH=$ODBCHOME

export PATH
export CLASSPATH=/usr/local/nz/lib/nzjdbc3.jar:/home/db2inst1/sqllib/java/db2jcc.jar:/home/db2inst1/sqllib/java/db2jcc_license_cu.jar

LD_LIBRARY_PATH=$ODBCHOME/lib:/usr/local/nz/lib:/usr/ibm/cognos/c10/lib:
LIBPATH=/usr/ibm/cognos/c10/bin:$ODBCHOME:/opt/odbc32v51/lib:$ODBCHOME/bin:/usr/local/nz/lib:/usr/local/nz/bin:/usr/lib:/lib:

export LIBPATH
export LD_LIBRARY_PATH

======================================================

Could anyone help me to solve this problem or have experience with this problem?
Thanks.

2

Hi,

are the drivers properly installed with Cognos 10?

type:
which iodbctest /usr/ibm/cognos/c10/bin/iodbctest

what does this command returns?

did you copy the odbc.ini to /usr/ibm/cognos/c10/bin and did you rename it to .odbc.ini

check this link too, maybe you see something you missed… :o

3

Hi MartijnC,

Thanks for your replay…

  1. I have type: which iodbctest /usr/ibm/cognos/c10/bin/iodbctest
    The command return :
    $ which iodbctest /usr/ibm/cognos/c10/bin/iodbctest
    /usr/ibm/cognos/c10/bin/iodbctest
    /usr/ibm/cognos/c10/bin/iodbctest

  2. yes, i already have copied the odbc.ini to /usr/ibm/cognos/c10/bin and rename it to .odbc.ini before.

  3. I have checked this link too, and follow their steps, one by one.
    http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page569.html?ca=drs-

    But, I still haven’t solved the problem.

Today i have rechecked again and the result is same.
I think .odbc.ini has never been execute by cognos iodbctest, because iodbctest always use $ODBCINI from .profile (Environment Variable set).

Any idea?

Thanks for your help.

4

which Netezza driver version do you use? 4.0 or higher?

check this too:

Open odbcinst.ini and update UnicodeTranslationOption value to utf32 (see below, by default, it is utf8)
This setting is the value needed by another ODBC driver manager such as DataDirect.
For iODBC, the setting needs to be utf32.

[NetezzaSQL]

UnicodeTranslationOption = utf32
CharacterTranslationOption = all

restart Cognos service to be safe.

5

Hi MartijnC,

Thanks for response.

I think Netezza driver that i have used is version 6 (npsclient.6.0.P4.tar.z for AIX).

I Have already tried to update UnicodeTranslationOption value and there is no impact at all,
but i will try it again today to make it sure.

Scenario for Test:

  1. Should i copy file odbcinst.ini to cognos_directory/bin ? and rename it to .odbcinst.ini?
  2. Should i copy file odbc.ini to another location(e.g. cognos_directory/bin without rename it to .odbc.ini)?
  3. Should i install iodbc driver too from 3rd party which not bundled with cognos?
    FYI : right now i have already installed netezza driver and Netezza-branded DataDirect Driver Manager (ODBC).

Here my detail odbc.ini :
;
; odbc.ini
;
[ODBC Data Sources]
NZSQL = NetezzaSQL

[NZSQL]
Driver = /usr/local/nz/lib/libnzodbc.so
Description = NetezzaSQL ODBC
Servername = 192.168.1.20
Port = 5480
Database = TEST
Username = admin
Password = password
ReadOnly = false
ShowSystemTables = false
LegacySQLTables = false
LoginTimeout = 0
QueryTimeout = 0
DateFormat = 1
NumericAsChar = false
SQLBitOneZero = false
StripCRLF = false

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/odbc32v51
Trace=1
TraceDll=/opt/odbc32v51/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0

And here’s odbcinst.ini :

[ODBC Drivers]
NetezzaSQL = Installed

[NetezzaSQL]
Driver = /usr/local/nz/lib/libnzodbc.so
Setup = /usr/local/nz/lib/libnzodbc.so
APILevel = 1
ConnectFunctions = YYN
Description = Netezza ODBC driver
DriverODBCVer = 03.51
DebugLogging = false
LogPath = /tmp
UnicodeTranslationOption = utf8
CharacterTranslationOption = all
PreFetch = 256
Socket = 16384

6

Hi MartijnC,

Thanks for your help.

Today after i have changed UnicodeTranslationOption value to utf32, its still give me an error.
But,when i tried to change the value to utf16, my nightmare is gone… ;D the problem has been solved…

FYI : the result from iodbctest is still same with previous, but the cognos connection is running well.

Thanks again for your advice… :slight_smile:

7

Good to hear that this is working and thanks for sharing your solution, Kevin. :slight_smile: