3. Configuring Schema Build Options

3. Configuring Schema Build Options

To create the analytic test schema based on the TPC-H specification you will need to select which benchmark and database you wish to use by choosing select benchmark from under the Options menu or under the benchmark tree-view. The initial settings are determined by the values in your XML configuration files. The following example shows the selection of SQL Server however the process is the same for all databases.

Figure 10.2. Benchmark Options

Benchmark Options


To create the TPC-H schema select the TPC-H schema options menu tab from the benchmark tree-view or the options menu. This menu will change dynamically according to your chosen database.

Figure 10.3. TPC-H Schema Build Options

TPC-H Schema Build Options


If selected from the Options menu the schema options window is divided into two sections. The “Build Options” section details the general login information and where the schema will be built and the “Driver Options” for the Driver Script to run after the schema is built. If selected from the benchmark tree-view only the “Build Options” are shown and these are the only options of importance at this stage. Note that in any circumstance you do not have to rebuild the schema every time you change the “Driver Options”, once the schema has been built only the “Driver Options” may need to be modified. For the “Build Options” fill in the values according to the database where the schema will be built as follows.

3.1. Oracle Schema Build Options

Figure 10.4. Oracle TPC-H Build Options

Oracle TPC-H Build Options

Table 10.1. Oracle Build Options

OptionDescription
Oracle Service NameThe Oracle Service Name is the service name that your load generation server will use to connect to the database running on the SUT database server.
System UserThe “system” user or a user with system level privileges
System User PasswordThe system user password is the password for the “system” user you entered during database creation. The system user already exists in all Oracle databases and has the necessary permissions to create the TPC-H user.
TPC-H UserThe TPC-H user is the name of a user to be created that will own the TPC-H schema. This user can have any name you choose but must not already exist and adhere to the standard rules for naming Oracle users. You may if you wish run the schema creation multiple times and have multiple TPC-H schemas created with ownership under a different user you create each time.
TPC-H User PasswordThe TPC-H user password is the password to be used for the TPC-H user you create and must adhere to the standard rules for Oracle user password. You will need to remember the TPC-H user name and password for running the TPC-H driver script after the schema is built.
TPC-H Default TablespaceThe TPC-H default tablespace is the tablespace that will be the default for the TPC-H user and therefore the tablespace to be used for the schema creation. The tablespace must have sufficient free space for the schema to be created.
TPC-H Temporary TablespaceThe TPC-H temporary tablespace is the temporary tablespace that already exists in the database to be used by the TPC-H User.
TimesTen Database CompatibleWhen selected this option means that the Oracle Service Name should be a TimesTen Data Source Name and will grey out non-compatible options.
Scale FactorThe Scale Factor is selected by a radio button with a choice of scale factors of 1,10,30,100,300 and 1000 corresponding to 1GB, 10GB, 30GB,100GB and 1000GB respectively, larger schema sizes can also be created with the datagen option. Note that the required space will be larger than these values due to the indexes required.
Virtual Users to Build SchemaThe Virtual Users to Build Schema is the number of Virtual Users to be created on the Load Generation Server that will complete your multi-threaded schema build. You should set this value to the number of cores on your Load Generation Server or SUT if HammerDB is running there.


3.2. SQL Server Schema Build Options

Figure 10.5. SQL Server Build Options

SQL Server Build Options

Table 10.2. SQL Server Build Options

OptionDescription
SQL ServerThe Microsoft SQL Server is the host name or host name and instance where the TPC-H database will be created.
TCPUse the TCP Protocol
SQL Server PortWhen TCP is enabled, the SQL Server Port is the network port that your load generation server will use to connect to the database running on the SUT database server. In most cases this will be the default port of 1433 and will not need to be changed.
AzureInclude the Database name in the connect string typical of Azure connections. To successfully build the schema this database must be created and empty.
SQL Server ODBC DriverThe Microsoft SQL ODBC Driver is the ODBC driver you will use to connect to the SQL Server database. To view which drivers are available on Windows view the ODBC Data Source Administrator Tool.
AuthenticationWhen installing SQL Server on Windows you will have configured SQL Server for Windows or Windows and SQL Server Authentication. On Linux you will be using SQL Server Authentication. If you specify Windows Authentication then SQL Server will use a trusted connection to your SQL Server using your Windows credentials without requiring a username and password. If SQL Server Authentication is specified and SQL Authentication is enabled on your SQL Server then you will be able connect by specifying a username and password that you have already configured on your SQL Server.
SQL Server User IDThe SQL Server User ID is the User ID of a user that you have already created on your SQL Server.
SQL Server User PasswordThe SQL Server User Password is the Password configured on the SQL Server for the User ID you have specified. Note that when configuring the password on the SQL Server there is a checkbox that when selected enforces more complex rules for passwords or if unchecked enables a simple password such as “admin”.
SQL Server TPCH DatabaseThe SQL Server Database is the name of the Database to be created on the SQL Server to contain the schema. If this database does not already exist then HammerDB will create it, if the database does already exist and the database is empty then HammerDB will use this existing database. Therefore if you wish to create a particular layout or schema then pre-creating the database and using this database is an advanced method to use this configuration.
MAXDOPThe MAXDOP setting defines the maximum degree of parallelism to be set as a default on the schema objects.
Clustered ColumnstoreThis option selects the database to be created with in-memory clustered columnstore indexes.
Scale FactorThe Scale Factor is selected by a radio button with a choice of scale factors of 1,10,30,100,300 and 1000 corresponding to 1GB, 10GB, 30GB,100GB and 1000GB respectively, larger schema sizes can also be created with the datagen option. Note that the required space will be larger than these values due to the indexes required.
Virtual Users to Build SchemaThe Virtual Users to Build Schema is the number of Virtual Users to be created on the Load Generation Server that will complete your multi-threaded schema build. You should set this value to the number of cores on your Load Generation Server or SUT if HammerDB is running there.


3.3. Db2 Schema Build Options

Figure 10.6. Db2 Build Options

Db2 Build Options

Table 10.3. Db2 Build Options

OptionDescription
Db2 UserThe name of the operating system user to connect to the DB2 database for example db2inst1.
Db2 PasswordThe password for the operating system DB2 user by default “ibmdb2”
Db2 DatabaseThe name of the Db2 database that you have already created, for example “tpcc”
Db2 Default TablespaceThe name of the existing tablespace where tables should be located if a specific tablespace has not been defined for that table in the tablespace list. The default is “USERSPACE1”.
Db2 Organize ByThe Organize by option is selected by a radio button and determines an optional organize by clause to be specified when creating the tables. The database version must be able to accept the option chosen and therefore the recommended choice is NONE to accept the defaults. When the setting DB2_WORKLOAD is set to analytics for example the default is configuration is for columnar storage. If for example this parameter is set you can then choose ROW configuration even when DB2_WORKLOAD is set to analytics to create row organized tables. The DATE option is mutually exclusive to the column store option however creates a ROW organized table that is organized by date which can accelerate some queries when row organized.
Scale FactorThe Scale Factor is selected by a radio button with a choice of scale factors of 1,10,30,100,300 and 1000 corresponding to 1GB, 10GB, 30GB,100GB and 1000GB respectively, larger schema sizes can also be created with the datagen option. Note that the required space will be larger than these values due to the indexes required.
Virtual Users to Build SchemaThe Virtual Users to Build Schema is the number of Virtual Users to be created on the Load Generation Server that will complete your multi-threaded schema build. You should set this value to the number of cores on your Load Generation Server or SUT if HammerDB is running there.


3.4. MySQL Schema Build Options

Figure 10.7. MySQL Build Options

MySQL Build Options


Table 10.4. MySQL Build Options

OptionDescription
MySQL HostThe MySQL Host Name is the host name of the SUT database server.
MySQL PortThe MySQL Port is the network port on the SUT database server. In most cases this will be the default port of 3306.
MySQL UserThe MySQL User is the user which has permission to create a database and you previously granted access to from the load generation server. The root user already exists in all MySQL databases and has the necessary permissions to create the TPC-H database.
MySQL User PasswordThe MySQL user password is the password for the user defined as the MySQL User. You will need to remember the MySQL user name and password for running the TPC-H driver script after the database is built.
MySQL DatabaseThe MySQL Database is the database that will be created containing the TPC-H schema creation. There must have sufficient free space for the database to be created.
Data Warehouse Storage EngineUse the "show engine" command to display available storage engines and select a storage engine that supports analytics. For MariaDB columnstore specify. "Columnstore"
Scale FactorThe Scale Factor is selected by a radio button with a choice of scale factors of 1,10,30,100,300 and 1000 corresponding to 1GB, 10GB, 30GB,100GB and 1000GB respectively, larger schema sizes can also be created with the datagen option. Note that the required space will be larger than these values due to the indexes required.
Virtual Users to Build SchemaThe Virtual Users to Build Schema is the number of Virtual Users to be created on the Load Generation Server that will complete your multi-threaded schema build. You should set this value to the number of cores on your Load Generation Server or SUT if HammerDB is running there.


3.5. PostgreSQL Schema Build Options

Figure 10.8. PostgreSQL Build Options

PostgreSQL Build Options

Table 10.5. PostgreSQL Build Options

OptionDescription
PostgreSQL HostThe host name of the SUT running PostgreSQL.
PostgreSQL PortThe port of the PostgreSQL service. By default this will be 5432 for a standard PostgreSQL installation or 5444 for EnterpriseDB.
PostgreSQL SuperuserThe PostgreSQL Superuser is a user with sufficient privileges to create both new users (roles) and databases to enable the creation of the test schema.
PostgreSQL Superuser PasswordThe PostgreSQL Superuser Password is the password for the PostgreSQL superuser which will have been defined during installation. If you have forgotten the password it can be reset from a psql prompt that has logged in from a trusted connection therefore requiring no password using postgres=# alter role postgres password ‘postgres’;
PostgreSQL Default DatabaseThe PostgreSQL default databases is the database to specify for the superuser connection. Typically this will be postgres for a standard PostgreSQL installation or edb for EnterpriseDB.
PostgreSQL UserThe PostgreSQL User is the user (role) that will be created that owns the database containing the TPC-H schema.
PostgreSQL User PasswordThe PostgreSQL User Password is the password that will be specified for the PostgreSQL user when it is created.
PostgreSQL DatabaseThe PostgreSQL Database is the database that will be created and owned by the PostgreSQL User that contains the TPC-H schema.
Greenplum Database CompatibleChoosing Greenplum Database Compatible creates a schema with Greenplum Database Options. Building the schema by inserting into Greenplum is not recommended and instead a bulk load of data created with the datagen option should be used.
Greenplum Compressed ColumnsBecomes active when Greenplum Database Compatible is selected and configures the columns in a compressed format.
Scale FactorThe Scale Factor is selected by a radio button with a choice of scale factors of 1,10,30,100,300 and 1000 corresponding to 1GB, 10GB, 30GB,100GB and 1000GB respectively, larger schema sizes can also be created with the datagen option. Note that the required space will be larger than these values due to the indexes required.
Virtual Users to Build SchemaThe Virtual Users to Build Schema is the number of Virtual Users to be created on the Load Generation Server that will complete your multi-threaded schema build. You should set this value to the number of cores on your Load Generation Server or SUT if HammerDB is running there.