Thanks Hans! Very helpful! I’ll have a try later. Best Regards, Kevin Xu
From: Hans Zeller [mailto:[email protected]] Sent: Monday, August 29, 2016 11:34 AM To: [email protected] Subject: Re: [help]How to execute a query in TMUDF Hi Kevin, yes, this is similar to the "group concatenation" UDF we have on the Trafodion code examples page<https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Code+Examples>. I hope that example will help with what you would like to do. The group concatenation UDF emits only one row per group. In your case, you would need to emit one output row for every input row. I would recommend to use the PARTITION BY clauses in the example UDF. That can help if you want to have multiple groups, not just one as in your example query. The ORDER BY will help making sure that the id values appear in order. Hans On Sat, Aug 27, 2016 at 12:25 AM, Xu, Kai-Hua (Kevin) <[email protected]<mailto:[email protected]>> wrote: 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]<mailto:[email protected]>] Sent: Saturday, August 27, 2016 2:43 PM To: [email protected]<mailto:[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
