UDF has me baffled

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

esmith2112 - 31 Mar 2008 18:03 GMT

Using 8.2 FP16 on AIX 5.3.3

I created a UDF to convert a 26-byte character string into a CHAR(13)
FOR BIT DATA item. During testing, a flaw was discovered (a known DB2
issue with CHR(0) = x'20' instead of x'00'), so I created a workaround
in the body of the UDF.

I did the following:

1. drop function db2admin.conv_item_id;

2. create function db2admin.conv_item_id (item_id char(26))
returns char(13) for bit data
language sql
contains sql
no external action
not deterministic
return <BODY OF FUNCTION>;

3. grant execute on function db2admin.conv_item_id to public;

All three statements executed with success. However, all calls to the
UDF now yield the dreaded SQL0440N error saying:
No authorized routine named "CONV_ITEM_ID" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

I'm used to this type of error when the schemas don't match, or the
wrong number or wrong data types are passed, but this one has me
baffled. I didn't change the input or output to the function. I can
see the function in the system catalogs. I tried dropping "specific
function" using the specificname, then recreating all with the same
results. A search of this newsgroup couldn't pinpoint the exact
cause.

Any clues?

Thanks,
Evan

Reply to this Message

esmith2112 - 31 Mar 2008 18:39 GMT

OK. Different search terms provided the answer in here in the
newsgroup. It's the old CHAR vs. VARCHAR thing on the input, where a
literal string is interpreted as VARCHAR. But what still has me
baffled, was that it worked the first time around during testing
without problem until I dropped and re-created it.

> Using 8.2 FP16 on AIX 5.3.3
>
[quoted text clipped - 34 lines]
> Thanks,
> Evan

Reply to this Message

Serge Rielau - 31 Mar 2008 18:41 GMT

Not all calls.. Just the ones where you pass in literals ;-)
Literals are VARCHAR; and VARCHAR is higher in the promotion chain than
CHAR.

In general, if you expect literals input use VARCHAR as input type

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Serge Rielau - 31 Mar 2008 18:47 GMT

> Not all calls.. Just the ones where you pass in literals ;-)
> Literals are VARCHAR; and VARCHAR is higher in the promotion chain than
> CHAR.
>
> In general, if you expect literals input use VARCHAR as input type

Oh.. and I think there is a DB2_COMPATIBILITY_VECTOR setting in DB2 9.5
FP1 to just flip literals to CHAR.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message