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

Posted on 2009-12-18
Last Modified: 2013-12-06
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.
Question by:subhorachana
    LVL 34

    Expert Comment

    by:Gary Patterson
    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

    LVL 14

    Expert Comment

    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.

    LVL 26

    Accepted Solution

    > ...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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now