ODBC Connection connects intermittently

[EDI_ITLS]

Hi All,

I am facing a weird issue with connecting my DB2 databse to datastage jobs.
I have configured the odbc.ini and uvodbc.config files as per needed but whenever i use a connector stage(ODBC predominantly) and test the connection sometimes it says connection successful and another second if i hit test again it says connection not established and gives the following message:

"SQLConnect" reported: SQLSTATE = 60: Native Error Code = 1,231: Msg = [IBM(DataDirect OEM)][ODBC 20101 driver]1231 (CC_OdbcConnection::connect, file CC_OdbcConnection.cpp, line 440)

I would also include that once a connection fails and i refresh my database in DB2 and then test the connection again it works. This is what making me confused to understand the problem. I have tried this from other systems(computers) also but it behaves the same.

So what really is the problem? DB2 or Datastage?

Thanks,
Manas Mishra

[kduke]

I think the issue is in ODBC. We get it currently on my job now in DEV and TEST environments. There is some place to setup the timeout for ODBC. I would bump that up. Let us know if that fixes it.

[EDI_ITLS]

Thanks Kim. This is a useful piece of information. But i suspect if ODBC can timeout in just few seconds? Because for me for one second it says connection NOT established and then in another 10seconds i go back and refresh my database check again and it CONNECTS.

Can you help me know where can i see the timeout setting for ODBC?

[kduke]

I looked for where this is stored and could not find it. It might be in with the driver setup. I would do a search surely this has been covered.

[EDI_ITLS]

Kim, It looks like there was some problem with .odbc.ini itself. I guess it got corrupted or may be some extra characters or spaces were creating a problem. I replaced the .odbc.ini with a backed up file and then added the DSNs again. Now the connection remains connected and i dont have to do refresh of DB2 again and again.

But now there is a new problem, if there are more than 2 odbc connector stages i am getting the following error log for some of the connectors.

ODBC function "SQLConnect" reported: SQLSTATE = HY000: Native Error Code = 1,231: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Required value not found. (CC_OdbcConnection::connect, file CC_OdbcConnection.cpp, line 440)
ODBC function "SQLEndTran(SQL_ROLLBACK)" reported: SQLSTATE = 08003: Native Error Code = 0: Msg = [DataDirect][ODBC lib] Connection not open (CC_OdbcConnection::rollback, file CC_OdbcConnection.cpp, line 1,002)

[ray.wurlod]

Make sure that the last line in .odbc.ini has a line terminator.

[EDI_ITLS]

Ray,
Thanks for replying. I checked .odbc.ini and found that i had missed the newline in the end. I included it at three places-
1. Inside .odbc.ini.
2. Inside DSEngine.
3. Inside < My Project>.

I am still receiving the same error while running job.
Do i need to look for .odbc.ini in some other places also? And does uvodbc.config also needs to have newline in the end?(Currently it has)

Thanks,
Manas Mishra

[ray.wurlod]

Only one .odbc.ini gets used, the one in DSEngine (or pointed to by ODBCINI environment variable).

You might like to check the same thing in the uvodbc.config files. There is one in DSEngine and one per project.

[EDI_ITLS]

Thanks Ray it solved my problem. Turned out that my uvodbc.config was not in sync across project folders. I had updated it only in my project and DSEngine.

Just one more small and i guess silly question Ray. Where can i locate ODBCINI environment variable. I have DS client installed in my WIN7.

[ray.wurlod]

ODBCINI is not a mandatory environment variable. If present it is on the server. Access Windows environment variables through Control Panel > System

[EDI_ITLS]

Thanks Ray and Kim for your help.
Appreciate that.

[chulett]

ODBCINI is not a mandatory environment variable.

And just for the record EDI_ITLS, it IS mandatory if your .odbc.ini file is in a "non-standard location" as that's how it knows where to find it.