We have created a wrapper SP for a RPG program and tried executing , it works fine.
CREATE PROCEDURE LibName.ProgName
IN BRCH CHAR(2),
IN SURE CHAR(6),
IN SUB CHAR(3),
OUT BAL CHAR(9)
EXTERNAL NAME LibName.ProgName
PARAMETER STYLE GENERAL;
,?); -- getting result. 000005108
After this, I created a SQL UDF "fnGetBalance"to get scalar value for each record.
CREATE FUNCTION LibName.fnGetBalance
DECLARE Bal VARCHAR(9) DEFAULT '';
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'
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.