Datometry Hyper-Q implements the semantics of the source system on the target system. Operations that exist natively on the target system may differ in semantics, requiring emulation to achieve semantically identical behavior.
Teradata UPDATE semantics differ from SQL DW UPDATE semantics when multiple source rows update the same target row. On Teradata, this results in an error, failing the UPDATE statement. In SQL DW, this UPDATE goes through and non-deterministically updates the target row with any of the source rows.
To provide Teradata semantics on SQL DW and error out when multiple source rows update the same target row, Hyper-Q generally emulates UPDATE statements on SQL DW. This emulation can be disabled upon request to pass through simple UPDATE statements directly to SQL DW, implying SQL DW semantics when multiple source rows update the same target row.
For additional information, please refer to the example below and to this article on complex UPDATE statements.
Consider the following two tables:
CREATE TABLE employees (empid INTEGER, empname VARCHAR(100), empphone VARCHAR(20));
CREATE TABLE empdepts (empid INTEGER, depname VARCHAR(100), empphone VARCHAR(20));
INSERT INTO employees VALUES (1, 'Francine Smith', NULL);
INSERT INTO empdepts VALUES (1, 'Marketing', '555-111-1111');
INSERT INTO empdepts VALUES (1, 'Engineering', '555-333-3333');
When updating the employee's phone number in table employees from the table empdepts by joining on empid, two source rows from empdepts are candidates to update the same target row in employees.
On Teradata, this operation fails:
FROM employees E, empdepts D
SET empphone = D.empphone
WHERE E.empid = D.empid;
[Teradata Database] [TeraJDBC 15.10.00.37] [Error 7547] [SQLState HY000] Target row updated by multiple source rows. [SQL State=HY000, DB Errorcode=7547]
On SQL DW, this operation succeeds:
SET empphone = D.empphone
FROM empdepts D
WHERE employees.empid = D.empid;
UPDATE employees successful
1 row affected
Enabling Native SQL DW UPDATE Semantics
To improve performance it can be desirable to skip Hyper-Q's emulation steps and execute UPDATE statements with native SQL DW semantics instead. If the workload does not exhibit UPDATEs with multiple source rows updating the same target row and/or does not rely on the detection of such a condition this change can be made without affecting the result of the UPDATE statements.
Native SQL DW update semantics can be enabled by adding the option
passthru_upd_with_join = true to the gateway in the
dtm.ini configuration file.