BTEQ Export truncating CLOB column value

Database
WAQ
Enthusiast

BTEQ Export truncating CLOB column value

Hi,

I have a simple stored procedure which returns CLOB value. I am calling the procedure from BTEQ and then exporting that value in a file.

Now the problem is that BTEQ is truncating the value from the end. I have used also used SET WIDTH but no luck. Following is my BTEQ code:

.LOGON ;

.SET WIDTH 65531;
.EXPORT REPORT FILE = C:/TEST/EX.txt
.SET LARGEDATAMODE ON;
.SET INDICDATA OFF
CALL TEST_PY(pAmount);
.EXPORT RESET

.LOGOFF;
Tags (3)
7 REPLIES
WAQ
Enthusiast

Re: BTEQ Export truncating CLOB column value

I have also tried using DEFERTRANS option but still no luck. BTEQ is not exporting more than 64k data. Below is the updated code:

.LOGON ;

.SET LARGEDATAMODE ON;
.EXPORT INDICDATA DEFERTRANS FILE = EX.txt
.SET WIDTH 65531
.SET INDICDATA OFF
CALL TEST_PY(pAmount);
.EXPORT RESET

.LOGOFF;
Teradata Employee

Re: BTEQ Export truncating CLOB column value

BTEQ can IMPORT LOB data over 64K using "AS DEFERRED", but cannot EXPORT it. (LARGEDATAMODE only works if you are displaying data to stdout in "hex dump" format, not in conjunction with EXPORT.)

Use the TPT SQL Selector operator to retrieve LOB column values "AS DEFERRED" into individual files.

WAQ
Enthusiast

Re: BTEQ Export truncating CLOB column value

Hi Fred, Thanks for your response.

I was going through the documentation (link given below) and its section "Exporting Large Data Objects in Deferred Mode" says that deferred mode transfers can handle LDOs (CLOB, BLOB, and XML data types) up to 2GB in size. . Each LDO that is transferred is exported to its own file.

Also, in the same documentation, example 7 and 8 are using LARGEDATAMODE with EXPORT to export CLOB column.

Have I misunderstood the contents of the documentation or is there any way to export CLOB bigger than 64k?

http://www.info.teradata.com/HTMLPubs/DB_TTU_15_00/index.html#page/Query_Management_Tools/B035_2414_...

Teradata Employee

Re: BTEQ Export truncating CLOB column value

You are correct. Support for exporting LDOs in BTEQ was introduced in TTU15.0.

You must use LARGEDATAMODE, INDICDATA, and DEFERTRANS as in example 8. Why did you .SET INDICDATA OFF in your script? I would expect that to cause you to revert to Field Mode and negate LARGEDATAMODE.

Note that the LOB data will not go to EX.txt. Does BTEQ create a file with suffix _r1 in the output directory?

WAQ
Enthusiast

Re: BTEQ Export truncating CLOB column value

Hi Fred, thanks for your response.

Yes you are right, I have removed .SET INDICDATA OFF and SET WIDTH from the code and its working fine. The code is given below:

.LOGON

.SET LARGEDATAMODE ON; -- Use for CLOB export
.EXPORT INDICDATA DEFERTRANS FILE = EXPFILE
SELECT VAL_1, VAL_2 FROM TE;
.EXPORT RESET

.LOGOFF;

If you notice, I have also replaced SP call with direct SELECT query on the table. But if I use SP call to export the returned CLOB value, it does not work and give me an error "Error: Encountered unknown data type: 926". However using SET WIDTH and INDICDATA OFF export the SP returned value but till 64k only.

Can't we use SP in place of table to export the data greater than 64k?

Teradata Employee

Re: BTEQ Export truncating CLOB column value

OUT (or INOUT) parameters in a SP require special handling by the client. I don't think you can export a LOB that way.

But you can return LOBs via a Dynamic Result Set.

WAQ
Enthusiast

Re: BTEQ Export truncating CLOB column value

Hi Fred, okay. Or we can put the result in a table in SP and then export the result from that table in BTEQ.