User Considerations
Users should consider the follow:
If possible, it is recommended to use the inline method to transfer LOB data between
Teradata PT and Teradata 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 64000 bytes, which is the current row-size limit imposed
by the Teradata PT data stream implementation.
When a massive amount of data (may be 1, 000, 000 rows or more) is loaded in a Teradata
Database table and the data row size is not larger than 64 000 bytes, the user may
consider loading BLOBs as VARBYTEs and CLOBs as VARCHARs. Teradata 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 Teradata table.
When the SQL Selector operator extracts deferred LOB data from a Teradata Database
table and the SQL Inserter loads it into another Teradata 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 Teradata 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 Teradata Database table from an external file (non-Teradata
source), a user can define both inline and deferred LOB data types in the same schema.
The job script uses the Data Connector 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
Note: If the job script reads LOB data from a Teradata table, the above schema is not valid.
A schema for this extraction scenario cannot contain both inline and deferred LOB
columns.