Query about SQL UDF

[Mahesh S]

Hi

I would like to accomplish the following using a SQL UDF in db2.

1. Invoke a java class from within the UDF and call a method which
takes a parameter and returns a result.

2. the UDF performs an insert / update operation in a table using the
result obtained.

3. Schedule the UDF to run on a timely basis. (for example, once a
day)

The reason for invoking a java class is that it calls a web service and
captures the result from the web service.

Alternatively, can I directly call the web service from the UDF?

Please help me out. Any code snippets will be appreciated.

Thanks
Mahesh

[Knut Stolze]

Mahesh S wrote:

Hi

I would like to accomplish the following using a SQL UDF in db2.

1. Invoke a java class from within the UDF and call a method which
takes a parameter and returns a result.

Then you don't need a SQL UDF. The UDF can be fully implemented in Java.

2. the UDF performs an insert / update operation in a table using the
result obtained.

Then you will need a table function.

3. Schedule the UDF to run on a timely basis. (for example, once a
day)

Scheduling is unrelated to UDFs. Use the admin scheduler, cronjob, or
whatever you have available.

The reason for invoking a java class is that it calls a web service and
captures the result from the web service.

Alternatively, can I directly call the web service from the UDF?

You can use DB2's SOAP functions:
http://publib.boulder.ibm.com/infoce...d/ciispfcn.htm

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Mahesh S wrote:

>Hi

I would like to accomplish the following using a SQL UDF in db2.

1. Invoke a java class from within the UDF and call a method which
takes a parameter and returns a result.

Then you don't need a SQL UDF. The UDF can be fully implemented in Java.

>2. the UDF performs an insert / update operation in a table using the
result obtained.

Then you will need a table function.

>3. Schedule the UDF to run on a timely basis. (for example, once a
day)

Scheduling is unrelated to UDFs. Use the admin scheduler, cronjob, or
whatever you have available.

>The reason for invoking a java class is that it calls a web service and
captures the result from the web service.

Alternatively, can I directly call the web service from the UDF?

You can use DB2's SOAP functions:
http://publib.boulder.ibm.com/infoce...d/ciispfcn.htm

In general what you ask for suggests more that you wuld want a stored
procedure.... UDFs are there to extend SQL capability. You simply want
to execute business logic.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Mahesh S]

Hi Knut

Thanks for the reply.

Regarding the scheduling, I understand its not related to the UDF but I
was wondering if there is some functionality available in db2 that
would let me run a code periodically.

Cheers
Mahesh

[Knut Stolze]

Mahesh S wrote:

Hi Knut

Thanks for the reply.

Regarding the scheduling, I understand its not related to the UDF but I
was wondering if there is some functionality available in db2 that
would let me run a code periodically.

Have a look at the Task Center and/or Admin Scheduler.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany