Requesting an 8-byte Activity Count - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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;
   ...
}