TPT loading LOB and generating large file

Tools & Utilities
Enthusiast

TPT loading LOB and generating large file

Greetings,

I have successfully loaded  CLOB data, from Oracle server with TPT into Teradata using ODBC connector.

 

However it looks like, the CLOB file is being created first onto my ETL environment, in this case Linux root directory and then the file is being loaded onto Teradata.  My undeerstanding was that, TPT should not extract and/or write the file onto local directory first and thus write to the RDBMS directly. 

 

In my script I am using a File Reader (FILE_READER) and Inserter oerator (SQL_INSERTER) for the Load.

 

My question is, is there a way I can turn off writing the CLOB data onto the local directory while I am loading to Teradata?

If not, how can I remove these files at the end or redirect them to a location of my desire?

In both of my schemas attributes, I have specified tracing off and private log to specific file. 

Any ideas?

Thanks in advance,

 

Tags (2)

Accepted Solutions
Teradata Employee

Re: TPT loading LOB and generating large file

OK, so you are just naming the ODBC operator FILE_READER in your script. I was thinking about the templates, $FILE_READER vs $ODBC.

With no LobDirectoryPath, the default should be the current working directory, so as a workaround you could always "cd" to some other path before invoking tbuild.

 

For "inline LOB", you simply specify the max length (64000 bytes or less) and omit the AS DEFERRED BY NAME clause from the schema. But then INSERTER requires an 8-byte binary length field prefixed to the actual LOB data, and I see that at present the ODBC operator does not support generating that length field. So that's not a viable alternative for you, after all.

 

You might be able to extract the data as VARCHAR (up to 64000 bytes) and load it that way if it is short enough. (Note that the limit is in bytes, so 64000 characters if you are using a single-byte session character set like default ASCII for the load, 32000 characters if you are using UTF16, and 21333 characters if you are using UTF8. And the character set used for the load needs to match the character set produced by the ODBC operator.)

1 ACCEPTED SOLUTION
3 REPLIES
Highlighted
Teradata Employee

Re: TPT loading LOB and generating large file

But I'm a bit confused by your reference to both ODBC and FILE_READER. Is this being done as a two-step process, ODBC to FILE_WRITER and FILE_READER to SQL_INSERTER? In that case the row images would also need to be stored locally, in addition to LOB data files.

 

LOB data that is DEFERRED BY NAME must be held (at least temporarily) in local files; the row image contains a VARCHAR field that names the file, and the database will "call back" to the client to elicit the actual data for the LOB field. (Recent releases allow "LOB" data that is known not to exceed 64K to be handled inline.)

 

Specify a LobDirectoryPath for the ODBC operator to control the placement of the files. The DeleteLobDataFiles=Yes option for the INSERTER operator will delete LOB data files as the inserts are committed, or you could just "clean" the directory in the script that invokes TPT.

Enthusiast

Re: TPT loading LOB and generating large file

Thanks Fred for your suggestion.

It's not a two step process.  This is what I am doing:

a. I am using the FILE_READER (), Operator: type : odbc,

b. I also have a DDL_OPERATOR(), operator; type ddl,

(This I am using for Delete operation from my table)

c. Finally SQL_INSERTER (),operator Type  INSERTER

where I am performing 'Delete' first and then Inserting data, SQL_INSERTER to FILE_READER ()

This process working okay.  Looking closely I do see the CLOB files are being deleted automatically. I did specify the LobDirectoryPath but that did not work.  It is still dumping my clob contents on the highercy id (where I am running the job)

I can try again, with the dir path you specified and with delete to yes option and update here.

Back to the other point, if my CLOB is 64K, and I want to modify it with INLINE option, how do I describe it on define statement? Is there a sample for it? I did not find any clear example.

 

Thanks,

 

Teradata Employee

Re: TPT loading LOB and generating large file

OK, so you are just naming the ODBC operator FILE_READER in your script. I was thinking about the templates, $FILE_READER vs $ODBC.

With no LobDirectoryPath, the default should be the current working directory, so as a workaround you could always "cd" to some other path before invoking tbuild.

 

For "inline LOB", you simply specify the max length (64000 bytes or less) and omit the AS DEFERRED BY NAME clause from the schema. But then INSERTER requires an 8-byte binary length field prefixed to the actual LOB data, and I see that at present the ODBC operator does not support generating that length field. So that's not a viable alternative for you, after all.

 

You might be able to extract the data as VARCHAR (up to 64000 bytes) and load it that way if it is short enough. (Note that the limit is in bytes, so 64000 characters if you are using a single-byte session character set like default ASCII for the load, 32000 characters if you are using UTF16, and 21333 characters if you are using UTF8. And the character set used for the load needs to match the character set produced by the ODBC operator.)