ODBC Connection connects intermittently
Moderators: chulett, rschirm, roy
ODBC Connection connects intermittently
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
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
ODBC Timeout
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?
Can you help me know where can i see the timeout setting for ODBC?
CONNECTION PROBLEM RESOLVED TO SOME EXTENT
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)
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
You might like to check the same thing in the uvodbc.config files. There is one in DSEngine and one per project.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: