Need to be able to access an AS400/iSeries/IBM System i database file from a website using html

Hi Experts.
I have a customer that wants to be able to access an AS400/iSeries database file from their website.   The Website is hosted offsite and the AS400/iSeries is onsite.  I have googled quite a bit and having trouble finding a lot on how to do this.  I have done iSeries stuff for years, but this out of the ordinary for this.  Any help appreciated.
Kevin CaldwellOwner of RUseeingRed Tech SolutionsAsked:
Who is Participating?
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
This expert suggested creating a Gigs project.
We've done it many times.  There are many different solutions.  Best solution for a specific shop depends on security requirements, nature of data required, data volumes, lookup volumes, performance considerations, build-vs-buy inclination, technical skill set, and more.

Security is generally a key concern.  Never a good practice to expose "general purpose" interfaces (ODBC/JDBC/DRDA) on a private network server (IBM i) to a server exposed directly to the web.  If the web server is compromised, or if a defect is discovered in the application, it can be used to attack the private network, and the IBM i.  Since in these environments the IBM i is usually hosting the core business applications and database, this system has to be carefully protected.

So safest approach, when practical is to replicate (push) the needed data from the IBM i to the web server - works best if it is a relatively small subset of data.  Replication can be timed (refresh daily, for example) for static data like an item catalog that only changes occasionally, or real-time for dynamic data, like current inventory.  Pushing data from IBM i to web server prevents the need to allow inbound connections from web server to IBM i on private network - which is almost always the case.  Third party tools can be used to handle replication task, or code can be developed.  Generally timed interfaces use a batch job to export data to a file (CPYTOIMPF), push it to web server (sFTP), and a web server job ingests data into a local database (MySQL, SQL Server, etc).  Real-time interfaces typically either utilize IBM i DB2 database triggers or scrape database journals to capture changes (journal scraping is my favorite,and generally is the highest-performance mechanism).  Captured changes are then pushed to the web server and applied to a local database on that machine.  There are third-party tools that do this, and it is also relatively easy to code these interfaces.  Java is well-suited, since an IBM i Java program can be written to directly update any remote database with a Tyoe 4 JDBC driver (like SQL Server).  Native programs (RPG/COBOL) can also do this - it just takes more work, or a third party JDBC interface for RPG/COBOL).  Messaging middleware (WebSphere MQ, for example) is also popular, since it provides a fast, easy way to push data from IBM i to a web server, where a simple MQ monitor program receives entries and applies them to the remote database.  Also common to use an IBM i program to invoke a remote web service on the web server to apply the changes.

When replication isn't practical (usually due to data volume, bandwidth limitations, or the need to call an IBM i program to extract and format data on a "per transaction" basis), it is common to develop an IBM i DB2 stored procedure or web service, and to make that stored procedure or web service available (in a secure fashion) to the web server.  IBM i DB2 stored procedures can be written in SQL, Java, RPG, or COBOL.  IBM i, starting in V5R4, offers a free tool (Integrated Web Services for IBM i) that makes it easy to develop and publish SOAP or REST web services using ILE RPG, COBOL, or C.

We can provide more specific suggestions if you can provide more information about the specific application.

Sometimes this approach is not practical - for example, when it is necessary to run an IBM i program to produce desired results, or when data volumes involved are too large to replicate.   ILE RPG/COBOL program.  We NEVER recommend exposing a full-blown database connection from any public server
Jon SnydermanConnect With a Mentor Commented:
We have done it a few times.   In two scenarios, we used an specialized hosting company and created a VPN between the sites.    In other scenarios, we just sent the info back and fourth by SFTP.   But that was not truly realtime or live.   But it did work fine.    I dont know any way to secure ODBC, especially on the 400.  That would be the magic pill.

Kevin CaldwellOwner of RUseeingRed Tech SolutionsAuthor Commented:
Thanks so much for the info guys.   I definitely sounds like I am better off sending data out from the iSeries vs letting a connection in.  And that makes it a lot easier to.
MurpheyApplication ConsultantCommented:
Hi Kevin,

Depends what he like to do, Its very simple to define a Web-service on your As/400 that will return a result (set)
If you write RPG, the webservice is just 5 minutes away from your final solution.

I prefer SOAPUI (with XML) but REst service with XML or Json is also possible,

Let me know if you need more info.
For a Webservice, you don't heve to "open" your system for the rest of the world :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.