CLOB and FastExport

Database
Enthusiast

CLOB and FastExport

What is the correct syntax to export a CLOB in fastexport.

Now I'm using:
.export
Outfile koen.dat
Outmod /bipt/dwh/dvw/app/operations/dlmt_vchar.so
Mode Record Format Text
;
but receive the error 5674: LOBs are not allowed to be selected in Record or Indicator modes.

--> The messages gives as remedy: use MultipartRecord response mode.
The fast export manual is not clear about this.

Log of execution:

$ fexp -r ".run file clob.fexp;"
========================================================================
= =
= FastExport Utility Release FEXP.13.00.00.009 =
= Platform LINUX =
= =
========================================================================
= =
= Copyright 1990-2009 Teradata Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 09:44:36 UTY2411 Processing start date: THU DEC 16, 2010
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .run file clob.fexp;

0002 .logtable DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG
;
0003 .run file .LogonFile
;
0004 .logon BIPDEV/userid,;
**** 09:44:36 UTY8400 Teradata Database Release: 13.00.00.25
**** 09:44:36 UTY8400 Teradata Database Version: 13.00.00.25
**** 09:44:36 UTY8400 Default character set: ASCII
**** 09:44:36 UTY8400 Current RDBMS has UDT support
**** 09:44:36 UTY8400 Current RDBMS has Large Decimal support
**** 09:44:36 UTY8400 Maximum supported buffer size: 1M
**** 09:44:36 UTY8400 Data Encryption supported by RDBMS server
**** 09:44:37 UTY6211 A successful connect was made to the RDBMS.
**** 09:44:37 UTY6217 Logtable 'DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0005 .begin export sessions 4
;

0006 .export
Outfile koen.dat
Mode Record Format Text
;

0007 Select Tablename, TableLoadingScript
From DVW_METADATA.META_EDW_LOADING_SCRIPS
;
0008 .end export
;
========================================================================
= =
= FastExport Initial Phase =
= =
========================================================================
**** 09:44:37 UTY8700 Options in effect for this FastExport task:
. Sessions: 4 session(s).
. Mode: RECORD
. Blocksize: 64330 bytes.
. Outlimit: No limit in effect.
**** 09:44:37 UTY8715 FastExport is submitting the following request:
Select NULL from DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG where (LogType =
220) and (Seq = 1) and (FExptSeq = 0);
**** 09:44:38 UTY8705 EXPORT session(s) requested: 4.
**** 09:44:38 UTY8706 EXPORT session(s) connected: 4.
**** 09:44:38 UTY8715 FastExport is submitting the following request:
BT;BEGIN FASTEXPORT;
**** 09:44:38 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from
DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG WHERE (LogType = 210) and (Seq =
1) and (FExptSeq IN (SELECT MAX(FExptSeq) from
DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG where (LogType = 210) and (Seq =
1)));
**** 09:44:38 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from
DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG WHERE (LogType = 212) and (Seq =
1) and (FExptSeq IN (SELECT MAX(FExptSeq) from
DVW_METADATA.META_EDW_LOADING_SCRIPS_LOG where (LogType = 212) and (Seq =
1)));
**** 09:44:38 UTY8715 FastExport is submitting the following request:
Select Tablename, TableLoadingScript
From DVW_METADATA.META_EDW_LOADING_SCRIPS
;
**** 09:44:38 UTY8724 Select request submitted to the RDBMS.
**** 09:44:38 UTY8713 RDBMS failure, 5674: LOBs are not allowed to be selected in Record or
Indicator modes.
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 09:44:38 UTY6215 The restart log table has NOT been dropped.
**** 09:44:39 UTY6212 A successful disconnect was made from the RDBMS.
**** 09:44:39 UTY2410 Total processor time used = '0.12 Seconds'
. Start : 09:44:36 - THU DEC 16, 2010
. End : 09:44:39 - THU DEC 16, 2010
. Highest return code encountered = '12'.
1 REPLY
bwb
Teradata Employee

Re: CLOB and FastExport

Currently, the FastExport protocol does not support LOB columns (the workaround given for 5674 applies to other protocols). There are a number of reasons for this which I won't go into. I can offer two alternatives.

First, although unlikely, if it happens that your CLOB data is always under 64KB, then you may be able to export it as VARCHAR. In your FastExport SELECT statement, CAST the CLOB column to a VARCHAR. Of course, you need to be sure you won't lose any data, since FastExport operates in Teradata mode, which will silently truncate excess data (see the Character-to-Character Conversion topic in chapter 17, Data Type Conversions, of the Teradata SQL Reference volume SQL Functions, Operators, Expressions, and Predicates).

Second, the better and more general solution is to use TPT (Teradata Parallel Transporter), beginning with TTU 13.10. TPT was enhanced in TTU 13.10 to support both insertion and retrieval of LOB data. The SQL Selector Operator (see Teradata Parallel Transporter Reference, Chapter 14) allows you to specify a directory to received the LOB data (one file per row per LOB column). The non-LOB data is placed in a flat file as usual, and includes pointers to the LOB data files (essentially, a VARCHAR field replaces each LOB field in the non-LOB data file, with the VARCHAR field containing the path name of the corresponding LOB data file). The SQL Inserter Operator (see Teradata Parallel Transporter Reference, Chapter 13) can be used to load the non-LOB and LOB data. Additional information is in the Teradata Parallel Transporter User Guide; see chapters 18 (SQL Selector Operator), 17 (SQL Inserter Operator), and 26 (Advanced Teradata Database Considerations, Large Objects topic).