Teradata Enterprise stage dynamic lookup
Moderators: chulett, rschirm, roy
Teradata Enterprise stage dynamic lookup
My TD Enterprise stage is acting as a source that queries a couple of tables using joins. These tables are getting parallelly updated by other jobs.
The records queried by the TDE stage pass through a lookup stage and based on a particluar column value the matching records are inserted/updated to the target table via TPUMP stage.
As and when the source tables get updated in the real time process the lookup needs to be refreshed i.e. the query in Teradata Enterprise stage
should be proccessed again. Is there any option to do so. Do we have any APT parameters that would help in this scenario?
The records queried by the TDE stage pass through a lookup stage and based on a particluar column value the matching records are inserted/updated to the target table via TPUMP stage.
As and when the source tables get updated in the real time process the lookup needs to be refreshed i.e. the query in Teradata Enterprise stage
should be proccessed again. Is there any option to do so. Do we have any APT parameters that would help in this scenario?
"In god we trust; rest all we virus scan"
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Teradata is one type of database that does not support sparse lookups (where the SQL is fired for every row being processed). It only supports normal lookups where the lookup data is cached when the job starts. You could try doing all your updates on a shadow table (dataset) on the ETL server using change capture and change apply stage and then load the changed table into Teradata.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
However when we connect ODBC enterprise stage with lookup stage and choose the sparse lookup option, we are not able to choose the column meant for lookup in the lookup stage. It says "Key expressions cannot be set on key columns of this link. The connected stage defines the key lookup."
"In god we trust; rest all we virus scan"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's correct. Therefore you must have a stream input column that matches the lookup key column name and data type. This will be used to supply the value to be looked up. Use a Copy stage which can rename the column at no cost.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Sparse lookups do not use drag and drop for matching of key columns, rather they automatically match columns where the lookup column is identified as a key and the input link has a column with an exact matching name and metadata (as pointed out by Ray).
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn