Variant records and TPT

Tools
Teradata Employee

Variant records and TPT

I have a file with fixed-length records, but they need to be interpreted in various ways, depending on first field (let's call it RECORD_TYPE). We currently do it easily with Multiload:

.LAYOUT usage_record_layout;

.FIELD RECORD_TYPE 1 CHAR(5);

/* First variant */
.FIELD variant1_field1 6 CHAR(15);
.FIELD variant1_field2 * CHAR(15);

/* Second variant */
.FIELD variant2_field1 6 CHAR(10);
.FIELD variant2_field2 * CHAR(12);
.FIELD variant2_field3 * CHAR(8);

/* (......) */

.IMPORT INFILE file_name
FORMAT TEXT
LAYOUT usage_record_layout
APPLY DML_for_variant1 WHERE RECORD_TYPE='TYPE1'
APPLY DML_for_variant2 WHERE RECORD_TYPE='TYPE2';

Can something similar be done with TPT?

I can't find a way - other than multiple passes through the file, which is unacceptable.

Data connector producer may have only one schema and the schema is just a collection of fields, it's not possible to create variant records (you can't specify offset or starting position of field).

Is it a case where Multiload cannot be replaced with TPT?

Regards,

Jacek

Tags (2)
5 REPLIES
Enthusiast

Re: Variant records and TPT

Have you seen this one where they use APPLY CASE WHEN? It can help you. 


Cheers,

Raja

Teradata Employee

Re: Variant records and TPT

Thanks Raja. I haven't seen this particular post, but I am aware of CASE DML construct in APPLY statement. It however does not solve my problem. 

My question is not only about applying changes to different tables according to conditions based on input schema fields (yes, it can be easily done with CASE DML), but first of all about interpreting the same string of input bytes differently (via different schemas) based on one of attributes. It's like an union in C language.

In my example, for each record, bytes beginning from 6-th will contain something completely different, and the info what exactly, is encoded in the first five bytes.

Jacek

Enthusiast

Re: Variant records and TPT

I remember few years back , when I had to clone the QA env to DEV environment, including the entire structure of unix and database. We wrote scripts to automate the entire structure, through unix scripts , since unix scripts accept and resolve parameters, down the line. So all DB scripts are generated based on requirements. Maybe you can think of in that direction if it befittingly suits your requirements. However , if it is one -off situation, I will not do it. I like programming in c using union , structure,etc but when it comes to pointer, it is tedious for me :). C++ maybe a better choice then.

Cheers,
Teradata Employee

Re: Variant records and TPT

I mentioned C union only to picture the idea of a variant record. I have no intention of developing any new tool :).

I just wonder whether it is possible to do in TPT what we currently do in Multiload. It seems that no. I would be happy if someone from TPT engineering team would answer.

Teradata Employee

Re: Variant records and TPT

If you are talking about the MultiLoad feature whereby you can specify the byte position in the input stream for the various columns, such as:

     COL1  1  INTEGER

     COL2  10   CHAR(10)

     COL3   5   CHAR(5)

then no, TPT does not yet support that feature. It is on our list of features to do, but there has not been a high enough demand to bump its priority.

-- SteveF