V7R1M0 updating remote MS SQL database via IBM i

I've been tasked with updating remote MS SQL database tables. I'd like best ideas for utilizing our IBM i to do this. I'm primarily and ILE-RPG, RPG Freeform programmer, but have worked using RPG-XML (which we have) and consuming  web services via a local MS SQL database. I'm also familiar with SQLLERPG to a degree.    

I have access to 'data source', 'Initial Catalog', 'User ID', and 'password' for the remote database tables.

All ideas are welcome and will rewards points accordingly.

Thanks,

Phil Knox
Phillip KnoxSenior Systems AnalystAsked:
Who is Participating?
 
Gary PattersonVP Technology / Senior Consultant Commented:
Yes, DRDA is one way to perform this connectivity.  You need some specific Microsoft products (Service for DRDA, and Host Integration Server), or other tools - and lets just say the setup and integration process is complex.  Enough so that unless you already have all this up and running in your environment, and someone who is familiar enough with it implement changes, I wouldn't recommend it - especially for a flow as simple as the one you describe.

For real-time, transactional flows, we generally recommend developing a Windows web service, and then writing a program on the IBM i side that talks to the web service (fired by a an IBM i DB2 trigger, or from a journal scraping program).  Windows developers generally know how to publish a simple web service, and while IBM i developers sometimes don't the learning curve is short, and there are plenty of tools to help.  One of my favorites for consuming a remote web service from IBM i is using the free IBM i DB2 REST tools.

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

For "batch" bulk loads, however,  there are simpler mechanisms that don't require special skills.  The decision usually revolves around who is going to "control" the operation:

1) Windows controls entire process.
2) IBM i controls entire process
3) "sender" exports, "receiver" imports (flat file exchange).

Option 1: Windows controls transfer.  Numerous ways to handle this but a common one is to simply configure a "Linked Server" in SQL Server that points to the IBM i box (usually only do this when both are under the same ownership and admin control).  Then you'd create an SSIS job that queried the linked IBM i DB2 database for the desired changes, and updates/inserts into the appropriate target tables.  Easy to configure, no special software required on either system, and no special skills required -(just SSIS, which is a common skill for most SQL Server administrators and developers).  You just need to load IBM i DB2 .NET/OLEDB/ODBC drivers onto the SQL Server box.  Those drivers are available in IBM i Access for Windows, or as an accessory to the newer IBM i Access Client Solutions.

Option 2: IBM i controls transfer.    Again, plenty of ways to do this, but here is one that I've seen more than once.  SQL Server has a nice built-in function to connect to a non-SQL Server database, as long as there is an ODBC, OLEdb, or ADO.NET compliant database driver for the target database (IBM i DB2 has all three available).  Unfortunately, IBM isn't quite so considerate, and no native IBM i DB2 facility exists to connect to SQL Server.  Now, there are open source and third party tools, like Scott Klements JDBC4R tools, but that's a topic for another day.  For plain vanilla native solutions, you can use CPYTOIMPF to export selected IBM i DB2 data to a flat file (CSV is nice), and then remotely execute the SQL Server sqlcmd or bcp tools using IBM i RUNRMTCMD, FTP RCMD, or SSH.  Again, we typically only do this when both servers are under common ownership and control.

Option 3: Export/import.  Most appropriate when servers are not under common ownership and control.  IBM i exports data (generally using CPYTOIMPF) and drops flat file to agreed-upon location via ftp or sFTP.  SQL Server runs a scheduled SSIS or bcp to import data from flat fie.

I will note that for the specific model that you mention, where there is a need to "publish" information to a number of possible subscribers, I prefer to use messaging middleware:  WebSphere MQ in particular.  WMQ allows on system to "publish" information (generally in the form of an XML document) to a message queue.  Other systems (one, or many) can "subscribe" to a particular queue and get these messages in near-real time.  Lots of benefits to using messaging middleware, and it also fits into Enterprise Service Bus environments very nicely - something common in large companies.  Decouples sending and receiving systems from ever needing to talk directly to each other.  Queue strategy means that messages can be audited, logged, tracked, or consumed by new applications without any changes required to the originating applications.  Excellent security, since there is no direct connection between publisher and consumer, and access to queues and messages can be secured and encrypted.

Hardest part about this question is that there are so many different permutations - each with different costs, complexities, technology dependencies, skill dependencies, security implications, etc., that it is difficult to provide good advice at a distance.

Post back if you'd like more, including links to relevant articles and threads covering technologies discussed above.

- Gary
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Transactional updates or bulk loads?
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
The client is calling for updates to the database every 30 minutes.  We're a transportation company with equipment (Units) and loads (available by location) that will be passed to the database along with sister companies to corporate. This will provide access company wide (sister companies) to equipment and loads that may be available in their default areas in the US and Canada. I'm not certain about transactional or bulk loads but will find out.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Phillip KnoxSenior Systems AnalystAuthor Commented:
I've also inquired if the remote SQL database supports DRDA.
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
I'm being told 'bulk loading'. Apparently just one instruction which will load/delete data based on the select statement?
0
 
MurpheyApplication ConsultantCommented:
Hi Phil,

"The client is calling for updates to the database every 30 minutes"
Could this call, a  call to a Webservice?
that supplies XML data?
0
 
Mark MurphyIT ConsultantCommented:
There are multiple ways to do this. Probably the easiest is to extract the data into a temporary table, and then convert it to CSV using CPYTOIMPF. Then you can send the data to your client via FTP or sftp. Let them load it into their database. Scott Klement created an FTPAPI that lets you FTP from your RPGLE program.

If your client has exposed a web service to receive the data, you can use Scott Klement's HTTPAPI to send the data.

Scot's website is http://scottklement.com/oss.html

If you want to directly load the data into your client's database, you can use Java and JDBC, but that may take some learning.
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
Theo, I'm told it's not a web service.

Mark, I'm checking into Scott Klement's JDBC driver setup and code, but it looks pretty old.
It's probably been updated but not sure where to get updated material.

Some research has spoken about 'DRDA'. Is this necessary to update the remote MS SQL database anyone?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
This expert suggested creating a Gigs project.
Also a good item to consider posting on Gigs, if that is an option for you.
0
 
Phillip KnoxSenior Systems AnalystAuthor Commented:
Gary,
Option 2 sounds like it may do what I need. I'm simply sending information to the SQL database, but I'm not sure how the
Database will want to receive it. I'm checking now.
Can you provide any samples of the process for Option 2?

Thanks,

Phil Knox
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Hi Phil,

Don't have something canned I can share, but it is pretty easy to do:

If you don't already have an ssh server installed on your Windows server, install one:

https://winscp.net/eng/docs/guide_windows_openssh_server

Also make sure ssh (client) is configured on IBM i, and configure for public key authentication:

http://www-01.ibm.com/support/docview.wss?uid=nas8N1012710
http://ibmsystemsmag.com/ibmi/administrator/systemsmanagement/sftp-tips/

Optionally, CREATE VIEW or CREATE TABLE AS on IBM i DB2 that matches format of the target table in SQL server.  

Then create a program (I'd use CL) that:

Optionally - RUNSQL command to extract data if needed to extract table

CPYTOIMPF to create CSV from view or extract table: http://www-01.ibm.com/support/docview.wss?uid=nas8N1017894

Transfer file using sftp: http://ibmsystemsmag.com/ibmi/administrator/systemsmanagement/sftp-tips/

Alternately, if this is just a single file transfer, it may be easier to use scp instead of sftp:  
QSH CMD('scp foobar.txt your_username@remotehost.com:/some/remote/directory')

Execute SQL Server bcp command using ssh:  
QSH CMD('ssh windowsuser@windowsmachine.com 'bcp ...')

You'll probably need some help with the BCP command if you aren't familiar with it.  Syntax varies depending on format of input file, format of target table, SQL Server security, SQL server instance, etc.
0
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.