TPT Question: Read two files simultaneously and write to Table

Tools & Utilities
Enthusiast

TPT Question: Read two files simultaneously and write to Table

TPT Question

Can i do perform below tasks in TPT if yes how? I will be using multi threads as there are 120 sets of files to read for each set and load will process 4.8 billion records.

FILE A :                                                                              FILE B:

1|A|B|C                                                                              X|Y|Z

2|D|E|F                                                                              U|V|W

OutPut should write to table as below. Treat '|' as separator for table columns

==============================================

1|A|B|C|X|Y|Z

2|D|E|U|V|W

How to Use File A & B in below code

DEFINE JOB BATCH_DIRECTORY_SCAN
DESCRIPTION 'Batch directory scanning'
(
APPLY $INSERT TO OPERATOR ($UPDATE[@UpdateInstances])
SELECT * FROM OPERATOR ($DATACONNECTOR_PRODUCER[@ReaderInstances]);
);
3 REPLIES
Teradata Employee

Re: TPT Question: Read two files simultaneously and write to Table

TPT is a parallel application meaning it can read multiple files at the same time.

However, the files are read asynchronously.

What you are asking for is for a single process to read 2 files at the same time, concatenating the contents of record 'x' of each file together.

TPT does not offer that capability.

-- SteveF
Enthusiast

Re: TPT Question: Read two files simultaneously and write to Table

Steve,

Thanks for the reply. I also figured out the same with all my testing. I have a question specific to TPT LOAD. 

Requirement:

File A

=====                  User variable Batch='1234'  SourceCode='USA'

1|A|B|C

Record should be loaded to table AS 

1|A|B|C|1234|USA

I am able to load it using TPT Update but i am facing difficulties uisng TPT LOAD. TPT LOAD gives error as 3727   Data format length must match target character length for %VSTR.

 APPLY
(
'INSERT INTO PARIJSX.APLD_PTD_PROCEDURE_1 (MODIFIED_BY,MODIFIED_DATE,MARKET_CODE,RECORD_KEY,PATIENT_GID,CLAIM_ID,SERVICE_LINE,CLAIM_TYPE,PROCEDURE_CODE,STRENGTH,PROCEDURE_DATE,SERVICE_FROM_DATE,SERVICE_TO_DATE,LOCATION_OF_SERVICE_CODE,PHYSICIAN_KEY,PHYSICIAN_ROLE,SERVICE_UNIT_NUMBER,CALCULATED_SERVICE_UNIT_NUMBER,SERVICE_CHARGE_AMOUNT,SOURCE_OF_PAY_CODE,IN_PATIENT_OUT_PATIENT,PATIENT_ADMIT_DATE,PATIENT_SOURCE_OF_ADMISSION,PATIENT_TYPE_OF_ADMISSION,REVENUE_CODE,PATIENT_DISCHARGE_STATUS,CALC_SRVC_UNIT_CEILING,DELETION_FLAG,DATA_SOURCE_CODE,LOGICAL_DATA_SOURCE_CODE,BATCH_ID,BATCH_STEP_RUN_ID,BATCH_RUN_NUMBER)
VALUES (USER,CURRENT_TIMESTAMP(0),:MARKET_CODE,:RECORD_KEY,:PATIENT_GID,:CLAIM_ID,:SERVICE_LINE,:CLAIM_TYPE,:PROCEDURE_CODE,:STRENGTH,:PROCEDURE_DATE,:SERVICE_FROM_DATE,:SERVICE_TO_DATE,:LOCATION_OF_SERVICE_CODE,:PHYSICIAN_KEY,:PHYSICIAN_ROLE,:SERVICE_UNIT_NUMBER,:CALCULATED_SERVICE_UNIT_NUMBER,:SERVICE_CHARGE_AMOUNT,:SOURCE_OF_PAY_CODE,:IN_PATIENT_OUT_PATIENT,:PATIENT_ADMIT_DATE,:PATIENT_SOURCE_OF_ADMISSION,:PATIENT_TYPE_OF_ADMISSION,:REVENUE_CODE,:PATIENT_DISCHARGE_STATUS,:CALC_SRVC_UNIT_CEILING,:DELETION_FLAG,'||@DATA_SOURCE_CODE||','||@LOGICAL_DATA_SOURCE_CODE||','||@BATCH_ID||','||@BATCH_STEP_RUN_ID||','||@BATCH_RUN_NUMBER||');'
)
TO OPERATOR
(
W_1_o_STG_APLD_PTD_PROCEDURE[1]
)
SELECT * FROM OPERATOR
(
W_0_o_STG_APLD_PTD_PROCEDURE[1]
);
);

Above code works for TPT Update but fails with TPT LOAD

tbuild -f Manual.txt -u "DATA_SOURCE_CODE='998896', LOGICAL_DATA_SOURCE_CODE = '901921', BATCH_ID='23800073',BATCH_STEP_RUN_ID='28900078',BATCH_RUN_NUMBER='572900021'"

Question: How to cast the user`s variables to match the data type with Fastload table?

I am trying to solve a customer`s problem. Using TPT LOAD with NOPI. it will able to load 4.8 Billions in 30 Minutes as per my testing but i want those variables should be loaded to the table uisng TPT LOAD. TPT update is also OK but runs little bit long. It does the job in 1 hour. 

--Thanks in Advance

Teradata Employee

Re: TPT Question: Read two files simultaneously and write to Table

Just to clarify, you are saying that the exact same INSERT statement, with the exact same command line options, results in a successful run with the Update operator but not the Load operator.

Is this correct?

-- SteveF