Allocating and Initializing Storage - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

During the Phase.AGR_INIT aggregation phase, a UDF must call the initCtx() method on the context input argument to allocate and initialize the intermediate storage area based on the object or byte array that the method needs to store.

The UDF cannot request more memory than the value of interim_size in the CLASS AGGREGATE clause of the CREATE FUNCTION statement. For best performance, allocate only enough memory to satisfy the needs of the UDF for intermediate storage.

If the method stores a byte array in the intermediate storage area, the size of the intermediate storage area can be determined by the total number of bytes required. For the standard deviation example, the total number of bytes required is 24, enough for 3 double values.

Alternatively, if the method stores an object in the intermediate storage area, the memory required can be computed as follows.

public static int getSize(Object obj){
   int size=0;
   try{
      ByteArrayOutputStream barr = new ByteArrayOutputStream();
      ObjectOutput s = new ObjectOutputStream(barr);
      s.writeObject(obj);
      s.close();
      size=barr.toByteArray().length;
      System.out.println("obj="+obj+",size="+size);

   }catch(IOException e){
      e.printStackTrace();
   }
   return size;
}

For the standard deviation example, the following code initializes an agr_storage object and allocates intermediate storage for the object:

agr_storage s1 = new agr_storage(0,0,0);
context[0].initCtx(s1);

Alternatively, for a method that uses a byte array to store intermediate results, the code looks like this:

context[0].initCtx(24);