An ITKnowledge Exchange (ITKE) member was recently looking for some information on the best way to execute a SQL...
statement on the iSeries, and also access information in a MS SQL Server. Thankfully, fellow techies jumped in on the conversation and offered some advice. Here is a portion of the conversation. Read the rest of the thread here.
I am trying to find a way to execute a SQL statement on the iSeries and access information in a MS SQL Server database that is on a different server. Can someone point me in the right direction to find any information on this?
" EXPERTJohnBrandt" responded:
You can't do this without a third-party package. There are three ways to accomplish this feat.
One way is to use an IBM product that is licensed by the iSeries CPU -- plus an additional Windows NT/2K/XP server. I'm not sure how it's priced, but the last time I checked, it wasn't cheap.
The second is to use a sockets-based custom written package that requires software on the remote SQL server, as well as the iSeries system. These generally are not that expensive, but depending on the vendor and the number of servers you are going to install. Unfortunately, none of the vendors post pricing, so I don't know for sure.
The last is a product my company developed that requires installation on the iSeries system only and can access as few/many SQL servers as you want to access and is charged based on the iSeries platform.
You do need some kind of intermediary software on the iSeries in order to connect to SQL Server, but you do not necessarily need additional software on the SQL box.
What you really need is some form of ODBC with a SQL Server-compatible driver (Sybase might just do it) on the iSeries. Once you have that, you can connect to the SQL box using TCP/IP, and possibly named pipes, but IP is easier to program for through the ODBC driver, pass SQL statements and receive returned data.
The caveat of doing it this way, is the version of MS SQL. Versions up to 7.0 should work just fine with any Sybase driver, but SQL2k changes something in data exchange. SQL2k5 will likely change even more.
Depending on your budget, you can also do something such as install DB2 on the iSeries, then have either DB2 or SQL Server replicate data, then have your iSeries application query the local database. You'll likely get better performance that way.
The other option is Java. Java provides a number of options to talk to an MSSQL server. First, MS provides a pure Java (Type 4) driver that connects to MS SQL. This is probably the best route. Second, there is an open-source project on SourceForge. With this software, a small daemon runs on a Windows machine on your network and accepts requests from the iSeries box, then sends them to any other ODBC-enabled server in the network.
Not yet using ITKnowledge Exchange, our innovative peer networking feature for iSeries and other IT professionals? Register and fill out your profile so you can ask specific sets of people your IT questions and also help out your fellow iSeries users.