16.20 - Design Considerations - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

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 indicates it is finished.

During the design of a table function, you must determine whether it makes sense for all table function loops on all AMP vprocs to participate in the processing.

For example, a typical constant mode table function 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 function 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 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;
  }
}