Update MS SQL database using IBM i v7r1 RPGLE if possible

RPGLE programmer using IBMi v7r1, need to insert/update data on a MS SQL database easily. I'm familiar reading and processing ifs file using bnddir('QC2LE') in my RPGLE programs.
Surely there's a way to push records to an MS SQL server?
Phillip KnoxSenior Systems AnalystAsked:
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.

MurpheyApplication ConsultantCommented:
You can use ODBC within your programs, I don't  have any example right now
0
Gary PattersonVP Technology / Senior Consultant Commented:
This expert suggested creating a Gigs project.
I've faced this challenge numerous times, and also worked with dozens of clients who dealt with it in different ways.  Here is the short summary:

Access IBM i DB2 from MS SQL Server:  Easy.  Microsoft supports it "out of the box" via Linked Servers.

Access MS SQL Server from IBM i DB2:  Not so easy.  IBM does not provide similar "out of the box" support, so you have to either get clever, write some code on both sides, or find tools.  No "one size fits all" - best approach depends on a number of factors:

1) Volume
2) Single application solution, or enterprise strategy for complex cross-platform data integration needs, or somewhere in between?
3) Available Windows and IBM i technical and programming skills
4) Support resources - troubleshooting cross-platform problems gets tricky.  Limited support capabilities = strong case for a package solution.
5) Budget

ODBC

Let's talk about ODBC, since Murphey mentioned it.  ODBC connections from MS to IBM i work fine.  Probably not the best connector to use from .NET, since there is a native .NET IBM i DB2 connector available from IBM that should perform better in .NET applications, but IBM does provides an up to date Windows ODBC driver.  But again, that doesn't help you when you're working on the IBM i side.

And going the other way, Microsoft doesn't provide an ODBC driver that runs on IBM i.

On IBM i, we have the SQL CLI Interface.  It is a set of APIs introduced in V3R6 that conform to an older ODBC standard.  It is an interface to IBM i DB2.  I've never seen an example of it used to connect to MS SQL Server - at least not that I can recall.  If that is possible, I'd definitely like to see how it is done.
 
https://www-03.ibm.com/systems/power/software/i/db2/support/tips/clifaq.html

Java / JDBC

This is a task that is probably best suited to Java on IBM i, due to the availability of type 4 JDBC drivers (pure Java) for MS SQL Server:

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server
http://jtds.sourceforge.net/

But, of course, that doesn't help you if IBM i Java isn't an option.

RPG DRDA

From RPG, DRDA is by far the easiest mechanism to integrate from the programmer's perspective - you just use embedded SQL in your RPG code, using the CONNECT statement to connect to a remote DB that supports DRDA.  DB2 databases on Microsoft, AIX, Linux, and Z/OS all support DRDA.  But for connections to SQL Server, DRDA requires some middleware investment.  I am not aware of any production-quality open source DRDA middleware alternatives.  See the following article for IBM and Microsoft options for DRDA middleware.  Note the article is out of date - you may want to talk to your IBM rep about InfoSphere Federation Server and if it is still supported for IBM i - I haven't seen it for a while.

Microsoft Service for DRDA
IBM InfoSphere Federation Server
http://www-01.ibm.com/support/docview.wss?uid=nas8N1018120

RPG / JDBC

To use JDBC from RPG, you'll need a type 4 JDBC driver for the target DBMS (links above for SQL Server), and I recommend that you take a look at Scott Klements JDBC presentations and open source middleware.  While this is not quite production-quality code (I've dealt with a few issues in shops that just treated it as production code and implemented it without thorough testing), it provides a very good starting point.  Be aware that this approach is going to fire up a JVM in each job you use it in.  This means that you'll want to NOT use it in interactive programs, or batch programs that are frequently submitted.  Best suited to a server style job, where you start one instance and feed it via a data queue, message queue, or some similar means.  Suggest you read through his resources in full, and do a proof of concept before making a decision on driver and approach:

https://www.scottklement.com/jdbc/

Some other techniques

MS SQL Server SSIS
 
  • Create SSIS jobs that run on a scheduled basis to query IBM i DB2 tables for updates.
  • Modify IBM i applications to stage changes in dedicated staging tables, and run an SSIS job to pull changes from stating tables and apply them to MS SQL Server.

Export/Import
  • Export to CSV in IFS or QNTC via CPYTOIMPF, schedule bulk import in SQL Server (or SSIS).
  • Export to CSV in IFS of QNTC via CPYTOIMPF, trigger bulk import via remote command (BCP, for example) to SQL Server.  Remote command to Windows opens up a whole additional thread of conversation, though.  My short answer is to install an ssh server on Windows and use QShell/PASE ssh support to execute commands from IBM i to Windows.

Data queue / MQ
Requires a little Microsoft-side code, but a fairly simple and elegant solution is to write a Windows program (.NET, Java, probably even PowerShell) that watches a queue for SQL statements sent from IBM i, and executes them on SQL Server.  Implement on WebSphere MQ, JMS, or even native IBM i data queues (IBM i Access for Windows Programmer's toolkit provides an API to access an IBM i data queue from Windows programs).

Web Service
Have your friendly neighborhood Windows programmer whip up a simple REST web service to perform the SQL Server updates for you.  Use the free IBM i DB2 REST functions to access the web services from SQL in your RPG programs:

https://www.ibm.com/developerworks/data/library/techarticle/dm-1105httprestdb2/index.html

Third party tools

Several third party tools provide support for IBM i DB2 to SQL Server replication (generally journal scraping), if that's what you need.  This is a pretty easy way to sync tables over to SQL Server, then do whatever you need there to integrate the data.  A few examples:

https://www.attunity.com/products/replicate/platform-support/
https://www.starquest.com/products/sqdr/technical-data-sheet/
http://www.boaweb.com/products/data-replication/

Post back if you need more.
1

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
Phillip KnoxSenior Systems AnalystAuthor Commented:
Thanks Gary, will try this out
0
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
SQL

From novice to tech pro — start learning today.

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.