Re: DB2 to MySQL

On 8/26/2024 12:37 PM, DEnglander--- via RPG400-L wrote:

Does anyone have any references as to how to set up an RPG program that
can send DB2 data to a MySQL database on a remote server?

I seem to be able to connect to the remote server in a test RPG program
and run a small set of simple commands [get a list of files in its DB],
but an not sure how to use the LOAD DATA LOCAL INFILE MySQL command to
load the DB2 rows. This test RPG program connection uses the JDBCR4
service program from Scott Klement.

There is an existing, buggy Java program the normally does this, but it is
having problems lately and I was hoping I could rewrite the daily job in
RPG. The Java program appears to get a list of column names for the file
to send and concatenate them together somehow into a predetermined format.
It then uses a Java command as MYSQL_STMT.setLocalInfileInputStream that
sets the input stream to something set via IOUtils.toInputStream(), which
appears to take the list of column names as input.

I have extremely limited Java experience.

I find the MySQL docs invaluable: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

LOAD DATA is a bulk uploader; it takes a text file and imports it into the MySQL table. This text file is frequently a csv file. If the Java program is constructing a column list, it's because that csv file doesn't contain all of the same columns in the same order as the MySQL table.

Where does that text file come from? I ask because if it originates on the IBM server, gets converted to a csv, then bulk imported into MySQL, you can cut out the middleman, and directly read that Db2 table and INSERT INTO the MySQL table - skip the whole bulk upload thing.

Any resources anyone has to help would be great. Can this be done in
embedded SQL somehow, or is JDBC my only option? The original Java program
is from 2015, and I assume there have been many improvements to the system
since then that may allow a better way to do this.

If JDBCR4 is working, you might as well keep on with that. A possible alternative is Ardgate https://sourceforge.net/projects/appserver4rpg/ This will allow regular embedded SQL exactly as though you were doing an INSERT INTO a Db2 table.

The good news is that you can install MySQL on your own work PC and tinker with that.

Although this is the RPG list, another approach is Python. You'll need to install the MySQL Connector for that to be seamless. And finally, I'd be remiss in omitting the thought that the least amount of work might be to find what the issue is with the Java program. There's a quiet Java list here on Midrange... If there are other Java programs at your place, it might be good for someone to be able to do routine maintenance on them.