Hi Hans, Thank you for so detailed info timely! Actually, I’m looking for somewhere to save required column value. Not sure if Trafodion has. For example, I’d like to have the following result. Is there any solution? Table name: test Id name age 1 a 11 2 b 11 3 a 11 4 a 11 5 b 11 6 b 11
Want to have: select save(id) from test where name=’b’; 2 2,5 2,5,6 Best Regards, Kevin Xu From: Hans Zeller [mailto:[email protected]] Sent: Saturday, August 27, 2016 2:43 PM To: [email protected] Subject: Re: [help]How to execute a query in TMUDF Hi Kevin, At this time, using the "default" connection is only supported for stored procedures in Java, not for TMUDFs. I would recommend to just make a regular JDBC (or ODBC, if you use C++) connection from the UDF. Just make sure to close the connection, even in cases where an exception is thrown. Your UDF will run in one or more instances, but certainly not 100 for a small query. If needed, you can request to run the UDF in a single instance, through the optimizer interface<https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-Optional:Specifythedegreeofparallelism>., by setting the "function type" of the UDR to GENERIC. For the opposite, to get parallel execution, set the function type to MAPPER or REDUCER. This assumes that the UDF carries no state between rows (MAPPER) or partitions (REDUCERS). See methods UDRInvocationInfo.setFuncType()<https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#setFuncType(org.trafodion.sql.udr.UDRInvocationInfo.FuncType)> and UDR.describeDesiredDegreeOfParallelism()<https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDR.html#describeDesiredDegreeOfParallelism(org.trafodion.sql.udr.UDRInvocationInfo,%20org.trafodion.sql.udr.UDRPlanInfo)>. At runtime you can find out how many instances there are and what instance the caller is. See methods UDRInvocationInfo.getNumParallelInstances()<https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#getNumParallelInstances()> and UDRInvocationInfo.getMyInstanceNum()<https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#getMyInstanceNum()>. Let's say you request a single instance. Your methods will still be called from two processes: One process will invoke the compiler interface(s), the other will invoke the run time interface processData(). Hans On Fri, Aug 26, 2016 at 11:16 PM, Xu, Kai-Hua (Kevin) <[email protected]<mailto:[email protected]>> wrote: Hi all, Could anybody help? Thanks! How to execute a query in TMUDF? Don’t know how to get current connection. Another question is that “how many TMUDF instance it has in a query”. Assume there is a query “select udr(a()) from xx”, 100 rows in xx, that udf will be executed 100 times. How many instance of UDF class will be created? Best Regards, Kevin Xu
