Teradata Benchmark Query Driver Reference

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Teradata Benchmark Query Driver Reference

New Release!!

See the new TdBench 7.2 version for Linux at http://downloads.teradata.com/download/tools/tdbench7-2

... TdBench V5 - MS DOS 

Teradata Benchmark Query Driver (TdBench) provides a set of tools to help you compare the performance within a data warehouse:

  • before/after a new release of the DBMS
  • before/after changes to the PDM (indexes, compression, etc)
  • relative performance of a new database platform

The tools provide a framework for executing benchmarks driven by a Windows Server or PC and reporting on the results using DBQL. There are also tools for extracting a cohesive set of Queries and Tables from DBQL to define the benchmark.

This reference guide explains how to install, configure and use the TdBench package.

TdBench is provided free of charge in the belief that good benchmarks are an accurate reflection of your real or planned production load. If Teradata customers and prospects can do a better job of executing such tests with the use of this software, they will be able to make better decisions on selection of tools, planning for rollout, and managing system growth.

This package was developed by the Teradata Americas Benchmark Center of Expertise in support of competitive benchmarks. However, we found that customers recommended its use in support of software and database changes. As a result, this package was presented at the Teradata Partners 2010 Conference in the session “Build Your Own Benchmark Center”.

Terminology

  • Test: one or more queries to be run in one or more sessions. Defined by a .sql, .btq, .bat, or .lst file located in the scripts subdirectory.
  • Run: an instance of an execution of a test, tracked in testtracking table on Teradata
  • Worker: Session(s) logged on to execute the test
  • Control user: Logon of the user to prepare tests for execution and monitors/terminates fixed period tests. For simple tests, this logon may also run the application queries for the benchmark.
  • Benchmark database: contains tables, views, macros, and stored procedures for the benchmark and is the parent of the worker logonid’s defined for the benchmark. The Control user is typically the same as this.
  • .sql files: list of SQL statements, each on a single line. These SQL statements be executed by multiple workers from one or more queues.
  • .btq files: a script for execution by BTEQ. (Note that BTEQ can run other utilities via .OS command). Each BTQ file can be executed by multiple workers. 
  • .bat files: a batch script. Each can execute utilities and multiple can be run during a test.
  • .lst files: a file of parameters defining a mixed workload.

Conceptual view of Query Driver

The QueryDriver manages the execution of a set of tests which are defined by

  • scripts executed in batch (.btq files),
  • a set of SQL commands (.sql files), generally executions of macros or calls to stored procedures
  • DOS batch commands for complex executions (.bat files)
  • or a mixed workload of assorted .sql, .btq and/or .bat files as defined in a .lst file.

Each execution of a test results in a “Run” which is identified by a sequentially numbered RunID in directories on the Windows Server or PC and in a TestTracking table on Teradata. The TestTracking table provides the precise start and stop timestamps for each test along with the identification of the test and your description of the conditions for that test.

A unique feature of this query driver is the support of one or more queue tables for SQL commands to be executed by worker sessions. This simplifies the definition and control of the tests by allowing different query workloads to be defined (e.g. heavy analytics, medium reporting, and light tactical), and arbitrarily changing the number of worker sessions without needing to change the scripts. It also supports different testing models such as fixed period, fixed work, and decay.

Reporting views are defined which uses the TestTracking table and DBQL or Resusage tables for creating reports. The design allows for the benchmark database to extract the historical data from the DBC database and retain multiple benchmark databases for future analysis. 

5 REPLIES
Enthusiast

Re: Teradata Benchmark Query Driver Reference

Hi, we are looking to implement the above and have hit a few issues. We are running using one que table per query, we have 75 queries of varying workloads defined via TASM. There is a user defined for each query, we are running the jobs uing a lst file like:

Scripts\query10.sql 1 Bench_usr_run001 QueryQueue001 1
...
Scripts\query11.sql 1 Bench_usr_run075 QueryQueue075 1

If we run this test for 5minutes we would expect the queries on TD to finish / be aborted. This does not happen, we see 4 queries in QTDELAYED, and looking in their queue table it is empty (as the test has finished). No end time has been updated this makes reporting using the view impossibly (due to looking for start / end time). Queries still running at that point stay running. All permissions on the control users have been granted, so am not sure where to look anymore.
Enthusiast

Re: Teradata Benchmark Query Driver Reference

Something to say? Add your comment here.

Re: Teradata Benchmark Query Driver Reference

how can i load the data in teradata database table using bteq

ex i have three columns like c1 c2 c3
1 2 srinu,rao
3 4 rama,rao how i eliminate the , from c3 column to laod the data into database
Enthusiast

Re: Teradata Benchmark Query Driver Reference

I like the package. I had to make a few changes so it would work on TD12 and use the PDRC data we have, but after making these changes it has provided us a ral benchmark to help on upgrade and comparing other hardware /databases.
Fan

Re: Teradata Benchmark Query Driver Reference

We also would like to implement it on V12 but it fails at setup. Could somebody tells us if there is a V12 compatible version of this tool or what needs to be changed to make it work on V12. Thanks.