I'm preparing for an upcoming integration project where I need to import/export some data to/from our ERP into the File System. We run JD Edwards World 9.2 on an IBM iSeries/AS400 V6R1 machine.
The software we are integrating with, requires that we integrate using CSV files that are exported/imported on a schedule and accessible via SFTP.
I have zend server (apache) installed and am happy using Apache, PHP and SQL but have virtually zero knowledge of CL and RPG programming (but willing to learn). We have no WebQuery either. The files exported require data between tables to be joined and filtered, so will need SQL or similar, so not a straight CPYTOIMPF i expect.
As a very rough outline I was thinking I could write a quick php file that would run an sql query to parse the results to a csv in IFS and vice-versa, loaded by a cron'd wget (or ibm equivalent).
Before I start, I want to see if anyone could recommend a better approach
What strategies and/or links you would you recommend? What are the cron and wget equivalents?
cron equivalent would be the job scheduler. It comes in two flavors from IBM, a basic version included with every system "Work with Job Schedule Entries (WRKJOBSCDE)" and the Advanced Job Scheduler , "Work with Jobs using Job Scheduler (WRKJOBJS)"
You may also have one or more third party schedulers on your system.
There isn't a wget equivalent built into the OS. There's various third party utilities and even some free and/or open source utilities. Also you could conceivably install and use the AIX version of wget in PASE.
http://yips.idevcloud.com/wiki/index.php/PASE/PackageManager
Now as far as import / export. IBM of course provides CPYTOIMPF and CPYFRMIMPF commands. But for performance and flexibility in error handling many prefer to roll their own using RPG. PHP would be an alternative for you.
Couple of more points...
While it's true that CPYTOIMPF doesn't support a query. It does support coping from an SQL view. So as long as you can build a view that provides the "joining and filtering" that you mention is required, then CPYTOIMPF could work.
Lastly, IBM does include a "Run SQL Statements" (RUNSQLSTM) command that will execute SQL statements contained in a source file member; including INSERT INTO somefile (SELECT ...) so you can load a physical table that you then can use CPYTOIMPF on. Also, in that source member, you can include CL commands by prefixing them with CL: