UDF in SELECT statement to get a result set (collection of records) running infinitely

We have created a wrapper SP for a RPG program  and tried executing , it works fine.
IN BRCH          CHAR(2),
IN  SURE         CHAR(6),
IN  SUB          CHAR(3),
OUT BAL          CHAR(9)
CALL LibName.ProgName('15','001408','789',?); -- getting result. 000005108
After this,  I created a SQL UDF "fnGetBalance"to get scalar value for each record.
CREATE FUNCTION LibName.fnGetBalance
BRCH          VARCHAR(2),
SURE          VARCHAR(6),
SUB           VARCHAR(3)
     CALL LibName.ProgName(BRCH,SURE,SUB,Bal);
     RETURN Bal;
 It failed and returned the error
Error Message:    Function OVRDBF is not allowed in a secondary thread
As SQL UDF creates a seperate thread for the execution, ProgName invoked in th secondary thread and inside ProgName we are calling  OVRDBF operation.  We found a option call "NOT FENCED" and used this while creation of UDF.  Now it works fine for single time execution.
SELECT    cuskey, fnGetBalance('15','001408','789')
FROM     LibName.FIleName
WHERE     cuskey = 9000001;
But when we invoke the UDF in SELECT statement to get a result set (collection of records).  Its running infinitely without giving any error.
Also, I found a forum with simillar situation and it says, if we call OVRDBF operation for each record it will lead to performance issue.

Please help me resolve the issue as I have run out of ideas.
Who is Participating?
tliottaConnect With a Mentor Commented:
> ...if we call OVRDBF operation for each record it will lead to performance issue.

Everything that happens affects performance. If things happen for each row, they will affect performance by a greater amount -- more or less multiplied by the number of rows.

A database override that is applied whenever a program is run implies a different file for every run. For SQL, if this happens for every single row that's processed, you're essentially trying to open and close some file every time the field (the function) is accessed. File opens and closes are expensive processes which is why you really only want it to happen once in a job.

In ILE, you have a useful way to influence how often a file is opened and closed. Consider creating your RPG program to run in a named activation group, perhaps by itself. Let it open the file only the first time it's called or when the file actually needs to change. (I assume the program can somehow figure that out; otherwise there's no useful purpose to the override.) Whenever a change happens, close the file before the new open.

Under most circumstances, such a program would only issue the override and open the file once for any SELECT statement. Without knowing why you're overriding in the first place, I can't say how many times it will happen for you.

Then, create your outer CL to run in the *CALLER activation group. Don't do the override in the CL -- move it inside of the RPG. You might even replace this outer CL with one that does nothing but call the RPG. You might bind a new CL module into the RPG to handle the override.

This creates a boundary. The outer CL runs in the activation group of whatever is running the SQL. The RPG runs in its own activation group separate from everything else in the job. Any file opens/closes will be isolated inside of that activation group; there should be very few of those. You can leave that named activation group there for the entire life of the SQL job or you can make something run RCLACTGRP to clean it up when you choose.

Gary PattersonVP Technology / Senior Consultant Commented:
You may have to modify that RPG program to eliminate the override, or create a special version for use as a function.

When you plan to use an existing legacy program as an SQL UDF, sometimes you have to make adjustments to the program for performance or compatibility reasons.  Hard to make any specific recommendations without seeing the actual program source code.

And yes, repeatedly performing overrides, opening files, and initializing programs is expensive, so if you do it over and over, the performance costs add up.

In legacy RPG programming, we typically solve this problem by doing overrides once, early in a batch job, opening files and leaving them open, and ending subprograms with a RETURN instead of an LR, until we are at the end of our batch, when we go through and shut down each active program in the stack, or clean up using RCLRSC, or other mechanisms.  

This is tricky behavior to emulate in SQL.

One high-performance technique, though it takes a bit of work, is to create function server programs.  

Function servers are jobs that are auto-started or pre-stated, and provide complex high i/o functions to other programs and processes.  The function server pre-initializes programs, opens files, and creates overrides, and then sits and waits for a request from another program (perhaps in the form of an entry on a data queue, or a sockets connection, or just about any inter-process communication mechanism you like).  The function server performs the operation, and returns the result to the calling program (perhaps through an entry placed on a response data queue), and then waits for another request.  

You can also build web services that follow this model.  Web services are really just function servers with a specific type of interface.

To make function servers (or web services) easy to use in SQL, you build a small front end program that handles placing the function request on the queue, and retrieving the response from the function server.  You then use this front-end program when you create your SQL procedure or function.

On another note...

By the way, since balances can usually change from execution to execution, you probably want to declare this as this NOT DETERMINISTIC.  Deterministic means that the function ALWAYS returns the same value if you pass the same parameter.  Usually that is not true for something dynamic like account balances.

- Gary Patterson

if you are using the OVrDBF to override to a different file then how about convetring the RPG to ILE then using the ExtFile op code in the F spec.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.