1

My requirement is to import DB2 400/AS400 tables to informatica powercenter. As i'm new to AS4oo db2 connectivity, i came to know that we can establish ODBC connection using "iSeries access ODBC driver".I tried my best in IBM website to download this driver and confused a lot.any help is appreciated here. How to download and install the above mentioned "iSeries access odbc driver". or any other way?

mike
  • 1,233
  • 1
  • 15
  • 36
AyrusTerminal
  • 13
  • 10
  • The ODBC driver is not available separately. It's part of the older [IBM i Access for Windows](https://www-03.ibm.com/systems/power/software/i/access/windows.html) product or the new [IBM i Access Client Solution](https://www-03.ibm.com/systems/power/software/i/access/solutions.html) Talk to your system admin to get the software. – Charles Jun 03 '16 at 12:48
  • @Charles Thank you for the reply. but, is this the correct ODBC Driver which is used to import tables from AS400 DB2 to Informatica Powercenter? – AyrusTerminal Jun 03 '16 at 13:08
  • It should work per Koushik's answer below. – Charles Jun 03 '16 at 13:24
  • "IBM Data Server Client" does this be any help in this aspect? – AyrusTerminal Jun 03 '16 at 13:26
  • No, that's for DB2 LUW. DB2 for i and DB2 for z/OS are different. – Charles Jun 03 '16 at 16:43
  • Yes it will work. Actually infa uses data direct for data movement and import definitions. – Koushik Jun 06 '16 at 05:49

1 Answers1

0

Informatica always provide Datadirect driver for DB2. Now, sometime DB2 version may not be compatible with DD diver. In that case you can ask Informatica corp. for older version or workaround. If you want to import DB2 tables, you have to follow these steps -

  • Create a DSN for DB2 database. Go to Start > Administrative Tools > Data Sources(ODBC) > System DSN > Add.
  • When Dialog box shows up, choose DataDirect xx.xx DB2 Wire Protocol. Where xx.xx is driver version and depend on infa vesion. Give a proper DSN name, DB host, port, DB Name Security selection may cause issue so try different options
  • Open informatica designer. GO to source > Import from database. Then select the DSN and provide user/password to login. Pretty standard process to import sources.

enter image description here

AyrusTerminal
  • 13
  • 10
Koushik
  • 61
  • 2
  • Is Datadirect a costing software? and i think the above process is for normal db2 relying on LUW. but my current souce is in iSeries AS400 machine (i5/OS) and informatica server on Windows 2012 R2. – AyrusTerminal Jun 03 '16 at 12:48
  • Will it work for AS400 DB2 connectivity with Powercenter? – AyrusTerminal Jun 03 '16 at 13:10
  • https://network.informatica.com/message/21010#21010 In this link, i found that we can use: 1.Datadirect 2.iSeries access ODBC driver 3.PowerExchange(best Option with risks). Many thanks @Koushik. – AyrusTerminal Jun 06 '16 at 05:25
  • Glad you found it. But to answer your questions, yes Datadirect is not free. but it comes with all informatica versions automatically with no extra cost. I have been using data direct for long so i can vouch for this. Only issue is with Unicode data movement and i dont want to go into details if you are not interested. – Koushik Jun 06 '16 at 05:46
  • I'm intrested, as i'm new to iSeries connectivity and Datadirect. I will be much obliged if you could share me the download, Installation and use steps or any doc resource for Datadirect. i googled, but no luck yet. – AyrusTerminal Jun 06 '16 at 09:43
  • Well, as i mentioned it comes with informatica package and get installed in client or server automatically. Nothing to do in terms of installation. To import using designer, add a DSN in ODBC manager. To extract/load data, follow below steps in server, 1. setup the DSN in odbc.ini file with DB2 info like host/port/DB name etc. 2. set $ODBCINI variable. 3 add $ODBCINI/lib to library path 4. Use above DSN in informatica connection parameter under ODBC connections. Only flaw i saw(may be i couldnt find fix for this) is it can not process special characters and unicode chars like spanish etc. – Koushik Jun 07 '16 at 06:12
  • Many Thanks @koushik, Really needful info!! where can i find this odbc.ini file in informatica installation. – AyrusTerminal Jun 13 '16 at 07:27
  • You are welcome. What is your OS version? Unix/Linux/Windows? – Koushik Jun 13 '16 at 07:44
  • OS version is Windows Server 2012 R2. – AyrusTerminal Jun 13 '16 at 15:13
  • if you already installed informatica at c:\app\Infoamtica9.5 then this odbc.ini should be in c:\app\Infoamtica9.5/ODBC7.0. Basically inside INFA_HOME location. Depending on version odbc folder name may change. If you haven't installed infa, then you need to install the infa client. – Koushik Jun 14 '16 at 05:45
  • Thanks Koushik, Can i do this through TDM, for mask, subset and generation? because i just saw an option called "DB2 for i5/OS" under Administrator tab -> connections in TDManager? – AyrusTerminal Jun 14 '16 at 10:16
  • I saw your other question on TDM. See, i think TDM/Powercenter, both should use some kind of ODBC connector to DB2. I think you need to create a DSN before using that. Make sure you create DSN in TDM server and client side. – Koushik Jun 17 '16 at 07:37
  • have you tried masking on AS400 DB2 with Data Direct drivers? – AyrusTerminal Jun 22 '16 at 13:05
  • nope, sorry. I used Data Direct to extract data. Only issue i faced is for some special and binary characters. – Koushik Jun 23 '16 at 06:47