All Forums Tools
archana_2016 16 posts Joined 05/16
16 May 2016
Executing multiple TPT scripts simultaneously will yield to better perfomance?

Hi
Can i execute multiple scripts with different producers(source tables) and different consumers(target files) simultaneously to get a better perfomance.
 
Thanks in advance

feinholz 1234 posts Joined 05/08
16 May 2016

Better performance than what?
 

--SteveF

archana_2016 16 posts Joined 05/16
17 May 2016

Hi Steve
I have the follwoing TPT script written to get table data exported to one file. I would like to know if it is possible to export into multiple files to get a better throughput, as of now it takes 1 minute to export .
USING CHARACTER SET UTF8
DEFINE JOB export_emea_analytics_eu_deal_flat
(
    DEFINE SCHEMA tpt_tab_export_schema FROM SELECT DELIMITED OF OPERATOR tpt_reader;
    DEFINE OPERATOR tpt_reader
    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
    TYPE EXPORT
    SCHEMA tpt_tab_export_schema
    ATTRIBUTES
    (
       VARCHAR USERNAME = 'user'
       ,VARCHAR SELECTSTMT = 'SELECT * FROM tpt_table;'
       ,VARCHAR USERPASSWORD = '********'
       ,VARCHAR TDPID = 'ip-address'
     );
    
    DEFINE OPERATOR tpt_writer
    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER FLAT FILE WRITER'
    TYPE DATACONNECTOR CONSUMER
    SCHEMA *
    ATTRIBUTES
    (
       VARCHAR OPENMODE = 'Write'
       ,VARCHAR DIRECTORYPATH = '/home/dev/tpt/test/'
       ,VARCHAR FILENAME = 'tpt_test.txt'
       ,VARCHAR INDICATORMODE = 'N'
       ,VARCHAR QUOTEDDATA = 'N'
       ,VARCHAR FORMAT = 'DELIMITED'
    ,VARCHAR TEXTDELIMITER = '|'
       ,INTEGER BUFFERSIZE = 524288
    );
    APPLY TO OPERATOR (tpt_writer)
        SELECT * FROM OPERATOR (tpt_reader);
  
);
 
Thanks in advance

feinholz 1234 posts Joined 05/08
17 May 2016

Yes, it is possible.
Specify multiple instances of the DataConnector operator (the file writer).
Each instance will write to a separate file.
And make sure to specify the -C (uppercase) command line option so that the data is sent to the DC operator instances in a round-robin fashion. You will then get a roughly even (not exact) distribution of rows per file.
 

--SteveF

archana_2016 16 posts Joined 05/16
18 May 2016

Thanks Steve for the information,
Currently we are executing as follows: tbuild -f <filename>
Should the command line be changed as follows for the round-robin fashion ?
tbuild -C <filename>

feinholz 1234 posts Joined 05/08
18 May 2016

It needs to be
tbuild -f <script-name> -C

--SteveF

archana_2016 16 posts Joined 05/16
30 May 2016

Thanks Steve for the response..
Need clarity on the multithread concept in TPT..

I am aware that we can perform exporting of multiple tables by creating more instance of the producers and the consumers in the same script. But is it done serially or parallely? And may i know if it can be done in the following way:

USING CHARACTER SET UTF8
DEFINE JOB export_emea_analytics_eu_deal_flat
(

DEFINE SCHEMA tpt_tab_export_schema FROM SELECT DELIMITED OF OPERATOR tpt_reader1;
DEFINE OPERATOR tpt_reader1
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR1'
TYPE EXPORT
SCHEMA tpt_tab_export_schema
ATTRIBUTES
(
VARCHAR USERNAME = 'user'
,VARCHAR SELECTSTMT = 'SELECT * FROM tpt_table1;'
,VARCHAR USERPASSWORD = '********'
,VARCHAR TDPID = 'ip-address'
);

DEFINE SCHEMA tpt_tab_export_schema FROM SELECT DELIMITED OF OPERATOR tpt_reader2;
DEFINE OPERATOR tpt_reader2
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR2'
TYPE EXPORT
SCHEMA tpt_tab_export_schema
ATTRIBUTES
(
VARCHAR USERNAME = 'user'
,VARCHAR SELECTSTMT = 'SELECT * FROM tpt_table2;'
,VARCHAR USERPASSWORD = '********'
,VARCHAR TDPID = 'ip-address'
);

DEFINE SCHEMA tpt_tab_export_schema FROM SELECT DELIMITED OF OPERATOR tpt_reader3;
DEFINE OPERATOR tpt_reader3
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR3'
TYPE EXPORT
SCHEMA tpt_tab_export_schema
ATTRIBUTES
(
VARCHAR USERNAME = 'user'
,VARCHAR SELECTSTMT = 'SELECT * FROM tpt_table3;'
,VARCHAR USERPASSWORD = '********'
,VARCHAR TDPID = 'ip-address'
);

DEFINE OPERATOR tpt_writer1
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER FLAT FILE WRITER1'
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR OPENMODE = 'Write'
,VARCHAR DIRECTORYPATH = '/home/dev/tpt/test/'
,VARCHAR FILENAME = 'tpt_test1.txt'
,VARCHAR INDICATORMODE = 'N'
,VARCHAR QUOTEDDATA = 'N'
,VARCHAR FORMAT = 'DELIMITED'
,VARCHAR TEXTDELIMITER = '|'
,INTEGER BUFFERSIZE = 524288
);

DEFINE OPERATOR tpt_writer2
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER FLAT FILE WRITER2'
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR OPENMODE = 'Write'
,VARCHAR DIRECTORYPATH = '/home/dev/tpt/test/'
,VARCHAR FILENAME = 'tpt_test2.txt'
,VARCHAR INDICATORMODE = 'N'
,VARCHAR QUOTEDDATA = 'N'
,VARCHAR FORMAT = 'DELIMITED'
,VARCHAR TEXTDELIMITER = '|'
,INTEGER BUFFERSIZE = 524288
);

DEFINE OPERATOR tpt_writer3
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER FLAT FILE WRITER3'
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR OPENMODE = 'Write'
,VARCHAR DIRECTORYPATH = '/home/dev/tpt/test/'
,VARCHAR FILENAME = 'tpt_test3.txt'
,VARCHAR INDICATORMODE = 'N'
,VARCHAR QUOTEDDATA = 'N'
,VARCHAR FORMAT = 'DELIMITED'
,VARCHAR TEXTDELIMITER = '|'
,INTEGER BUFFERSIZE = 524288
);

APPLY TO OPERATOR (tpt_writer1)
SELECT * FROM OPERATOR (tpt_reader1);

APPLY TO OPERATOR (tpt_writer2)
SELECT * FROM OPERATOR (tpt_reader2);

APPLY TO OPERATOR (tpt_writer3)
SELECT * FROM OPERATOR (tpt_reader3);
);
 

feinholz 1234 posts Joined 05/08
31 May 2016

The use of:
APPLY TO OPERATOR (tpt_writer1)
SELECT * FROM OPERATOR (tpt_reader1);
APPLY TO OPERATOR (tpt_writer2)
SELECT * FROM OPERATOR (tpt_reader2);
APPLY TO OPERATOR (tpt_writer3)
SELECT * FROM OPERATOR (tpt_reader3);
 
is not possible in TPT.
 
Each use of the APPLY-SELECT must appear in its own job step and each job step would be executed serially.
 

--SteveF

You must sign in to leave a comment.