Informix Database connectivity Issue

[pradkumar]

Hi,

we have a project in Datastage version 7.5.1 on HP Unix box. The source and target database is Informix. The informix database resides on the same HPUnix box. Informix Cli stage has been used in the jobs.

Recently i have installed IBM websphere datastage version 8.0.1 on AIX box. My job is to migrate the whole project from 7.5 version on Unix to version 8.0.1 on AIX.I have imported the whole project from 7.5 to version 8.0.1 on AIX. Iam not able to set up the database connectivity from AIX to Informix database. Could you suggest how to set up database connectivity in the new version 8.0.1 on AIX.

I tried to configure connection to informix with Data Direct Informix wire Protocol Driver and when i try to import metadata iam able to view the tables but if try to view the data iam getting error as Transaction not available.

I even tried to connect with data direct Informix Driver,when i try to import the metadata it is throwing an error [ODBC] [DataDirect] [ODBC Informix driver] [Informix] Unable to load locale categories.

Note: In older version the informix database and datastage is on the same machine .

I asked the persons who designed the project in version 7.5.1 whether they have used wireprotocol driver, informix driver or informix client but they were not sure even though they have used Informix Cli stage in the jobs.

what are all the environment variables need to set up?How to run the migrated project from older version to new version by setting the Informix database connectivity.

It would be great if you could help me in resolving this connnectivity issue.

I can give the copy of older version dsenv file, .odbc.ini and uvodbc.config file and also the job designs.Please let me know..

I even searched the forum with key word load locale and found the similar post but couldnt able to figure it out. Some one can respond so that i can copy the configuration files from older version and newer version.


Thanks in Advance

[eldonp]

what we usually do is the following - although we have informix and datastage on seperate machines:
1. Install the informix software on the datastage server
2. Set up the /etc/hosts file on the datastage server to see the informix machine
3. Set up the sqlhosts file somewhere in the informix software directory
4. Set up odbc connections in the datastage server engine .odbc.ini file (ensuring that the path to the *.so libraries is valid)
5. Set up the informixserver variable in the dsenv file

try that...

p.s make sure that you re-create the odbc connections in the 8.0.1 .odbc.ini file and that you do not overwrite it with the 7.5.* version.

[pradkumar]

Hi thanks for you reply.

I have installed Informix sdk client 3.0(32-bit) for AIX on the machine. The directory is:dsadm@CommDSdev1/dss/clientsdk.

I setup the /etc/hosts file on datastage server to see the Informix machine
Internet Address Hostname # Comments
10.1.11.11 uwghp01.uwg.corp uwghp01.uwgrocers.com uwghp01

I setup the sql hosts file in the Informix software directory
Title: sqlhosts.demo
# Description:
# Default sqlhosts file for running demos.
#
#**************************************************************************

#demo_on onipcshm on_hostname on_servername
#demo_se seipcpip se_hostname sqlexec
dbengine1_tcp onsoctcp uwghp01 dbengine1_tcp

odbc.ini :

[Informix_Test1]
Driver=/dss/opt/IBM/InformationServer/Server/branded_odbc/lib/VMinf22.so
Description=DataDirect Informix driver
Database=dss
LogonID=dsadm
Password=dsadm1
ServerName=dbengine1_tcp
HostName=10.1.11.11
Service=online
Protocol=onsoctcp
EnableInsertCursors=0
GetDBListFromInformix=0
CursorBehavior=0
CancelDetectInterval=0
TrimBlankFromIndexName=1
ApplicationUsingThreads=1

uvodbc.config file under project level as <Informix_Test1>
DBMSTYPE = ODBC

dsenv file :

# PLATFORM SPECIFIC SECTION

set +u

if [ -z "$DSHOME" ] && [ -f "/.dshome" ]
then
DSHOME=`cat /.dshome`
export DSHOME
fi

if [ -z "$DSHOME" ]
then
DSHOME=/dss/opt/IBM/InformationServer/Server/DSEngine; export DSHOME
fi

if [ -z "$APT_ORCHHOME" ]
then
APT_ORCHHOME=/dss/opt/IBM/InformationServer/Server/PXEngine; export APT_ORCHHOME
fi

#if [ -z "$UDTHOME" ]
#then
UDTHOME=/dss/opt/IBM/InformationServer/Server/DSEngine/ud41 ; export UDTHOME
UDTBIN=/dss/opt/IBM/InformationServer/Server/DSEngine/ud41/bin ; export UDTBIN
#fi

#if [ -z "$ASBHOME" ] && [ -f "$DSHOME/.asbnode" ]
#then
ASBHOME=`cat $DSHOME/.asbnode`
export ASBHOME
#fi

#if [ -z "$ASBHOME" ]
#then
#ASBHOME=`dirname \`dirname $DSHOME\``/ASBNode
#export ASBHOME
#fi

if [ -n "$DSHOME" ] && [ -d "$DSHOME" ]
then
ODBCINI=$DSHOME/.odbc.ini; export ODBCINI
HOME=${HOME:-/}; export HOME

#LANG="<langdef>";export LANG
#LC_ALL="<langdef>";export LC_ALL
#LC_CTYPE="<langdef>";export LC_CTYPE
#LC_COLLATE="<langdef>";export LC_COLLATE
#LC_MONETARY="<langdef>";export LC_MONETARY
#LC_NUMERIC="<langdef>";export LC_NUMERIC
#LC_TIME="<langdef>";export LC_TIME
#LC_MESSAGES="<langdef>"; export LC_MESSAGES

LIBPATH=`dirname $DSHOME`/branded_odbc/lib:`dirname $DSHOME`/DSComponents/lib:`dirname $DSHOME`/DSComponents/bin:$DSH
OME/lib:$DSHOME/uvdlls:$ASBHOME/apps/jre/bin:$ASBHOME/apps/jre/bin/classic:$ASBHOME/lib/cpp:$ASBHOME/apps/proxy/cpp/aix-all-p
pc:$LIBPATH
export LIBPATH

ulimit -d unlimited
ulimit -m unlimited
ulimit -s unlimited
ulimit -f unlimited

LDR_CNTRL=MAXDATA=0x60000000@USERREGS
export LDR_CNTRL

fi


# informix variables
INFORMIXSERVER=dbengine1_tcp; export INFORMIXSERVER
ONCONFIG=onconfig.dbengine1_tcp; export ONCONFIG
INFORMIXDIR=/dss/sdkclient; export INFORMIXDIR
LIBPATH=$LIBPATH:$INFORMIXDIR/lib:$INFORMIXDIR/bin:$INFORMIXDIR/lib/esql:$INFORMIXDIR/etc:$INFORMIXDIR/lib/cli;export LIBPATH
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
export PATH=$PATH:$INFORMIXDIR/bin

But still iam not able to import the data from tabledefinitons and the error iam getting is [ODBC] [DataDirect] [ODBC Informix driver] [Informix] Unable to load locale categories.

Even when i try to view the data from the CLI stage from the job which was imported from version 7.5.1 on Unix its throwing an error as
ds_loadlibrary: error in dlopen

Does anyone know what is wrong and how to fix this issue.

If i use the Infromix wire protocol driver i am able to import the table definitions but the if i try to view the data its throwing an error as Transaction is not available.
But i need to fix the issue with informix cli stage because need to execute the project imported from Unix 7.5.1.

Thanks

[ray.wurlod]

Is the database set up with no logging? I've seen that cause "transaction not available" in the past.

[pradkumar]

yes the database has no transaction logging. I even got the same error with informix wire protocol driver and odbc stage on windows machine for version 8.0.1 , for that i got a patch from IBM.

Now recently i have installed the version 8 on AIX machine . i dont have much idea on AIX and installtion part.The issue is i need to run the jobs which were imported from older version . In those jobs Informix CLI stage has been used. Could you give an idea where am i going worng and how to work CLI stage .As per my knowledge the Informix wire protocol driver is used for ODBC stage not for CLI stage . Inorder to run the jobs with the CLI stage need to use non wire driver( informix data direct), need to have the informix client on the server ( which version of client need to install)and set the environment variables in dsenv. I have mentioned in the above post what i have done . i can see the DSN name created with the Datadirect Informix driver when i try to import the table definition but its throwing an error can not load locale catagories.

How to set up dsn connection inorder to work the Informix Cli stage jobs.

In older version the datastage and database resides on the same box.

Thanks

[pradkumar]

Hi ,

Thanks for your suggestions .The issue got resolved by installing the Informix client software on the datastage server and by setting the sqlhosts and services.

Once again thanks

[QuestExpert]

Hi,
this is my first time connecting to Informix from DataStage, and have no idea what to do and how to do it.
could you please help me with the steps, my DS and Informix are on different Unix Boxes.
do i need to install the Informix client on the DS Server? is it free?
what other options are there to connect Datastage Server job with Informix DB, i have tested Infromix CLI and Dynamic RDBMS Stages but they both are giving me Error
"ds_loadlibrary" Error in dlopen - Dynamic Error" when i am trying to view the data

Thanks

"

Hi ,

Thanks for your suggestions .The issue got resolved by installing the Informix client software on the datastage server and by setting the sqlhosts and services.

Once again thanks

[ray.wurlod]

This will be answered in your thread on this topic.

Nothing is to be gained by repeating the question tagged onto the end of a closed thread.