Hi,
i have 2 databases(say A and B),both of them are on b2V8.2(fp5) solaris.these databses are fedetated. the tables are present in database B which is federated with database A.the queries are running against database A.there is a UDF defined in database B.we know we create nicknames in database A for tables present in database B.so is there any similar way for the UDF?..
or can anyone please teel me how to use the UDF present in Database B in database A.
any help is very much appreciated.

thanks...

Have a look at CREATE FUNCTION MAPPING: http://publib.boulder.ibm.com/infoce...n/r0002168.htm

Note that if you have a LANGUAGE SQL function, you don't have to worry about it. This gets compiled into the SQL statement and DB2 can try to figure out to route it to the data source. (A problem is if you decided to ignore the relational model and used procedural logic in the UDF body.)

If your function is an external one, you will have to create it in the data source as well. Then you can create the function mapping to tell the federated server that the local function is also available at the data source (possibly with a different name).

earlier i had the same function in the both the servers.but there was no mapping earlier.the query was executed in the federated server as the function also existed in this server.even after i create a function mapping the optimizer is showing the same access plan.do you think the optimizer has
choosen the one function in federated server instead of source server,as the same fuction exists(ofcourse with different names) in both the servers.

actually i have created the same function in both the servers with different names then mapped them using CREATE FUNCTION MAPPING.(i did not used
the function template).in our access plan the data in the tables after applying some predicates in the source server are beeing shifted to federated server and doing some nested loop joins.
we want that nested loop joins to be happen at the source server so that the performance improves.how does db2 uses the push down analysis.what are the factors for having the query to be executed completely on the source and then just ship those results to the federated server.
i have used the following mapping statement.in this the function DGA.R_VALUE already exits in the federated server(same as in source server)

CREATE FUNCTION MAPPING Galna FOR DGA.R_VALUE( INTEGER,INTEGER,INTEGER )
SERVER db2_rmm
OPTIONS (REMOTE_NAME 'udbts1.GET_r_VALUE' );

if we want to create function with AS TEMPLATE do we need to include the whole text of the function in the CREATE FUNCION statement?this is how i created(created with out any function text)
CREATE FUNCTION abc (id integer,no integer,type integer) RETURNS varchar(30) AS TEMPLATE NO EXTERNAL ACTION
when i tried to create function mapping with the CREATE MAPPING STATEMENT like the above one its not creating.it took long time so i just stoped that.i nthe first mapping statement the function was created i the federated database where as here i created the function as template.?
any suggestions plz?...
Thanks in advance...

I don't know if the DRDA wrapper actually supports the push-down of user-defined functions. You may want to give function templates a spin. Also, you may want to give DB2 V9 and/or V9.5 a try.

Some more details on function mappings can be found here: http://publib.boulder.ibm.com/infoce...n/c0005009.htm

In the worst case, you can't push-down your UDFs. Then you can work around this quite ofter by creating a view that incorporates the function call at the data source and then querying the view via a nickname. Not really nice - but works well.

p.s: Since you didn't provide any specifics on the various statements and the exact error messages, it's hard to say anything that may be helpful.

thanks stolze for you reply.
actually i am not very much concerned of this function mapping push down analysis.i thought it might be one of the reason for my slow response of the query.
when i run my query on the data source its taking me around 3 min.
and when i am running the same query on federated server its taking me around 9 min. (actually for some reason now the developers have to run on the federated server even though its possible for me to run on data source).
i just wonder why db2 wont try to do much of work on the data source and then ship the results to federated server.
what factors really matter for db2 to execute most of the query in data source(for example when only one table is present in the federated server and the remaining all tables present in source).if you have any document or link that explains about push down analysis in general could you please post that in this forum..
thanks a lot for your replies....

DB2 has a cost-based optimizer. Thus, it costs the different variations to execute the statement - with and without push-down of various query blocks - and then picks the plan that has the lowest costs. In order to get a good plan, it is necessary to have accurate statistics in the federated server about local data and also remote data. Those are the basics...

Another issue with federated is simply: DB2 can't push down a part of the query if the remote data source simply does not support the respective operation or if DB2 does not know how the remote data source supports it. If DB2 cannot push-down something for whatever reason, it will compensate at the federated server. Of course, this compensation can result in slower query response time compared to a complete execution at the remote data source. As a (not really good) example), let's assume that the data source cannot do a GROUP BY. So the federated server has to retrieve all qualifying rows from the data source, and perform the grouping and aggregation locally. If each group contains about 1 million rows, the federated server has to retrieve 1 million times more data from the remote data source than would have been necessary if the GROUP BY operation could have been pushed down.

Besides the DB2 manual, I am not aware of such a document. If something is missing in the manuals, please use the Feedback bottom in the Information Center to get any issues fixed in the manual - separate books are usually not a good idea for such stuff.