3

Teradata has an "intelligent" in-memory engine that dynamically loads some data into memory based on usage.

How can I force it to create or store a table in memory all the time?

1 Answer 1

4

The answer to your question and other information about Teradata Intelligent Memory (TIM) can be found in the Orange Book about Teradata Intelligent Memory. Also in the appendixes are some SQL samples to query Teradata to verify what the temperature of a specific table is.

Please be advised that there is a general advice to NOT load data directly as "very hot", but to "hot" and let TVS then handle the temperature characteristics. Otherwise cooling down could be a problem.

To directly answer your question, there are 3 options:

1) Wait and access the table until TVM sees that the data is "very hot". If normal query characteristics access the table often there is a high chance the table will get loaded in memory and stay there.

2) Use a queryband to force it to "very hot"

SET QUERY_BAND='TVSTEMPERATURE_PRIMARY=VERYHOT;' FOR SESSION;

However you need to have access to the macro DBC.VHCTRL(). Otherwise TD will silently set your data to "hot".

3) Use FERRET:

> force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
FORCE command changed the temperature of table ADW_DB.INVOICE
to VERY-HOT.
The temperature of 638 cylinders have been changed to VERY-HOT.

Please also note that a DBA is not amused if you suddenly load a huge table in TIM in production, forcing other data to not reside in TIM. There is a reason why the queryband is restricted...

2
  • Is TIM enabled by default after TD version 14.10? Is there any explicit way to turn on TIM for Teradata?
    – Code_Jamer
    Commented Dec 1, 2016 at 7:22
  • 1
    It depends I think. For the new systems where the requirements for TIM is met it is enabled by default. For the older systems TIM can be enabled. In general the requirements are Teradata version 14.10 or higher and more then 5 GB/amp of memory available for appliances and 8GB/amp for EDW platforms. TIM refuses to work if the requirements are not met. Commented Jan 28, 2017 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.