V7R1M0 updating remote MS SQL database via IBM i

Transactional updates or bulk loads?

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.

I've also inquired if the remote SQL database supports DRDA.

I'm being told 'bulk loading'. Apparently just one instruction which will load/delete data based on the select statement?

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?

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.

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?

Also a good item to consider posting on Gigs, if that is an option for you.

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

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.