TdBench is a query driver optimized for data warehouse benchmarks and works with any database that has a JDBC driver. It runs on Windows or Linux. It has been tested and used in benchmarks with Teradata, Postgresql, MySQL, Redshift, Netezza, IBM SailFish, Greenplum, Azure ASDW, SQL Server, and Snowflake. TdBench accumulates test results in an internal H2 database for analysis and can link to host database query logging for complete analysis of test results.

Instructional Videos:

Overview:

A TdBench test is created interactively or in batch with commands:

               define [testname] {title of test}

               queue [ queuename ] [ sql statements; | "OS" OS commnds; | filename | seach path ]

               worker [ queuename ] [ database alias | class server user password ]

               run { time specification { kill } }

For example, with queries in 2 directories to run for 30 minutes, the following script is needed:

define  10user  test with 7 reporting and 3 data mining users
queue  reporting  reporting_queries/*.sql
worker  reporting  mydb(rptuser01:10)  7
queue  mining  mining_queries/*.sql
worker  mining  mydb(dmuser01:05)  3
run 30m

To double the number of sessions, the worker counts 7 and 3 above would be changed to 14 and 6 respectively and the define statement adjusted. “mydb” is a database alias defined with a DB command and a CLASS command to say how to find the JDBC driver. The RUN statement may be given without a time specification in which case it will run all work and stop (a fixed work test).  The example above is a "fixed initiation period" test which will cycle the queries until 30 minutes have elapsed. If you add the word "kill" to the end of the RUN statement, it becomes a "fixed execution period" test.  In addition, you can put STOP or KILL into a queue to have it only execute once (e.g. an ETL queue). You can also put STOP ALL or KILL ALL into a queue so that a queue of primary interest can kill the other (noise) queues when it completes its work.

TdBench has help files for commands and topics. Putting a ? behind any command gives the brief syntax for that command. In addition, when running interactively, you can enter the ? by itself and TdBench will describe what you have done so far and what you need to do next to execute a test.  When the test is complete, you can save a test created interactively into a file and edit it for future use. 

TdBench has commands for

• setting the arrival rate of queries,
• setting start time of single queries for query replay or an entire queue of work
• forcing a test to continue until selected queues complete
• limiting the maximum run time of queries in a queue
• sleeping for a duration or until a scheduled time to start a test
• associating files of parameters with queues to multiply the number of queries
• immediate SQL and OS commands that can set up environment for test
• IF and GOTO statements for conditionally executing tests

Environment variables can be used to substitute text into scripts, there are program variables describing the current and most recent test, there are worker variables that can be substituted into queries, there are variables set by the INCLUDE statement that can change the included text, and there are parameter variables taken from records in files attached to a queue of work. 

TdBench assigns a RunID to each test and logs information about the test in an internal H2 Database:

• TestTracking - one row for each test, showing start/stop timestamps, execution counts, error counts and notes
• TestTrackingLog - the input lines plus output messages and errors from TdBench execution by RunID
• TestResults - One row for every query executed with timings, error code, and parameters used. 

H2 has a fairly robust SQL implementation that can be accessed within TdBench to capture test results from the point of view of the client pc/server. You can export results to a delimited file for further analysis.  In addition, you can define commands to execute before and after runs automatically to maintain a TestTracking table on the host with its start and stop timestamps for tests so you can analyze details of query execution.  At this point there are setup scripts for Teradata's DBQL and Resusage reporting from DBC and PDCR databases.

You can also define automatic saving of the logs from a test to be zipped up and placed in a directory by RunID so you can analyze issues if necessary or submit as proof of test execution. One customer had vendors submit their H2 database file for proof of execution. With the latest releases, you can use the DUMP command to zip up results and the RESTORE to import them into a common H2 database for analysis across vendors. 

TdBench 8.0.16 (September 6, 2019) has a new setup to display and assist in configuring for different DBMS's, CLASS statement allows wild card reference to JDBC since Google BigQuery has dozens of .jar files, added OPTION statement to control recordset creation to prevent performance issues on RedShift, allows parameters at both the queue and query level,  support for multi-statement requests, use color on console output to distinguish input from normal messages and error messages, allow parameters to TdBench Scripts to be included on the tdbench.bat or tdbench.sh to schedule a series of executions of a script with different parameters, and added a DEBUG statement to run scripts validating file references and database connections.  Full version history is in the readme.txt.  

We make TdBench available publicly for use on other database platforms to promote fair and productive benchmarks. It is still our intellectual property and in accordance with the license terms, you may not sell, rent, modify, embed, sublicense, relabel the product or remove copyrights.  Copyright (c) 2019, 2011, 2013, 2015, 2016, 2018, 2019 by the Teradata Corporation. 

Questions?  Comments?  Requests?  Ideas?  Contributions?

Go to https://support.teradata.com/community, select the “Database” forum and ask your question from the text box provided. If you are not yet registered as a Community User, select “Register” at the upper right to get an account.