TPC-DS - dsqgen

Tpc Ds Data Flow


dsqgen translates the query templates into fully functional SQL, which is known as executable query text (EQT).

dsqgen can create:


Since some common SQL features do not have ANSI standard forms (e.g. “LIMIT” and “BEGIN/COMMIT”), the dsqgen utility must be told which “dialect” to use. The following “dialect templates” are supported:

  • db2.tpl,
  • netezza.tpl,
  • oracle.tpl,
  • sqlserver.tpl


The following information are mandatory:

  • a distribution file.
    • By default the tpcds.idx file (in the current working dir)
    • or the distribution option given at the command line
  • a dialect file.
    • By default, the ansi.tpl file (in query_templates_dir)
    • or the dialect option given at the command line
  • and either:
    • a TEMPLATE option for the generation of one SQL query
    • or INPUT for the generation of a SQL stream

Help on Windows:

dsqgen.exe /help
qgen2 Population Generator (Version 2.8.0)
Copyright Transaction Processing Performance Council (TPC) 2001 - 2018

USAGE: qgen2 [options]

Note: When defined in a parameter file (using -p), parmeters should
use the form below. Each option can also be set from the command
line, using a form of '/param [optional argument]'
Unique anchored substrings of options are also recognized, and
case is ignored, so '/sc' is equivalent to '/SCALE'

General Options
FILE =  <s>              -- read parameters from file <s>
VERBOSE =  [Y|N]         -- enable verbose output
HELP =  [Y|N]            -- display this message
OUTPUT_DIR =  <s>        -- write query streams into directory <s>
QUIET =  [Y|N]           -- suppress all output (for scripting)
STREAMS =  <n>           -- generate <n> query streams/versions
INPUT =  <s>             -- read template names from <s>
SCALE =  <n>             -- assume a database of <n> GB
LOG =  <s>               -- write parameter log to <s>
QUALIFY =  [Y|N]         -- generate qualification queries in ascending order

Advanced Options
DISTRIBUTIONS =  <s>     -- read distributions from file <s>
PATH_SEP =  <s>          -- use <s> to separate path elements
RNGSEED =  <n>           -- seed the RNG with <n>
RELEASE =  [Y|N]         -- display QGEN release info
TEMPLATE =  <s>          -- build queries from template <s> ONLY
COUNT =  <n>             -- generate <n> versions per stream (used with TEMPLATE)
DEBUG =  [Y|N]           -- minor debugging outptut
FILTER =  [Y|N]          -- write generated queries to stdout
DIALECT =  <s>           -- include query dialect defintions found in <s>.tpl
DIRECTORY =  <s>         -- look in <s> for templates


One Query

The following example generates a SQL file (named query_0.sql) from the query1 template for a 100GB database using Oracle syntax.

  • Linux
dsqgen `
  –template query1.tpl `
  –directory query_templates `
  –dialect oracle `
  –scale 100
  • Windows (With relative path)
dsqgen  ^
  /template "..\query_templates\query1.tpl" ^
  /scale 100 ^
  /directory "..\query_templates"


See Query stream generation

Documentation / Reference

  • See QGEN.doc in test directory

Discover More
Tpc Ds Data Flow

TPC-DS was designed to be representative of a traditional report-based workload. TPC-DS models the decision support functions of a retail product supplier. TPC-DS does not benchmark...
Tpcds Visual Studio Build
TPC-DS - Build

How to build the TCP-DS tool. Ie and OS Prerequisites: TPCDS version v2.8.0rc4 Download Visual Studio 2013 for Windows Desktop (versie 12) at: Download...
Tpc Ds Data Flow
TPC-DS - Query Stream

A query stream is defined as the sequential execution of a permutation of queries submitted by a single emulated user. Ie this is a file containing multiple queries. See with Linux Windows:...
Tpc Ds Data Flow
TPC-DS - Query Template

A query template is translated by dsqgen into fully functional SQL, which is known as executable query text (EQT). The query templates are primarily phrased in compliance with SQL1999 core (with OLAP...
Tpc Ds Data Flow
TPC-DS - executable query text (EQT)

An executable query text (EQT) is a query that: is created with the dsqgen tool from a query template file can be executed against a database
Tpc Ds Data Flow
TPC-DS - tpcds.idx (distcomp utility)

The distcomp utility build an tpcds.idx file that defines the data distribution. This file is read by: dsdgen. and dsqgen The below is called before the build of dsdgen. where: tpcds.dst...

Share this page:
Follow us:
Task Runner