October 14, 2010 at 7:45 am
I've got a crazy situation where we have been timesharing on an as400 box. Now we want to move the database which is some flavor of DB2 from the as400 to sql server 2005. I've looked high and low for an as400 odbc client and haven't been able to come up with one. Does anybody have and idea where I could get this client from? The regular odbc for db2 won't work.
Thanks in advance.
October 14, 2010 at 2:02 pm
I believe what you are looking for is DB2OLEDB Provider - please review this link for a download. This download is only good for Enterprise and Developer Editions
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 17, 2010 at 6:44 am
Thanks a million. I'll give it a try!
October 20, 2010 at 7:43 am
I tried this on an enterprise edition and it keeps telling me that I must be on an enterprise or developer addition. Kinda strange. Thanks for your help though!
October 21, 2010 at 10:03 am
You need to install the AS400 iSeries client on the host machine that needs the ODBC driver. You can obtain the iSeries client from your AS400 system admin or from IBM. This assumes your AS400 has all the proper licensing.
October 21, 2010 at 10:51 am
I also had trouble with AS400/SQL Server connectivity in the late 90's. I've never been able to get the DB2OLEDB driver to work, but this configuration has worked for over a decade now -- even through all the SQL and OS updates.
1.) Install "IBM AS400 Client Access Express for Windows" on the SQL box. The install includes "IBM Operations Navigator" -- follow the tutorial to setup a connection to your AS400. It also adds the "Client Access ODBC Driver (32-bit)" which you'll use in a moment. Your AS400 team should have the install disc.
2.) On the SQL box, create an ODBC connection to the AS400 using the Client Access driver. The less obvious option settings to use are:
Default Libraries = The AS400 libraries(s) you want to access.
Commit Mode = Commit immediate (*NONE)
Maximum field data returned = 32
Search Pattern = Checked
Enable extended dynamic (package) support = checked
Default package library = QGPL
Enable lazy close support = unchecked
Enable pre-fetch during execute = unchecked
Enable data compression = checked
Record Blocking Type = Block except if FOR UPDATE OF specified
Record Blocking Size = 128
OS/400 library view = Default library list
Sort Type = Sort based on HEX values
Allow Unsupported Character = unchecked
Connection Type = Read/Write (all SQL statements allowed)
Object description type = OS/400 object description
Scrollable cursor = Always scrollable
Translation = Translate CCSID 65535
3.) Create a Linked Server where: (I like to name the Linked Server the same as the ODBC connection.)
Provider = Microsoft OLE DB Provider for ODBC Drivers
Data Source = your ODBC connection name
Provider String = MSDASQL1
Collation Compatible = True
Data Access = True
Rpc = False
Rpc Out = False
Use Remote Collation = True
Connection Timeout = 0
Query Timeout = 1600
Other details that may help:
SQL Version = 9.0.4273 (32-bit)
Client Access Version = Version 4 Release 4 Modification Level 0
OS400 Version = Version 5 Release 4 Modification Level 0
IBM documentation will understandably say the OS400 and Client Access versions must match. My versions do not match and it has not been an issue. (But having said that now, the next OS400 update will surely prove me wrong.)
I have many servers -- on very rare occasions one box seems to get stuck connecting to the AS400. The connection just spins and spins with no results and no error. Killing it has no effect. Only a server re-boot resolves it. Again though, very very rare.
Lastly, I'm sure there is a better way and I'd love to hear it. But sometimes whatever works is good enough. Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply