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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jon SnydermanCommented:
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.

Gary PattersonVP 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Theo KouwenhovenApplication 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 :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.