Moving LOB Data from one Teradata Database Table to Another - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2436
lifecycle
previous
Product Category
Teradata Tools and Utilities

Moving LOB Data from one Teradata Database Table to Another

To move LOB data from one Teradata Database table to another, a Teradata PT script must use:

  • The SQL Selector operator to extract LOB data from a source table, and
  • The SQL Inserter operator to load the data to a target table.
  • Using the Inline Method

    If you want to use the inline method for the job, you must define the LOB columns as BLOB (lengthBytes) or CLOB (lengthBytes), according to their data content, in the schema.

    Using the Deferred Method

    If you want to use the deferred method for the job, you must define the LOB columns as BLOB (lengthBytes) AS DEFERRED BY NAME or CLOB (lengthBytes) AS DEFERRED BY NAME, according to their data content, in the schema.

    You should be aware of the following events during the job run:

  • The SQL Selector operator retrieves data from deferred LOB columns and writes the data to external data files. These data files reside temporarily under the directory specified in LobDirectoryPath attribute or under the current working directory if the LobDirectoryPath attribute is not used.
  • One data file represents data for one LOB column.
  • These LOB data files have formulaic names as follows:
  • <file-basename>_<column-name>_<job-id>_p<#>_r<#>.<file-extension>

    where

  • <file-basename> is the value specified in the LobFileBaseName attribute.
  • <column-name> is the column name defined in a source table.
  • <job-id> is the job name from the tbuild command line.
  • <#> is an integer number generated internally by Teradata PT
  • <file-extension> is the value specified in the LobFileExtension attribute.
  • These LOB data files will be deleted immediately after their data has been inserted into the target table.

    For a sample Teradata PT script in the sample/userguide directory:

    uguide18.txt: Exporting BLOB/CLOB Data from One Teradata Database Table and Loading It into Another.