2

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?

Buck Calabro
  • 7,278
  • 18
  • 24
Dimitri Shukuroglou
  • 355
  • 1
  • 4
  • 12
  • 1
    So there's an external partner who will periodically put data on a server, and the plan is to have IBM i periodically poll that server? And then IBM i will transform and import that data (EDI PO 850?) into JDE, presumably run some process and return a dataset (PO 856?) to the partner? – Buck Calabro May 18 '15 at 19:15
  • No, plan is to open up an IFS folder over SFTP on the IBM i. IBM i will periodically output a dataset to this folder and import another dataset. The partner will periodically upload import and download export the datasets. – Dimitri Shukuroglou May 19 '15 at 15:19
  • What is your definition of `periodically` in both cases? In particular, is it a set, predetermined schedule or is it 'as needed'? – user2338816 May 20 '15 at 05:25
  • The way I first thought about it was on a predetermined schedule, but I have my reservations with that. 'As needed' would be a more efficient approach and better design wise, if you have any suggestions. – Dimitri Shukuroglou May 20 '15 at 18:40
  • 1
    Make sure the FTP is locked down. Many times you can cd.. and bam you have root access. – danny117 May 27 '15 at 14:57

2 Answers2

4

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:

Charles
  • 16,475
  • 1
  • 13
  • 36
3

The System I Navigator has the 'Run SQL Scripts' tool. This tool has an option to Save Results and one of the options is to a csv file.

Scott Mildenberger
  • 871
  • 5
  • 11
  • 1
    Hi Scott, thanks for the reply! is there a way I could schedule that? or even better contain the entire functionality within the iseries machine? – Dimitri Shukuroglou May 18 '15 at 19:12