Life Cycle of a Teradata Query



Teradata users or any other applications submit a query in the form of SQL (Structure Query Language) and receive the answer. But behind the scene, it has to pass many tests which determine its fate.

Below are the steps which describe the Life Cycle of a Teradata Query:

Query Submit:-

The starting point of a Teradata query starts when user submits a query. A call level interface (CLI) prepares parcel from that SQL query. Parcels can be two types –

  • Request parcel – contains SQL statement.
  • Data parcel – contains any parameter values that may be supplied.

Connect to Database:-

Then parcels are sent to Micro Teradata Director Program (MTDP) which establish session to the Teradata database. MTDP prepares a request message containing the parcel for transmission to the Teradata database via network. A Micro Operating System Interface (MOSI) is used to accept this request message to the operating system and network protocol like TCP/IP.

Teradata query life cycle

Extract Parcel:-

The PE receives the request message via gateway and PE will extract request parcel and data parcel. The PE is composed of several modules: syntaxer, resolver, security, optimizer, generator and dispatcher.

Passes through security:-

  • Syntaxer: Check the SQL query for proper syntax.
  • Resolver: look up for existence of objects in data dictionary.
  • Security: Determine if the user or database has proper access rights.
  • Optimizer: It creates most optimal plan for a query.
  • Generation: Create steps for the winning plan.
  • Dispatcher: Dispatches the steps to AMP via bynet.

Execute the query:-

Each AMP owns a portion of data and manages its own sets of lock and has its own sets of data dictionary. Each one works independently of the other AMPs. Execution steps are received in parallel by all AMPs participating in the step, and an AMP worker task (AWT) on each AMP is assigned to execute the step.

AWT may sort, aggregate and redistribute the data as needed to produce answer set.

Produce result Sets:-

Each participating AMP will returns it portion of result to the Bynet which merge all the result set and prepare the final result and pass it to dispatcher.

Return Answer to the user:-

Dispatcher now packages the result into response parcel and dispatch it to the Gateway to deliver over the network connection as response message. The response parcel is then extracted from response message and the answer set is returned to the user.