Teradata Parallel Transporter - queries

Tools
Enthusiast

Teradata Parallel Transporter - queries

Hi

I am using Teradata Parallel Transporter v13.10. I have few questions in this regard.

1. In MLOAD LAYOUT we can define .FILLER, which will not be sent to database. Can we define anything similar in TPT UPDATE also?

2. Running the following DDL step.

STEP DDL_STEP3

(

APPLY ('CREATE MULTISET TABLE DBNAME.SAMPLE2, NO FALLBACK (COL1 INTEGER NOT NULL,COL2 VARCHAR(20),COL3 DATE) PRIMARY INDEX(COL1) 

PARTITION BY RANGE_N(COL3 BETWEEN DATE '1980-01-01' AND DATE '2005-12-31' EACH INTERVAL '1' YEAR, NO RANGE) ; ')

TO OPERATOR (DDL_OPERATOR) ;

)

;

It is giving me error, '1980-01-01' missing RPAREN_ in rule. Can't we create a PPI table like the above using DATE ranges by TPT DDL operator?

3. Running the following TPT UPDATE script.

DEFINE JOB TPT_UPDATE_INSERT

DESCRIPTION 'TPT UPDATE OPERATOR TO INSERT DATA'

(

DEFINE SCHEMA SAMPLE1

(

C1 VARCHAR(10),

C2 VARCHAR(20)

)

;

STEP UPDATE_STEP

(

APPLY ('INSERT INTO DBNAME.SAMPLE1 VALUES (:C1, :C2) ') 

TO OPERATOR 

(

UPDATE_OPERATOR

TYPE UPDATE

SCHEMA *

ATTRIBUTES

(

VARCHAR PRIVATELOGNAME = 'UPDATE_LOG.txt',

VARCHAR LOGONMECH = 'LDAP',

VARCHAR TDPID = 'xxx',

VARCHAR USERNAME = 'yyy',

VARCHAR USERPASSWORD = 'zzz',

VARCHAR LOGTABLE = 'DBNAME.SAMPLE1_LOG',

VARCHAR TARGETTABLE = 'DBNAME.SAMPLE1',

VARCHAR ERRORTABLE1 = 'DBNAME.SAMPLE1_ET',

VARCHAR ERRORTABLE2 = 'DBNAME.SAMPLE1_UV',

VARCHAR WORKINGDATABASE = 'DBNAME'

)

)

SELECT * FROM 

OPERATOR

(

FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA SAMPLE1

ATTRIBUTES

(

VARCHAR PRIVATELOGNAME = 'FILE_READER_LOG.txt',

VARCHAR DIRECTORYPATH = 'C:\SAMPLE',

VARCHAR FILENAME = 'MLOAD_SAMPLE1.csv',

VARCHAR FORMAT = 'DELIMITED',

VARCHAR TEXTDELIMITER = ','

)

)

;

)

;

)

;

It is giving error, Operator 'UPDATE_OPERATOR' is undefined. Can't we define operators like this?

Responses from experts are appriciated.

Thanking You

Santanu Ghosh

3 REPLIES
Teradata Employee

Re: Teradata Parallel Transporter - queries

For #1:

Ok, so in TPT we do things a little differently in our script language but we accomplish the same thing. The schema object defines what the incoming data record looks like.

The SELECT statement (in the APPLY-SELECT) allows you to define which fields from the input record you would like to send from the producer operator (i.e. the file reader) to the consumer operator (i.e. the Update operator).

Thus, if your schema had these 5 columns, but you only wanted to send a subset of those columns to the DBS, you would do something like this:

DEFINE SCHEMA abc

   F1 INTEGER,

   F2 CHAR(10),

   F3 DECIMAL(18,2),

   F4 CHAR(2),

   F5 VARCHAR(50)

);

. . . .

APPLY

   <some-DML>

TO OPERATOR ($UPDATE[1])

SELECT F2, F3, F5 FROM OPERATOR ($FILE_READER[1]);

-- SteveF
Teradata Employee

Re: Teradata Parallel Transporter - queries

For #2, the DML statement is enclosed in single-quotes. Thus, if you have text inside those single-quotes that are enclosed in single-quotes, you need to escape them (by doubling each single-quote).

For #3, no you define the operators first. The APPLY-SELECT statement is just an executable statement that references the operators. You do notput an operator definition in the APPLY-SELECT.

Please refer to the documentation for proper script syntax.

-- SteveF
Enthusiast

Re: Teradata Parallel Transporter - queries

Hi Feinholz

Thanks for your updates. They are really helpful.

Just for #3, I saw that kind of syntax somewhere on web. I guess that was only for shorter display purpose and I misinterpreted that. :-)

Thanks

Santanu