MySQL data transfer

[Pete]

I am having a discussion with our web guy here at work, and he is telling me how great PHP and MySQL are, and how we should start using it for production work. I did a quick Google search, and I see that IBM has recently added support for PHP. I also see that MySQL can run in a Linux partition on the 400, or within the PASE environment. If I load MySQL into PASE on my box, how would I transfer my data from DB2 to MySQL? Is there some magic command to run? Can I connect to MySQL through an SQL CONNECT? Thanks for any assistance.

Pete

[jamief]

Re: MySQL data transfer

When you load linux on its own partion then you are provided by IBM a
ODBC driver to use from LINUX directly hitting the db2 database.

I would not recommend you coping data from production tables and inserting them in
a MYSQL table....


let me know if I can be of further use...
Huge improvements have been made in PHP / LINUX in V5R4

also check these guys out

http://www.i5php.net/

jamie

[arrow483]

Re: MySQL data transfer

I've used MySQL before, and while I found it adequate for a small database, I wouldn't recommend it for production.

DB2/UDB can do everything MySQL can, and more. So why would you want to use it on the iSeries ? A small PC appkication is its best use, IMHO.

I haven't used PHP, but I've heard good things about it.

[pjk]

Re: MySQL data transfer

If you're planning to stick with the AS/400 as a server (I'm assuming you are), then you might as well stick with DB2 and take advantage of all the AS/400's architecture suited to DB2.

In PHP, this is a basic example of how to use it with ODBC:

make a database connection:
$dbConnect1 = odbc_connect("databaseID", "userID", "password");

execute an SQL request (or can be an UPDATE/DELETE/INSERT):
$dbRcdSet1 = odbc_exec($dbConnect1, "select * from libName.fileName");

do a fetch of the records returned:
odbc_fetch_into($dbRcdSet1, $RcdSet1);

$RcdSet1 is an array from which you can get each fields actual values, for each record you fetch--just put the fetch_into into a while loop.

And once you get familiar with that, you'll want to put checks and IFs on these to ensure they executed properly, so you can check for SQL errors, including on inserts when there might be dup records. But this is just to get started.

[EDIT] fyi: We are running SUSE on the Linux partition

[Pete]

Re: MySQL data transfer

The intent is too replicate a small subset of data from the production 400 to our webserver which sits in a DMZ. The great unwashed would submit queries to the websever, which would handle the PHP/MySQL stuff. I was looking to get it going on the 400 first to test the data transmission between DB2 and MySQL, but the web guy is pretty sharp, he's already got a little mockup going with the PHP/MySQL stuff. I've just got to figure out how to get my stuff out of DB2 and into MySQL by using AS400 tools, because those higher up the food chain want this done on a shoestring. I am looking at creating an entry using WRKRDBDIRE pointing to MySQL, and connecting via SQL, but that doesn't seem to working.
Any ideas?

[littlepd]

Re: MySQL data transfer

That better be a pretty hefty shoe...PHP on the iSeries is expensive.

[Pete]

Re: MySQL data transfer

?????

According to this:

http://publib-b.boulder.ibm.com/Redbooks.nsf/RedpieceAbstracts/redp3639.html

and this:

http://www.i5php.net/index.php?name=Downloads&c=2

it's free for the PASE versions. The PC versions of PHP and MySQL that the web guy is using are also free. That's why we're using it....

[arrow483]

Re: MySQL data transfer

If the database does not need to be dynamic (for example, daily refresh), you could write create a file on the iSeries, FTP it to the PC server, and then load it to MySQL.

If it needs to be dynamic, have the PC pass an SQL select thru ODBC (or other method) to get the data.

[Pete]

Re: MySQL data transfer

According to IBM, they have a number of ARD (Application Requester Driver) programs that can be plugged into the ADDRDBIRE command to connect to non-IBM databases, but MySQL is not one of the DBs supported. It was suggested that I download the JDBC driver from MySQL and write my own ARD program. I know I don't have that kind of programming knowledge, so if someone is looking for a business opportunity........
I ended up running some SQL and writing the file directly to the web server with CPYTOIMPF. I run it a couple of times a day through the job scheduler, and the web guy runs a script to update his table. Not real technical, but it works.

Pete