Exporting Large Data Objects - Basic Teradata Query

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

For inline LDO exports, the DEFERTRANS and DEFERLIMITS options must not be used. In this case, BTEQ does not differentiate LDOs from non-LDO columns. All data is sent from the database in one parcel and stored in a single export file. Inline LDOs are also limited to 64K in size.

Deferred mode LDOs can be larger (up to 2GB) and are sent by the database after all non-LDO data is processed.

Non-deferred mode LDOs can also be up to 2GB in size but are sent by the database at the same time non-LDO data is processed.

Deferred and non-deferred exports are only allowed when using the INDICDATA format along with the LARGEDATAMODE setting turned on. If LARGEDATAMODE is not on, BTEQ will turn it on automatically for the life of the export. Conversely, when importing LDOs, deferred and non-deferred imports can be done with INDICDATA, DATA, and VARTEXT formats.

For both deferred and non-deferred exports, LDOs are stored in their own file for easier accessibility. A main export file (defined by the FILE/DDNAME option) will still be created, containing non-LDO data along with filenames (as VARCHAR data types) for each LDO file, based on column titles. The main export file will be in INDICDATA format. CLOB and JSON files will be written as text, based on the session character set. XML files will always be written in text with a UTF-8 encoding. BLOB files will be written with raw data.

Deferred exports are usually best used for large LDOs and non-deferred exports for small LDOs, as determined by elapsed time performance. Some testing/tuning may be needed to determine the optimal way to export records containing LDOs. The following describes how to define your exports for deferred mode versus non-deferred mode:

• Export all LDOs in deferred mode: Use the DEFERLIMITS option with values "0,0".
• Export all LDOs in non-deferred mode: Use the DEFERLIMITS option, using values sufficiently high enough to account for all LDOs. This method is not recommended for very large LDOs.
• Export some deferred LDOs and some non-deferred LDOs:
  • Use the DEFERTRANS option. This will default the DEFERLIMITS values to "1000,10000". Any LDOs within these limits will be exported non-deferred. All others will be exported deferred.
  • Use the DEFERLIMITS option to specify your own limits. Any LDOs within those limits will be exported non-deferred, while the rest will be exported deferred mode.

When the DEFERLIMITS option is specified, BTEQ will display the number of deferred and non-deferred LDOs that were exported for each statement. The message will look similar to:

*** Total LDOs: 48, Non-deferred: 10, Deferred: 38

For workstation platforms, each LDO filename is created based on the LDO column title. The filename syntax is:

[prefix]<col-name>_r<rec-nbr>[.suffix]

where:

• prefix = The optional LDOPREFIX value. This can include a relative or absolute directory structure.
• col-name = The LDO column title, which must contain only Latin characters.
• rec-nbr = The record number. This value will be incremented for each record encountered. When a new EXPORT command is submitted, the record number is reset.
• suffix = The optional LDOSUFFIX value.

On the mainframe, each LDO filename is created using an associated DDNAME that is based on the LDO column title. Users are responsible for pre-defining all needed DDNAMEs in their JCL scripts. The DDNAME naming convention is:

<col-name><rec-nbr>

where:

• col-name = The LDO column title, which must contain only Latin characters.
• rec-nbr = The record number. This value will be incremented for each record encountered. When a new EXPORT command is submitted, the record number is reset.

Since DDNAMEs are limited to 8 characters, and the first character must be a letter, the maximum number of records that can be retrieved is 9,999,999. This, however, may be superseded by the z/OS DDNAME limit.

It is important to make sure that the filenames/DDNAMEs for each LDO column are unique. If a naming conflict occurs between multiple LDO columns, use the TITLE clause in the query to create new column names. This is also a good workaround for a column name that contains non-Latin characters. For example, on the mainframe, the following query has two conflicting LDO column names (when truncated):

SELECT customer_name_clob, customer_image_blob FROM customer_table;

But it can be fixed by making a change similar to:

SELECT customer_name_clob (TITLE 'CNAME'),
       customer_image_blob (TITLE 'CIMAGE')
       FROM customer_table;

LDO data is written to its own file as is, without any formatting. The only exception is with the possible insertion of a Unicode Byte Order Mark (BOM). A BOM can be inserted at the beginning of XML objects, which are always transferred in the UTF-8 encoding, or for character based objects when a Unicode session character set is used. The user can disable BOM insertion by using the NOBOM option.

Individual LDO files are never appended. If the file already exists, BTEQ will simply overwrite the existing data.

If LDO exports are to be subsequently used during an import, the LDO columns should be “ordered” first in the SELECT list. This is a requirement of the IMPORT command when the DEFERCOLS option is used.

Examine examples 7, 8, and 9 (EXPORT Examples) to see how deferred and non-deferred exports can be used within a script.