Accessing MS SQL Server DB from iSeries

0 pts.
Tags:
AS/400
i5
iSeries
OS/400
SQL Server 2000
I am trying to find a way to execute a SQL statement on the IBM iSeries and access info in a MS SQL Server DB that is on a different server. Can someone point me in the right direction to find any info on this?? Thanks, Dave
ASKED: December 14, 2004  4:16 PM
UPDATED: December 20, 2004  9:38 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Dave, you can’t do this without a 3rd party package. Feel free to e-mail me offline for info on how to do it with a 3rd party package.

John Brandt
iStudio400.com

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • EXPERTJohnBrandt
    By request in private messages, I will respond in public at the risk of the forum police. There are 3 ways to accomplish this feat. One 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 last time I checked, it wasn't cheap. The second is to use a sockets based custom written or package that requires software on the remote SQL server as well as the iSeries system. These generally are not that expensive, depending on the vendor and the number of servers you are going to install, but none of them post pricing, so I don't know for sure. The last is a product my company developed that only requires installation on the iSeries system and can access as few/many SQL servers as you want to access and is charged based on the iSeries platform. If you'd like more information, visit http://www.iStudio400.com Or e-mail me directly at pgmr@experts.tzo.com John Brandt iStudio400.com
    10 pointsBadges:
    report
  • Sonotsky
    Disclaimer: I know very little about iSeries-slash-AS/400 boxen. 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 MSSQL. Versions up to 7.0 should work just fine with any Sybase driver, but SQL2k changes something in data exchange (Google for it). SQL2k5 will likely change even more. Depending on your budget, you can also do something like install DB2 on the iSeries, then have either DB2 or SQL Server replicate data, then have your iSeries app query the local database. Likely get better performance that way.
    695 pointsBadges:
    report
  • EXPERTJohnBrandt
    While I understand that you "know very little about iSeries-slash-AS/400 boxen", the first thing you need to understand is that there are no ODBC drivers that run natviely on OS/400 or i5/OS. The AS/400-iSeries and i5 machines run a proprietary, big endian, EBCDIC system called OS/400 or i5/OS respectivly. The only form of software with ODBC connectivity to any SQL server other than DB2 is as I described above. The Sybase ODBC drivers you elude to only support ODBC 3 and TDS 4.2 (Tabular Data Structure) and do not run on OS/400. As you said: "The caveat of doing it this way, is the version of MSSQL. Versions up to 7.0 should work just fine with any Sybase driver, but SQL2k changes something in data exchange (Google for it). SQL2k5 will likely change even more." SQL2K supports TDS version 7.0 and SQL2k3 and above support TDS version 8.0. Then there is the CCSID translations and the EBCDIC to ASCII translations. DB2 IS the OS/400-i5/OS database and it comes with the machine. If you have a DB2 server running on Linux or WinTel, you can create a connection, but in order to replicate, you must purchase IBM's iSeries product to access the SQL server directly on the iSeries machine THROUGH the DB2 machine.
    10 pointsBadges:
    report
  • JoePluta99
    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 will connect to MS SQL. This is probably the best route. Second, there is an open source project on SourceForge (http://sourceforge.net/projects/os400bridge/). With this softwaer, a small daemon runs on a Windows machine on your network and accepts requests from the AS/400 box, then sends them to any other ODBC-enabled server in the network. Joe
    0 pointsBadges:
    report
  • MikeWiz
    Actually, there is one more option that you could try. That is to use MS SQL Data Transformation Services (DTS). This sounds like a mouthful, but it actually is a pretty easy method to run SQL on a SQL Server from a command initiated by an OS/400. It won't give you record level access, per se, but you can run a SQL script and return the results to your iSeries system. It is also capable of much more technical tasks, but it fits my needs pretty nicely.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following