Want to write a standard user defined function in the db2

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?

re: Want to write a standard user defined function in the db2

Maroon wrote:
[color=blue]
> Hi,
> I want to write a standard java user defined function, like this
> example:-
> select db_fun("roll"), roll from student;[/color]

SQL uses single-quote as string delimiter.
[color=blue]
> **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()?[/color]

The "roll" you pass to the function is just like a variable name. For each
row in your table, the UDF will be called once and the value of the column
"roll" in each row is passed to the function. What the function does with
that value is completely the function's business.

Note that a function cannot have any idea on which table it operates - if it
even operates on values that stem from a base table. You can call
functions without any table being in the picture:

VALUES myFunction(...)

VALUES LENGTH('abc')
[color=blue]
> 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.[/color]

If you don't pass the values of the "name" column to the function as well,
and if you don't want/can to query your database from the Java code
(perhaps because you don't know the name of the table you need to query to
get the information), you're stuck. You simply have to have a way to get
the table name based on the "role" value from somewhere, solely by
evaluating the values. That's what SQL is all about: everything is based on
values only - no pointers, object IDs or whatever...

If you have a table (either in your database, in the file system or
hard-coded in your function), you can access that table from the UDF and do
the mapping that way. No problem there...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

re: Want to write a standard user defined function in the db2

Sorry, I couldn't understand well what do you want to do with db_fun().

You wrote(I capitalized some words.)[color=blue]
> here db_fun is the function that i want to write.
>
> The STUDENT TABLE is:-
> roll name
> 1 'A'
> 2 'B'
> 3 'C'[/color]
This seems the result of following simple SQL and not the result of
db_fun().
select roll, name from the student

Later, you wrote[color=blue]
> 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.
>From this statement, I guess that you want following result.[/color]
(and I guess "corresponding to the roll" means "including a column
named 'roll')

----------------------------------
ROLL Table name
1 A_TAB
2 B_TAB
3 XYZ
.....

Is it right?

re: Want to write a standard user defined function in the db2

Sorry, I couldn't understand well what do you want to do with db_fun().


You wrote(I capitalized some words.)[color=blue]
> here db_fun is the function that i want to write.
>
> The STUDENT TABLE is:-
> roll name
> 1 'A'
> 2 'B'
> 3 'C'[/color]

This seems the result of following simple SQL and not the result of
db_fun().
select roll, name from the student

Later, you wrote[color=blue]
> 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.[/color]
[color=blue]
>From this statement, I guess that you want following result.[/color]
(and I guess "corresponding to the roll" means "including a column
named 'roll')

----------------------------------
ROLL Table name
1 A_TAB
2 B_TAB
3 XYZ
.....


Is it right?

re: Want to write a standard user defined function in the db2

Sorry, I couldn't understand well what do you want to do with db_fun().


You wrote(I capitalized some words.)[color=blue]
> here db_fun is the function that i want to write.[/color]
[color=blue]
> The STUDENT TABLE is:-
> roll name
> 1 'A'
> 2 'B'
> 3 'C'[/color]

This seems the result of following simple SQL and not the result of
db_fun().
select roll, name from the student

Later, you wrote[color=blue]
> 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.[/color]
[color=blue]
>From this statement, I guess that you want following result.[/color]
(and I guess "corresponding to the roll" means "including a column
named 'roll')

----------------------------------
ROLL Table name
1 A_TAB
2 B_TAB
3 XYZ
.....

Is it right?

re: Want to write a standard user defined function in the db2

Thanks for your reply.
I am looking a function like Mysql:-
select md5(concat_ws("roll","name")) as md5, roll from student;
Here md5 is the mysql build in function.

If the table is:-
Student:-
roll name
1 A
2 B
3 C
4 D
Then the output will be for mysql databse is:-
md5 roll
as...sdf890 1
sd...dsfd98 2
sd.....0743 3
sd...j837nj 4

Here md5 column is the 32 char.

Now i want to write like a mysql function in the db2 databse.
I already write a user defined finction in the db2 database. but i
could not make it standard function that will work for all table of the
database.

With Regrads
Maroon
www.jence.com

re: Want to write a standard user defined function in the db2

Maroon wrote:[color=blue]
> Thanks for your reply.
> I am looking a function like Mysql:-
> select md5(concat_ws("roll","name")) as md5, roll from student;
> Here md5 is the mysql build in function.
>
> If the table is:-
> Student:-
> roll name
> 1 A
> 2 B
> 3 C
> 4 D
> Then the output will be for mysql databse is:-
> md5 roll
> as...sdf890 1
> sd...dsfd98 2
> sd.....0743 3
> sd...j837nj 4
>
> Here md5 column is the 32 char.
>
> Now i want to write like a mysql function in the db2 databse.
> I already write a user defined finction in the db2 database. but i
> could not make it standard function that will work for all table of the
> database.[/color]
It has been a long time since I took a class on learning by example
algorithms, but mind giving us a description of what md5() does (oh and
concat_ws() while you're at it :)

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

re: Want to write a standard user defined function in the db2

Hava you worked other database( Mysql ) before? I mentioned a example.
Anyway.
concat_ws add the string that are given to its parameter.
and md5 will implement a algoritham like ssh

Look...
This is a table:- Student
Column :- roll, name, marks
The data:-
roll name marks
1 a 87
2 b 83
3 c 75
Query:-
select roll, myfun("roll","name","marks") as md5 from student.
Here:- myfun is the user defined function that i want to
write.

The output will be
roll md5
1 sdfsdf98874
2 sadfsdf4566
3 gffdsgasd34

re: Want to write a standard user defined function in the db2

Maroon wrote:[color=blue]
> Hava you worked other database( Mysql ) before? I mentioned a example.
> Anyway.
> concat_ws add the string that are given to its parameter.
> and md5 will implement a algoritham like ssh
>
> Look...
> This is a table:- Student
> Column :- roll, name, marks
> The data:-
> roll name marks
> 1 a 87
> 2 b 83
> 3 c 75
> Query:-
> select roll, myfun("roll","name","marks") as md5 from student.
> Here:- myfun is the user defined function that i want to
> write.
>
> The output will be
> roll md5
> 1 sdfsdf98874
> 2 sadfsdf4566
> 3 gffdsgasd34
>[/color]

Are you trying to encrypt contents of columns name and marks for each roll?

As in:
select roll, encrypt(roll concat name concat marks) from student;

You may modify this depending on column types for roll, name and marks.
Review online searchable documentation available at:

http://publib.boulder.ibm.com/infoce...help/index.jsp

Or you are trying to calculate MD5 checksum of the contents of columns name and
marks for each student?

You may look up alghoritms for MD5 calculations on the internet - I would
recommend concatenation of character representation of columns roll, name and
marks as a single input argument to MD5 user written function - so invocation
could be:

select roll, MD5(roll concat name concat marks) from student;


Jan M. Nelken

re: Want to write a standard user defined function in the db2

Maroon wrote:
[color=blue]
> Hava you worked other database( Mysql ) before? I mentioned a example.
> Anyway.
> concat_ws add the string that are given to its parameter.[/color]

That's done by the CONCAT (or ||) operator in the SQL standard and also DB2.
So that is already covered.
[color=blue]
> and md5 will implement a algoritham like ssh[/color]

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

re: Want to write a standard user defined function in the db2

Knut Stolze wrote:[color=blue]
> Maroon wrote:
>
>[color=green]
>>Hava you worked other database( Mysql ) before? I mentioned a example.
>>Anyway.
>> concat_ws add the string that are given to its parameter.[/color]
>
>
> That's done by the CONCAT (or ||) operator in the SQL standard and also DB2.
> So that is already covered.
>
>[color=green]
>> and md5 will implement a algoritham like ssh[/color]
>
>
> 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.
>[/color]
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

re: Want to write a standard user defined function in the db2

Knut,
Thanks for your kind reply.
[color=blue]
> 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.[/color]
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:[color=blue]
> Maroon wrote:
>[color=green]
> > Hava you worked other database( Mysql ) before? I mentioned a example.
> > Anyway.
> > concat_ws add the string that are given to its parameter.[/color]
>
> That's done by the CONCAT (or ||) operator in the SQL standard and also DB2.
> So that is already covered.
>[color=green]
> > and md5 will implement a algoritham like ssh[/color]
>
> 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[/color]

re: Want to write a standard user defined function in the db2

Maroon wrote:
[color=blue]
> Knut,
> Thanks for your kind reply.
>[color=green]
>> 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.[/color]
> How can i access the current rows in the CONCAT_WS function?[/color]

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!
[color=blue]
> 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?[/color]

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

re: Want to write a standard user defined function in the db2

Knut
Thanks

Maroon
www.jence.com