Connecting Linux HammerDB cli to MS SQL Server.

[garyjlittle]

Hi Steve, I cannot seem to get my Linux HammerDB CLI to connect to SQL server. I saw in a previous post about using tdbc::odbc::drivers to check that hammerdb can connect to the appropriate drivers. For me the output is empty, but I do have drivers in stalled and "isql" works perfectly fine and can connect to my SQLSERVER db.

I am not sure how to debug further.

gary@dellboy:~/HammerDB-4.4$ ./hammerdbcli
HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>package require tdbc::odbc
1.1.1
hammerdb>tdbc::odbc::drivers

hammerdb>gary@dellboy:~/HammerDB-4.4$ odbcinst -q -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/gary/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
gary@dellboy:~/HammerDB-4.4$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=2

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
UsageCount=1

[sm-shaw]

First step to troubleshoot is does sqlcmd work using your login id and password (note I've changed my password to 'password' in the output shown) (SQL Server is installed on RHEL8), I'm going to login and drop my existing database this way.

For example:

$ sqlcmd -S localhost -U sa -P password
1> SELECT Name from sys.databases;
2> go
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
tpcc                                                                                                                            

(5 rows affected)
1> drop database tpcc;
2> go

Then make sure that the connection credentials are correct in particular linux_server, linux_authent and linux_odbc as well as the uid and password

hammerdb>print dict
Dictionary Settings for MSSQLServer
connection {
 mssqls_server         = (local)
 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           = password
}

In this example I used the GUI to create the schema:

Then I have a test script as follows:

dbset db mssqls
dbset bm TPC-C
diset connection mssqls_linux_server localhost
diset connection mssqls_uid sa
diset connection mssqls_pass password
diset connection mssqls_linux_odbc "ODBC Driver 17 for SQL Server"
diset tpcc mssqls_driver timed
diset tpcc mssqls_timeprofile false
diset tpcc mssqls_rampup 0
diset tpcc mssqls_duration 1
loadscript
vuset vu 2
vucreate
vurun
runtimer 100
vudestroy

and run it

$ ./hammerdbcli auto mssqlstest.tcl
HammerDB CLI v4.5
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Database set to MSSQLServer
Benchmark set to TPC-C for MSSQLServer
Value localhost for connection:mssqls_linux_server is the same as existing value localhost, no change made
Value sa for connection:mssqls_uid is the same as existing value sa, no change made
Value password for connection:mssqls_pass is the same as existing value password, no change made
Value ODBC Driver 17 for SQL Server for connection:mssqls_linux_odbc is the same as existing value ODBC Driver 17 for SQL Server, no change made
Value timed for tpcc:mssqls_driver is the same as existing value timed, no change made
Changed tpcc:mssqls_timeprofile from true to false for MSSQLServer
Value 0 for tpcc:mssqls_rampup is the same as existing value 0, no change made
Value 1 for tpcc:mssqls_duration is the same as existing value 1, no change made
Script loaded, Type "print script" to view
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
3 Virtual Users Created with Monitor VU
Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 0 minutes
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 1 in minutes
Vuser 2:RUNNING
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 1:1 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:2 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 2806 NOPM from 6467 SQL Server TPM
Vuser 1:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Timer: 1 minutes elapsed
runtimer returned after 60 seconds
vudestroy success

If sqlcmd works then hammerDB should also work with the same credentials

[garyjlittle]

Hi Steve,

sqlcmd does work with the same credentials. I am using HammerDBcli Linux connecting to a Windows SQL Server over TCP. I am using sql for mssqls_authentication and mssqls_linux_authent since I am remote. I tried the same on HammerDB running on Windows OS and it works as expected.

Below is the output from sqlcmd

~$ /opt/mssql-tools/bin/sqlcmd -S sqlserver -U nutanix -P nutanix
1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
tpcc    

my dictionary looks like this.

HammerDB CLI v4.5
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
hammerdb>print dict
Dictionary Settings for MSSQLServer
connection {
 mssqls_server         = sqlserver
 mssqls_linux_server   = sqlserver
 mssqls_tcp            = true
 mssqls_port           = 1433
 mssqls_azure          = false
 mssqls_authentication = sql
 mssqls_linux_authent  = sql
 mssqls_odbc_driver    = ODBC Driver 17 for SQL Server
 mssqls_linux_odbc     = ODBC Driver 17 for SQL Server
 mssqls_uid            = nutanix
 mssqls_pass           = nutanix
}
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         = 5
 mssqls_allwarehouse     = true
 mssqls_timeprofile      = false
 mssqls_async_scale      = false
 mssqls_async_client     = 10
 mssqls_async_verbose    = false
 mssqls_async_delay      = 1000
 mssqls_connect_pool     = false
}

I make a file like yours

$ cat mssqlstest.tcl 
dbset db mssqls
dbset bm TPC-C
diset connection mssqls_linux_server sqlserver
diset connection mssqls_uid nutanix
diset connection mssqls_pass nutanix
diset connection mssqls_linux_odbc "ODBC Driver 17 for SQL Server"
diset tpcc mssqls_driver timed
diset tpcc mssqls_timeprofile false
diset tpcc mssqls_rampup 0
diset tpcc mssqls_duration 1
loadscript
vuset vu 2
vucreate
vurun
runtimer 100
vudestroy

But when I run it no work is done at the database

HammerDB-4.5$ ./hammerdbcli auto mssqlstest.tcl 
HammerDB CLI v4.5
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Database set to MSSQLServer
Benchmark set to TPC-C for MSSQLServer
Value sqlserver for connection:mssqls_linux_server is the same as existing value sqlserver, no change made
Value nutanix for connection:mssqls_uid is the same as existing value nutanix, no change made
Value nutanix for connection:mssqls_pass is the same as existing value nutanix, no change made
Value ODBC Driver 17 for SQL Server for connection:mssqls_linux_odbc is the same as existing value ODBC Driver 17 for SQL Server, no change made
Value timed for tpcc:mssqls_driver is the same as existing value timed, no change made
Value false for tpcc:mssqls_timeprofile is the same as existing value false, no change made
Changed tpcc:mssqls_rampup from 1 to 0 for MSSQLServer
Changed tpcc:mssqls_duration from 5 to 1 for MSSQLServer
Script loaded, Type "print script" to view
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
3 Virtual Users Created with Monitor VU
Vuser 1:RUNNING
Vuser 2:RUNNING
Vuser 3:RUNNING
Timer: 1 minutes elapsed
runtimer returned after 100 seconds
Virtual Users remain running in background or shutting down, retry

[sm-shaw]

For this error you see

Vuser 1:RUNNING

instead of:

Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 0 minutes

So it is not getting past this section. It takes your parameters, then builds a connect string and calls tdbc::odbc::connection to connect. So if you need to troubleshoot further then this is the area to run manually to create a connection.

proc connect_string { server port odbc_driver authentication uid pwd tcp azure db encrypt trust_cert} {
    if { $tcp eq "true" } { set server tcp:$server,$port }
    if {[ string toupper $authentication ] eq "WINDOWS" } {
        set connection "DRIVER=$odbc_driver;SERVER=$server;TRUSTED_CONNECTION=YES"
    } else {
        if {[ string toupper $authentication ] eq "SQL" } {
            set connection "DRIVER=$odbc_driver;SERVER=$server;UID=$uid;PWD=$pwd"
        } else {
            puts stderr "Error: neither WINDOWS or SQL Authentication has been specified"
            set connection "DRIVER=$odbc_driver;SERVER=$server"
        }
    }
    if { $azure eq "true" } { append connection ";" "DATABASE=$db" }
    if { $encrypt eq "true" } { append connection ";" "ENCRYPT=yes" } else { append connection ";" "ENCRYPT=no" }
    if { $trust_cert eq "true" } { append connection ";" "TRUSTSERVERCERTIFICATE=yes" }
    return $connection
}

set connection [ connect_string $server $port $odbc_driver $authentication $uid $pwd $tcp $azure $database $encrypt $trust_cert ]
            if [catch {tdbc::odbc::connection create odbc $connection} message ] {
                error "Connection to $connection could not be established : $message"
                }

Note in this example it includes additional parameters for ODBC Driver 18 as described here #406

[sm-shaw]

BTW to be clear of course you can simply the section above to just use the actual connect string to test:
(example from current ODBC 18 version)

hammerdb>package require tdbc::odbc
1.1.3
hammerdb>tdbc::odbc::connection create odbc {DRIVER=ODBC Driver 18 for SQL Server;SERVER=(local)\SQLDEVELOP;TRUSTED_CONNECTION=YES;ENCRYPT=yes;TRUSTSERVERCERTIFICATE=yes}
::odbc
hammerdb>

[garyjlittle]

From my Windows version of HammerDB4.5

hammerdb>package require tdbc::odbc
1.1.3
hammerdb>tdbc::odbc::drivers
{SQL Server} {APILevel=2 ConnectFunctions=YYY CPTimeout=60 DriverODBCVer=03.50 FileUsage=0 SQLLevel=1 UsageCount=1} {SQL Server Native Client 11.0} {UsageCount=1 APILevel=2 ConnectFunctions=YYY CPTimeout=60 DriverODBCVer=03.80 FileUsage=0 SQLLevel=1} {ODBC Driver 17 for SQL Server} {UsageCount=1 APILevel=2 ConnectFunctions=YYY CPTimeout=60 DriverODBCVer=03.80 FileUsage=0 SQLLevel=1}

From my Linux version of HammerDB4.5

hammerdb>package require tdbc::odbc
1.1.3
hammerdb>tdbc::odbc::drivers

[garyjlittle]

Update - everything seems to work as expected in HammerDB 4.4

HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
hammerdb>package require tdbc::odbc
1.1.1
hammerdb>tdbc::odbc::drivers
{ODBC Driver 17 for SQL Server} {{Description=Microsoft ODBC Driver 17 for SQL Server} Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1 UsageCount=1}

Transactions run...

./hammerdbcli auto mssqltest.tcl 
HammerDB CLI v4.4
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Database set to MSSQLServer
Benchmark set to TPC-C for MSSQLServer
Changed connection:mssqls_linux_server from localhost to sqlserver for MSSQLServer
Changed connection:mssqls_uid from sa to nutanix for MSSQLServer
Changed connection:mssqls_pass from admin to nutanix for MSSQLServer
Value ODBC Driver 17 for SQL Server for connection:mssqls_linux_odbc is the same as existing value ODBC Driver 17 for SQL Server, no change made
Clearing Script, reload script to activate new setting
Script cleared
Changed tpcc:mssqls_driver from test to timed for MSSQLServer
Value false for tpcc:mssqls_timeprofile is the same as existing value false, no change made
Changed tpcc:mssqls_rampup from 2 to 0 for MSSQLServer
Changed tpcc:mssqls_duration from 5 to 1 for MSSQLServer
Script loaded, Type "print script" to view
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
3 Virtual Users Created with Monitor VU
Vuser 1:RUNNING
Vuser 1:Beginning rampup time of 0 minutes
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 1 in minutes
Vuser 2:RUNNING
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 1:1 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:2 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 12255 NOPM from 28330 SQL Server TPM
Vuser 1:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
Timer: 1 minutes elapsed
runtimer returned after 60 seconds
vudestroy success