A constant mode table function is sent to all AMP vprocs and each copy is passed the same input arguments. Each copy is called repeatedly until the function finishes.
When designing a table function, determine whether all table function loop on all AMP vprocs to participate in the processing.
For example, a constant mode table function typically reads data from outside the database to produce result rows. If the external data is available on only one node, you may want only one function copy on one vproc. If the external data is available on each node, you may want the function to be able to read the data from all AMP vprocs.
| What You Want | What You Do |
|---|---|
| All copies of the table function to participate in the processing | Use the following code excerpt as a guideline to implement your function.FNC_Phase Phase;
if ( FNC_GetPhase(& Phase) != TBL_MODE_CONST)
{
/* set sqlstate to an error and return */
strcpy(sqlstate, "U0005");
return;
}
/* depending on the phase decide what to do */
switch(Phase)
{
case TBL_PRE_INIT:
{
break;
}
case TBL_INIT:
{
/* Open any files here. */
...
break;
}
case TBL_BUILD:
{
/* Read from files and build the result row here. */
/* On EOF, set sqlstate to "02000" (no row to build). */
...
break;
}
case TBL_END:
{
/* Everyone done. Close files. */
...
break;
}
case TBL_ABORT:
{
/* A copy called FNC_TblAbort. Close files. */
...
break;
}
}
|
| A table function that can run on any AMP and only needs one copy to participate | Call the FNC_TblFirstParticipant library function from all copies of the table function. The first copy to make the call is the copy that participates. All other copies must call FNC_TblOptOut and return. Use the following code excerpt as a guideline to implement your function: FNC_Phase Phase;
if ( FNC_GetPhase(& Phase) != TBL_MODE_CONST)
{
/* set sqlstate to an error and return */
strcpy(sqlstate, "U0005");
return;
}
/* depending on the phase decide what to do */
switch(Phase)
{
case TBL_PRE_INIT:
{
switch (FNC_TblFirstParticipant() )
{
case 1: /* participant */
return;
case 0: /* not participant */
if (FNC_TblOptOut())
strcpy(sqlstate, "U0006"); /* error */
return;
default: /* -1 or other error */
strcpy(sqlstate, "U0007");
return;
}
break;
}
case TBL_INIT:
{
/* Open any files here. */
...
break;
}
case TBL_BUILD:
{
/* Read from files and build the result row here. */
/* On EOF, set sqlstate to "02000" (no row to build). */
...
break;
}
case TBL_END:
{
/* Everyone done. Close files. */
...
break;
}
case TBL_ABORT:
{
/* A copy called FNC_TblAbort. Close files. */
...
break;
}
}
|
| One copy of the table function to be the controlling copy of all other copies running on all other AMP vprocs | Call the FNC_TblControl library function to designate a copy of the table function as the controlling copy. Distribute data to other table function copies by calling FNC_TblAllocCtrlCtx to allocate a control scratchpad. Use the following code excerpt as a guideline to implement your function. For the complete code example, see C Table Function. typedef struct {
unsigned short cntrl_fnc_AMP
int qfd;
...
} ctrl_ctx;
ctrl_ctx *options;
FNC_Phase Phase;
AMP_Info_t *LocalConfig;
if ( FNC_GetPhase(& Phase) != TBL_MODE_CONST)
{
/* Set sqlstate to an error and return. */
strcpy(sqlstate, "U0005");
return;
}
/* Depending on the phase decide what to do. */
switch(Phase)
{
case TBL_PRE_INIT:
{
LocalConfig = FNC_AMPInfo();
/* Run controlling copy on the lowest AMP on the node. */
if (LocalConfig->LowestAMPOnNode)
{
/* Run on the node that can access external file. */
if (access('filetoread')
{
if ( FNC_TblControl() )
{
/* Use scratchpad to distribute data to other */
/* function copies during the TBL_INIT phase. */
options = FNC_TblAllocCtrlCtx(sizeof(ctrl_ctx));
options->ctrl_fnc_AMP = LocalConfig->AMPId;
...
}
}
}
}
case TBL_INIT:
{
/* Get the data from the controlling copy. */
options = FNC_TblGetCtrlCtx();
...
break;
}
case TBL_BUILD:
{
/* Build result row or set sqlstate to "02000" if no data */
...
break;
}
case TBL_END:
{
/* Everyone done. Close files. */
/* Controlling copy must do extra cleanup. */
...
break;
}
case TBL_ABORT:
{
/* A copy called FNC_TblAbort. Close files. */
...
break;
}
}
|