Calling an iSeries RPG program from a MS SQL Server Stored Procedure

How would you call one from the command line?  

Given this information, you can write a batch file and execute it in xp_cmdshell (option 1), emulate it in a .net code snippet which you make into a UDF (option 2), create a SSIS Job that will perform the task and call it from TSQL code (option 3), or try something else.

What is UDF?

What is SSIS?

Which is better?

Thanks,
VK

Hi VK,

If your AS/400 has a running webserver (apache oid) that it is possible to call the program as URL
but you have to look to the apache config to see where the program should be stored.
In that case you can run i tas any page with extention pgm

e.g.

Http://myas400_ip:port/myrpg.pgm

murphey2,

That is an interesting approach.  We do have a web service running on the iSeries (AS/400), so we will test this option as well.

Thanks,
VK

murphey2,

do you have an example of launching a URL in a SQL Server Stored Procedure?

VK

No,

It was just mentioning an alternate way how you can start a as/400 program.

How do you launch a program from a SQL Server Stored Procedure if the program is NOT running on an AS/400?

murphey2,

We have a MS SQL Server 2005 / 2008 Stored Procedure that we need (require) to launch an AS/400 RPG and/or CLP program for additional processing on the iSeries.   We have CGI applications written in RPG on the iSeries so being able to launch them simply via a URL is an option of interest, if it can be done within the Stored Procedure residing on our MS SQL Server (not the iSeries).

Hope that helps explain our scenario better.

VK

I never did it that way, (because I'm a RPGxx as/400 etc. developer)

I did things like it but with the as/400 trigger maganism.

1. On the as/400 create a physical-file (a table) e.g. JOB2RUN
2. Create a program that read, delete and execute the first line of file JOB2RUN
3. Add this program as create trigger to file JOB2RUN

Now if you add an as/400 command to file JOB2RUN (can use a simple insert command) and the trigger will read and execute it. This can be a simple call from a RPG or CL program, but also a call with parameters. As long as you respect the AS/400 syntax, the trigger program will run everything :-)

Regards,
Murphey

Does the AS/400 program need to return any result back to its caller? Or is it simply to be started to let it run on its own?

It might be simplest just to do a straight stored proc CALL to the AS/400 database. If no parameters or return values are needed, you won't even have to configure/create a stored proc definition in DB2.

Tom

being unfamiliar with SSIS, it required some research on my part.