Want to write a standard user defined function in the db2

[Maroon]

Hi,
I want to write a standard java user defined function, like this
example:-
select db_fun("roll"), roll from student;
**db_fun() will work for all tables of the all databse
here db_fun is the function that i want to write.

The student table is:-
roll name
1 'A'
2 'B'
3 'C'

I used the column name as a parameter of the function. and the
db_fun () will return the name of the table corresponding to the roll.

Now, how can i access the name column from the db_fun()?

This is the same as the query:-
select name, roll from the student.
But i want, the name will return from the user defined
function.

Can i write such type of user defined function in the db2 databse?

[Serge Rielau]

Knut Stolze wrote:

Maroon wrote:

Hava you worked other database( Mysql ) before? I mentioned a example.
Anyway.
concat_ws add the string that are given to its parameter.

That's done by the CONCAT (or ||) operator in the SQL standard and also DB2.
So that is already covered.

and md5 will implement a algoritham like ssh

I think you're mixing up encryption, hash algorithms and network protocols.
md5() is a function that "Calculates an MD5 128-bit checksum for the
string." (according to the MySQL manual).
I believe the biggest issue here is to understand that functions have
nothing to do with tables per se. A function takes a set of input values
and returns an output value. Period.

In the original query, the function CONCAT_WS takes the two strings of the
current row in the "student" table and concatenates them to a single
string. That string is now passed to the MD5 function, which calculates
the MD5 checksum (a hash value basically).
Porting this to DB2 requires that you implement a function which takes a
string as input and returns the string's checksum. Register that function
(or Java method) as UDF, and off you go.

Look for this thread:
"Have any function in the DB2 database that can generate unique id for
each String?"
I posted a hash function on Oct 17 in this forum.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Maroon]

Knut,
Thanks for your kind reply.

In the original query, the function CONCAT_WS takes the two strings of the
current row in the "student" table and concatenates them to a single
string. How can i access the current rows in the CONCAT_WS function?
if the table is student and data is in the following:-
roll name
1 A
2 B
3 C

select roll, md5(concat_ws(" roll","name")) from student.

for the concat_ws--->The output will be

1 1A
2 2A
3 3C

How can i find the current rows in my user defined function?

I am looking forward for ur kind reply.

Knut Stolze wrote: Maroon wrote:

Hava you worked other database( Mysql ) before? I mentioned a example.
Anyway.
concat_ws add the string that are given to its parameter.

That's done by the CONCAT (or ||) operator in the SQL standard and also DB2.
So that is already covered.

and md5 will implement a algoritham like ssh

I think you're mixing up encryption, hash algorithms and network protocols.
md5() is a function that "Calculates an MD5 128-bit checksum for the
string." (according to the MySQL manual).
I believe the biggest issue here is to understand that functions have
nothing to do with tables per se. A function takes a set of input values
and returns an output value. Period.

In the original query, the function CONCAT_WS takes the two strings of the
current row in the "student" table and concatenates them to a single
string. That string is now passed to the MD5 function, which calculates
the MD5 checksum (a hash value basically).
Porting this to DB2 requires that you implement a function which takes a
string as input and returns the string's checksum. Register that function
(or Java method) as UDF, and off you go.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Maroon wrote:

Knut,
Thanks for your kind reply.

In the original query, the function CONCAT_WS takes the two strings of
the
current row in the "student" table and concatenates them to a single
string. How can i access the current rows in the CONCAT_WS function?

That's the thing: you don't access the row from the function but rather the
values of the columns of the current row are passed to the function. You
just have it backwards!
if the table is student and data is in the following:-
roll name
1 A
2 B
3 C

select roll, md5(concat_ws(" roll","name")) from student.

for the concat_ws--->The output will be

1 1A
2 2A
3 3C

How can i find the current rows in my user defined function?

What would you need that rows for. The function is called 3 times, once for
each row. The first call gets the concatenation of "1" and "A" as input,
calculates the MD5 checksum and return that value. The second call gets
"2" and "B" as input, and the third gets "3" and "C" - just like functions
work in pretty much all other programming languages.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Maroon]

Knut
Thanks

Maroon
www.jence.com