Hi Doug,
I'd be inclined to do something along the lines of Chuck's advice for what
you're doing. I'm an SQL function fanatic, so I like that you're
considering them, but based on what I've gathered so far, I don't think I'd
use a function as you've described.
To my knowledge an external SQL function can only be pointed to the primary
interface of a *PGM object, or a service program procedure, neither of
which sounds like what you're inquiring to do.
To give you potential ideas for alternatives to Chuck's suggestions, if you
truly need a function:
- Note: Last time I checked at V7R1, DB2 on i does not let you create
functions in QTEMP. Hence, a temporary function has to be created in a
permanent library.
- You can create and drop functions on the fly at run time.
- To simulate a temporary function, you could generate a unique function
name, create the function, and then reduce the ability of other users from
calling the function through REVOKE and GRANT statements as needed.
- Call the function, and then drop it when done.
If you could elaborate on that subprocedure task(s), I can likely provide
ideas on if / how SQL functions might be a good design fit. Communicating
within a program doesn't sound like a good fit for functions though.
I'm taking a wild guess here. If your program is generating an SQL result
set, and you want to call that subprocedure for every row of the result
set, then consider doing something like this:
- Relocate the code in that subprocedure to a function.
- Embed the function call in the SQL that generates the initial result
set.
- I do lots of processing like that, but there are learning curves
involved, and I anticipate most production code would require a full
rewrite or extensive refactoring to take advantage of a design like that.
- To call functions in the middle of result set generation, in many
cases you have to structure the SQL in such a way as to force the database
engine to pass the rows to the function in your desired order. In most
cases, using recursive SQL is the best way to do this. If the rows can be
passed to the function in random order, then you don't have to jump through
those hoops. Note: adding an ORDER BY clause to the SQL does not guarantee
the order in which the rows are passed to the function.
Mike
date: Mon, 2 Nov 2015 12:57:28 -0600
from: "Englander, Douglas" <Douglas_Englander@xxxxxxxxxxxxx>
subject: SQL Functions in RPG
Does anyone know if I can create a subprocedure in an ILE RPG program, and
use embedded SQL in that program to reference that subprocedure as a SQL
function, instead of creating a SQL function that points to a subprocedure
located in a Service Program? I am looking for something like a SQL
"temporary function" that would be used only by that program, and only when
that program is active. If so, can you point to some examples?
Thank you,
Doug
midrange.com
