Setting up DB2 ODBC or Native DB2 database Connection in Informatica
There are two ways to create connection to DB2 in Informatica. First way is to use the DB2 in native mode using DB2 powerconnect module. Second way is to use odbc drivers.To connect to DB2 in native mode, you need DB2 PowerConnect module installed on your informatica server machine.
The steps to create DB2 connection using DB2 powerconnect module is below:
1) Create a remote DB2 database connection entry using command:db2 CATALOG TCPIP NODE <nodename> REMOTE <hostname_or_address> SERVER <port number>
2) Create Catalog entries for the database in unix using command:
db2 CATALOG DATABASE <dbname> as <dbalias> at NODE <nodename>
3) Commands to check the database entries are below:
db2 list database directory
db2 list node directory
4) Verify the connection to the DB2 database using command:
CONNECT TO <dbalias> USER <username> USING <password>
5) Create a relational connection (type:DB2) with connect string as “nodename” in Informatic workflow manager (Sample below):
To connect to DB2 using odbc, you need odbc driver for db2 installed on your informatica server machine.
The steps to create DB2 connection using odbc is below:
1) Create entry in .odbc.ini file on your Informatica server machine.[mydb2database]
Driver=/informatica/ODBC6.1/lib/DWdb225.so
Description=DataDirect 5.2 DB2 Wire Protocol
ApplicationUsingThreads=1
ConnectionRetryCount=0
ConnectionRetryDelay=3
#Database applies to DB2 UDB only
Database=MYDB2DB
DynamicSections=200
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=101.101.701.101
LoadBalancing=0
#Location applies to OS/390 and AS/400 only
Location=
LogonID=<yourid>
Password=<yourpasswd>
PackageOwner=
ReportCodePageConversionErrors=0
SecurityMechanism=0
TcpPort=4456
UseCurrentSchema=1
WithHold=1
2) Create relational database connection of type odbc using the connection string created in step 1.
