Prev: how to view a vertical table in horizontal way
Next: REplication of DB2 V9.7 tables with XML columns
From: andyhe on 21 Jan 2010 05:31
Hi,
I am trying to create a function in db2 that takes in a parameter, and
returns 1 value. I can make it work as long as there is no input
parameter defined (see second part, func2). But for some reason, once
there is an input parameter, I can no longer findthe function (error
SQL0440N No authorized routine ... found).
This must be something I'm missing, but I just can't see it.
I don't think it's user access rights, or schema name or function path
not set correctly because the other function works great.
Any pointer would be appreciated
Thanks! Andy

Content file 'func':
drop function andyhe.echothis(char(6));
create function andyhe.echothis(p1 char(6) ) returns char(6) language
sql no external action deterministic return 'thisis';
End-content

db2 -vtf func

Output:
drop function andyhe.echothis(char(6))
DB20000I The SQL command completed successfully.
create function andyhe.echothis(p1 char(6) ) returns char(6) language
sql no external action deterministic return 'thisis'
DB20000I The SQL command completed successfully.
End-output

db2 "values(andyhe.echothis('123456'))"
db2 "values(andyhe.echothis(''))"
db2 "values(andyhe.echothis())"
Output for all 3 commands:
SQL0440N No authorized routine named "ECHOTHIS" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884
End-Output

------------------------

Content file 'func2':
drop function andyhe.echothis2();
create function andyhe.echothis2() returns char(6) language sql no
external action deterministic return 'thisis';
End-content


db2 -vtf func2

Output:
drop function andyhe.echothis2()
DB20000I The SQL command completed successfully.
create function andyhe.echothis2() returns char(6) language sql no
external action deterministic return 'thisis'
DB20000I The SQL command completed successfully.
End-output

db2 "values(andyhe.echothis2())"
Output:
1
------
thisis

1 record(s) selected.
End-Output
From: Tonkuma on 21 Jan 2010 07:01
I guessed that it must be a typical pitfall of function resolution
algorithm.
Try
create function andyhe.echothis(p1 VARchar(6) ) returns char(6)
language
From: andyhe on 21 Jan 2010 07:40
On 21 jan, 13:01, Tonkuma <tonk...(a)fiberbit.net> wrote:
> I guessed that it must be a typical pitfall of function resolution
> algorithm.
> Try
> create function andyhe.echothis(p1 VARchar(6) ) returns char(6)
> language

works like a charm!

although this will need some tweaking to avoid getting errors when the
input is too large for instance, but that shouldn't be a problem
shouldn't this be mentioned in the manuals somewhere? I hard a really
hard time finding information relevant to this problem (I usually
don't post to get help).

anyway: Great, thanks for the swift reply, you were a real help

Andy
From: Tonkuma on 21 Jan 2010 09:27
> shouldn't this be mentioned in the manuals somewhere? I hard a really
> hard time finding information relevant to this problem (I usually
> don't post to get help).
Yes, it's not so directly documented.
Related with two items(both are documented in the manual "DB2 SQL
Reference Volume 1") additional to function resolution algorithm.
1) A character string constant specifies a varying-length character
string.
2) Promotion of data types.
OK: CHAR to VARCHAR, CLOB
OK: VARCHAR to CLOB
Not: VARCHAR to CHAR

From: Serge Rielau on 21 Jan 2010 13:36
On 1/21/2010 9:27 AM, Tonkuma wrote:
>> shouldn't this be mentioned in the manuals somewhere? I hard a really
>> hard time finding information relevant to this problem (I usually
>> don't post to get help).
> Yes, it's not so directly documented.
> Related with two items(both are documented in the manual "DB2 SQL
> Reference Volume 1") additional to function resolution algorithm.
> 1) A character string constant specifies a varying-length character
> string.
> 2) Promotion of data types.
> OK: CHAR to VARCHAR, CLOB
> OK: VARCHAR to CLOB
> Not: VARCHAR to CHAR
>
...you will have similar problems when using a SMALLINT parameter since
literal numbers are at least INTEGER.

Note that in DB2 9.7 implicit casting kicks in. At that point DB2 will
downcast the varchar to char and find the function.

Another think you may consider is to us:
db2set DB2_COMPATIBILITY_VECTOR=100
(db2stop/db2start)
That setting makes all char literals smaller than 255 CHAR.
It was introduced in an early DB2 9.5 Fixpack I believe...

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

 |  Next  |  Last
Pages: 1 2 3
Prev: how to view a vertical table in horizontal way
Next: REplication of DB2 V9.7 tables with XML columns