Verify the ODBC driver whether it supports the Community Edition(Optional)
Prerequisites:
· Install the MySQL Community Server and Client.
· Create a user and a database for test
· Install the informatica powercenter client.
Configure the DSN and test the connection
· Double click Control Panel\All Control Panel Items\Administrative Tools\ Data Sources (ODBC)
and then configure an ODBC Data Source Name (DSN) Using the MySQL ODBC Driver shipped with informatica.
· Fill out the DSN, and test the connection, if it shows a warning message like the following figure, that means you should read this document, otherwise, Close the document and sip tea.
Download the MySQL Community ODBC Driver and Upload it to Powercenter Server
Download
· You should download the ODBC Driver from MySQL Website. Here is the download link http://dev.mysql.com/downloads/connector/odbc/
· Select platform
· Download the Compressed TAR Archive
Upload
· Decompress the tar Archive and then you will find the libraries in the lib folder.
· Upload the libmyodbc5a.so (for ASCII) and libmyodbc5w.so (for Unicode) to the $INFA_HOME/ODBC6.1/lib folder (please make sure that file names are unique to other files.)
· Change the owner(same with the powercenter service owner) and permission(644)
Configure the $ODBCINI and $ODBCINST files
Attention
-
Please don’t copy and paste directly;
-
The Driver is completely accurate;
-
Set DriverUnicodeType to 1 when the codepage is Unicode
-
Set Charset to utf8 when the codepage is Unicode, otherwise some characters such as Chinese are garbled.
Configure $ODBCINST file
[ODBC Drivers]
MySQL ODBC Community UNICODE=Installed
MySQL ODBC Community ASCII=Installed
Append these two sections:
[MySQL ODBC Community UNICODE]
Driver=/opt/informatica/powercenter/910/ODBC6.1/lib/libmyodbc5w.so
Description=ODBC For MySQL Community Unicode
DriverUnicodeType=1
DriverODBCVer=5.3
FileUsage=0
[MySQL ODBC Community ASCII]
Driver=/opt/informatica/powercenter/910/ODBC6.1/lib/libmyodbc5a.so
Description=ODBC For MySQL Community ASCII
DriverODBCVer=5.3
FileUsage=0
Configure $ODBCINI file
[MySQL ODBC Community UNICODE]
Driver=/opt/informatica/powercenter/910/ODBC6.1/lib/libmyodbc5w.so
DESCRIPTION=MySQL Community Unicode
Database=<MySQL_Database>
Server=<Server_Hostname_OR_IP>
User=<Login_user>
Password=<Password>
Socket=
Charset=utf8
DriverUnicodeType=1
[MySQL ODBC Community ASCII]
Driver=/opt/informatica/powercenter/910/ODBC6.1/lib/libmyodbc5a.so
DESCRIPTION=MySQL Community ASCII
Database=<MySQL_Database>
Server=<Server_Hostname_OR_IP>
User=<Login_user>
Password=<Password>
Socket=
Install MySQL Community ODBC Driver on the client
See Download the MySQL Community ODBC Driver
Test the MySQL Community ODBC Driver
Please the ssgodbc tool to test the MySQL Community ODBC Driver on the Powercenter Server.
Ssgodbc help guide
Here’s the command’s help:
Usage: ssgodbc -d dsn -u username -p password [-t user] [-v] [-g]
-d Data Source Name
-u odbc username
-p odbc password
-v verbose output
-c column description only
-g get array size only
-t time SQLTablesW for user