Hi there,

I have built a stored procedure, say sp_proc1. Within the stored procedure, I call a user defined function, say my_udf1. I get the following error message:

Error: SQL0440N No authorized routine named "MY_UDF1" of type "FUNCTION" having compatible argument was found. SQLSTATE=42884 (State:42884, Native Code: FFFFFE48)

It seems that the UDF my_udf1 with right argrument either not exist or I have no right to access. However, sp_proc1 and my_udf1 are under the same data schema, and I have full rights to access both of them. Do anyone has idea or hints for me to shoot this problem?

Thanks in advance.
Athens Yan.

P.S.: The DB server version is 8.1.4 (AIX), and font-end client version is 8.1.0 (Windows).

Hi.
Please, check the numbers and data type of your paramenters.

Also, does it work from a command line

Cheers
Sathyaram

First, thanks all your help.

To: achiola

I have searched the IBM web site, and obtained some hints. However, they don't give me any help to overcome my problem even I follow their ways. So, I would like anybody have met the same problem to give me a practicle advise.

To: sathyaram_s

The same error message is appeared on CLP and Command Center. Any idea?

Moreover, in accordance with IBM's official supporting information, the problem may be caused by client settings on DB2CLI.INI (a parameter CURRENTFUNCTIONPATH). I have made the change on it as follows:

CURRENTFUNCTIONPATH="DB2HK"

Note: "DB2HK" is the schema of my database.

The result is unchanged, and error SQL0440N still exists.

Regards,
Athens.

One more fact: I can call all built-in UDF of DB2. For example, the following SQL command is worked very well:

select UCASE(my_field) from my_table;

Athens.

Hav you tried calling the function with qualifer?

i.e. adding the specifik schema in front of your
function inside the SP?

To Tank:

Yes, I have tried this way before. Any further suggestion?

Regards,
Athens.

Can you post your function definition and the output of
"select p.routineschema,p.routinename,ordinal,typename from syscat.routines r,syscat.routineparms p where p.routineschema=r.routineschema and p.routinename=r.routinename and p.routinename='<your routine name>' and routinetype='F'"

Plus, of course, you exact function call ...

Someone may be able to help

Cheers
Sathyaram

Hi Sathyaram,

After executing your suggestion SQL command, I get the following result:

ROUTINESCHEMA ROUTINENAME ORDINAL TYPENAME
-------------------- ----------------------- ----------- ------------
DB2HK SF_GET_CDE_DESC 0 VARCHAR
DB2HK SF_GET_CDE_DESC 1 CHARACTER
DB2HK SF_GET_CDE_DESC 2 CHARACTER
DB2HK SF_GET_CDE_DESC 3 CHARACTER

This function is defined as follows:

CREATE FUNCTION SF_GET_CDE_DESC(
PIC_COMPANY_NUM CHARACTER(1)
,PIC_CDE_TYP CHARACTER(5)
,PIC_CDE_ITM CHARACTER(8)
)
RETURNS VARCHAR(2000)
F1: BEGIN ATOMIC
RETURN
SELECT cde_desc
FROM cde_desc
WHERE company_num = pic_company_num
AND cde_typ = pic_cde_typ
AND cde_itm = pic_cde_itm;
END

And the function 'SF_GET_CDE_DESC' is called within a stored procedure which is defined as follows:

CREATE PROCEDURE sp_eserv_get_clt_policy_list (
IN pic_company_num char(1)
,IN pic_client_num char(8)
,IN pic_adviser_num char(10)
,INOUT pioi_sqlcode integer
)
DYNAMIC RESULT SETS 3
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT distinct
'2' as dummy_col
,c.contract_num as policy_num
,SF_GET_CDE_DESC(pic_company_num, 'EPOS', c.contract_status) as policy_status
,c.currency_cde as currency
,SF_GET_CDE_DESC(pic_company_num, 'MTHD', c.payment_method) as payment_method
,c.commence_dte as commence_date
,b.short_description as benefit_desc
,SF_GET_CLT_BEN_AMT(pic_company_num, c.contract_num, pic_client_num, a.comp_cde, a.comp_option) as benefit_amount
,SF_GET_CLT_PRM_AMT(pic_company_num, c.contract_num, pic_client_num, a.comp_cde, a.comp_option) as premium_amount
,( SELECT case when count(1) > 0 then 'Y' else 'N' end
FROM contract_extra_premium
WHERE company_num = pic_company_num
AND contract_num = c.contract_num
AND comp_cde = a.comp_cde ) as premium_loading
, a.comp_option
, a.sum_assured
, a.basic_instalment_premium
, b.present_order
FROM contract_header c
,life_comp a
,component_master b
WHERE c.company_num = a.company_num
AND c.contract_num = a.contract_num
AND a.client_num = pic_client_num
AND a.comp_cde = b.comp_cde
AND c.contract_status IN ('40', '41', '50', '51', '52', '53', '54')
AND a.comp_cde NOT IN ('PFUSD', 'PFHKD')
AND a.comp_cde <> 'RTU'
AND ( ( pic_adviser_num is not null AND
EXISTS ( select 1
from contract_adviser s1
where s1.company_num = a.company_num
and s1.contract_num = a.contract_num
and s1.adviser_num = pic_adviser_num ) )
OR pic_adviser_num is null
)
ORDER BY c.contract_num, b.present_order
;

BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION
set pioi_sqlcode = -1;

if pic_adviser_num = '' then
set pic_adviser_num = null;
end if;

open c1;
END;
END


The procedure 'sp_eserv_get_clt_policy_list' always return nothing due to the said error by calling the embraced UDF 'SF_GET_CDE_DESC'.

I have recreated all the related UDFs, but I am still caught by the error. This error almost drives me crazy, if anybody gets solving solution, please help.


Thanks,
Athens Yan.

If you have chars as input parms, you will be to explicitly cast each input parm as a char of that length ...

ie

SF_GET_CDE_DESC(char(pic_company_num,1), char('EPOS',8), char(c.contract_status,10)) as policy_status

You may have to create your function to accept VARCHAR as the input parms instead of CHARs , then you can use the function as you have used in the proc ...

There may be better ideas ...

Cheers
Sathyaram

Hi Sathyaram,

It works!! Thank you so much. However, it also confuses me. Is 'char' not compatible with 'character'? I always treat them as the same thing on my SQL stored procedures/functions.

Regards,
Athens.