MySql Connection Issue In Datastage 11.5

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

MySql Connection Issue In Datastage 11.5

Post by Chon »

I have configured the odbc.ini to connect to mysql database, but when trying testing it I received a Connection Failed error

Error Message:
ODBC function "SQLConnect" reported: SQLSTATE = HY000: Native
Error Code = 2,0003: Msg = [IBM(DataDirect OEM)[ODBC MySQL Wire Protocol driver]
Can't connect to MySQL server on '13.250.184.188'

odbc.ini
[MYSQLPROD]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmysql00.so
Description=DataDirect 7.0 MySQL Wire Protocol
HostName=13.250.184.188
PortNumber=3306
Database=sodv3
LogonID=
Password=
OPTION=3
SOCKET=

uvodbc.config
<SKYRPD>
MYSQLPROD = ODBC
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Using your DataStage user, can you Login to the command line on your DataStage Server and do a "ping 13.250.184.188"
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

After trying it , this is the result

dsadm@p2lp18:/home/dsadm>ping 13.250.184.188
PING 13.250.184.188: (13.250.184.188): 56 data bytes

then nothing is happening after that one liner result
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

That means that your Problem doesn't lie with datastage, but that the MySQL machine at address 13.250.184.188 cannot be reached from your UNIX DataStage Server.

You should contact your machine Administrator to get access opened between the two Servers.
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

I tried the same step on the database we already using in DataStage, but I got the same result.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Is the machine "p2lp18" your DataStage engine Tier?

To which working database did you try to "ping"? Can you ping your Workstation from the Server? (If not, then the admins might have blocked ping)
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

Is the machine "p2lp18" your DataStage engine Tier?
- Yes

To which working database did you try to "ping"?
- Yes. I ping'ed the MySQL connection in our engine tier which is p2lp18 and I got a one liner result. I also tried it in our database that we already using in DataStage but I got the same result.


BTW, we are using AIX.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Can you ping your Workstation from the AIX Server?
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

Yep. I can ping my workstation from AIX server
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Doing a ping is a reasonable, general test but not quite the same as making a database connection. Just because ping succeeds or fails does not mean that you will be able to connect to the database.

It is better to do a ' telnet target_db_server db_port ' command from your AIX Information Server.

If the telnet command connects OK, then double check how you defined the database connection, because if it is defined correctly then it should work.

If the telnet command times out or fails to connect, then you probably have a firewall blocking the traffic and need to get your network/firewall people to open it up.

A few other ideas:

- check your file name (the default .odbc.ini file name begins with a dot)

- list the data source name in the [ODBC Data Sources] header section of the .odbc.ini file, such as db_name=DataDirect 7.0 MySQL Wire Protocol
Last edited by qt_ky on Wed Mar 14, 2018 7:21 am, edited 1 time in total.
Choose a job you love, and you will never have to work a day in your life. - Confucius
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

To add to the previous post, use "Telnet 13.250.184.188 3306" to attempt to connect to the IP address and to the appropriate port as you specified in your odbc.ini

Usually "ping" is sufficient, but only at sites with no restrictions imposed by System Administration,.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I have also found that the ODBC DSN needs to be <= 8 characters; you've got 9.

And, the DSN <SKYRPD> in the uvodbc.config file needs to match the DSN [MYSQLPROD] in the .odbc.ini file.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

I tried using telnet but I got this result

admin@p2lp18:/home/dsadm>telnet 13.250.184.188 3306
Trying...
admin@p2lp18:/home/dsadm>


This is my odbc config (.odbc.ini)
[SKYRPD]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmysql00.so
Description=DataDirect 7.0 MySQL Wire Protocol
HostName=13.250.184.188
PortNumber=3306
Database=sodv3
LogonID=
Password=
OPTION=3
SOCKET=
EncryptionMethod=0
ValidateServerCertificate=0
DriverUnicodeType=1

This is my uvodbc config (uvodbc.config)
<SKYRPD>
DBMSTYPE = ODBC
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Before going further into the Problem, can you talk to one of your AIX Administrators and explain the connectivity issues - perhaps something else is blocking connectivity.

One more thing, could you post the Output of the command "traceroute 13.250.184.188"
Chon
Participant
Posts: 19
Joined: Mon Mar 12, 2018 4:01 am

Post by Chon »

Here is the result

admin@p2lp18:/home/dsadm>traceroute 13.250.184.188
ksh: ^Vtraceroute: not found.
Post Reply