The AMP vprocs that participate are those containing rows pertaining to the input data provided to the method from the correlated table name specified prior to the table function in the SELECT statement. The assumption is that the input argument determines what to process. Each copy of the method will be called with the same input data repeatedly until the method returns with the no more data condition.
Although it might be possible to read additional data from outside the database in this mode, it probably will not work. This is because the participating AMP vprocs are determined based on where the selected database rows reside. It might not be on all AMP vprocs.
Use the following code excerpt as a guideline to implement your method using Tbl.getPhase():
public class local_ctx {
String xml_ctx;
public void local_ctx() {
xml_ctx = new String(50);
}
} ;
public class UDFExample {
public static void getStoreData( int storeData,
int[] storeNo,
int[] itemNo)
{
Tbl tbl = new Tbl();
int[] phase = new int[1];
local_ctx state_info;
if ( tbl.getPhase(phase) != Tbl.TBL_MODE_VARY )
{
/* set SQLSTATE to an error and return */
throw new SQLException("Wrong mode", "38U06");
return;
}
/* depending on the phase decide what to do */
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
state_info = new local_ctx();
...
break;
case Tbl.TBL_INIT:
/* Allocate scratchpad to retain data between iterations. */
tbl.allocCtx(state_info);
/* Preprocess data here. */
...
break;
case Tbl.TBL_BUILD:
/* Get scratchpad and build the result row here. */
state_info = (local_ctx)tbl.getCtxObject();
...
/* Or, if no more rows to build, set SQLSTATE to "02000". */
throw new SQLException("no more data", "02000");
...
break;
case Tbl.TBL_FINI:
/* Reset for the next set of data. */
...
break;
case Tbl.TBL_END:
/* Everyone done. */
...
break;
}
}
}
If your table UDF does not build a result row until after it receives all of the input rows for the AMP on which it runs, you can use Tbl.getPhaseEx() with the TBL_LASTROW option instead of Tbl.getPhase(). This will build the row during the TBL_BUILD_EOF phase. Use the following code excerpt as a guideline to implement your UDF using Tbl.getPhaseEx():
public class local_ctx {
String xml_ctx;
public void local_ctx() {
xml_ctx = new String(50);
}
} ;
public class UDFExample {
public static void getStoreData( int storeData,
int[] storeNo,
int[] itemNo)
{
Tbl tbl = new Tbl();
int[] phase = new int[1];
local_ctx state_info;
if ( tbl.getPhaseEx(phase, Tbl.TBL_LASTROW) != Tbl.TBL_MODE_VARY )
{
/* set SQLSTATE to an error and return */
throw new SQLException("Wrong mode", "38U06");
return;
}
/* depending on the phase decide what to do */
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
state_info = new local_ctx();
...
break;
case Tbl.TBL_INIT:
/* Allocate scratchpad to retain data between iterations. */
tbl.allocCtx(state_info);
/* Preprocess data here. */
...
break;
case Tbl.TBL_BUILD:
/* Get scratchpad and save data here. */
state_info = (local_ctx)tbl.getCtxObject();
...
/* Set SQLSTATE to "02000". */
throw new SQLException("no more data", "02000");
...
break;
case Tbl.TBL_BUILD_EOF:
/* Get scratchpad and build the result row here. */
state_info = (local_ctx)tbl.getCtxObject();
...
/* Or, if no more rows to build, set SQLSTATE to "02000". */
throw new SQLException("no more data", "02000");
...
break;
case Tbl.TBL_FINI:
/* Reset for the next set of data. */
...
break;
case Tbl.TBL_END:
/* Everyone done. */
...
break;
}
}
}
If you want to get a new row on each table UDF invocation, use Tbl.getPhaseEx() with the Tbl.TBL_NEWROW option. The following code excerpt shows an example of this usage.
import java.io.*;
import java.sql.*;
import com.teradata.fnc.*;
public class TableFunctions
{
static boolean debug = true;
public static void fnc_phase_new1(int in1, String in2, int[] out1, String[] out2)
throws SQLException, Exception
{
class GenCtx implements Serializable
{
public int count;
public GenCtx(){}
public GenCtx (int count)
{
this.count = count;
}
}
try
{
int [] phase = new int[1];
GenCtx obj;
Tbl tbl = new Tbl();
// Only ask for the phase on each row
int mode = tbl.getPhaseEx(phase, tbl.TBL_NOOPTIONS);
if (mode != Tbl.TBL_MODE_VARY) {
if (debug)
System.err.println("Table function being called in unsupported context");
throw new SQLException("Table function being called in unsupported context", "U0006");
}
if (debug) System.err.println("MODE =" + mode);
if (debug) System.err.println("Phase =" + phase[0]);
switch(phase[0])
{
case Tbl.TBL_PRE_INIT:
// Ask for a new row on each call to build
tbl.getPhaseEx(phase, tbl.TBL_NEWROW);
if (debug) System.err.println("Phase: TBL_PRE_INIT");
//Init and allocate the context
obj = new GenCtx();
tbl.allocCtx(obj);
tbl.setCtxObject(obj);
trace ("\n In Pre Init");
break;
case Tbl.TBL_INIT:
if (debug) System.err.println("Phase: TBL_INIT");
//Get the context
obj = (GenCtx)tbl.getCtxObject();
//set value and store
obj.count = 1;
tbl.setCtxObject(obj);
trace ("\n In Init");
break;
case Tbl.TBL_BUILD:
if (debug) System.err.println("Phase: TBL_BUILD");
//Get the context
obj = (GenCtx)tbl.getCtxObject();
if (debug){
System.err.println("Phase: TBL_BUILD getObject ="+obj+", count="+obj.count);
}
trace ("\n In Build, input 1 is " + in1);
out1[0] = in1;
out2[0] = in2;
if (debug)
System.err.println("Phase: TBL_BUILD in1"+in1+", in2="+in2+", out1[0]="+out1[0]+", out2[0]="+out2[0]);
break;
case Tbl.TBL_BUILD_EOF:
trace ("\n In Build_EOF");
if (debug) System.err.println("Phase: TBL_BUILD_EOF");
break;
case Tbl.TBL_FINI:
trace ("\n In FINI");
if (debug) System.err.println("Phase: TBL_FINISH");
break;
case Tbl.TBL_END:
trace ("\n In END");
if (debug) System.err.println("Phase: TBL_END");
break;
case Tbl.TBL_ABORT:
trace ("\n In ABORT");
if (debug) System.err.println("Phase: TBL_ABORT");
break;
default:
throw new SQLException("Entering default phase.", "U0006");
}
} catch (ClassNotFoundException e)
{
e.printStackTrace();
} catch(IOException e)
{
e.printStackTrace();
}
}
}