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

Can an iSeries RPG or CL program be called from a MS SQL Server Stored Procedure?  

If so, how?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
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.
vt-tcAuthor Commented:
What is UDF?

What is SSIS?

Which is better?

Brendt HessSenior DBACommented:
UDF = User Defined Function

In SQL Server 2005 and later, .Net code can be used to create a user-defined function.  This code has some limitations on what can be done from within the SQL Server instance, so researching those limitations as they relate to your situation would be needed before making a decision on how to proceed.

SSIS = SQL Server Integration Services

This is a jobs engine based on a subset of Visual Studio .NET.  In it, you can write complete (non-interactive) applications related to data processing.  It would be more certain that your code could accomplish what you need if it was run through a SSIS job than if it was run as a UDF.

Which is better?  It really depends on what you need to do, as covered in the above short descriptions.  An example of how you would call an RPG report or CL program from your server without considering SQL Server at all would be useful while analyzing this issue, as I do not have an AS400 machine around to test with.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

MurpheyApplication ConsultantCommented:
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


vt-tcAuthor Commented:

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.

vt-tcAuthor Commented:

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

MurpheyApplication ConsultantCommented:

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?

vt-tcAuthor Commented:

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.

MurpheyApplication ConsultantCommented:
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 :-)

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.

vt-tcAuthor Commented:
being unfamiliar with SSIS, it required some research on my part.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.