Loading using Teradata Parallel Transporter Load utility.

Tools & Utilities
Enthusiast

Loading using Teradata Parallel Transporter Load utility.

Hi,

I am working on parameterizing the TPTLOAD job script that loads a target table in teradata.I have a schema file where the target table schema has been defined. I now want to pass the schema file name to the TPTLOAD job script dynamically i.e as a parameter as a part of the tbuild command.

For instance we have the TPTLOAD job script as

USING CHARACTER SET UTF16

DEFINE JOB EXTRACT_FILE_LOAD

DESCRIPTION 'Export rows from a Teradata table to a file'

USING CHARACTER SET UTF16

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

INCLUDE 'filename.schema';

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES (

VARCHAR TdpId = @tdpid,

VARCHAR UserName = @userid,

VARCHAR UserPassword = @password,

.....................

In the above example we have the INCLUDE 'filename.schema'; where we neeed to pass the filename as a parameter just as we pass VARCHAR UserName = @userid through the tbuild command using the -u option.

Please help me with this......

Thanks in advance :)

22 REPLIES
Teradata Employee

Re: Loading using Teradata Parallel Transporter Load utility.

Have you experimented?

Job variables can be used anywhere. So, just like how you can use it for UserName, you can use it for the INCLUDE statement.

     INCLUDE @my_file_name;

and:

tbuild -f <script> -u "my_file_name='some-file.txt'"

Try it and see if it works.

-- SteveF
Enthusiast

Re: Loading using Teradata Parallel Transporter Load utility.

Hi,

I have the following TPT export script to use the field report mode feature to export data from the Teradata Database and transform it into delimited VARTEXT format using the DataConnector operator :

    DEFINE JOB EMPLOYEE_SOURCE_LOAD

    DESCRIPTION 'DATA EXPORT JOB'

    (

      DEFINE OPERATOR FILE_WRITER

      DESCRIPTION 'DataConnector WRITER'

      TYPE DATACONNECTOR CONSUMER

      SCHEMA *

      ATTRIBUTES

      (

            VARCHAR PrivateLogName = 'dataconlog',

            VARCHAR DirectoryPath = '.',

VARCHAR FileName = 'dataf.txt',

VARCHAR IndicatorMode = 'N',

VARCHAR OpenMode = 'Write',

VARCHAR Format = 'Delimited'

                  );

                  DEFINE SCHEMA EMPLOYEE_SOURCE_SCHEMA

                  DESCRIPTION 'EMPLOYEE INFORMATION SCHEMA'

                  (

                     EmpNo    VARCHAR(11),

                     EmpName  VARCHAR(12),

                     DeptNo   VARCHAR(3),

                     JobTitle VARCHAR(12),

                     Salary   VARCHAR(10),

                     YrsExp   VARCHAR(2),

                     DOB      VARCHAR(11),

                     Sex      VARCHAR(1),

                     Race     VARCHAR(1),

                     MStat    VARCHAR(1),

                     EdLev    VARCHAR(2),

                     HCap     VARCHAR(2)

                  );

                  DEFINE OPERATOR SQL_SELECTOR

                  DESCRIPTION 'SQL OPERATOR'

                  TYPE PRODUCER

                  SCHEMA EMPLOYEE_SOURCE_SCHEMA

                  ATTRIBUTES

                  (

                        VARCHAR PrivateLogName

                        VARCHAR UserName = 'MyUser',

                        VARCHAR UserPassword = 'MyPassword',

                        VARCHAR TdpId = 'MyDatabase',

                        VARCHAR ReportModeOn = 'Y',

                        VARCHAR SelectStmt = 'select * from personnel.employee;'

                  );

                  LOAD  INTO OPERATOR

                  ( FILE_WRITER  [1] )

                  SELECT * FROM OPERATOR

                  ( SQL_SELECTOR [1] );

);

 

But upon running the tbuild command , I encounter the error as :

 

Teradata Parallel Transporter Version 13.10.00.05

TPT_INFRA: Syntax error at or near line 43 of Job Script File 'rrs_city_readableoutfile.ctl':

TPT_INFRA: At "ATTRIBUTES" missing EXTERNAL_ in Rule: DEFINE OPERATOR

Compilation failed due to errors. Execution Plan was not generated.

Job script compilation failed.

Job terminated with status 8.

 

PLease suggest as to why this is happening or what could be the possible solution for this.

 

Varsha Joshi
Teradata Employee

Re: Loading using Teradata Parallel Transporter Load utility.

If you look in the TPT 13.10 User Guide, page 446, there is an example script for what you want to do.

When using the SQL Selector operator, you need to specify:

   TYPE SELECTOR

instead of:

   TYPE PRODUCER

When you use "TYPE PRODUCER", TPT is expecting you to provide the "external name" for the operator library file. We do not use that anymore.

By specifying "TYPE SELECTOR", TPT knows which operator file to load dynamically.

-- SteveF
Enthusiast

Re: Loading using Teradata Parallel Transporter Load utility.

Thanks it worked  ! :)

Enthusiast

Re: Loading using Teradata Parallel Transporter Load utility.

Hi ,

I need to know if the WildcardInsert = 'Y' can be used in a stream_operator.

There is a TPT User Guide which refers to using WildcardInsert and refers to using it as :

VARCHAR WildcardInsert /*'Y' builds the INSERT statement from the target table definition, similar to a HELP TABLE statement in a FastLoad script. Use when input file schema exactly matches table schema. Permits the INSERT portion of the APPLY statement to simply specify INSERT INTO tablename, which reduce the amount of user coding.

I have a TPT Stream job script where I want to use the WildcardInsert as Y but the loading fails everytime as :

STREAM_OPERATOR: TPT10508: RDBMS error 3707: Syntax error, expected something like a 'SELECT' keyword or a 'VALIDTIME' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword be

I know doubt whether WildcardInsert can be used in a stream_operator at all ! If not what could be the alternate solution.

Please help....

Teradata Employee

Re: Loading using Teradata Parallel Transporter Load utility.

No, the WildcardInsert feature is only for the Load operator (which only uses a single DML statement).

The documentation (13.10 version I just looked at) seemed pretty clear to me that it is not supported for the Stream operator or Update operator but is supported for the Load operator.

-- SteveF

Re: Loading using Teradata Parallel Transporter Load utility.


How to use TPT for loading all the fields of a file(in one line) in a single column of a table

I have  multiple files to be loaded into a table using tptload. Scenario is, i have two columns in my table (one is varchar(100) and another is varchar(50000)).

In the first column, i want the name of the file to be inserted for all the rows loaded using that file.

In the second column, i want to insert the data present in all the fields of a single row of that file. So, basically "i want the TextDelimiter to be new line character in tptload script."

I am not sure of what TextDelimiter to be set here.

Please refer the example below:

First File data (filename abcd.txt)

a#b#c#d

e#f

j#k#l

h

Second file data (filename efgh.txt)

j#k#l#m

n#d

In table

First column Second column

abcd.txt      a#b#c#d

abcd.txt      e#f

abcd.txt      j#k#l

abcd.txt      h

efgh.txt      j#k#l#m

efgh.txt      n#d

Can anyone provide any solution for this?

Teradata Employee

Re: Loading using Teradata Parallel Transporter Load utility.

Did you try a record format of "unformatted" instead of "binary"?

-- SteveF
Teradata Employee

Re: Loading using Teradata Parallel Transporter Load utility.

Re: the post about the 2 columns and loading all of the data into a single column:

TPT has a feature whereby you indicate in the schema a column that is a metadata column specifically for the file name, and TPT will insert the file name into that column for the user.

Check the documentation for the "metadata" keyword in the schema object. I think it is something like:

    DEFINE SCHEMA
(
Column_name VARCHAR(100) METADATA(FILENAME),
Column_data VARCHAR(50000)
)

TPT does not support a newline character as a field delimiter.

But pick a delimiter as a character (or series of characters whose sequence) you know will not be part of the data and that should be sufficient.

-- SteveF