A method that implements a constant mode table UDF is executed on all AMP vprocs and each copy is passed the same input arguments. Each copy is called repeatedly until the method indicates it is finished.
During the design of a table UDF, you must determine whether it makes sense for all methods on all AMP vprocs to participate in the processing.
For example, a typical constant mode table UDF most likely reads data from outside the database to produce result rows. If the external data is only available on one node, it might be practical to have only one method copy on one vproc do anything useful. On the other hand, if the external data is available on each node, then perhaps it can be read from all AMP vprocs.
| IF you want … | THEN … |
|---|---|
| all copies of the method to participate in the processing | use the following code excerpt as a guideline to implement your method.Tbl tbl = new Tbl();
int[] phase = new int[1];
if ( tbl.getPhase(phase) != Tbl.TBL_MODE_CONST )
{
/* 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:
{
break;
}
case Tbl.TBL_INIT:
{
/* Perform preprocessing or initializations here. */
...
break;
}
case Tbl.TBL_BUILD:
{
/* Read from files and build the result row here. */
/* On EOF, set SQLSTATE to "02000" (no row to build). */
...
break;
}
case Tbl.TBL_END:
{
/* Everyone done. */
...
break;
}
case Tbl.TBL_ABORT:
{
/* A copy called Tbl.abort(). */
...
break;
}
}
|
| a method that that can run on any AMP and only needs one copy to participate | call Tbl.firstParticipant() from all copies of the method. The first copy to make the call is the copy that participates. All other copies must call Tbl.optOut() and return. Use the following code excerpt as a guideline to implement your method: Tbl tbl = new Tbl();
int[] phase = new int[1];
if ( tbl.getPhase(phase) != Tbl.TBL_MODE_CONST )
{
/* 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:
{
if (tbl.firstParticipant()) {
return; /* participant */
} else {
if (!tbl.optOut()) { /* not a participant */
throw new SQLException("Opt out failure", "38U06");
}
return;
}
break;
}
case Tbl.TBL_INIT:
{
/* Perform preprocessing or initializations here. */
...
break;
}
case Tbl.TBL_BUILD:
{
/* Read from files and build the result row here. */
/* On EOF, set SQLSTATE to "02000" (no row to build). */
...
break;
}
case Tbl.TBL_END:
{
/* Everything is done. */
...
break;
}
case Tbl.TBL_ABORT:
{
/* A copy called Tbl.abort(). */
...
break;
}
}
|
| one copy of the method to be the controlling copy of all other copies running on all other AMP vprocs | call Tbl.control() to designate a copy of the table UDF as the controlling copy. Distribute data to other copies by calling Tbl.setCtrlCtx(). Use the following code excerpt as a guideline to implement your method. class ctrl_ctx implements Serializable {
int ctrl_AMP;
int qfd;
...
} ;
public class UDFExample {
public static void getStoreData(int storeData,
int[] storeNo,
int[] itemNo)
{
ctrl_ctx options;
Tbl tbl = new Tbl();
int[] phase = new int[1];
if ( tbl.getPhase(phase) != Tbl.TBL_MODE_CONST )
{
/* 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:
AMPInfo localCfg = new AMPInfo();
/* Run controlling copy on lowest AMP on node. */
if (localCfg.lowestAMPOnNode())
{
/* Run on node that can access external file. */
...
if ( tbl.control() )
{
/* Use scratchpad to distribute data to */
/* copies during the Tbl.TBL_INIT phase. */
options.ctrl_AMP = localCfg.getAMPId();
tbl.setCtrlCtx(options);
...
}
}
break;
case Tbl.TBL_INIT:
/* Get the data from the controlling copy. */
options = tbl.getCtrlCtx();
...
break;
case Tbl.TBL_BUILD:
/* Build result row or set SQLSTATE */
/* to "02000" if no data. */
...
break;
case Tbl.TBL_END:
/* Everyone done. */
...
break;
case Tbl.TBL_ABORT:
/* A copy called Tbl.abort(). */
...
break;
}
}
}
|