3. Configuring Schema Build Options

To create the analytic test schema based on the TPROC-H 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 12.2. Benchmark Options

Benchmark Options


To create the TPROC-H schema select the TPROC-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 12.3. TPROC-H Schema Build Options

TPROC-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 12.4. Oracle TPROC-H Build Options

Oracle TPROC-H Build Options

Table 12.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 TPROC-H user.
TPROC-H UserThe TPROC-H user is the name of a user to be created that will own the TPROC-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 TPROC-H schemas created with ownership under a different user you create each time.
TPROC-H User PasswordThe TPROC-H user password is the password to be used for the TPROC-H user you create and must adhere to the standard rules for Oracle user password. You will need to remember the TPROC-H user name and password for running the TPROC-H driver script after the schema is built.
TPROC-H Default TablespaceThe TPROC-H default tablespace is the tablespace that will be the default for the TPROC-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.
TPROC-H Temporary TablespaceThe TPROC-H temporary tablespace is the temporary tablespace that already exists in the database to be used by the TPROC-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 12.5. SQL Server Build Options

SQL Server Build Options

Table 12.2. SQL Server Build Options

OptionDescription
SQL ServerThe Microsoft SQL Server is the host name or host name and instance where the TPROC-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.
Encrypt ConnectionProvides the same functionality as the "Encrypt Connection" option in the SQL Server Management Studio connect dialog. This option is the default with ODBC Driver 18.
Trust Server CertificateProvides the same functionality as the "Trust Server Certificate" option in the SQL Server Management Studio connect dialog. This option is the default with ODBC Driver 18.
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.
Use BCP OptionThis option uses the SQL Server BCP utility to bulk load data. Temporary staging files will be created and deleted in the location defined by the TEMP environment variable. When unselected an insert based build is used. A BCP build offers vastly improved performance over an insert based build.


3.3. Db2 Schema Build Options

Figure 12.6. Db2 Build Options

Db2 Build Options

Table 12.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 12.7. MySQL Build Options

MySQL Build Options


Table 12.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 TPROC-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 TPROC-H driver script after the database is built.
MySQL DatabaseThe MySQL Database is the database that will be created containing the TPROC-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. MariaDB Schema Build Options

Figure 12.8. MariaDB Build Options

MariaDB Build Options


Table 12.5. MySQL Build Options

OptionDescription
MariaDB HostThe MariaDB Host Name is the host name of the SUT database server.
MariaDB PortThe MariaDB Port is the network port on the SUT database server. In most cases this will be the default port of 3306.
MariaDB UserThe MariaDB 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 MariaDB databases and has the necessary permissions to create the TPROC-H database.
MariaDB User PasswordThe MariaDB user password is the password for the user defined as the MariaDB User. You will need to remember the MariaDB user name and password for running the TPROC-H driver script after the database is built.
MariaDB DatabaseThe MariaDB Database is the database that will be created containing the TPROC-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.6. PostgreSQL Schema Build Options

Figure 12.9. PostgreSQL Build Options

PostgreSQL Build Options

Table 12.6. 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 TPROC-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 TPROC-H schema.
PostgreSQL TablespaceThe PostgreSQL Tablespace where the PostgreSQL Database will be installed.
Prefer PostgreSQL SSL ModeIf both the PostgreSQL client and server have been compiled to support SSL this option when selected enables "prefer" SSL, when unselected it sets this option to "disable" for connections. Other valid options such as "allow" or "require" can be set directly in the build and driver scripts if required.
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.