Dynamic Merge Statement in TPT

Tools & Utilities
Teradata Employee

Dynamic Merge Statement in TPT

 

Hello,

 

I am trying to make dynamic statments using tpt but wheneever i run tpt, it ends up with following error 

 

LOAD_OPERATOR: TPT10508: RDBMS error 2556: Duplicate match tag found. (please see following log for further detail)

 

Log File

--------------------------------------------------Tue 01/16/2018 20:09:57.38------------------------------------------------------------ 

Teradata Parallel Transporter Version 15.10.01.02 64-Bit
Job log: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/logs/demo_tpt_tpt_cdc_Demo_RESTORE-1379.out
Job id is demo_tpt_tpt_cdc_Demo_RESTORE-1379, running on WPKZA186003-650
Found CheckPoint file: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/checkpoint\demo_tpt_tpt_cdc_Demo_RESTORELVCP
This is a restart job; it restarts at step MAIN_STEP.
Teradata Parallel Transporter Update Operator Version 15.10.01.02
LOAD_OPERATOR: private log not specified
Teradata Parallel Transporter DataConnector Operator Version 15.10.01.02
FILE_READER[1]: Instance 1 directing private log report to 'dtacop-za186003-19316-1'.
FILE_READER[1]: DataConnector Producer operator Instances: 1
FILE_READER[1]: ECI operator ID: 'FILE_READER-19316'
FILE_READER[1]: Operator instance 1 processing file '\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'.
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table(s)
LOAD_OPERATOR: entering DML Phase
a
LOAD_OPERATOR: disconnecting sessions
FILE_READER[1]: Total files processed: 0.
LOAD_OPERATOR: Total processor time used = '0.3125 Second(s)'
LOAD_OPERATOR: Start : Tue Jan 16 20:10:00 2018
LOAD_OPERATOR: End : Tue Jan 16 20:10:02 2018
Job step MAIN_STEP terminated (status 12)
Job demo_tpt_tpt_cdc_Demo_RESTORE terminated (status 12)
Job start: Tue Jan 16 20:09:58 2018
Job end: Tue Jan 16 20:10:02 2018

 

 

TPT Script

USING CHARACTER SET UTF8
DEFINE JOB "demo_tpt_tpt_cdc_Demo_1516018197546"
DESCRIPTION 'Data Loading Job'
(
DEFINE SCHEMA tpt_cdc_Demo_SCHEMA
DESCRIPTION 'SCHEMA DETAILS'
(
Product_ID VARCHAR(4)
, Product_Name VARCHAR(40)
, Product_Code VARCHAR(15)
, Product_History VARCHAR(15)
, Created_Date VARCHAR(19)
, Updated_Date VARCHAR(19)
, End_Date VARCHAR(19)
);
DEFINE OPERATOR FILE_READER
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR DIRECTORY_PATH = 'C:\TeradataOffice\td2tdapp\demo_tpt\'
,VARCHAR FileName = 'C:\TeradataOffice\td2tdapp\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'
,VARCHAR IndicatorMode = 'N'
,VARCHAR OpenMode = 'Read'
,VARCHAR Format = @Format
,VARCHAR TextDelimiter = ','
);
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE UPDATE
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR TdpId = @TargetTdpId
,VARCHAR UserName = @TargetUserName
,VARCHAR UserPassword = @TargetUserPassword
,VARCHAR TargetTable = 'TPT_Test.tpt_cdc_Demo'
,VARCHAR ErrorTable1 = 'TPT_Test.tpt_cdc_Demo_ET1'
,VARCHAR ErrorTable2 = 'TPT_Test.tpt_cdc_Demo_ET2'
,VARCHAR LogTable = 'TPT_Test.tpt_cdc_Demo_LT'
,INTEGER MaxDecimalDigits = 38
,VARCHAR SpoolMode = 'NoSpoolOnly'
,INTEGER MaxSessions=32
,INTEGER Minsessions=8
,VARCHAR DateForm='AnsiDate'
);
APPLY (' MERGE INTO TPT_TEST.tpt_cdc_Demo
USING VALUES (50,''NewTransaction'',''0024'',''50'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'')
AS Dept (Product_ID, Product_Name, Product_Code, Product_History,
Created_Date, Updated_Date, End_Date)
ON Dept.Product_ID =tpt_cdc_Demo.Product_ID
WHEN MATCHED THEN UPDATE
SET End_Date = Dept.End_Date
WHEN NOT MATCHED THEN INSERT
VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,
Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);')
TO OPERATOR (LOAD_OPERATOR)
SELECT * FROM OPERATOR (FILE_READER[1]););
);

 

 

Any idea?


Accepted Solutions
Teradata Employee

Re: Dynamic Merge Statement in TPT

The SET clause is invalid. The left hand side must not have a table name or alias, and you can't modify the source value.

 

SET Product_Code = Dept.Product_Code

1 ACCEPTED SOLUTION
9 REPLIES
Teradata Employee

Re: Dynamic Merge Statement in TPT

I am looking into this with a DBS architect.

In the meantime a question and suggestion:

1. is it possible to provide the contents of the data file?  (how many rows are in the data file?)

2. for readability you should change the name of the loading operator from "LOAD_OPERATOR" to "UPDATE_OPERATOR" so as not to confuse the reader as to which operator is being used.

3. the schema name on the consumer operator should always be "SCHEMA *" (deferred); this allows for more flexibility in case someone changes the script and adds a filter to the SELECT statement (changing the effective schema for the consumer operator)

4. SpoolMode is not a valid attribute for any operator except the Export operator; right now it is being ignored by the Update operator, but just thought I would point it out

 

-- SteveF
Teradata Employee

Re: Dynamic Merge Statement in TPT

Seems like the error message could be improved, but your TPT script makes no sense. Can you explain what you are trying to accomplish?

 

The UPDATE operator does not support a MERGE SQL statement. For "upsert" behavior, the APPLY clause should have an UPDATE statement and an INSERT statement (both targeting the same table and referencing the schema fields), and the INSERT FOR MISSING UPDATE ROWS option.

 

Or you could use the STREAM operator with (parameterized) UPSERT or MERGE. But if you are intending to dynamically generate a bunch of MERGE VALUES statements, BTEQ would probably be a better fit (though you could use TPT DDL operator).

Teradata Employee

Re: Dynamic Merge Statement in TPT

In speaking with the DBS architect, the DBS should have rejected the DML Phase submittal of the MERGE statement.

You could open an incident with the GSC to have the DBS folks take a look at that.

The "duplicate match tag" error is the wrong error for that job.

 

-- SteveF
Teradata Employee

Re: Dynamic Merge Statement in TPT

@Fred

Thanks for the reply.i'm trying to implmement change data capture(CDC) using tpt so this script reads the changed value from .csv file and persist into tagert table. So values could be new or updated one's 

 

Moving forward, i've tried DDL operator but seems it work with hardcoded values like 

APPLY (' MERGE INTO TPT_TEST.tpt_cdc_Demo
USING VALUES (50,''NewTransaction'',''0024'',''50'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'',''2018-01-10 10:14:59'')

However, when i try to load values from csv file it doesn't work properly. 

 

Now, i have two things to know when i remove the hardcoded values with dynamic one

 

MERGE INTO TPT_TEST.tpt_cdc_Demo
 USING VALUES (:Product_ID,:Product_Name,:Product_Code,:Product_History,:Created_Date,:Updated_Date,:End_Date)

 

1) How would i get rid off the following error "Cannot use value (or macro parameter) to match 'Product_ID'."

Teradata Parallel Transporter Version 15.10.01.02 64-Bit
Job log: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/logs/demo_tpt_tpt_cdc_Demo_RESTORE-1423.out
Job id is demo_tpt_tpt_cdc_Demo_RESTORE-1423, running on WPKZA186003-650
Found CheckPoint file: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/checkpoint\demo_tpt_tpt_cdc_Demo_RESTORELVCP
This is a restart job; it restarts at step D_MERGE.
Teradata Parallel Transporter SQL DDL Operator Version 15.10.01.02
DDL_OPERATOR: private log not specified
DDL_OPERATOR: connecting sessions
DDL_OPERATOR: sending SQL requests
DDL_OPERATOR: TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match 'Product_ID'.
DDL_OPERATOR: disconnecting sessions
DDL_OPERATOR: Total processor time used = '0.34375 Second(s)'
DDL_OPERATOR: Start : Wed Jan 17 19:43:29 2018
DDL_OPERATOR: End   : Wed Jan 17 19:43:29 2018
Job step D_MERGE terminated (status 12)
Job demo_tpt_tpt_cdc_Demo_RESTORE terminated (status 12)
Job start: Wed Jan 17 19:43:28 2018
Job end:   Wed Jan 17 19:43:29 2018

 2. How can i read value from csv file and pass it to Merge statement dynamically ? i tried it with following chunk

 

STEP D_MERGE
               (
	  APPLY
                              (
	 ' MERGE INTO TPT_TEST.tpt_cdc_Demo
 USING VALUES (:Product_ID,:Product_Name,:Product_Code,:Product_History,:Created_Date,:Updated_Date,:End_Date)
 AS Dept (Product_ID, Product_Name, Product_Code, Product_History,Created_Date, Updated_Date, End_Date) 
 ON Dept.Product_ID = tpt_cdc_Demo.Product_ID
 WHEN MATCHED THEN UPDATE
 SET Dept.Product_Code = tpt_cdc_Demo.Product_Code
 WHEN NOT MATCHED THEN INSERT
 VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,
		Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);')
		TO OPERATOR (DDL_OPERATOR)
		SELECT * FROM OPERATOR (FILE_READER[1]))
		
							  
               );


);	

But it gives me an error like

 

 

Teradata Parallel Transporter Version 15.10.01.02 64-Bit
Job script compilation failed.

Job terminated with status 8.
TPT_INFRA: Semantic error at or near line 62 of Job Script File 'C:\TeradataOffice\td2tdapp\demo_tpt\/tpt_cdc_Demo_RESTORE.tpt':
TPT_INFRA: TPT03294: Operator 'DDL_OPERATOR' is not of type 'Consumer'.
Operator is rejected as a target of APPLY operation.
Compilation failed due to errors. Execution Plan was not generated.

 

 

Eventhough i change the Type of DATACONNECTOR  from PRODUCER to Consumer but doesn't work

 

 

Complete TPT_SCRIPT

USING CHARACTER SET UTF8
DEFINE JOB "demo_tpt_tpt_cdc_Demo_1516018197546"
DESCRIPTION 'Data Loading Job'
(
DEFINE SCHEMA tpt_cdc_Demo_SCHEMA
DESCRIPTION 'SCHEMA DETAILS'
(
Product_ID VARCHAR(4)
, Product_Name VARCHAR(40)
, Product_Code VARCHAR(15)
, Product_History VARCHAR(15)
, Created_Date VARCHAR(19)
, Updated_Date VARCHAR(19)
, End_Date VARCHAR(19)
);
DEFINE OPERATOR FILE_READER
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR DIRECTORY_PATH = 'C:\TeradataOffice\td2tdapp\demo_tpt\'
,VARCHAR FileName = 'C:\TeradataOffice\td2tdapp\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'
,VARCHAR IndicatorMode = 'N'
,VARCHAR OpenMode = 'Read'
,VARCHAR Format = @Format
,VARCHAR TextDelimiter = ','
);
DEFINE OPERATOR DDL_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR TdpId = @TargetTdpId
,VARCHAR UserName = @TargetUserName
,VARCHAR UserPassword = @TargetUserPassword
,VARCHAR TargetTable = 'TPT_Test.tpt_cdc_Demo'
,VARCHAR ErrorTable1 = 'TPT_Test.tpt_cdc_Demo_ET1'
,VARCHAR ErrorTable2 = 'TPT_Test.tpt_cdc_Demo_ET2'
,VARCHAR LogTable = 'TPT_Test.tpt_cdc_Demo_LT'
,INTEGER MaxDecimalDigits = 38
,VARCHAR SpoolMode = 'NoSpoolOnly'
,INTEGER MaxSessions=32
,INTEGER Minsessions=8
,VARCHAR DateForm='AnsiDate'
);

STEP D_MERGE
(
APPLY
(
' MERGE INTO TPT_TEST.tpt_cdc_Demo
USING VALUES (:Product_ID,:Product_Name,:Product_Code,:Product_History,:Created_Date,:Updated_Date,:End_Date)
AS Dept (Product_ID, Product_Name, Product_Code, Product_History,Created_Date, Updated_Date, End_Date)
ON Dept.Product_ID = tpt_cdc_Demo.Product_ID
WHEN MATCHED THEN UPDATE
SET Dept.Product_Code = tpt_cdc_Demo.Product_Code
WHEN NOT MATCHED THEN INSERT
VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,
Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);')
TO OPERATOR (DDL_OPERATOR)
SELECT * FROM OPERATOR (FILE_READER[1]))


);


);

 

and DEMO_TPT_TPT_CDC_DEMO.csv contains the following data

50,NewTransaction,0024,50,2018-01-10 10:14:59,2018-01-10 10:14:59,2018-01-10 10:14:59

Any idea?

 

Teradata Employee

Re: Dynamic Merge Statement in TPT

That's what I was trying to point out before. The DDL Operator is "stand-alone" so is what you would use only if you are supplying the values as constants. If you are using one of the consumer operators then your SQL should be reference the input data as parameters.

 

If you want to produce data from a file and pass to a MERGE, use the STREAM operator. And reference the host variables in the query, but don't add a USING clause.

Teradata Employee

Re: Dynamic Merge Statement in TPT

Thanks @Fred. Can you please give me some help on this? mean what and where do i need to make these changes in script? 

Teradata Employee

Re: Dynamic Merge Statement in TPT

@Fred. I gave another try using Stream operator but looks like things are in good shape yet. I'm now encountered in following error :

Stream_OPERATOR: TPT10508: RDBMS error 3810: Column/Parameter 'TPT_TEST.tpt_cdc_Demo1.Dept' does not exist.

 

Log File:

 

Teradata Parallel Transporter Version 15.10.01.02 64-Bit
Job log: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/logs/demo_tpt_tpt_cdc_Demo_RESTORE-1438.out
Job id is demo_tpt_tpt_cdc_Demo_RESTORE-1438, running on WPKZA186003-650
Found CheckPoint file: C:\Program Files\Teradata\client\15.10\Teradata Parallel Transporter/checkpoint\demo_tpt_tpt_cdc_Demo_RESTORELVCP
This is a restart job; it restarts at step D_MERGE.
Teradata Parallel Transporter DataConnector Operator Version 15.10.01.02
Teradata Parallel Transporter Stream Operator Version 15.10.01.02
Stream_OPERATOR: private log not specified
FILE_READER[1]: Instance 1 directing private log report to 'dtacop-za186003-10848-1'.
FILE_READER[1]: DataConnector Producer operator Instances: 1
FILE_READER[1]: ECI operator ID: 'FILE_READER-10848'
FILE_READER[1]: Operator instance 1 processing file 'C:\TeradataOffice\td2tdapp\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'.
Stream_OPERATOR: Start-up Rate: UNLIMITED statements per Minute
Stream_OPERATOR: Operator Command ID for External Command Interface: Stream_OPERATOR19760
Stream_OPERATOR: connecting sessions
Stream_OPERATOR: TPT16102: An error occurred while submitting the following DML statement: 
USING Product_ID(VARCHAR(4)),Product_Name(VARCHAR(40)),Product_Code(VARCHAR(15)),Product_History(VARCHAR(15)),Created_Date(VARCHAR(19)),Updated_Date(VARCHAR(19)),End_Date(VARCHAR(19))  MERGE INTO TPT_TEST.tpt_cdc_Demo1 USING VALUES (:Product_ID,:Product_Name,:Product_Code,:Product_History,:Created_Date,:Updated_Date,:End_Date) AS Dept (Product_ID, Product_Name, Product_Code, Product_History,Created_Date, Updated_Date, End_Date)  ON Dept.Product_ID = tpt_cdc_Demo1.Product_ID WHEN MATCHED THEN UPDATE SET Dept.Product_Code = tpt_cdc_Demo1.Product_Code WHEN NOT MATCHED THEN INSERT VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,  Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);
Stream_OPERATOR: TPT10508: RDBMS error 3810: Column/Parameter 'TPT_TEST.tpt_cdc_Demo1.Dept' does not exist.
Stream_OPERATOR: disconnecting sessions
FILE_READER[1]: Total files processed: 0.
Stream_OPERATOR: Total processor time used = '0.15625 Second(s)'
Stream_OPERATOR: Start : Wed Jan 17 22:20:00 2018
Stream_OPERATOR: End   : Wed Jan 17 22:20:01 2018
Job step D_MERGE terminated (status 12)
Job demo_tpt_tpt_cdc_Demo_RESTORE terminated (status 12)
Job start: Wed Jan 17 22:20:00 2018
Job end:   Wed Jan 17 22:20:01 2018

TPT SCRIPT

USING CHARACTER SET UTF8
DEFINE JOB "demo_tpt_tpt_cdc_Demo_1516018197546"
DESCRIPTION 'Data Loading Job'
(
DEFINE SCHEMA tpt_cdc_Demo_SCHEMA
DESCRIPTION 'SCHEMA DETAILS'
(
Product_ID VARCHAR(4)
, Product_Name VARCHAR(40)
, Product_Code VARCHAR(15)
, Product_History VARCHAR(15)
, Created_Date VARCHAR(19)
, Updated_Date VARCHAR(19)
, End_Date VARCHAR(19)
);


DEFINE OPERATOR FILE_READER
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA tpt_cdc_Demo_SCHEMA
ATTRIBUTES
(
VARCHAR DIRECTORY_PATH = 'C:\TeradataOffice\td2tdapp\demo_tpt\'
,VARCHAR FileName = 'C:\TeradataOffice\td2tdapp\demo_tpt\DEMO_TPT_TPT_CDC_DEMO.csv'
,VARCHAR IndicatorMode = 'N'
,VARCHAR OpenMode = 'Read'
,VARCHAR Format = @Format
,VARCHAR TextDelimiter = ','
);

DEFINE OPERATOR Stream_OPERATOR
 TYPE STREAM
 SCHEMA *
 ATTRIBUTES
(
VARCHAR TdpId = @TargetTdpId
,VARCHAR UserName = @TargetUserName
,VARCHAR UserPassword = @TargetUserPassword
,VARCHAR TargetTable = 'TPT_Test.tpt_cdc_Demo1'
,VARCHAR ErrorTable1 = 'TPT_Test.tpt_cdc_Demo1_ET1'
,VARCHAR ErrorTable2 = 'TPT_Test.tpt_cdc_Demo1_ET2'
,VARCHAR LogTable = 'TPT_Test.tpt_cdc_Demo1_LT'
,INTEGER MaxDecimalDigits = 38
,VARCHAR SpoolMode = 'NoSpoolOnly'
,INTEGER MaxSessions=32
,INTEGER Minsessions=8
,VARCHAR DateForm='AnsiDate'
);
STEP D_MERGE
               (
	  APPLY
                              (
	 ' 
 MERGE INTO TPT_TEST.tpt_cdc_Demo1 USING VALUES (:Product_ID,:Product_Name,:Product_Code,:Product_History,:Created_Date,:Updated_Date,:End_Date)
 AS Dept (Product_ID, Product_Name, Product_Code, Product_History,Created_Date, Updated_Date, End_Date) 
 ON Dept.Product_ID = tpt_cdc_Demo1.Product_ID
 WHEN MATCHED THEN UPDATE
 SET Dept.Product_Code = tpt_cdc_Demo1.Product_Code
 WHEN NOT MATCHED THEN INSERT
 VALUES (Dept.Product_ID, Dept.Product_Name, Dept.Product_Code, Dept.Product_History,
		Dept.Created_Date, Dept.Updated_Date, Dept.End_Date);')
		TO OPERATOR (Stream_OPERATOR)
		SELECT * FROM OPERATOR (FILE_READER[1]);
		
							  
               );


);			   
			   

 

Teradata Employee

Re: Dynamic Merge Statement in TPT

The SET clause is invalid. The left hand side must not have a table name or alias, and you can't modify the source value.

 

SET Product_Code = Dept.Product_Code

Teradata Employee

Re: Dynamic Merge Statement in TPT

Thanks @Fred @feinholz . Issue has been fixed using stream operator.