SQL Inserter Help with TPT and CLOB Data Load

Tools
Enthusiast

SQL Inserter Help with TPT and CLOB Data Load

Hello,

I have a table created on Teradata RDBMS 14, I'm using TTU 14 on my PC, and the TPT Wizard. I'm attempting to load a large ASCII comma delimited flat file, that has two columns/comma separations of very large blocks of text, among other data.

The table is designed with VARCHAR(255) on all the columns, except two, they are as:

Text_1 CLOB(2097088000) CHARACTER SET LATIN,

Text_2 CLOB(2097088000) CHARACTER SET LATIN,

In the TPT wizard, the corresponding text_1/2 columns are as VARCHAR(64000), I seem to have no choice, CLOB is not an option, and nothing higher than 6400 is allowable.

The Operator Type Selection of both "Load Operator" and "Update Operator" have both been used, and both yield the same error:

TPT10508: RDBMS error 5685: FastLoad is not supported on LOB columns.

&

TPT10508: RDBMS error 5685: MultiLoad is not supported on LOB columns.

I'm requesting help in how and where I need to make the proper adjustment for using SQL Inserter, I do not understand if it is something that I must do table design wise, or edit freehand in the script the TPT wizard is creating.

Thanks,

JPQ

Tags (1)
3 REPLIES
Teradata Employee

Re: SQL Inserter Help with TPT and CLOB Data Load

Please do not use the Wizard.

It is a tool that will have a limited shelf life (the plan is to discontinue it soon), and it does not support all of the features of TPT.

Also, it generates scripts in a way that is contradictory to how we are recommending scripts to be written.

(We are not enhancing the Wizard, so it will always continue to generate scripts in a way we do not recommend.)

The FastLoad and MultiLoad "protocols" do not support LOBs.

If you would like to load LOBs, you need to use the SQL Inserter, and the details are provided here:

http://developer.teradata.com/tools/articles/large-object-loading-with-teradata-parallel-transporter

And even that article was written prior to when we enhanced the script language to use operator templates and job variable files.

You can use that article to help get you started, but then I would recommend enhancing the job script to the much simpler form once you are familiar with how to load LOBs with the SQL Inserter operator.

-- SteveF
Enthusiast

Re: SQL Inserter Help with TPT and CLOB Data Load

Thank you for the link Steve, after using the Wizard, it did seem confining, and I prefer a script that I can edit without always having to use a GUI.

Enthusiast

Re: SQL Inserter Help with TPT and CLOB Data Load

I've gone through the Web info and it primarily covers BLOB. Does anyone have any sample BTEQ or TPT run through SQLA that loads CLOB? I namely need to see how the control file deals with the CLOB data file. I will have a single large file of comma delimited data, with the 1st column being the chat log data, and want to know how that is addressed in the scripting for locating the file, the column and data type.