17.10 - Requesting an 8-byte Activity Count - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

If you want the database to return 8-byte activity counts to the SQL requests from the external stored procedure, both the parent session and the default session have to request for it. The 8-byte activity count is returned in the Enhanced Statement Status (ESS) response parcel; therefore, you can request for an 8-byte activity count by requesting for the ESS parcel.

A parent session that is running from BTEQ (or most of the other SQL processors) requests the ESS parcel by default. The external stored procedure code must also request for the ESS parcel and handle this parcel in the SQL responses from the database.

The following sample code shows how you can code your external stored procedure to request for the ESS parcel and handle the ESS parcel in the responses from the database. The relevant code is shown in bold font.

/* Set DBCAREA options. */

static void
set_options(DBCAREA *dbcarea_pt)
{
   dbcarea_pt->change_opts = 'Y';
   ...
   dbcarea_pt->data_encryption= 'N';
   dbcarea_pt->statement_status = 'E'; 
   dbcarea_pt->create_default_connection = 'Y';
}

/* Fetch parcels and check activity type. */

static Int16
fetch_request(DBCAREA *dbcarea_pt,
              int stmnt_no,
              char *resultx,
              SQL_TEXT *error_message)
{
   ...
   struct CliSuccessType *SuccPcl;
   struct CliOkType *OKPcl;
   struct CliFailureType *FailPcl;
   struct CliStatementStatusType *EssPcl; 
   ...
   while (status == OK)
   {
      DBCHCL(&result, cnta, dbcarea_pt);
      count=1;
      if (result == REQEXHAUST) status = STOP;
      else if (result != EM_OK) status = FAILED;
      else
      {
         switch(dbcarea_pt->fet_parcel_flavor)
         {
            case PclSUCCESS :
               SuccPcl = 
                  (struct CliSuccessType *)dbcarea_pt->fet_data_ptr;
               memcpy(acc.acc_c, SuccPcl->ActivityCount, 4);
               sprintf(strptr, "[%d]Succ:Act(%d)|\0", stmnt_no,
                       SuccPcl->ActivityType);
               strcat(resultx, strptr);
               break;
            case PclOK :
               OKPcl = (struct CliOkType *)dbcarea_pt->fet_data_ptr;
               sprintf(strptr, "[%d]OK:Act(%d)|\0", stmnt_no,
                       OKPcl->ActivityType);
               strcat(resultx, strptr);
               break;
            case PclSTATEMENTSTATUS: 
                EssPcl = (struct CliStatementStatusType *) 
                         dbcarea_pt->fet_data_ptr; 
                sprintf(strptr, "[%d]ESS:Act(%d)|\0", stmnt_no, 
                       EssPcl->ActivityType); 
                strcat(resultx, strptr); 
                break; 
            case PclRECORD :
               memcpy(&item, dbcarea_pt->fet_data_ptr, 4);
               //cntx = item;
               break;
   ...
}