4. CLI & Scripted Commands

The web service CLI can also be used for both interactive and scripted commands. Note that whether using the GUI, CLI or Web Service the Virtual Users run exactly the same workloads. There is no difference in the Virtual User workload based on the chosen interface. As an example the following script executes a schema build and then exits using waittocomplete.

dbset db mssqls
dbset bm TPC-C
diset connection mssqls_server {(local)\\SQLDEVELOP}
diset tpcc mssqls_count_ware 20
diset tpcc mssqls_num_vu 8
buildschema
waittocomplete

When run at the CLI the following output is shown.

HammerDB Web Service v4.3
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Initialized SQLite on-disk database C:/Users/Steve/AppData/Local/Temp/hammer.DB using existing tables
Starting HammerDB Web Service on port 8080
Listening for HTTP requests on TCP port 8080
hammerws>source sqlbuild.tcl
{"success": {"message": "Database set to MSSQLServer"}}
{"success": {"message": "Benchmark set to TPC-C for MSSQLServer"}}
{"success": {"message": "Changed connection:mssqls_server from (local) to (local)\\SQLDEVELOP for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_count_ware from 1 to 20 for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_num_vu from 1 to 8 for MSSQLServer"}}
{"success": {"message": "Building 20 Warehouses with 9 Virtual Users, 8 active + 1 Monitor VU(dict value mssqls_num_vu is set to 8): JOBID=618E54ED9E5D03E233732393"}}

The key difference from the standard CLI is that no output is printed to stdout and is instead stored in the SQLite database under jobid "618E54ED9E5D03E233732393".

The interface is closed after the build (as we used waittocomplete) and restarted and the output remains persisted to the database to be queried at any time. For example the list below shows querying the timestamp when the job started, the dict configuration for the build and the status of the build having completed successfully.

hammerws>jobs 618E54ED9E5D03E233732393 timestamp

{"618E54ED9E5D03E233732393": {"2021-11-12": "11:50:05"}}

hammerws>jobs 618E54ED9E5D03E233732393 dict

{
  "connection": {
    "mssqls_server": "(local)\\SQLDEVELOP",
    "mssqls_linux_server": "localhost",
    "mssqls_tcp": "false",
    "mssqls_port": "1433",
    "mssqls_azure": "false",
    "mssqls_authentication": "windows",
    "mssqls_linux_authent": "sql",
    "mssqls_odbc_driver": "ODBC Driver 17 for SQL Server",
    "mssqls_linux_odbc": "ODBC Driver 17 for SQL Server",
    "mssqls_uid": "sa",
    "mssqls_pass": "admin"
  },
  "tpcc": {
    "mssqls_count_ware": "20",
    "mssqls_num_vu": "8",
    "mssqls_dbase": "tpcc",
    "mssqls_imdb": "false",
    "mssqls_bucket": "1",
    "mssqls_durability": "SCHEMA_AND_DATA",
    "mssqls_total_iterations": "10000000",
    "mssqls_raiseerror": "false",
    "mssqls_keyandthink": "false",
    "mssqls_checkpoint": "false",
    "mssqls_driver": "test",
    "mssqls_rampup": "2",
    "mssqls_duration": "5",
    "mssqls_allwarehouse": "false",
    "mssqls_timeprofile": "false",
    "mssqls_async_scale": "false",
    "mssqls_async_client": "10",
    "mssqls_async_verbose": "false",
    "mssqls_async_delay": "1000",
    "mssqls_connect_pool": "false"
  }
}

hammerws>jobs 618E54ED9E5D03E233732393 status

[
  "0",
  "Ready to create a 20 Warehouse MS SQL Server TPROC-C schema\nin host (LOCAL)\\SQLDEVELOP in database TPCC?",
  "0",
  "Vuser 1:RUNNING",
  "0",
  "Vuser 2:RUNNING",
  "0",
  "Vuser 3:RUNNING",
  "0",
  "Vuser 4:RUNNING",
  "0",
  "Vuser 5:RUNNING",
  "0",
  "Vuser 6:RUNNING",
  "0",
  "Vuser 7:RUNNING",
  "0",
  "Vuser 8:RUNNING",
  "0",
  "Vuser 9:RUNNING",
  "0",
  "Vuser 6:FINISHED SUCCESS",
  "0",
  "Vuser 9:FINISHED SUCCESS",
  "0",
  "Vuser 7:FINISHED SUCCESS",
  "0",
  "Vuser 8:FINISHED SUCCESS",
  "0",
  "Vuser 4:FINISHED SUCCESS",
  "0",
  "Vuser 5:FINISHED SUCCESS",
  "0",
  "Vuser 2:FINISHED SUCCESS",
  "0",
  "Vuser 3:FINISHED SUCCESS",
  "0",
  "Vuser 1:FINISHED SUCCESS",
  "0",
  "ALL VIRTUAL USERS COMPLETE"
]

hammerws>

The output can also be queried using the HTTP interface directly.

Figure 10.2. Query Build Job

Query Build Job

For the run script this is the same as would be run using the CLI except for the addition of the jobs command. Note that as the command line parameters are converted to JSON a backslash will be recognised as an escape character, therefore in this example the database name has the backslash itself preceded by the escape character {(local)\\SQLDEVELOP}. This behaviour is different from the CLI where the brackets will prevent this happening. An example is shown below of the incorrect and correct usage.

hammerws>diset connection mssqls_server {(local)\SQLDEVELOP}
{"error": {"message": "Not a valid escaped JSON character: 'S' in { \"dict\": \"connection\", \"key\": \"mssqls_server\", \"value\": \"(local)\\SQLDEVELOP\" }"}}

hammerws>diset connection mssqls_server {(local)\\SQLDEVELOP}
{"success": {"message": "Changed connection:mssqls_server from (local) to (local)\\SQLDEVELOP for MSSQLServer"}}

The example run script is configured as follows.

dbset db mssqls
dbset bm TPC-C
diset connection mssqls_server {(local)\\SQLDEVELOP}
diset tpcc mssqls_driver timed
diset tpcc mssqls_rampup 1
diset tpcc mssqls_duration 2
diset tpcc mssqls_timeprofile true
diset tpcc mssqls_allwarehouse true
tcset refreshrate 10
loadscript
foreach z {1 2} {
puts "$z VU TEST"
vuset vu $z
vucreate
tcstart
set jobid [ vurun ]
runtimer 200
tcstop
jobs $jobid result
jobs $jobid timing
vudestroy
}

When the script is run it again reports a jobid that can be queried for all of the job output. In the example the job result and summary timing data is queried automatically after the job.

HammerDB Web Service v4.3
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Initialized SQLite on-disk database C:/Users/Steve/AppData/Local/Temp/hammer.DB using existing tables
Starting HammerDB Web Service on port 8080
Listening for HTTP requests on TCP port 8080
hammerws>source sqlrun.tcl
{"success": {"message": "Database set to MSSQLServer"}}
{"success": {"message": "Benchmark set to TPC-C for MSSQLServer"}}
{"success": {"message": "Changed connection:mssqls_server from (local) to (local)\\SQLDEVELOP for MSSQLServer"}}
{"success": {"message": "Set driver script to timed, clearing Script, reload script to activate new setting"}}
{"success": {"message": "Changed tpcc:mssqls_rampup from 2 to 1 for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_duration from 5 to 2 for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_timeprofile from false to true for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_allwarehouse from false to true for MSSQLServer"}}
{"success": {"message": "Transaction Counter refresh rate set to 10"}}
{"success": {"message": "script loaded"}}
1 VU TEST
{"success": {"message": "Virtual users set to 1"}}
{"success": {"message": "2 Virtual Users Created with Monitor VU"}}
{"success": {"message": "Transaction Counter Thread Started"}}
{"success": {"message": "Running Virtual Users: JOBID=618E6DD7782203E263932303"}}
{"success": {"message": "Timer: 1 minutes elapsed"}}
{"success": {"message": "Timer: 2 minutes elapsed"}}
{"success": {"message": "Timer: 3 minutes elapsed"}}
{"success": {"message": "runtimer returned after 181 seconds"}}
{"success": {"message": "Transaction Counter thread running with threadid:tid0000000000003C54"}}{"success": {"message": "Stopping Transaction Counter"}}
[
  "618E6DD7782203E263932303",
  "2021-11-12 13:36:23",
  "1 Active Virtual Users configured",
  "TEST RESULT : System achieved 35839 NOPM from 82362 SQL Server TPM"
]
{
  "NEWORD": {
    "elapsed_ms": "179711.0",
    "calls": "107839",
    "min_ms": "0.414",
    "avg_ms": "0.687",
    "max_ms": "62.606",
    "total_ms": "74062.174",
    "p99_ms": "1.0",
    "p95_ms": "0.887",
    "p50_ms": "0.679",
    "sd": "3590.836",
    "ratio_pct": "41.212"
  },
  "PAYMENT": {
    "elapsed_ms": "179711.0",
    "calls": "107137",
    "min_ms": "0.331",
    "avg_ms": "0.583",
    "max_ms": "153.081",
    "total_ms": "62447.329",
    "p99_ms": "1.136",
    "p95_ms": "0.926",
    "p50_ms": "0.554",
    "sd": "5546.55",
    "ratio_pct": "34.749"
  },
  "DELIVERY": {
    "elapsed_ms": "179711.0",
    "calls": "10662",
    "min_ms": "1.245",
    "avg_ms": "1.498",
    "max_ms": "336.131",
    "total_ms": "15974.446",
    "p99_ms": "1.832",
    "p95_ms": "1.659",
    "p50_ms": "1.442",
    "sd": "32468.41",
    "ratio_pct": "8.889"
  },
  "SLEV": {
    "elapsed_ms": "179711.0",
    "calls": "10837",
    "min_ms": "0.581",
    "avg_ms": "1.284",
    "max_ms": "442.591",
    "total_ms": "13913.749",
    "p99_ms": "1.029",
    "p95_ms": "0.913",
    "p50_ms": "0.768",
    "sd": "142934.778",
    "ratio_pct": "7.742"
  },
  "OSTAT": {
    "elapsed_ms": "179711.0",
    "calls": "10805",
    "min_ms": "0.19",
    "avg_ms": "0.72",
    "max_ms": "405.053",
    "total_ms": "7776.908",
    "p99_ms": "1.303",
    "p95_ms": "1.014",
    "p50_ms": "0.495",
    "sd": "77492.84",
    "ratio_pct": "4.327"
  }
}
{"success": {"message": "vudestroy success"}}
2 VU TEST
{"success": {"message": "Virtual users set to 2"}}
{"success": {"message": "3 Virtual Users Created with Monitor VU"}}
{"success": {"message": "Transaction Counter Thread Started"}}
{"success": {"message": "Running Virtual Users: JOBID=618E6E8EE53403E243832333"}}
{"success": {"message": "Timer: 1 minutes elapsed"}}
{"success": {"message": "Timer: 2 minutes elapsed"}}
{"success": {"message": "Timer: 3 minutes elapsed"}}
{"success": {"message": "runtimer returned after 181 seconds"}}
{"success": {"message": "Transaction Counter thread running with threadid:tid0000000000003374"}}{"success": {"message": "Stopping Transaction Counter"}}
[
  "618E6E8EE53403E243832333",
  "2021-11-12 13:39:26",
  "2 Active Virtual Users configured",
  "TEST RESULT : System achieved 68606 NOPM from 157773 SQL Server TPM"
]
{
  "NEWORD": {
    "elapsed_ms": "179673.5",
    "calls": "100437",
    "min_ms": "0.433",
    "avg_ms": "0.724",
    "max_ms": "937.718",
    "total_ms": "72671.06",
    "p99_ms": "1.252",
    "p95_ms": "0.877",
    "p50_ms": "0.671",
    "sd": "36684.99",
    "ratio_pct": "40.532"
  },
  "PAYMENT": {
    "elapsed_ms": "179673.5",
    "calls": "100658",
    "min_ms": "0.341",
    "avg_ms": "0.64",
    "max_ms": "1558.282",
    "total_ms": "64373.678",
    "p99_ms": "1.259",
    "p95_ms": "0.987",
    "p50_ms": "0.556",
    "sd": "66749.215",
    "ratio_pct": "35.905"
  },
  "DELIVERY": {
    "elapsed_ms": "179673.5",
    "calls": "10196",
    "min_ms": "1.273",
    "avg_ms": "1.582",
    "max_ms": "89.852",
    "total_ms": "16134.015",
    "p99_ms": "2.57",
    "p95_ms": "1.791",
    "p50_ms": "1.503",
    "sd": "13087.684",
    "ratio_pct": "8.999"
  },
  "SLEV": {
    "elapsed_ms": "179673.5",
    "calls": "10190",
    "min_ms": "0.619",
    "avg_ms": "1.157",
    "max_ms": "448.198",
    "total_ms": "11792.671",
    "p99_ms": "1.221",
    "p95_ms": "0.986",
    "p50_ms": "0.824",
    "sd": "81906.924",
    "ratio_pct": "6.577"
  },
  "OSTAT": {
    "elapsed_ms": "179673.5",
    "calls": "9943",
    "min_ms": "0.212",
    "avg_ms": "0.811",
    "max_ms": "480.319",
    "total_ms": "8064.587",
    "p99_ms": "1.429",
    "p95_ms": "1.105",
    "p50_ms": "0.572",
    "sd": "76772.016",
    "ratio_pct": "4.498"
  }
}
{"success": {"message": "vudestroy success"}}

hammerws>

Later we can use the jobs command to query the information about the jobs. In the example below it queries the database, workload and timestamp when the job started and verifies that the job finished successfully. It also retrieves the configuration that was set for the job and the result. It also queries the xtprof timing data for virtual user 2 only.

hammerws>job 618E6DD7782203E263932303 db

["MSSQLServer"]

hammerws>job 618E6DD7782203E263932303 bm

["TPC-C"]

hammerws>job 618E6DD7782203E263932303 timestamp

{"618E6DD7782203E263932303": {"2021-11-12": "13:36:23"}}

hammerws>job 618E6DD7782203E263932303 status

[
  "0",
  "Vuser 1:RUNNING",
  "0",
  "Vuser 2:RUNNING",
  "0",
  "Vuser 2:FINISHED SUCCESS",
  "0",
  "Vuser 1:FINISHED SUCCESS",
  "0",
  "ALL VIRTUAL USERS COMPLETE"
]

hammerws>job 618E6DD7782203E263932303 dict

{
  "connection": {
    "mssqls_server": "(local)\\SQLDEVELOP",
    "mssqls_linux_server": "localhost",
    "mssqls_tcp": "false",
    "mssqls_port": "1433",
    "mssqls_azure": "false",
    "mssqls_authentication": "windows",
    "mssqls_linux_authent": "sql",
    "mssqls_odbc_driver": "ODBC Driver 17 for SQL Server",
    "mssqls_linux_odbc": "ODBC Driver 17 for SQL Server",
    "mssqls_uid": "sa",
    "mssqls_pass": "admin"
  },
  "tpcc": {
    "mssqls_count_ware": "1",
    "mssqls_num_vu": "1",
    "mssqls_dbase": "tpcc",
    "mssqls_imdb": "false",
    "mssqls_bucket": "1",
    "mssqls_durability": "SCHEMA_AND_DATA",
    "mssqls_total_iterations": "10000000",
    "mssqls_raiseerror": "false",
    "mssqls_keyandthink": "false",
    "mssqls_checkpoint": "false",
    "mssqls_driver": "timed",
    "mssqls_rampup": "1",
    "mssqls_duration": "2",
    "mssqls_allwarehouse": "true",
    "mssqls_timeprofile": "true",
    "mssqls_async_scale": "false",
    "mssqls_async_client": "10",
    "mssqls_async_verbose": "false",
    "mssqls_async_delay": "1000",
    "mssqls_connect_pool": "false"
  }
}

hammerws>job 618E6DD7782203E263932303 result

[
  "618E6DD7782203E263932303",
  "2021-11-12 13:36:23",
  "1 Active Virtual Users configured",
  "TEST RESULT : System achieved 35839 NOPM from 82362 SQL Server TPM"
]

hammerws>job 618E6DD7782203E263932303 timing 2

{
  "NEWORD": {
    "elapsed_ms": "179711.0",
    "calls": "107839",
    "min_ms": "0.414",
    "avg_ms": "0.687",
    "max_ms": "62.606",
    "total_ms": "74062.174",
    "p99_ms": "1.0",
    "p95_ms": "0.887",
    "p50_ms": "0.679",
    "sd": "3590.836",
    "ratio_pct": "41.212"
  },
  "PAYMENT": {
    "elapsed_ms": "179711.0",
    "calls": "107137",
    "min_ms": "0.331",
    "avg_ms": "0.583",
    "max_ms": "153.081",
    "total_ms": "62447.329",
    "p99_ms": "1.136",
    "p95_ms": "0.926",
    "p50_ms": "0.554",
    "sd": "5546.55",
    "ratio_pct": "34.749"
  },
  "DELIVERY": {
    "elapsed_ms": "179711.0",
    "calls": "10662",
    "min_ms": "1.245",
    "avg_ms": "1.498",
    "max_ms": "336.131",
    "total_ms": "15974.446",
    "p99_ms": "1.832",
    "p95_ms": "1.659",
    "p50_ms": "1.442",
    "sd": "32468.41",
    "ratio_pct": "8.889"
  },
  "SLEV": {
    "elapsed_ms": "179711.0",
    "calls": "10837",
    "min_ms": "0.581",
    "avg_ms": "1.284",
    "max_ms": "442.591",
    "total_ms": "13913.749",
    "p99_ms": "1.029",
    "p95_ms": "0.913",
    "p50_ms": "0.768",
    "sd": "142934.778",
    "ratio_pct": "7.742"
  },
  "OSTAT": {
    "elapsed_ms": "179711.0",
    "calls": "10805",
    "min_ms": "0.19",
    "avg_ms": "0.72",
    "max_ms": "405.053",
    "total_ms": "7776.908",
    "p99_ms": "1.303",
    "p95_ms": "1.014",
    "p50_ms": "0.495",
    "sd": "77492.84",
    "ratio_pct": "4.327"
  }
}

hammerws>

This output can also be queried over HTTP, for example querying the transaction counter output.

Figure 10.3. Query Transaction Count

Query Transaction Count

The Web Service provides an alternative to the CLI where it is wished to store the output of jobs in a database repository that can be queried returning JSON output rather than using the log file output. This scenario is most likely when building automated testing systems.