17.00 - 17.05 - 例: Clobを使用して中間記憶域のデータを保持するテーブルUDF - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1147-170K-JPN
Language
日本語 (日本)

このサンプル コードでは、テーブルclob_tblについて見ていきます。定義とデータは次のとおりです。

CREATE TABLE clob_tbl(id INTEGER,
   clob_column_2 CLOB(20)) PRIMARY INDEX(id);

INSERT clob_tbl(1, 'First 3 New Rows');
INSERT clob_tbl(2, 'Second 3 New Rows');

テーブル関数ClobTblは次のようにして作成されています。

CALL SQLJ.INSTALL_JAR('CJ!ClobFunctions.jar', 'ClobFunctions',0);

REPLACE FUNCTION ClobTbl(in_parm CLOB(20))
   RETURNS TABLE (id   INTEGER,

                  clobcontent  VARCHAR(20))
   LANGUAGE JAVA
   NO SQL
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'ClobFunctions:ClobFunctions.ClobTbl';

関数ClobTbl()のソース コードは次のとおりです。次のコメント部分は、コードの中で説明する部分を示しています。

コメント 詳細
/** PROBLEM **/
   ...
/** END PROBLEM **/
これらのコメントは、local_ctx_clobクラスのオブジェクトであるclob_infoのインスタンスを作成し、allocCtx(clob_info)を呼び出して記憶域を割り当てるコードを分離しています。メソッドallocCtx()はclob_infoをバイナリ ストリームにシリアライズし、バイナリ ストリームと同じサイズの記憶域を予約します。クラスlocal_ctx_clobにはjava.sql.Clob[]型の配列があります。ただし、TBL_PRE_INITフェーズでは、java.sql.Clob[]配列にあるすべてのエントリはデフォルトでNULLになります。このため、allocCtx()の呼び出しによりclob_infoがシリアライズされると、シリアライズするjava.sql.Clobオブジェクトは無くなります。これにより、必要以上に細かくシリアライズされたバイナリ ストリームが作成されます。このようにして、割り当てられた記憶域も必要以上に小さくなります。その後、TBL_INITフェーズで最初のlobデータが処理されると、UDFが予約された記憶域にある以上のデータを書き出そうとするため、Tbl.setCtxObject()を呼び出すとエラーになります。
/** SOLUTION **/
   ...
/** END SOLUTION **/
これらのコメントは、問題を解決するコードを分離します。このコードは各item_clobオブジェクトをチェックし、メンバーのmyclobがNULLの場合には、ダミーのバイト アレイをシリアライズされたバイナリ ストリームに書き出して、後で利用する際に十分に大きなサイズになるようにします。ダミーのバイト アレイのサイズは64です。このサイズは、完全にシリアライズされたTeradataのjava.sql.Clobとjava.sql.Blobオブジェクトのサイズです。
import java.io.*;
import java.sql.*;
import com.teradata.fnc.*;

class item_clob implements Serializable
{
   int id;
   java.sql.Clob myclob;

   private void writeObject(java.io.ObjectOutputStream out)
   throws IOException
   {
      byte[] dummy = new byte[64];
      out.defaultWriteObject();
      /************************* SOLUTION *************************/
      /* Allocate storage for lob by writing the dummy bytes  */
      /* into its serialized binary stream if myclob is null. */
      if (myclob == null)
         out.write(dummy);
      /*********************** END SOLUTION ***********************/
   }
   private void readObject(java.io.ObjectInputStream in)
   throws IOException, ClassNotFoundException
   {
      in.defaultReadObject();
   }
}

class local_ctx_clob implements Serializable
{
   int Num_Row;
   int Cur_Row;
   item_clob[] Clob_List;
   public local_ctx_clob(){}
   public local_ctx_clob(int NR, int CR, item_clob[] Clob_List)
   {
      this.Num_Row=NR;
      this.Cur_Row=CR;
      this.Clob_List=Clob_List;
   }
}

public class ClobFunctions
{
   static int cnt = 0;

   /* ClobTbl uses each lob value in clob_tbl.clob_column_2 */
   /* to generate 3 new rows for the return table.          */
   public static void ClobTbl( java.sql.Clob  in_parm, int[]  id,
                               String[] clobcontent )
   throws SQLException
   {
      local_ctx_clob clob_info;
      int[]          phase = new int[1];
      Tbl            tbl = new Tbl();

      try
      {
         switch (tbl.getPhase(phase))
         {
            case Tbl.TBL_MODE_CONST:
               throw new SQLException("not for const mode", "U0006");
            case Tbl.TBL_MODE_VARY:
               switch(phase[0])
               {
                  case Tbl.TBL_PRE_INIT:
                  {
                     /******************* PROBLEM *******************/
                     /* Allocate storage space to hold intermediate */
                     /* data which is used to generate 3 new rows.  */
                     item_clob[] clob_arr = new item_clob[3];
                     for( int i=0; i<clob_arr.length; i++)
                        clob_arr[i]=new item_clob();
                     clob_info = new local_ctx_clob(3, 0, clob_arr);
                     tbl.allocCtx(clob_info);
                     /***************** END PROBLEM *****************/
                  }
                  break;

                  case Tbl.TBL_INIT:
                  {
                     /* Each lob passed in is stored in an       */
                     /* intermediate structure that is used to   */
                     /* construct 3 new rows in TBL_BUILD phase. */
                     item_clob[]    clob_arr = new item_clob[3];
                     for( int i=0; i<clob_arr.length; i++) {
                        clob_arr[i] = new item_clob();
                        clob_arr[i].myclob = in_parm;
                        if (in_parm != null) clob_arr[i].id = cnt++;
                     }
                     clob_info = new local_ctx_clob(3, 0, clob_arr);
                     /* Store the intermediate data */
                     tbl.setCtxObject(clob_info);
                  }
                  break;

                  case Tbl.TBL_BUILD:
                  {
                     /* Get intermediate data stored in TBL_INIT */
                     /* phase and use it to generate 3 rows */
                     clob_info = (local_ctx_clob)tbl.getCtxObject();
                     if ( clob_info.Cur_Row >= clob_info.Num_Row ||
                        clob_info.Clob_List[0].myclob == null) {
                        throw new SQLException("no more data","02000");
                     } else {
                        id[0] =
                           clob_info.Clob_List[clob_info.Cur_Row].id;
                           Reader R =                            clob_info.Clob_List[clob_info.Cur_Row].myclob.getCharacterStream();
                           char[] chars = new char[20];
                           R.read(chars);
                           R.close();
                           clobcontent[0] = (new String(chars)).trim();
                           clob_info.Cur_Row++;
                     }
                     tbl.setCtxObject(clob_info);
                  }
                  break;

                  case Tbl.TBL_FINI:
                     clob_info = (local_ctx_clob)tbl.getCtxObject();
                     clob_info.Cur_Row = 0;
                     clob_info.Num_Row = 0;
                     clob_info.Clob_List= null;
                  break;

                  case Tbl.TBL_END:
                     clob_info = (local_ctx_clob)tbl.getCtxObject();
                  break;

                  case Tbl.TBL_ABORT:
                     clob_info = (local_ctx_clob)tbl.getCtxObject();
                  break;

               }
               return;
         }
      }catch(ClassNotFoundException e){
            throw new SQLException("Class not located Error", "U0006");
      }catch(StreamCorruptedException e){
            throw new SQLException("Stream Corrupt Error", "U0006");
      }catch(IOException e){
            e.printStackTrace();
      }
   }
}	

テーブルclob_tblの内容は次のとおりです。

BTEQ -- Enter your DBC/SQL request or BTEQ command:

sel * from clob_tbl;

*** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         id  clob_column_2
-----------  --------------------
          1  First 3 New Rows
          2  Second 3 New Rows

ソリューション コードを指定せずにClobTbl()を呼び出すと、エラーになります。

BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT new_tbl.id, new_tbl.clobcontent
        FROM TABLE (ClobTbl(clob_tbl.clob_column_2)) AS new_tbl
        ORDER by new_tbl.id;

*** Failure 7828 Unexpected Java Exception SQLSTATE 38000: An
java.lang.Error (Tbl.setCtxObject failed because object was larger
than general scratchpad.) exception was thrown.
                Statement# 1, Info =0
 *** Total elapsed time was 3 seconds.

ソリューション コードを指定してClobTbl()を呼び出すと、正しい答えが得られます。

BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT new_tbl.id, new_tbl.clobcontent
        FROM TABLE (ClobTbl(clob_tbl.clob_column_2)) AS new_tbl
        ORDER by new_tbl.id;

 *** Query completed. 6 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         id  clobcontent
-----------  --------------------
          0  First 3 New Rows
          1  First 3 New Rows
          2  First 3 New Rows
          3  Second 3 New Rows
          4  Second 3 New Rows
          5  Second 3 New Rows