Home Page for the TeradataForum
 

 

UDF Library: 'stat_values'

Component: 'stat_values.c'


 
<< stat_values_howto.txt stat_values.h >>

#pragma pack(1)
/*-------------------------------------------------------------------
** s t a t _ v a l u e s
** This is a Table UDF that converts FieldStatistics to a group of
** ordinary columns and returns them as a table.
**
** 2008-04-28  G. Rommel: Initial version. Based on Dieter N�th's
**   statistics view as of 2007-05-22.
** 2008-05-14  G. Rommel: Added support for TD12 (stats vsn 3).
**-----------------------------------------------------------------*/
#define SQL_TEXT Latin_Text
#include 
#include 

typedef unsigned char Byte;

struct stats_common {
 Byte     year_yy;
 Byte     year_cc;
 Byte     month;
 Byte     day;
 Byte     hour;
 Byte     minute;
 Byte     seconds;
 Byte     centiseconds;
 short    stats_version;
 Byte     sample_flag;
 Byte     sample_pct;
 double   num_nulls;
 Byte     fill001[2];
 Byte     numeric_flag;
 Byte     fill002[1];
  /* total length = 24 bytes */
};

struct stats_v02 {
 struct stats_common  common;
 Byte     fill003[16];
 double   d_offset40;
 double   d_offset48;
 double   d_offset56;
 double   d_offset64;
 double   d_offset72;
 double   d_offset80;
};

struct stats_v03 {
 struct stats_common  common;
 Byte     fill003[32];
 unsigned short    n_amps;
 double   d_offset58;
 double   d_offset66;
 double   d_offset74;
 double   d_offset82;
 double   d_offset90;
 double   d_offset98;
 double   d_offset106;
 double   d_offset114;
};

#define DB_NULL  -1
#define DB_NOTNULL  0

/* Scratchpad. On each call to the function, we need to return only
   one row, but the engine will call us in TBL_BUILD phase until we
   tell it to stop. The scratchpad holds a flag that says whether
   we have returned our row or not. */
#define SCRPAD_LEN  16
struct stat_scrpad {
  short     row_returned;
};

/*-------------------------------------------------------------------
** The following macro converts the FieldStatistics bytes to
** the desired row. This is the same for both CONST and VARY modes.
**-----------------------------------------------------------------*/
#define convert_row() \
    if ( my_scrpad->row_returned ) { \
        /* Row has already been returned */ \
       strcpy(sqlstate, "02000"); \
    } else { \
       if (*ident_i == DB_NULL) { \
          *res_ident = 0; \
          *res_ident_i = DB_NULL; \
       } else { \
          *res_ident = *ident; \
          *res_ident_i = DB_NOTNULL; \
       } \
       if (*FieldStats_i == DB_NULL) { \
          *res_collectdate_i = DB_NULL; \
          *res_collecttime_i = DB_NULL; \
          *res_statsversion_i = DB_NULL; \
          *res_samplesize_i = DB_NULL; \
          *res_numrows_i = DB_NULL; \
          *res_numvalues_i = DB_NULL; \
          *res_numnulls_i = DB_NULL; \
          *res_modefreq_i = DB_NULL; \
          my_scrpad->row_returned = 1; \
          strcpy(sqlstate, "00000"); \
          return; \
       } \
 \
       common_ptr = (struct stats_common *) FieldStats->bytes; \
       v02_ptr = (struct stats_v02 *) FieldStats->bytes; \
       v03_ptr = (struct stats_v03 *) FieldStats->bytes; \
 \
       *res_collectdate = \
         (((common_ptr->year_cc * 256) + common_ptr->year_yy - 1900) \
            * 10000) \
            + (common_ptr->month * 100) + common_ptr->day; \
       res_collecttime->hour   = common_ptr->hour; \
       res_collecttime->minute = common_ptr->minute; \
       workint = common_ptr->seconds * 1000000; \
       res_collecttime->seconds = (DECIMAL4)  workint; \
 \
       *res_statsversion = common_ptr->stats_version; \
        /* Sample size */ \
       if (common_ptr->sample_flag == 0x00) \
          *res_samplesize = 100; \
       else \
          *res_samplesize = common_ptr->sample_pct; \
       *res_numnulls = common_ptr->num_nulls; \
       if (common_ptr->stats_version == 2) { \
          *res_numamps = 0;  /* Field not available */ \
          *res_numamps_i = DB_NULL; \
          if (common_ptr->numeric_flag == 0x00) { \
             *res_numrows = v02_ptr->d_offset72; \
             *res_numvalues = v02_ptr->d_offset64; \
             *res_modefreq = v02_ptr->d_offset56; \
          } else { \
             *res_numrows = v02_ptr->d_offset56; \
             *res_numvalues = v02_ptr->d_offset48; \
             *res_modefreq = v02_ptr->d_offset40; \
          } \
       } else { \
          *res_numamps = v03_ptr->n_amps; \
          *res_numamps_i = DB_NOTNULL; \
          if (common_ptr->numeric_flag == 0x00) { \
             *res_numrows = v03_ptr->d_offset106; \
             *res_numvalues = v03_ptr->d_offset98; \
             *res_modefreq = v03_ptr->d_offset90; \
          } else { \
             *res_numrows = v03_ptr->d_offset90; \
             *res_numvalues = v03_ptr->d_offset82; \
             *res_modefreq = v03_ptr->d_offset74; \
          } \
       } \
 \
       *res_collectdate_i = DB_NOTNULL; \
       *res_collecttime_i = DB_NOTNULL; \
       *res_statsversion_i = DB_NOTNULL; \
       *res_samplesize_i = DB_NOTNULL; \
       *res_numrows_i = DB_NOTNULL; \
       *res_numvalues_i = DB_NOTNULL; \
       *res_numnulls_i = DB_NOTNULL; \
       *res_modefreq_i = DB_NOTNULL; \
 \
       my_scrpad->row_returned = 1; \
       strcpy(sqlstate, "00000"); \
    }


void stat_values (
    /*--- Inputs */
  INTEGER  *ident,      /* Unique row identifier supplied by user */
  VARBYTE  *FieldStats, /* FieldStatistics column */
    /*--- Results */
  INTEGER    *res_ident,  /* Echo the row identifier */
  DATE       *res_collectdate,
  ANSI_Time  *res_collecttime,
  SMALLINT   *res_statsversion,
  SMALLINT   *res_samplesize,
  INTEGER    *res_numamps,
  FLOAT      *res_numrows,
  FLOAT      *res_numvalues,
  FLOAT      *res_numnulls,
  FLOAT      *res_modefreq,
    /*--- Input indicators */
  int  *ident_i,
  int  *FieldStats_i,
    /*--- Result indicators */
  int  *res_ident_i,
  int  *res_collectdate_i,
  int  *res_collecttime_i,
  int  *res_statsversion_i,
  int  *res_samplesize_i,
  int  *res_numamps_i,
  int  *res_numrows_i,
  int  *res_numvalues_i,
  int  *res_numnulls_i,
  int  *res_modefreq_i,
  char sqlstate[6],
  SQL_TEXT fncname[129],
  SQL_TEXT sfncname[129],
  SQL_TEXT error_message[257] )
{
FNC_Phase   Phase;
FNC_Mode    Mode;
int         offset, workint;
struct stat_scrpad * my_scrpad;
struct stats_common   * common_ptr;
struct stats_v02      * v02_ptr;
struct stats_v03      * v03_ptr;
void *   stats_ptr;

Mode = FNC_GetPhase(&Phase);

switch (Mode)
{
/*-------------------------------------------------------------------
** CONSTANT MODE
** Since the FieldStatistics string contains all the data we need,
** we don't have to pass data from one phase to another, and we can
** do it on one AMP. All work is done in the BUILD phase.
**-----------------------------------------------------------------*/
 case TBL_MODE_CONST:
    switch(Phase)
    {
     case TBL_PRE_INIT:
         /* We only need one AMP, so if this copy is not the
            first participant, bail out. */
        if (FNC_TblFirstParticipant() != 1) {
           FNC_TblOptOut();
           return;
        }

        my_scrpad = FNC_TblAllocCtx(SCRPAD_LEN);
        if (my_scrpad == NULL) {
           strcpy(sqlstate, "U0002");
           strcpy(error_message, "Unable to allocate scratchpad");
           return;
        }
        my_scrpad->row_returned = 0;
        return;
     case TBL_INIT:
        return;
     case TBL_BUILD:
        my_scrpad = FNC_TblGetCtx();
        convert_row();
        return;
     case TBL_END:
     case TBL_ABORT:
        return;
    }
    break;

/*-------------------------------------------------------------------
** VARYING MODE
** In this case, each AMP gets a set of rows to work on. We process
** each row once. In TBL_INIT, we have received a new row from the
** DBMS; in TBL_BUILD, we process that row.
**-----------------------------------------------------------------*/
 case TBL_MODE_VARY:
    switch(Phase)
    {
     case TBL_PRE_INIT:
        my_scrpad = FNC_TblAllocCtx(SCRPAD_LEN);
        if (my_scrpad == NULL) {
           strcpy(sqlstate, "U0002");
           strcpy(error_message, "Unable to allocate scratchpad");
           return;
        }
        my_scrpad->row_returned = 0;
        return;
     case TBL_INIT:
         /* The engine has sent us a new row. */
        my_scrpad = FNC_TblGetCtx();
        my_scrpad->row_returned = 0;
        return;
     case TBL_BUILD:
        my_scrpad = FNC_TblGetCtx();
        convert_row();
        return;
     case TBL_END:
     case TBL_ABORT:
        return;
    }
    break;
 default: /* not CONST or VARY */
    strcpy(sqlstate, "U0001");
    sprintf(error_message, "Invalid table function mode %d", Mode);
}
return;
}


<< stat_values_howto.txt stat_values.h >>





 
  Top Home Privacy Feedback  
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky
Copyright 2016 - All Rights Reserved
Last Modified: 28 Jun 2020