- If possible, it is recommended to use the inline method to transfer LOB data between Teradata PT and the database because this method gives better performance, especially when multiple instances of the Inserter operator are used in the loading job.
However, there is a drawback in using the inline method, which is that the entire data row cannot be larger than 1000000 bytes, which is the current row-size limit imposed by the database.
- When a massive amount of data (may be 1,000,000 rows or more) is loaded in a database table and the data row size is not larger than 1000000 bytes, the user may consider loading BLOBs as VARBYTEs and CLOBs as VARCHAR. The database can perform the trivial data conversion of VARBYTE to BLOB and VARCHAR to CLOB. That allows Teradata PT to use the Export operator to extract LOB data (defined to it as VARBYTE or VARCHAR columns) and the Load or Update operator to load LOB data (defined to it as VARBYTE or VARCHAR columns) at high speed.
- If you want to load BLOB or CLOB data into a table as VARBYTE or VARCHAR, the following needs to be done in a Teradata PT job script:
- BLOB columns must be defined as VARBYTE.
- CLOB columns must be defined as VARCHAR.
- Use the CAST function in the SQL SELECT statement specified in the SelectStmt attribute to explicitly convert BLOB to VARBYTE and CLOB to VARCHAR if data is extracted from a database table.
- When the SQL Selector operator extracts deferred LOB data from a database table and the SQL Inserter loads it into another database table, the LOB data files associated with each row are deleted after the row has been inserted successfully. By the end of the loading job, all LOB data files that the SQL Selector operator created are deleted.
- When a Teradata PT job extracts LOB data from a database table using the deferred method and writes it to an external target, the LOB data files associated with each record written to the external target will not be deleted at the end of the job.
- For a job loading LOB data into a database table from an external file (non-database source), a user can define both inline and deferred LOB data types in the same schema. The job script uses the DataConnector operator as producer and Inserter operator as consumer. Thus, the following schema is valid for the job:
col1 CLOB(2000), col2 BLOB(2000), col3 CLOB(75000) AS DEFERRED BY NAME, col4 BLOB(75000) AS DEFERRED BY NAME
If the job script reads LOB data from a database table, the above schema is not valid. A schema for this extraction scenario cannot contain both inline and deferred LOB columns.