437 lines
17 KiB
Plaintext
Executable File
437 lines
17 KiB
Plaintext
Executable File
# @(#)README 2.4.0
|
|
|
|
Table of Contents
|
|
===================
|
|
0. What is this document?
|
|
1. What is DBGEN?
|
|
2. What will DBGEN create?
|
|
3. How is DBGEN built?
|
|
4. Command Line Options for DBGEN
|
|
5. Building Large Data Sets with DBGEN
|
|
6. DBGEN limitations and compliant usage
|
|
7. Sample DBGEN executions
|
|
8. What is QGEN?
|
|
9. What will QGEN create?
|
|
10. How is QGEN built?
|
|
11. Command Line Options for QGEN
|
|
12. Query Template Syntax
|
|
13. Sample QGEN executions and Query Templates
|
|
14. Environment variable
|
|
15. Version Numbering in DBGEN and QGEN
|
|
16. Validated Platforms
|
|
|
|
0. What is this document?
|
|
|
|
This is the general README file for DBGEN and QGEN, the data-
|
|
base population and executable query text generation programs
|
|
used in the TPC-H benchmark. It covers the proper use
|
|
of DBGEN and QGEN. For information on porting the utility to your
|
|
particular platform see Porting.Notes.
|
|
|
|
1. What is DBGEN?
|
|
|
|
DBGEN is a database population program for use with the TPC-H benchmark.
|
|
It is written in ANSI 'C' for portability, and has
|
|
been successfully ported to over a dozen different systems. While the
|
|
TPC-H specification allow an implementor to use any utility
|
|
to populate the benchmark database, the resultant population must exactly
|
|
match the output of DBGEN. The source code has been provided to make the
|
|
process of building a compliant database population as simple as possible.
|
|
|
|
2. What will DBGEN create?
|
|
|
|
Without any command line options, DBGEN will generate 8 separate ascii
|
|
files. Each file will contain pipe-delimited load data for one of the
|
|
tables defined in the TPC-H database schema. The default tables
|
|
will contain the load data required for a scale factor 1 database. By
|
|
default the file will be created in the current directory and be
|
|
named <table>.tbl. As an example, customer.tbl will contain the
|
|
load data for the customer table.
|
|
|
|
When invoked with the '-U' flag, DBGEN will create the data sets to be
|
|
used in the update functions and the SQL syntax required to delete the
|
|
data sets. The update files will be created in the same directory as
|
|
the load data files and will be named "u_<table>.set". The delete
|
|
syntax will be written to "delete.set". For instance, the data set to
|
|
be used in the third query set to update the lineitem table will be
|
|
named "u_lineitem.tbl.3", and the SQL to remove those rows will be
|
|
found in "delete.3". The size of the update files can be controlled
|
|
with the '-r' flag.
|
|
|
|
3. How is DBGEN built?
|
|
|
|
Create an appropriate makefile, using makefile.suite as a basis,
|
|
and type make. Refer to Porting.Notes for more details and for
|
|
suggested compile time options.
|
|
|
|
4. Command Line Options for DBGEN
|
|
|
|
DBGEN's output is controlled by a combination of command line options
|
|
and environment variables. Command line options are assumed to be single
|
|
letter flags preceded by a minus sign. They may be followed by an
|
|
optional argument.
|
|
|
|
option argument default action
|
|
------ -------- ------- ------
|
|
-h Display a usage summary
|
|
|
|
-f none Force. Existing data files will be
|
|
overwritten.
|
|
|
|
-F none yes Flat file output.
|
|
|
|
-D none Direct database load. ld_XXXX() routines
|
|
must be defined in load_stub.c
|
|
|
|
-s <scale> 1 Scale of the database population. Scale
|
|
1.0 represents ~1 GB of data
|
|
|
|
-T <table> Generate the data for a particular table
|
|
ONLY. Arguments: p -- part/partuspp,
|
|
c -- customer, s -- supplier,
|
|
o -- orders/lineitem, n -- nation, r -- region,
|
|
l -- code (same as n and r),
|
|
O -- orders, L -- lineitem, P -- part,
|
|
S -- partsupp
|
|
|
|
-O d Generate SQL for delete function
|
|
instead of key ranges
|
|
|
|
-O f Allow over-ride of default output file
|
|
names
|
|
|
|
-O h Generate headers in flat ascii files.
|
|
hd_XXX routines must be defined in
|
|
load_stub.c
|
|
|
|
-O m Flat files generate fixed length records
|
|
|
|
-O r Generate key ranges for the UF2 update
|
|
function
|
|
|
|
-O v Verify data set without generating it.
|
|
|
|
-r <percentage> 10 Scale each udpate file to the given
|
|
percentage (expressed in basis points)
|
|
of the data set
|
|
|
|
-v none Verbose. Progress messages are
|
|
displayed as data is generated.
|
|
|
|
-n <name> Use database <name> for in-line load
|
|
|
|
-C <children> Use <children> separate processes to
|
|
generate data
|
|
|
|
-S <n> Generate the <n>th part of a multi-part load
|
|
or update set
|
|
|
|
-U <updates> Create a specified number of data sets
|
|
in flat files for the update/delete
|
|
functions
|
|
|
|
-i <n> Split the inserted rows in an refresh pair
|
|
between <n> files
|
|
|
|
-d <n> Split the deleted rows in an refresh pair
|
|
between <n> files
|
|
|
|
5. DBGEN limitations and compliant usage
|
|
|
|
DBGEN is meant to be a robust population generator for use with the
|
|
TPC-H benchmark. It is hoped that DBGEN will make it easier
|
|
to experiment with and become proficient in the execution of TPC decision
|
|
support benchmarks. As a result, it includes a number of command line
|
|
options which are not, strictly speaking, necessary to generate a compliant
|
|
data set for a TPC-D run. In addition, some command line options will accept
|
|
arguments which result in the generation of NON-COMPLIANT data sets. Options
|
|
which should be used with care include:
|
|
|
|
-s -- scale factor. TPC-H runs are only compliant when run against SF's
|
|
of 1, 10, 100, 300, 1000, 3000, 10000, 30000, 100000
|
|
-r -- refresh percentage. TPC-H runs are only compliant when run with
|
|
-r 10, the default.
|
|
|
|
6. Sample DBGEN executions
|
|
|
|
DBGEN has been built to allow as much flexibility as possible, but is
|
|
fundementally intended to generate two things: a database population
|
|
against which the queries in TPC-H can be run, and the updates
|
|
that are used during the update functions in TPC-H. Here are
|
|
some sample uses of DBGEN.
|
|
|
|
1. To generate the database population for the qualification database
|
|
dbgen -s 1
|
|
2. To generate the lineitem table only, for a scale factor 10 database,
|
|
and over-write any existing flat files:
|
|
dbgen -s 10 -f -T L
|
|
4. To geterate a 100GB data set in 1GB pieces, generate only the part and
|
|
partsupplier tables, and include some progress reports along the way:
|
|
dbgen -s 100 -S 1 -C 100 -T p -v (to generate the first 1GB file)
|
|
dbgen -s 100 -S 2 -C 100 -T p -v (to generate the second 1GB file)
|
|
(and so on, incrementing the argument to -S each time)
|
|
5. To generate the update files needed for a 4 stream run of the throughput
|
|
test at 100 GB, using an existing set of seed files from an 8 process
|
|
load:
|
|
dbgen -s 100 -U 4 -C 8
|
|
|
|
|
|
7. What is QGEN?
|
|
|
|
QGEN is a query generation program for use with the TPC-H benchmark.
|
|
It is written in ANSI 'C' for portability, and has been successfully
|
|
ported to over a dozen different systems. While the benchmark specifications
|
|
allow an implementor to use any utility to create the benchmark query
|
|
sets, QGEN has been provided to make the process of building
|
|
a benchmark implementation as simple as possible.
|
|
|
|
8. What will QGEN create?
|
|
|
|
QGEN is a filter, triggered by :'s. It does line-at-a-time reads of its
|
|
input (more on that later), scanning for :foo, where foo determines the
|
|
substitution that occurs. Including:
|
|
|
|
:<int> replace with the appropriate value for parameter <int>
|
|
:b replace with START_TRAN (from tpcd.h)
|
|
:c replace with SET_DBASE (from tpcd.h)
|
|
:n<int> replace with SET_ROWCOUNT(<int>) (from tpcd.h)
|
|
:o replace with SET_OUTPUT (from tpcd.h)
|
|
:q replace with query number
|
|
:s replace with stream number
|
|
:x replace with GEN_QUERY_PLAN (from tpcd.h)
|
|
|
|
Qgen takes an assortment of command line options, controlling which of these
|
|
options should be active during the translation from template to EQT, and a
|
|
list of query "names". It then translates the template found in
|
|
$DSS_QUERY/<name>.sql and puts the result of stdout.
|
|
|
|
Here is a sample query template:
|
|
|
|
{ Sccsid: @(#)1.sql 9.1.1.1 1/25/95 10:51:56 }
|
|
:n 0
|
|
:o
|
|
select
|
|
l_returnflag,
|
|
l_linestatus,
|
|
sum(l_quantity) as sum_qty,
|
|
sum(l_extendedprice) as sum_base_price,
|
|
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
|
|
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
|
|
avg(l_quantity) as avg_qty,
|
|
avg(l_extendedprice) as avg_price,
|
|
avg(l_discount) as avg_disc,
|
|
count(*) as count_order
|
|
from lineitem
|
|
where l_shipdate <= date '1998-12-01' - interval :1 day
|
|
group by l_returnflag, l_linestatus
|
|
order by l_returnflag, l_linestatus;
|
|
|
|
And here is what is generated:
|
|
$ qgen -d 1
|
|
|
|
{return 0 rows}
|
|
|
|
select
|
|
l_returnflag,
|
|
l_linestatus,
|
|
sum(l_quantity) as sum_qty,
|
|
sum(l_extendedprice) as sum_base_price,
|
|
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
|
|
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
|
|
avg(l_quantity) as avg_qty,
|
|
avg(l_extendedprice) as avg_price,
|
|
avg(l_discount) as avg_disc,
|
|
count(*) as count_order
|
|
from lineitem
|
|
where l_shipdate <= date('1998-12-01') - interval (90) day to day
|
|
group by l_returnflag, l_linestatus
|
|
order by l_returnflag, l_linestatus;
|
|
|
|
See "Query Template Syntax" below for more detail on converting your prefered query
|
|
phrasing for use with QGEN.
|
|
|
|
9. How is QGEN built?
|
|
|
|
QGEN is built by the same makefile that creates DBGEN. If the makefile
|
|
is successfully creating DBGEN, no further compilation modifications
|
|
should be necessary. You may need to modify some of the options which
|
|
allow QGEN to integrate with your preferred query tool. Refer to
|
|
Porting.Notes for more detail.
|
|
|
|
10. Command Line Options for QGEN
|
|
|
|
Like DBGEN, QGEN is controlled by a combination of command line options
|
|
and environment variables (See "Environment Variables", below for more
|
|
detail). Command line options are assumed to be single
|
|
letter flags preceded by a minus sign. They may be followed by an
|
|
optional argument.
|
|
|
|
option argument default action
|
|
------ -------- ------- ------
|
|
-c none Retain comments in translation of template to
|
|
EQT
|
|
|
|
-d none Default. Use the parameter substitutions
|
|
required for query validation
|
|
|
|
-h Display a usage summary
|
|
|
|
-i <file> Use contents of <file> to init a query stream
|
|
|
|
-l <file> Save query parameters to <file>
|
|
|
|
-n <name> Use database <name> for queries
|
|
|
|
-N Always use default rowcount, and ignore :n directives
|
|
|
|
-o <path> Save query n's output in <path>/n.<stream>
|
|
Uses -p option, and uses :o tag
|
|
|
|
-p <stream> Use the query permutation defined for
|
|
stream <stream>. If this option is
|
|
omited, EQT will be generated for the
|
|
queries named on the command line.
|
|
|
|
-r <n> Seed the rnadom number generator with <n>
|
|
|
|
-s <n> Set scale to <n> for parameter
|
|
substitutions.
|
|
|
|
-t <file> Use contents of <file> to complete a query
|
|
stream
|
|
|
|
-T none Use time table format for date substitution
|
|
|
|
-v none Verbose. Progress messages are
|
|
displayed as data is generated.
|
|
|
|
-x none Generate a query plan as part of query
|
|
execution.
|
|
|
|
11. Query Template Syntax
|
|
|
|
QGEN is a simple ASCII text filter, meant to translate query generalized
|
|
query syntax("query template") into the executable query text(EQT) re-
|
|
quired by the benchmarks. It provides a number of shorthands and syntactic
|
|
extensions that allow the automatic generation of query parameters and some
|
|
control over the operation of the benchmark implementation.
|
|
|
|
QGEN first strips all comments from the query template, recognizing both
|
|
{comment} and --comment styles. Next it traverses the query template
|
|
one line at a time, locating required substitution points, called
|
|
parameter tags. The values substituted for a given tag are summarized
|
|
below. QGEN does not support nested substitutions. That is, if
|
|
the text substituted for tag itself contains a valid tag the second tag
|
|
will not be expanded.
|
|
|
|
Tag Converted To Based on
|
|
=== ============ ========
|
|
:c database <dbname>;(1) -n from the command line
|
|
:x set explain on;(1) -x from the command line
|
|
:<number> paremeter <number>
|
|
:s stream number
|
|
:o output to outpath/qnum.stream;(1)
|
|
-o from command line, -s from
|
|
command line
|
|
:b BEGIN WORK;(1) -a from comand line
|
|
:e COMMIT WORK(1) -a from command line
|
|
:q query number
|
|
:n <number> sets rowcount to be returned
|
|
to <number>, unless -N appears on the command line
|
|
|
|
Notes:
|
|
(1) This is Informix-specific syntax. Refer to Porting.Notes for
|
|
tailoring the generated text to your database environment.
|
|
|
|
12. Sample QGEN executions and Query Templates
|
|
|
|
QGEN translates generic query templates into valid SQL. In addition, it
|
|
allows conditional inclusion of the commands necessary to connect to a
|
|
database, produce diagnostic output, etc. Here are some sample of QGEN
|
|
usage, and the way that command line parameters and the query templates
|
|
interact to produce valid SQL.
|
|
|
|
Template, in $DSS_QUERY/1.sql:
|
|
:c
|
|
:o
|
|
select count(*) from foo;
|
|
:x
|
|
select count(*) from lineitem
|
|
where l_orderdate < ':1';
|
|
|
|
1. "qgen 1", would produce:
|
|
select count(*) from foo;
|
|
select count(*) from lineitem
|
|
where l_orderdate < '1997-01-01';
|
|
Assuming that 1 January 1997 was a valid substitution for parameter 1.
|
|
|
|
2. "qgen -d -c dss1 1, would produce:
|
|
database dss1;
|
|
select count(*) from foo;
|
|
select count(*) from lineitem
|
|
where l_orderdate < '1995-07-18';
|
|
Assuming that 18 July 1995 was the default substitution for parameter 1,
|
|
and using Informix syntax.
|
|
|
|
3. "qgen -d -c dss1 -x -o somepath 1, would produce:
|
|
database dss1;
|
|
output to "somepath/1.0"
|
|
select count(*) from foo;
|
|
set explain on;
|
|
select count(*) from lineitem
|
|
where l_orderdate < '1995-07-18';
|
|
Assuming that 18 July 1995 was the default substitution for parameter 1,
|
|
and using Informix syntax.
|
|
|
|
|
|
13. Environment Variables
|
|
|
|
Enviroment variables are used to control features of DBGEN and QGEN
|
|
which are unlikely to change from one execution to another.
|
|
|
|
Variable Default Action
|
|
------- ------- ------
|
|
DSS_PATH . Directory in which to build flat files
|
|
DSS_CONFIG . Directory in which to find configuration files
|
|
DSS_DIST dists.dss Name of distribution definition file
|
|
DSS_QUERY . Directory in which to find query templates
|
|
|
|
14. Version Numbering in DBGEN and QGEN
|
|
|
|
DBGEN and QGEN use a common version numbering algorithm. Each executable
|
|
is stamped with a version number which is displayed in the usage messages
|
|
available with the '-h' option. A version number is of the form:
|
|
|
|
V.R.P.M
|
|
| | | |
|
|
| | | |
|
|
| | | |
|
|
| | | -- modification: alphabetic, incremented for any trivial changes
|
|
| | | to the source (e.g, porting ifdef's)
|
|
| | ---- patch level: numeric, incremented for any minor bug fix
|
|
| | (e.g, qgen parameter range)
|
|
| ------- release: numeric, incremented for each minor revision of the
|
|
| specification
|
|
|-------- version: numeric, incremented for each major revision of the
|
|
specification
|
|
|
|
An implementation of TPC-H is valid only if it conforms to the
|
|
following version usage rules:
|
|
|
|
-- The Version of DBGEN and QGEN must match the integer portion of the
|
|
current specification revision
|
|
|
|
15. The current revisions are:
|
|
DBGEN: 2.4.0
|
|
QGEN: 2.4.0
|
|
|
|
16. Validated Platforms
|
|
The following platforms have been validated to produce the reference
|
|
data set for TPC-H 2.4.0
|
|
Processor Operating System (version) Compiler (version) Compiler Flags
|
|
----------------------------------------------------------------------------
|
|
POWER5 AIX 64-bit (5.3) C for AIX Compiler, v7 -q64 (no -g)
|
|
IA-64 HPUX 64-bit () icc
|
|
Linux 32-bit () gcc
|
|
|