Use HammerDB to run performance tests on Amazon RDS for Db2

To ensure that you properly size your Amazon Relational Database Service (Amazon RDS) for Db2 instances and achieve comparable or better performance than your on-premises systems, you can use HammerDB. By using this tool, you can generate OLTP-type workloads using TPC-C tests, enabling you to compare performance between your on-premises Db2 and Amazon RDS for Db2 systems. By using HammerDB, you can evaluate how different instance types, network speeds, and Amazon Elastic Block Storage (Amazon EBS) characteristics such as IOPS and throughput impact your database performance. This approach helps you make informed decisions when migrating your Db2 workloads to the AWS cloud and helps ensure optimal performance in your RDS for Db2 environment.

This post guides you through running HammerDB tests on RDS for Db2. We provide a step-by-step process for creating an RDS for Db2 instance using an AWS CloudFormation template, setting up a Db2 client, and configuring HammerDB. You learn how to execute tests and interpret results to properly size your RDS for Db2 instances.

Solution overview

The solution architecture explains the use of a CloudFormation template to create an RDS for Db2 system, creation of an Amazon Elastic Compute Cloud (Amazon EC2) instance with Db2 client, and the use of HammerDB software to run the performance tests.

The following components are used in this solution:

Amazon RDS for Db2 – This is a managed IBM Db2 environment provided by Amazon.
Db2 client on Amazon EC2 – We need a client to connect to RDS for Db2. The solution shows installing a Db2 client on Amazon EC2 to connect to RDS for Db2.
HammerDB – The HammerDB generates the datasets for different types of TPC-C workloads. We run tests by varying the size of the database and the number of users to generate the workload and identify performance bottlenecks arising out of either instance size limitations or insufficient IOPS for the storage.
RDP client – You can use a Remote Desktop Protocol (RDP) client to connect to the Db2 client running on either Windows or on Ubuntu by installing support for XRDP.

The following diagram illustrates the architecture for implementing this solution.

The solution consists of the following steps:

Create an RDS for Db2 instance using AWS CloudFormation
Configure RDS for Db2 multi-Availability Zone with a standby database
Creating an EC2 instance using CloudFormation for a Db2 client
Connection to RDS for Db2 using the Amazon EC2 Db2 client
Configuration of the Db2 client for connecting to RDS for Db2 instance
Configuration of HammerDB in Amazon EC2 using a Db2 client
Running the HammerDB tests

Prerequisites

For this solution to work, you must have the following:

IBM Db2 license
- Bring your own license (BYOL) – You will need to specify IBM customer ID and IBM Site ID for creating an RDS for Db2 instance.
- AWS Market Place – You can subscribe to AWS Marketplace to obtain hourly usage licenses for Standard Editions and Advanced Editions. You can also make use of the IBM private offers to get discounts for bulk usage.
IBM account – You can create a free IBM account to download the free IBM Db2 client to connect to RDS for Db2.
AWS account – You need an AWS account
Amazon EC2 key pair – You need to create public/private keys to connect to Amazon EC2 from your local machine. You can build a new key pair or use your own in your AWS account.
RDP client program – This program can be based on either Apple MacOS or Microsoft Windows.

Note that this solution will incur costs for creating an RDS for Db2 instance. However, you can use a free tier of Amazon EC2 to create a Db2 client. You can the AWS Pricing Calculator to estimate costs.

Deploy RDS for Db2

We walk you through creating an RDS for Db2 instance and an EC2 instance using a CloudFormation template. You can download and preview the CloudFormation template .

You can modify or add any additional parameters such as VPC CIDR in the downloaded CloudFormation template. You can use the your modified CloudFormation template by uploading it to an Amazon Simple Storage Service (Amazon S3) bucket or by directly uploading the modified CloudFormation template in AWS Management Console for CloudFormation.

To launch the CloudFormation template with pre-configured settings, choose Launch Stack.
When you run the CloudFormation template, it will ask for several input parameters.
1. For ClientIpCidr, enter your PC IP address by visiting whatismyipaddress.com and finding your IP. Enter the IP address using the format xxx.xxx.xxx.xxx/32. This CIDR will be to restrict access to Amazon EC2 to only your IP address.
2. For DBInstanceIdentifier, use db2tpcc as stated in the example to be consistent with scripts. You can use any other name, but you will need to modify the scripts to do so.
3. For Db2DbName, enter the database name in uppercase letters or use the default name.
4. For Db2ServicePort, enter the database service port or use the default port, such as 50001.
5. For IbmCustomerId, enter your IMB customer ID.
6. For IbmSiteId, enter your IMB site ID.
7. For MasterUsername, enter the primary username for your Db2 database or the default username, such as db2inst1.
8. For MasterUserPassword, enter the password for the primary username.
9. For SshKeyPairName, choose your key pair for Amazon EC2.
Creating the stack will take approximately 15 minutes. When the stack creation is complete, you can view the output values on the Outputs tab. The output values might be different for each user than the values shown in the following screenshot.
You can check the resources of RDS for Db2 on the Amazon RDS console. Choose Databases in the navigation pane and choose the database identifier that AWS CloudFormation created.
On the Connectivity & security tab, you can find connection-related information such as endpoints and ports available in the database. You can also identify network-related information such as VPC, Availability Zone, and security groups.
On the Configuration tab, you can find the database configuration information. This includes details such as database instance ID and engine version. Additionally, you can verify information such as vCPU and RAM allocated to the RDS for Db2 instance.
When you view the parameter group details, you can check various Db2 parameter settings. You can also change the parameter values to your desired values.

Configure the Db2 client on Amazon EC2

In this section, we walk through the steps to set up the Db2 client in Amazon EC2.

Check the client Ubuntu EC2 instance

The CloudFormation template created an EC2 instance running Ubuntu Linux. On Outputs tab of the stack, you can see the access information. The Ubuntu Linux instance that was created supports RDP access. When configured through the CloudFormation template, it’s also configured to allow users to log in using port 3389. You can connect similar to connecting to Windows.

SSH login

Log in with the SSH command by using the connection information shown in the output of the CloudFormation stack. The IP address of each user might be different.

To log in with SSH command, you need to prepare the private key pair file and set the file permission as 400 on your computer.
```
chmod 400 <Your Key Pair>.pem
```

Then, you can log in as the Ubuntu user:

ssh -i <Your Key Pair>.pem ubuntu@<Your instance's public IP address>

After you’re logged in as the Ubuntu user, use the following command to log in as the root user:
```
sudo -i
```

Download the Db2 binary client packages

Visit the IBM Support page and download the IBM Data Server Client Packages. If you have an IBM account, these client packages are free to download. As of the writing this post, the latest version available for download is IBM Data Server Client Packages Version 11.5 Mod 9 Fix Pack 0. We recommend downloading the driver for Linux-x64.
You can download the client driver for Db2 by selecting the options FTPS/SFTP, HTTPS, or Download Director. Among those options, you can download by using the FTPS/SFTP option.
You can also download it from the Ubuntu environment that was created using the CloudFormation template.

To use HammerDB with the GUI, you need to configure HammerDB and the Db2 client to the root user. Thus, you need to log in using the sudo -i command for downloading and installing Db2 client:

sudo -i

sftp <IBM FTP UserID>@delivery04-bld.dhe.ibm.com
<IBM FTP UserID>@delivery04-bld.dhe.ibm.coms password: <IBM FTP Password>

sftp> mget *gz
Fetching /v11.5.9_linuxx64_client.tar.gz to v11.5.9_linuxx64_client.tar.gz
/v11.5.9_linuxx64_client.tar.gz                              100%  658MB   2.1MB/s   05:21
sftp> bye

Unzip the *.gz file for installation:

tar -xvzf v11.5.9_linuxx64_client.tar.gz

Install the Db2 client

Use the following command to install the client. You are prompted with [yes/no] two times. Each time, enter yes to proceed.

cd client
./db2_install -f sysreq

Connect to RDS for Db2 using the Db2 client

In this section, we walk you through the steps to connect to RDS for Db2 using the Db2 client.

Create a db2profile for Ubuntu and root users

Create a db2profile for the Ubuntu user. While logged in as the root user, navigate to the directory where Db2 client is installed. Use the following command to register ubuntu as the Db2 user:
```
cd /opt/ibm/db2/V11.5/instance
./db2icrt -u ubuntu ubuntu
```

The output should be similar to the following.

# ./db2icrt -u ubuntu ubuntu
DBI1446I  The db2icrt command is running.


DB2 installation is being initialized.

 Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)

Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end

Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end

Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end

The execution completed successfully.

For more information see the DB2 installation log at "/tmp/db2icrt.log.76900".
DBI1070I  Program db2icrt completed successfully.

Add the following configuration to the end of root’s .profile file. The Ubuntu user’s .profile file has been added with the following code. This process adds the profile configuration to the root’s .profile file so the root user can also use the db2profile configuration.x
```
cat << EOF >> ~/.profile
# The following three lines have been added by IBM Db2 instance utilities.
if [ -f /home/ubuntu/sqllib/db2profile ]; then
    . /home/ubuntu/sqllib/db2profile
fi
EOF
```
While logged in as the root user, run the .profile file with the following command:
```
. ~/.profile
```
Run the db2 command to verify that the Db2 client is working as the root OS user:
```
db2
```

The following is the expected output:

(c) Copyright IBM Corporation 1993,2007
Command Line Processor for Db2 Client 11.5.9.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => quit
DB20000I  The QUIT command completed successfully.

Test the Db2 connection using the root user

Use the following command to catalog the node for Db2. The DNS of RDS for Db2 and port should be used by checking the values from the output of the CloudFormation stack.
```
db2 catalog tcpip node r_dev_db remote <Endpoint_of_RDS_for_DB2> server <Port>
db2 list node directory
```

The following is the expected output:

# db2 catalog tcpip node r_dev_db remote <Endpoint_of_RDS_for_DB2> server <Port>
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
# db2 list node directory

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = R_DEV_DB
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = <Endpoint_of_RDS_for_DB2>
 Service name                   = <Port>

#

Catalog the database using the following command:

db2 catalog db tpcc at node r_dev_db authentication server_encrypt
db2 list db directory

The following is the expected output:

# db2 catalog db tpcc at node r_dev_db authentication server_encrypt
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
# db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = TPCC
 Database name                        = TPCC
 Node name                            = R_DEV_DB
 Database release level               = 15.00
 Comment                              =
 Directory entry type                 = Remote
 Authentication                       = SERVER_ENCRYPT
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

#

When the cataloging is complete, run the connection test using the following command. The primary username and password can be found by checking the values in the output of the CloudFormation.
```
db2 connect to tpcc user <MasterUsername> using <MasterUserPassword>
```

The following is an example of the expected output:

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TPCC

After you connect to the Db2 database, TPCC, check whether the connection is successful by performing the following SELECT query:
```
db2 "select * from sysibm.sysdummy1"

IBMREQD

Y

1 record(s) selected.
```

If you want to monitor the TPCC database, we recommend the db2top utility. With db2top, you can monitor various Db2 database metrics and check database status in real time.

Install the required packages to install db2top:
```
sudo apt install libncurs* -y
```

Run db2top with the following command:

db2top -d tpcc -n r_dev_db -u db2inst1
Enter current password for db2inst1: <input your password>

To check the functions and shortcut keys of db2top, press h on the db2top page.
You can save the db2top run parameter environment by pressing w. It’s created as a ~/.db2toprc file. You can run it with just the db2top command without any options:
```
db2top
```

Configure HammerDB on the Db2 client

In this section, we walk through the steps to configure HammerDB on the Db2 client.

Install the HammerDB program

Connect to the Ubuntu client EC2 machine with an SSH command if not already connected, then log in as root and install HammerDB. The IP address of the EC2 instance is different for each user.

ssh -i MyKeyPair.pem ubuntu@43.200.244.7 
sudo -i
wget https://github.com/TPC-Council/HammerDB/releases/download/v4.11/HammerDB-4.11-Linux.tar.gz -O HammerDB-4.11-Linux.tar.gz
tar -xvzf HammerDB-4.11-Linux.tar.gz

The following is the expected output:

# wget https://github.com/TPC-Council/HammerDB/releases/download/v4.11/HammerDB-4.11-Linux.tar.gz -O HammerDB-4.11-Linux.tar.gz
--2024-09-02 10:21:27--  https://github.com/TPC-Council/HammerDB/releases/download/v4.11/HammerDB-4.11-Linux.tar.gz
Resolving github.com (github.com)... 20.200.245.247
Connecting to github.com (github.com)|20.200.245.247|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/159393955/7ad73ada-3ea6-4ead-a6e1-89e9f5e9af95?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240902%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240902T102127Z&X-Amz-Expires=300&X-Amz-Signature=22ca7b0ca0c8b2ac72d911dfc57091e64bb52744ec70c058363361380d57058f&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=159393955&response-content-disposition=attachment%3B%20filename%3DHammerDB-4.11-Linux.tar.gz&response-content-type=application%2Foctet-stream [following]
--2024-09-02 10:21:27--  https://objects.githubusercontent.com/github-production-release-asset-2e65be/159393955/7ad73ada-3ea6-4ead-a6e1-89e9f5e9af95?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20240902%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240902T102127Z&X-Amz-Expires=300&X-Amz-Signature=22ca7b0ca0c8b2ac72d911dfc57091e64bb52744ec70c058363361380d57058f&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=159393955&response-content-disposition=attachment%3B%20filename%3DHammerDB-4.11-Linux.tar.gz&response-content-type=application%2Foctet-stream
Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ...
Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.108.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 6611887 (6.3M) [application/octet-stream]
Saving to: ‘HammerDB-4.11-Linux.tar.gz’

HammerDB-4.11-Linux.tar.gz                          100%[=================================================================================================================>]   6.30M  --.-KB/s    in 0.03s

2024-09-02 10:21:28 (185 MB/s) - ‘HammerDB-4.11-Linux.tar.gz’ saved [6611887/6611887]
#
# tar -xvzf HammerDB-4.11-Linux.tar.gz
HammerDB-4.11/
HammerDB-4.11/images/
HammerDB-4.11/images/icons.tcl
HammerDB-4.11/images/logo.tcl
HammerDB-4.11/hammerdb
<<skip similar lines>>
HammerDB-4.11/src/oracle/oramet.tcl
HammerDB-4.11/src/oracle/oraoltp.tcl
#

Run the HammerDB program

Connect to the Ubuntu EC2 instance using Microsoft RDP on MacOS, or using remote desktop on Windows. The following example uses Microsoft RDP on MacOS.

Choose Add PC.
Enter the public IP of the Ubuntu EC2 instance for PC name and the root user for User account.
You can check the public IP through the output of the CloudFormation stack.
Double-click the created remote PC to open it. When you connect to Ubuntu using RDP, a login page appears.
Enter root as the username and use the password that you provided when you created the CloudFormation stack, then choose Next.
When you log in for the first time, the basic OS usage guide page appears. Choose Activities to open the search menu.
Search for and launch the Terminal program.
At a prompt, run the following command to confirm that the db2profile configuration is applied under the root user:
```
. ~/.profile
```
Use the following commands to move the HammerDB installation directory, then run the HammerDB program:
```
cd HammerDB-4.11
./hammerdb
```

Configure HammerDB for RDS for Db2

To configure HammerDB for RDS for Db2, complete the following steps:

Double-click Db2 in the navigation pane.
Select Db2 and TPROC-C.
Choose OK.
Choose OK in the pop-up to confirm.

Build the schema for the TPC-C workload

Complete the following steps to set up the TPC-C workload.

Choose the arrow to the left of TPROC-C. Choose the arrow to the left of Schema. Double-click Options under Schema in the navigation pane.
For TPROC-C Db2 User, enter the username you specified when creating the CloudFormation stack.
For TPROC-C Db2 User Password, enter the password you specified when creating the CloudFormation stack.
In the same menu, choose the Settings
For Number of Warehouses, enter a number high enough to accommodate your test. For example, 100.
Enter the same number for Virtual users to Build Schema to simultaneously build the schema. For example, 100.
Choose OK.
Double-click Build and choose Yes to confirm.
When all the Status icons of virtual users have changed to green as shown in the following figure, you can delete the virtual users.
Choose the red stop icon (Destroy Virtual User) to end the build session.

Configure driver options

Complete the following steps to configure driver options:

Choose the arrow to the left of Driver Script. Double-click Options under Driver Script in the navigation pane. In the popup dialog box, choose the Settings
You can configure Db2 TPRC-C Driver options in this menu as you want. For test purposes, you can leave all the options at the default values and choose OK.
Double-click the Load menu in the Driver Script By doing that, you can load generated scripts from Driver Options.

Configure virtual users

Complete the following steps to configure Virtual Users options:

Choose the arrow to the left of Virtual User. Double-click Options under Virtual User in the navigation pane.
Enter your desired number of virtual users, such as 100, and choose OK.
Double-click Create to create the users. By doing that, you can create the desired number of users and plus one more user because it requires a user for monitoring.

Run the TPC-C workload

Choose the green play icon to create a benchmark workload.

Monitor the benchmark workload with TPM graph

Choose the graph icon to see the TPM graph.
You will see the progress of the graph as shown in the following figure.
When the benchmark workload is complete, the TPM value will drop.

Run HammerDB with the CLI

In addition to the GUI, HammerDB also provides the ability to use command-line (CLI) commands. You can use the CLI functionality to automate repetitive tests.

Enter the Hammerdbcli prompt

To use HammerDB with the CLI, you need to use the hammerdbcli terminal. Navigate to the directory where HammerDB is installed and run hammerdbcli.
```
sudo -i
cd HammerDB-4.11
./hammerdbcli
```

The following is the expected output.

# cd HammerDB-4.11
# ./hammerdbcli
HammerDB CLI v4.11
Copyright (C) 2003-2024 Steve Shaw
Type "help" for a list of commands
Initialized new Jobs on-disk database /tmp/hammer.DB
hammerdb>

hammerdb>

Configure the schema build setting

Run the following command to set the database management system (DBMS) to be tested as db2. Then, run the print db command to check the DBMS name.
```
dbset db db2
print db
```

The following is the expected output.

hammerdb>dbset db db2
Database set to Db2

hammerdb>
hammerdb>print db
Database Db2 set.
To change do: dbset db prefix, one of:
Oracle = ora MSSQLServer = mssqls Db2 = db2 MySQL = mysql PostgreSQL = pg MariaDB = maria
hammerdb>

Run the following command to set the workload type to TPC-C.
```
dbset bm TPC-C
print bm
```

The following is the expected output.

hammerdb>dbset bm TPC-C
Benchmark set to TPC-C for Db2

hammerdb>

hammerdb>print bm
Benchmark set to TPC-C

hammerdb>

By using the print dict command, you can see the settings for the current workload.

hammerdb>print dict
Dictionary Settings for Db2
connection {
 db2_def_user  = db2inst1
 db2_def_pass  = ibmdb2
 db2_def_dbase = db2
}
tpcc       {
 db2_count_ware       = 1
 db2_num_vu           = 1
 db2_user             = db2inst1
 db2_pass             = ibmdb2
 db2_dbase            = tpcc
 db2_def_tab          = USERSPACE1
 db2_tab_list         = C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""
 db2_partition        = false
 db2_total_iterations = 10000000
 db2_raiseerror       = false
 db2_keyandthink      = false
 db2_driver           = timed
 db2_rampup           = 2
 db2_duration         = 5
 db2_monreport        = 0
 db2_allwarehouse     = false
 db2_timeprofile      = false
 db2_async_scale      = false
 db2_async_client     = 10
 db2_async_verbose    = false
 db2_async_delay      = 1000
 db2_connect_pool     = false
}

hammerdb>

You can change these settings with the diset command. Replace the password with your own password using the following command. Also, Set the number of warehouses and virtual users to the number you need for your test.
```
diset connection db2_def_pass ibmdb2123
diset tpcc db2_pass ibmdb2123
diset tpcc db2_count_ware 100
diset tpcc db2_num_vu 100
```

The following is the expected output.

hammerdb>diset connection db2_def_pass ibmdb2123
Changed connection:db2_def_pass from ibmdb2 to ibmdb2123 for Db2

hammerdb>diset tpcc db2_pass ibmdb2123
Changed tpcc:db2_pass from ibmdb2 to ibmdb2123 for Db2

hammerdb>diset tpcc db2_count_ware 100
Changed tpcc:db2_count_ware from 1 to 100 for Db2

hammerdb>diset tpcc db2_num_vu 100
Changed tpcc:db2_num_vu from 1 to 100 for Db2

hammerdb>

Run the print dict command again to verify that it changed correctly.

hammerdb>print dict
Dictionary Settings for Db2
connection {
 db2_def_user  = db2inst1
 db2_def_pass  = ibmdb2123
 db2_def_dbase = db2
}
tpcc       {
 db2_count_ware       = 100
 db2_num_vu           = 100
 db2_user             = db2inst1
 db2_pass             = ibmdb2123
 db2_dbase            = tpcc
 db2_def_tab          = USERSPACE1
 db2_tab_list         = C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""
 db2_partition        = false
 db2_total_iterations = 10000000
 db2_raiseerror       = false
 db2_keyandthink      = false
 db2_driver           = timed
 db2_rampup           = 2
 db2_duration         = 5
 db2_monreport        = 0
 db2_allwarehouse     = false
 db2_timeprofile      = false
 db2_async_scale      = false
 db2_async_client     = 10
 db2_async_verbose    = false
 db2_async_delay      = 1000
 db2_connect_pool     = false
}

hammerdb>

Build the schema

Build the schema using the preceding configuration information.

Run the following command to build the schema.
```
buildschema
```

The following is the expected output.

hammerdb>buildschema
Script cleared
Building 1 Warehouses(s) with 1 Virtual User
Ready to create a 1 Warehouse Db2 TPROC-C schema
under user DB2INST1 in database TPCC?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:CREATING DATABASE tpcc
Vuser 1:DATABASE tpcc already exists
Vuser 1:Connecting to database tpcc
Vuser 1:Connection established
Vuser 1:DATABASE tpcc is empty, using tpcc
Vuser 1:CREATING TPCC TABLES
Vuser 1:Loading Item
Vuser 1:Loading Items - 10000
Vuser 1:Loading Items - 20000
<<..skip..>>
Vuser 1:3..
Vuser 1:4..
Vuser 1:Statistics Complete
Vuser 1:DB2INST1 SCHEMA COMPLETE
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Schema Build jobid=66D16747620E03E233933303
hammerdb>

After this is done, delete the virtual user that was used during build. Run the following command to delete the user, and then check the status to make sure it was successfully deleted.
```
vudestroy
vustatus
```

The following is the expected output.

hammerdb>vudestroy
vudestroy success

hammerdb>
hammerdb>vustatus
No Virtual Users found

hammerdb>

Configure the driver

Configure settings for the driver that performs the load. Set the details of the load transaction, such as rampup and duration.

Use the following commands to modify existing settings, and print dict to verify that the settings are correct.
```
diset tpcc db2_rampup 1
diset tpcc db2_duration 4
print dict
```

The following is the expected output.

hammerdb>diset tpcc db2_rampup 1
Changed tpcc:db2_rampup from 2 to 1 for Db2

hammerdb>diset tpcc db2_duration 4
Changed tpcc:db2_duration from 5 to 4 for Db2

hammerdb>
hammerdb>print dict
Dictionary Settings for Db2
connection {
 db2_def_user  = db2inst1
 db2_def_pass  = ibmdb2123
 db2_def_dbase = db2
}
tpcc       {
 db2_count_ware       = 100
 db2_num_vu           = 100
 db2_user             = db2inst1
 db2_pass             = ibmdb2123
 db2_dbase            = tpcc
 db2_def_tab          = USERSPACE1
 db2_tab_list         = C "" D "" H "" I "" W "" S "" NO "" OR "" OL ""
 db2_partition        = false
 db2_total_iterations = 10000000
 db2_raiseerror       = false
 db2_keyandthink      = false
 db2_driver           = timed
 db2_rampup           = 1
 db2_duration         = 4
 db2_monreport        = 0
 db2_allwarehouse     = false
 db2_timeprofile      = false
 db2_async_scale      = false
 db2_async_client     = 10
 db2_async_verbose    = false
 db2_async_delay      = 1000
 db2_connect_pool     = false
}

hammerdb>

If the settings for the driver change, you need to reload the script with the changes. You can load the script with the following command.
```
loadscript
```

The following is the expected output.

hammerdb>loadscript
Script loaded, Type "print script" to view

hammerdb>

Configure virtual users

You can set the number of virtual users to carry the load and make additional settings, such as whether to print to log.

The following command is an example of a command to create five virtual users and set up log output.
```
print vucreated
vuset vu 5
vuset logtotemp 1
print vuconf
```

The following is the expected output.

hammerdb>print vucreated
0 Virtual Users created

hammerdb>vuset vu 5

hammerdb>vuset logtotemp 1

hammerdb>print vuconf
Virtual Users = 5
User Delay(ms) = 500
Repeat Delay(ms) = 500
Iterations = 1
Show Output = 1
Log Output = 1
Unique Log Name = 0
No Log Buffer = 0
Log Timestamps = 0

hammerdb>

You can verify that the virtual users were successfully created by running the vucreate command. As you can see in the following result, in addition to the five virtual users that performed the creation request, there is one more virtual user for monitoring.

hammerdb>vucreate
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
Vuser 6 created - WAIT IDLE
Logging activated
to /tmp/hammerdb.log
6 Virtual Users Created with Monitor VU

hammerdb>

You can check the status of the current virtual users by running the vustatus command.

hammerdb>vustatus
1 = WAIT IDLE
2 = WAIT IDLE
3 = WAIT IDLE
4 = WAIT IDLE
5 = WAIT IDLE
6 = WAIT IDLE


hammerdb>

Run the workload

The workload prepared by the setup so far can be run by using the vurun command. The following is the expected output.

hammerdb>vurun
Vuser 1:RUNNING
Vuser 1:Connecting to database tpcc
Vuser 1:Connection established
Vuser 1:Beginning rampup time of 1 minutes
Vuser 2:RUNNING
Vuser 2:Connecting to database tpcc
Vuser 2:Connection established
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Connecting to database tpcc
Vuser 3:Connection established
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 4:RUNNING
Vuser 4:Connecting to database tpcc
Vuser 4:Connection established
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 5:RUNNING
Vuser 5:Connecting to database tpcc
Vuser 5:Connection established
Vuser 5:Processing 10000000 transactions with output suppressed...
Vuser 6:RUNNING
Vuser 6:Connecting to database tpcc
Vuser 6:Connection established
Vuser 6:Processing 10000000 transactions with output suppressed...
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:1 ...,
Vuser 1:2 ...,
Vuser 1:3 ...,
Vuser 1:4 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:5 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 5432 NOPM from 23995 Db2 TPM
Vuser 1:FINISHED SUCCESS
Vuser 6:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Benchmark Run jobid=66D16CE6620E03E253730303
hammerdb>

As you can see from the preceding results, it will display what’s being done, and the final TPM will be displayed. After the process is complete, use the vudestroy command to delete the vu.
```
vudestroy
vustatus
```

The following is the expected output.

hammerdb>vudestroy
vudestroy success

hammerdb>

hammerdb>vustatus
No Virtual Users found

hammerdb>

Perform a batch using a Tcl script

HammerDB provides a way to run workloads in batch using Tcl scripts. Tcl batch scripts are a convenient way to perform repetitive tasks with minor configuration changes.

Use the following command to create a simple Tcl batch script. The following batch command is an example of running a test repeatedly while incrementing the vu number in the form of 1, 2, and 4.

cat << 'EOF' > batch-test.tcl
#!/usr/bin/tclsh
puts "SETTING CONFIGURATION"
dbset db db2
diset tpcc db2_rampup 1
diset tpcc db2_duration 4
vuset logtotemp 1
loadscript
puts "SEQUENCE STARTED"
foreach z { 1 2 4 } {
puts "$z VU TEST"
vuset vu $z
vucreate
vurun
vudestroy
}
puts "TEST SEQUENCE COMPLETE"
EOF

Use the auto keyword in hammerdbcli to run a Tcl batch file, as shown in the following example command.
```
./hammerdbcli auto batch-test.tcl
```

The following is the expected output.

# ./hammerdbcli auto batch-test.tcl
HammerDB CLI v4.11
Copyright (C) 2003-2024 Steve Shaw
Type "help" for a list of commands
Initialized Jobs on-disk database /tmp/hammer.DB using existing tables (65,536 KB)
SETTING CONFIGURATION
Database set to Db2
Value 1 for tpcc:db2_rampup is the same as existing value 1, no change made
<<..skip..>>
Vuser 4:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
vudestroy success
TEST SEQUENCE COMPLETE

Monitoring the HammerDB workload

When you run benchmark tests, it’s possible that the tests aren’t performing at the desired level because of insufficient system resources. To check this, you can look at the OS metrics.

You can view OS-related metrics of a database by going to the Amazon RDS console while the benchmark workload is running and choosing Databases from the navigation pane, selecting the database, and then choosing the Monitoring tab.
You can use the monitoring menu to view various metrics related to Amazon RDS system resources.

By checking the OS metrics, you can see if CPU, Memory, IOPS, and Network have sufficient capacity. If you encounter a situation where these resources aren’t sufficient, you can adjust the instance type, storage type, and IOPS through the Modify function in the RDS console to resolve the resource shortage.

You can monitor the status of the database through the db2top command. db2top is a real-time monitoring tool and you can run it immediately before running the benchmark workload and then monitor while the benchmark workload is running.

Run db2top with the following command.
```
db2top
```
By entering l, you can see the status of the current session.
By entering B, you can see the level of bottleneck of the database.

Configure Db2 parameters

Depending on what kind of workload you want to verify by using TPC-C, you need to configure the parameters of Db2. A parameter group in Amazon RDS is a collection of database configuration parameters that can be applied to one or more database instances.

If you have multiple different workloads, it might be better to create different custom parameter groups tailored for each workload type.

To create a parameter group, use the Amazon RDS console. Choose Parameter groups from the navigation pane, and then choose Create parameter group.

When using RDS for Db2, you can modify database manager parameters and registry variables through parameter groups. The following screenshot shows the BYOL parameter settings accepted in a parameter group.

You can change database parameters through the management database. To use the management database, the first thing to do is to catalog rdsadmin and then connect to rdsadmin. Then, you can run the rdsadmin procedure to update parameters as shown in the following example:
```
db2 catalog db rdsadmin at node r_dev_db authentication server_encrypt
db2 connect to rdsadmin user <MasterUsername> using <MasterUserPassword>
db2 "call rdsadmin.update_db_param('tpcc','auto_maint','ON')"
```

The following is the expected output of the command:

# db2 catalog db rdsadmin at node r_dev_db authentication server_encrypt
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
#
# db2 connect to rdsadmin user <MasterUsername> using <MasterUserPassword>

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0
 SQL authorization ID   = DB2INST1
 Local database alias   = RDSADMIN

#
# db2 "call rdsadmin.update_db_param('tpcc','auto_maint','ON')"

  Return Status = 0
#

RDS for Db2 instances performance testing

The HammerDB can be used to assess the performance of RDS for Db2 instances by executing the HammberDB TPROC-C workload—which emulates an OLTP workload—on RDS for Db2. The HammerDB workload can be also executed against Db2 on-premises for comparison.

After running the HammerDB benchmarking, you can analyze the result by considering the following aspects. When the benchmarking is also performed against an on-premises Db2 environment, the results can be compared with the results obtained from the RDS for Db2 instance, helping you to evaluate performance of the provisioned RDS for Db2 configuration compared to the on-premises Db2 configuration.

TPROC-C workload performance
1. Evaluate the throughput performance using the two key indicators such as the number of new order transactions per minute (NOPM) and the overall transactions per minute (TPM) at the end of the run.
2. Capture and review the latency performance of the five TPROC-C transactions by enabling the Time Profile option available in the Driver Script Options. During the benchmark run, this feature will generate response time percentiles for all the virtual users executing these transactions on the system, logging the results to the /tmp/hdbxtprofile.log file for comprehensive comparison.
3. After the run, open the /tmp/hdbxtprofile.log file to review the response time percentiles of the five transactions of all the virtual users.
RDS for Db2 resource utilization.
1. By using Amazon CloudWatch or enhanced monitoring, you can gain a comprehensive understanding of the system’s performance characteristics under the HammerDB benchmark workload. By using this approach, you can compare resource utilization across the cloud and on-premises environments along with the TPROC-C workload performance, and ensure that your RDS for Db2 instance is provisioned with a comparable size and configuration to your on-premises Db2 setup.
2. The Cloudwatch metrics CPUUtilization, FreeableMemory, ReadLatency, and WriteLatency provide valuable insights into resource utilization, specifically CPU usage, memory consumption, and disk I/O latency. This enables a comprehensive comparison with an on-premises Db2 database along with the TPROC-C workload performance metrics.
Based on the preceding NOPM and TPM transaction response time comparison with on-premises and RDS for Db2 resource utilization metrics:
1. To achieve the desired target of NOPM or TPM for RDS for Db2 compared to on-premises Db2, the performance bottlenecks coming from CPUs, memory, I/O or the database itself should be identified and mitigated by adjusting the RDS for Db2 configuration.
2. The following chart shows an example of the HammerDB TPROC-C benchmark NOPM for RDS for Db2 instances with four vCPUs and eight vCPUs configurations. If the NOPM of RDS for Db2 with four vCPUs doesn’t meet the desired performance target, the instance can be scaled to as much as an eight vCPUs configuration until it meets the performance goal.

Clean up

If you no longer need this setup and want to avoid future charges, you can delete the resources that you created as part of this setup.

To delete all of the resources that were launched as part of the CloudFormation stack, complete the following steps:

On the CloudFormation console, choose Stacks in the navigation pane.
Select the stack you created, then choose Delete.
Choose Delete stack when prompted.

For more information, see Deleting a stack on the AWS CloudFormation console.

Conclusion

In this post, we described how to configure the HammerDB benchmark tool and what considerations you should take into account when performing benchmark testing in an RDS for Db2 environment. There aren’t many tools that can benchmark Db2 compared to other popular database management services. HammerDB is a powerful benchmarking tool that streamlines the process of evaluating and configuring the performance of RDS for Db2 instances. This tool also enables competitive analysis between RDS for Db2 configurations and on-premises Db2 databases, helping organization make informed decisions for optimal resource allocation. In addition to HammerDB, there is an open source tool called Apache JMeter and a benchmark tool called DTW from IBM that you can use to compare performance.

Try out the HammerDB benchmark tool for yourself, and share your thoughts in the comments section.

About the authors

Byeong-eok Kang is a Solutions Architect at AWS. He has over 20 years of experience in building and consulting on IT systems for financial companies, helping customers configure and use the right cloud systems for their needs. In addition to that, he also has experience in various other technology areas such as databases, AI/ML, analytics, and SaaS. Outside of work, he likes bicycling, playing with his cat, and reading books.

YunCheol Ha is a Senior PostgreSQL and Db2 specialist Solutions Architect at AWS with over 25 years of experience in designing and implementing mission-critical operational systems and large-scale data warehouses. He is passionate about database performance optimization and delivering high-performance and scalable solutions.

SeongHee Kang, a Partner SA, has experience as both a database engineer and a Data Analytics platform engineer, having built and conducted pre-sales across various industries. He is currently supporting partner solutions businesses in the Industry & Partner Solution team.

Gyoubyoung Kim is an Analytics Sales Specialist at AWS. He has worked as a technology expert and consultant in the data and analytics field in the large enterprise markets in Korea. He has specialized experience with high-volume, high-availability Db2 environments for financial and enterprise customers.

AWS Database Blog

Use HammerDB to run performance tests on Amazon RDS for Db2

Solution overview

Prerequisites

Deploy RDS for Db2

Configure the Db2 client on Amazon EC2

Check the client Ubuntu EC2 instance

SSH login

Download the Db2 binary client packages

Install the Db2 client

Connect to RDS for Db2 using the Db2 client

Create a db2profile for Ubuntu and root users

Test the Db2 connection using the root user

Configure HammerDB on the Db2 client

Install the HammerDB program

Run the HammerDB program

Configure HammerDB for RDS for Db2

Build the schema for the TPC-C workload

Configure driver options

Configure virtual users

Run the TPC-C workload

Monitor the benchmark workload with TPM graph

Run HammerDB with the CLI

Enter the Hammerdbcli prompt

Configure the schema build setting

Build the schema

Configure the driver

Configure virtual users

Run the workload

Perform a batch using a Tcl script

Monitoring the HammerDB workload

Configure Db2 parameters

RDS for Db2 instances performance testing

Clean up

Conclusion

About the authors

Resources

Blog Topics

Follow