Using a Byte Array to Hold Data Between Iterations - 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™

For better performance, you can use a byte array to hold the data to retain between iterations in Java table UDFs.

The methods for allocating the context object, setting it and retrieving it are:

  • Tbl.allocCtx(int) without a return value.
  • Tbl.setCtxObject(byte[]) without a return value.
  • getCtxObject(byte[]) with a return value of byte[].

Note that you will have to provide methods for serializing and deserializing the context object accordingly.

The following is an example that uses a byte array to hold an integer value as the context object. The supporting methods byteArrayToInt(byte []) and intToByteArray(int) are provided as needed.

import com.teradata.fnc.Tbl;
import java.sql.*;

public class Measure

{
   public static final int byteArrayToInt(byte [] b)
   {
      return (b[0] << 24)
                      + ((b[1] & 0xFF) << 16)
                      + ((b[2] & 0xFF) << 8)
                      + (b[3] & 0xFF);
   }
   public static final byte[] intToByteArray(int value)
   {
      return new byte[] {
                     (byte)(value >>> 24),
                     (byte)(value >>> 16),
                     (byte)(value >>> 8),
                     (byte)value  };
   }

   public static void j_noop_table1_bytearray(double a, double[] c) throws SQLException
   {
      byte[] ctxByteArr = {0, 0, 0, 0}; // scratch pad
      int count;
      try
      {
         int [] phase = new int[1];
         Tbl tbl = new Tbl();
         int mode = tbl.getPhase(phase);
         if (mode!=Tbl.TBL_MODE_VARY) throw new SQLException("Wrong Mode", "U0001");
         switch(phase[0])

         {
            case Tbl.TBL_PRE_INIT:
               tbl.allocCtx(4); // 4-byte integer
               break;

            case Tbl.TBL_INIT:
               count = 1;
               ctxByteArr = intToByteArray(count);
               tbl.setCtxObject(ctxByteArr);
               break;

            case Tbl.TBL_BUILD:
               ctxByteArr = (byte[])tbl.getCtxObject(ctxByteArr);
               count = byteArrayToInt(ctxByteArr);
               if (count == 0) throw new SQLException("no more data", "02000");

               c[0] = a;

               count--;
               ctxByteArr = intToByteArray(count);
               tbl.setCtxObject(ctxByteArr);
               break;

            case Tbl.TBL_FINI:
            case Tbl.TBL_END:
            case Tbl.TBL_ABORT:
            default:
               break;
         }
      } catch (ClassNotFoundException e) {
         throw new SQLException("ClassNotFoundException", "U0002");
      } catch(IOException e){
         throw new SQLException("IOException", "U0003");
      }
   }
}