-
Notifications
You must be signed in to change notification settings - Fork 4
SQL Server
https://hub.docker.com/_/microsoft-mssql-server
Example to launch docker container
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=topsecret' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15
Make sure that sqlcmd and bcp utilities are on the PATH.
Test the connection from command line
sqlcmd -S tcp:thinkde,1433 -U SA
Install JDBC driver
https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
Unpack Java JDBC driver in /opt directory making it accessible by the application.
Connect as SA user
create database perfdb
create login perf with password='secret123?'
use perfdb
create user perf for login perf
grant alter to perf
grant control to perf
Make sure that user can connect and has full control in perfdb database.
sqlcmd -S tcp:thinkde,1433 -U perf -d perfdb -P secret123?
1> create table x (x int);
2> go
1> insert into x values(1);
2> go
(1 rows affected)
1> select * from x;
2> go
x
-----------
1
(1 rows affected)
1> drop table x;
2> go
https://github.com/stanislawbartkowski/mytpcds#tpc-ds-preparation
cd query_templates
vi mssql.tpl
define __LIMITA = "";
define __LIMITB = "top %d";
define __LIMITC = "";
define _BEGIN = "-- start query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];
define _END = "-- end query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];
Copy access template
cp env.templates/mssql.rc env
Configure env/mssql.rc
| Parameter | Description | Default value |
|---|---|---|
| DBNAME | TCP/DS database name | perfdb |
| DBUSER | Database username | perf |
| DBPASSWORD | Database user password | secret123? |
| DBTYPE | Database identifier | mssql |
| DBPORT | Database connection port | 5443 |
| DBURL | JDBC URL string | "jdbc:sqlserver://$DBHOST;databaseName=$DBNAME" |
| JAVADRIVER | JDBC driver file path | /opt/"sqljdbc_8.2\enu"/mssql-jdbc-8.2.2.jre8.jar |
In conn.rc file, uncomment mssql line
...........
#export ENV=env/netezza
#export ENV=env/derby
#export ENV=env/jsqsh
export ENV=env/mssql
In run.sh launching script, uncomment querystreams or queryqualification
#./tpc.sh querystreams
#./tpc.sh queryqualification
In run.sh, uncomment testconnect and run
./run.sh
Connecting to jdbc:sqlserver://thinkde;databaseName=perfdb
Log info
INFO: User: perf, password: XXXX
PASSED
In test job, the mytpcds deploys also several supporting UDFs.
| QUALIFY | RESULT |
|---|---|
| SQL coverage | 93 queries |
| Qualify test | MATCHES 70 DIFFER 23 FAILED 6 |