16.10 - Importing Large Data Objects - Basic Teradata Query

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.10
created_date
May 2017
category
Programming Reference
featnum
B035-2414-057K

LDOs (Large Data Objects) consist of CLOB, BLOB, XML, and JSON data types. LDOs can be sent from BTEQ to the database either inline, as deferred transfers, or as non-deferred transfers. How they are sent is dependent upon the use of the DEFERCOLS, TOTCOLS, and DEFERLIMITS options.

The following bulleted sections compare how LDOs can be transferred:

LDOs for Inline Transfer
  • Are not described with AS DEFERRED or AS DEFERRED BY NAME qualifiers in the SQL request's USING clause.
  • Are sent up front to the database along with the SQL request. Their actual value is stored in the main import data file along with non-LDO column values.
  • Are limited by FastLoad format's overall length indicator, less if more than one column is being transferred for each import record. The overall length indicator is limited to 64KB or 1MB, depending upon the current import RECORDLENGTH setting. Each LDO must still have an 8-byte (rather than 2-byte) length indicator present in the import record before their value. Otherwise, an alternate data type which requires a 2-byte length indicator must be used in the USING clause. For example, VARCHAR rather than CLOB. .
  • Do not require use of any transfer-related IMPORT command options but can only be provided using DATA or INDICDATA for the FastLoad format option.
LDOs for Deferred Transfer
  • Are described with AS DEFERRED or AS DEFERRED BY NAME qualifiers in the SQL request's USING clause.
  • Have their data stored in their own separate LDO files. The names for these files must be placed in the main import data file using a VARCHAR type value. The maximum size for an LDO file name is 1024 bytes. Each LDO gets separately elicited by the database after the SQL request has been received.
  • Are limited to 2GB size.
  • For AS DEFERRED columns, the DEFERCOLS (and possibly TOTCOLS) option need to be used. No transfer-related IMPORT command option need be used for AS DEFERRED BY NAME columns.
LDOs for Possible Non-Deferred Transfer
  • Can only be described with an AS DEFERRED qualifier and must be listed first in the SQL request's USING clause.
  • Have their data stored in their own separate LDO files. The names for these files must be placed in the main import data file using a VARCHAR type value. The maximum size for an LDO file name is 1024 bytes. LDOs will be sent up front to the database along with the SQL request, rather than being separately elicited by the database after the SQL request has been received, assuming sufficient space is available in the import request and the DEFERLIMITS values have not been exceeded. This reduces the number of parcels being sent between BTEQ and the database, which may improve performance.
  • The combined size of non-deferred LDOs and non-LDO data is limited to approximately 1MB per import request.
  • The combination of the DEFERCOLS option and the DEFERLIMITS  import setting, or the combination of the DEFERCOLS option and the PACK factor dictates which LDOs will be transferred non-deferred.   The TOTCOLS option must be specified if using the INDICDATA format.

A single import can include LDOs which are both defined with AS DEFERRED and AS DEFERRED BY NAME qualifiers. When this is the case, do not include the AS DEFERRED BY NAME columns in the DEFERCOLS value. For example, if an import includes three AS DEFERRED columns and two AS DEFERRED BY NAME columns, use DEFERCOLS=3.

LDOs defined with an AS DEFERRED qualifier must be listed first within the USING clause. AS DEFERRED BY NAME columns may occur anywhere in the USING clause after the AS DEFERRED columns.

BTEQ supports deferred and non-deferred LDO imports with the DATA, INDICDATA, and VARTEXT formats, but not the REPORT format. If exporting LDOs, deferred and non-deferred exports are only allowed with the INDICDATA format.

When using LDOs defined with the AS DEFERRED qualifier, BTEQ replaces VARCHAR file name values with 4-byte integer tokens before submitting the request to the database. When a single-byte file name is supplied, three bytes (two for the length of the VARCHAR value and one for the file name value) will get replaced by four bytes. This replacement means that the overall size of the import data record might become too large to successfully contain all generated tokens.

For deferred and non-deferred LDO imports using z/OS BTEQ, import data records must contain fully qualified dataset names, which can be sequential data sets or PDS members, such as USERID.CLOB.DATA1 or USERID.CLOB(DATA).

Non-deferred LDO transfers (via the DEFERLIMITS option) may change the effectiveness of packed records, when the PACK setting is greater than one. Non-deferred LDOs are passed to the database along with packed records, whereas deferred LDOs are sent at a later time. However, importing small LDOs via non-deferred transfers may offer better performance compared with deferred transfers, as determined by elapsed times. Some testing/tuning may be needed to determine the optimal way to import records containing LDOs. If performance is negatively affected, non-deferred transfers can be completely disabled by entering "DEFERLIMITS=0,0".

The following table shows which DEFERLIMITS values will be used in relation to the PACK setting:

PACK Setting DEFERLIMITS Option (default or specified) Actual Records Packed Maximum Bytes for an Individual LDO Cumulative LDO Bytes for a Record
0 or 1 Default (on) 1 1,000 10,000
0 or 1 User specified 1 User defined User defined
2+ Default (off) 1..PACK 0 0
2+ User specified 1..PACK User defined User defined

Using the DEFERLIMITS option does not guarantee that an LDO will be sent as a non-deferred transfer. It depends upon the arguments supplied, the size of the LDO files, and whether the LDO columns are define with AS DEFERRED or AS DEFERRED BY NAME within the USING clause.

Following is an example showing how this works. Assume 6 CLOBs are listed in a USING clause and 3 records will be imported. The DEFERLIMITS option is set to "2000,5000". Notice how BTEQ treats each LDO based on the USING clause qualifier used and the LDO size.

Record 1
   AS DEFERRED          (1000 bytes)   Non-deferred
   AS DEFERRED          (500 bytes)    Non-deferred
   AS DEFERRED          (1000 bytes)   Non-deferred
   AS DEFERRED          (500 bytes)    Non-deferred
   AS DEFERRED          (1000 bytes)   Non-deferred
   AS DEFERRED BY NAME  (500 bytes)    Deferred
Record 2
   AS DEFERRED          (3000 bytes)   Deferred
   AS DEFERRED          (1000 bytes)   Non-deferred
   AS DEFERRED          (6000 bytes)   Deferred
   AS DEFERRED          (2000 bytes)   Non-deferred
   AS DEFERRED          (5000 bytes)   Deferred
   AS DEFERRED BY NAME  (1000 bytes)   Deferred
Record 3
   AS DEFERRED          (1000 bytes)   Non-deferred
   AS DEFERRED          (2000 bytes)   Non-deferred
   AS DEFERRED          (2000 bytes)   Non-deferred
   AS DEFERRED          (1000 bytes)   Deferred
   AS DEFERRED          (2000 bytes)   Deferred
   AS DEFERRED BY NAME  (1200 bytes)   Deferred

When the DEFERLIMITS option is specified, BTEQ will display the number of deferred and non-deferred LDO transfers for each import. The message will look similar to:

*** Total LDOs: 50, Non-deferred: 18, Deferred: 32

Multiple sessions can be used to take advantage of the database's ability to handle requests in parallel.

BTEQ can assign up to 200 sessions for use. However, each session requires a dedicated file handle which may exceed a system's resources. If BTEQ is not able to open a file, the associated request is aborted. Therefore, the appropriate number of sessions to use should be determined in advance of running LDO import jobs for production environment purposes.

Notes:
  • The use of Unicode sessions to transfer AS DEFERRED columns is not supported.
  • The Teradata Database currently does not support the JSON keyword within a USING clause. When importing JSON data, define the column as a CLOB type instead, within the USING clause.
  • For z/OS BTEQ, text LDOs which are imported in deferred or non-deferred mode will not contain end-of-record indicators (newline characters).