HammerDB usage query

[pinakesh]

Hi Team,

Please suggest if it is possible to run hammerDB with user defined queries/driver scripts and on user data (rather than tpcc database created by hammerdb), if yes, then please can you provide the steps?

Regards,
Pinakesh

[sm-shaw]

Yes of course - HammerDB is entirely scripted and in the GUI the file and load dialog can allow you to load your own script and run it against your own data - in the command line it is the customscript command - There is an example here of converting an Oracle trace file into a bespoke script and running it against your own data: https://www.hammerdb.com/docs/ch14.html. Even if not using Oracle it can serve as a guide on how to do this.
If you prefer you can always write your own scripts in Python instead of TCL as described here https://www.hammerdb.com/blog/uncategorized/how-to-use-python-with-hammerdb/
Note that the schema builds and the drivers are both scripts so looking at the existing ones gives a guide on best practices for writing your own. Best approach is to start and then follow up with questions.

[pinakesh]

Hi Steve,

Thanks for reverting, however I didn't find any oracle equivalent trace file or alternative for postgres that can be used with HammerDB. Also, I am not acquainted with TCL or python hence can't use those either to write custom script.

I tried modifying the oracle driver script but wasn't able to do so as it contains everything related to oracle viz. oralogon, oraopen, orasql, etc, I dont know the equivalent keywords and syntax for postgres.

Hence, I am asking if it would it be possible for you to provide an example cuctom script for postgres such as a simple 'select (*) from table_name' (queried from user created db - not created by HammerDB automatically).

If I can get such a script then I will be able to edit it and use it as per my config.

[sm-shaw]

Yes, if you look at the PostgreSQL build and driver scripts, this will show you how to interact with PostgreSQL. Nevertheless the following is an basic example that you can use as a framework. Note that by default the language used is TCL.

Firstly I set the path and create a user and database for testing.

$ export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
postgres=# create user test password 'test';
CREATE ROLE
postgres=# create database test owner test;
CREATE DATABASE

The following script is an example to connect to Postgres, create a table, insert data into it and then select the data.

#!/usr/local/bin/tclsh8.6
set library Pgtcl
set host localhost
set port 5432
set user test
set password test
set dbname test
if [catch {package require $library} message] { error "Failed to load $library - $message" }
proc ConnectToPostgres { host port user password dbname } {
if {[catch {set lda [pg_connect -conninfo [list host = $host port = $port user = $user password = $password dbname = $dbname ]]} message]} {
set lda "Failed" ; puts $message
error $message
 } else {
pg_notice_handler $lda puts
set result [ pg_exec $lda "set CLIENT_MIN_MESSAGES TO 'ERROR'" ]
pg_result $result -clear
        }
return $lda
}

set lda [ ConnectToPostgres $host $port $user $password $dbname ]
if { $lda eq "Failed" } {
error "error, the database connection to $host could not be established"
} else {
puts "Connected to PostgreSQL"
	}

set sql(createtab) "CREATE TABLE TEST_TABLE (ID NUMERIC(2, 0), NAME CHAR(12))"
set result [ pg_exec $lda $sql(createtab) ]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
error "[pg_result $result -error]"
        } else {
pg_result $result -clear
        }       

set result [ pg_exec $lda "insert into test_table (id, name) values (1, 'first row'),(2, 'second row'),(3, 'third row')" ]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
            error "[pg_result $result -error]"
        } else {
        pg_result $result -clear
        }

pg_select $lda "select * from test_table" array {
parray array
}

When this is run in hammerdbcli the output is as follows:

$ ./hammerdbcli 
HammerDB CLI v3.2
Copyright (C) 2003-2019 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>source pgtestscript.tcl
Connected to PostgreSQL
array(.headers) = id name
array(.numcols) = 2
array(.tupno)   = 0
array(id)       = 1
array(name)     = first row   
array(.headers) = id name
array(.numcols) = 2
array(.tupno)   = 1
array(id)       = 2
array(name)     = second row  
array(.headers) = id name
array(.numcols) = 2
array(.tupno)   = 2
array(id)       = 3
array(name)     = third row   
hammerdb>

[sm-shaw]

As per follow up message putting the insert statements in a loop of 10000 iterations inserting 3 lines per insert does insert 30000 rows as would be expected and not 60000 - perhaps the test was ran twice? query after test and test script shown below.

You are now connected to database "test" as user "postgres".
test=# select count(*) from test_table;
 count 
-------
 30000
(1 row)

test=# select * from test_Table limit 10;
 id |     name     
----+--------------
  1 | first row   
  2 | second row  
  3 | third row   
  1 | first row   
  2 | second row  
  3 | third row   
  1 | first row   
  2 | second row  
  3 | third row   
  1 | first row   
(10 rows)

#!/usr/local/bin/tclsh8.6
set library Pgtcl
set host localhost
set port 5432
set user test
set password test
set dbname test
set total_iterations 10000
if [catch {package require $library} message] { error "Failed to load $library - $message" }
proc ConnectToPostgres { host port user password dbname } {
if {[catch {set lda [pg_connect -conninfo [list host = $host port = $port user = $user password = $password dbname = $dbname ]]} mess
age]} {
set lda "Failed" ; puts $message
error $message
} else {
pg_notice_handler $lda puts
set result [ pg_exec $lda "set CLIENT_MIN_MESSAGES TO 'ERROR'" ]
pg_result $result -clear
}
return $lda
}

set lda [ ConnectToPostgres $host $port $user $password $dbname ]
if { $lda eq "Failed" } {
error "error, the database connection to $host could not be established"
} else {
puts "Connected to PostgreSQL"
}

set sql(createtab) "CREATE TABLE TEST_TABLE (ID NUMERIC(2, 0), NAME CHAR(12))"
set result [ pg_exec $lda $sql(createtab) ]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
error "[pg_result $result -error]"
        } else {
pg_result $result -clear
        }       

for {set it 0} {$it < $total_iterations} {incr it} {
puts "it is $it"
set result [ pg_exec $lda "insert into test_table (id, name) values (1, 'first row'),(2, 'second row'),(3, 'third row')" ]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
error "[pg_result $result -error]"
} else {
puts "$it:$result"
pg_result $result -clear
}
}

[sm-shaw]

Closed as no further update - assume that no further issues identified.