Skip to content
This repository has been archived by the owner on Aug 23, 2021. It is now read-only.

Add SAP HANA for TPCC #262

Open
wants to merge 9 commits into
base: master
Choose a base branch
from
Open

Add SAP HANA for TPCC #262

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
5d9cb6d
add hana
suafeng Oct 6, 2018
1c1e353
solve bug
suafeng Oct 6, 2018
a298b3e
add hana tpcc config
suafeng Oct 6, 2018
1445c0c
add sap driver
suafeng Oct 6, 2018
3002c90
Merge branch 'master' into merge-hana
apavlo Oct 7, 2018
bcac79e
fix
suafeng Oct 7, 2018
c63af8b
Merge branch 'merge-hana' of https://github.com/suafeng/oltpbench int…
suafeng Oct 7, 2018
8dde581
fix ddl
suafeng Oct 7, 2018
ef9f112
Merge branch 'master' into merge-hana
apavlo May 17, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
43 changes: 43 additions & 0 deletions config/sample_hanatpcc_config.xml
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,43 @@
<?xml version="1.0"?>
<parameters>

<!-- Connection details -->
<dbtype>hana</dbtype>
<driver>com.sap.db.jdbc.Driver</driver>
<DBUrl>jdbc:sap://server:39015/tpcc</DBUrl>
<username>user</username>
<password>password</password>
<isolation>TRANSACTION_READ_COMMITTED</isolation>

<!-- Scale factor is the number of warehouses in TPCC -->
<scalefactor>16</scalefactor>

<!-- The workload -->
<terminals>64</terminals>
<works>
<work>
<time>300</time>
<rate>unlimited</rate>
<weights>45,43,4,4,4</weights>
</work>
</works>

<!-- TPCC specific -->
<transactiontypes>
<transactiontype>
<name>NewOrder</name>
</transactiontype>
<transactiontype>
<name>Payment</name>
</transactiontype>
<transactiontype>
<name>OrderStatus</name>
</transactiontype>
<transactiontype>
<name>Delivery</name>
</transactiontype>
<transactiontype>
<name>StockLevel</name>
</transactiontype>
</transactiontypes>
</parameters>
Binary file added lib/ngdbc.jar
View file
Open in desktop
Binary file not shown.
3 changes: 2 additions & 1 deletion src/com/oltpbenchmark/api/Procedure.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -128,7 +128,8 @@ public final PreparedStatement getPreparedStatementReturnKeys(Connection conn, S

// HACK: If the target system is Postgres, wrap the PreparedStatement in a special
// one that fakes the getGeneratedKeys().
if (is != null && this.dbType == DatabaseType.POSTGRES) {
if (is != null && this.dbType == DatabaseType.POSTGRES ||
(this.dbType == DatabaseType.HANA)) {
Copy link

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Is this necessary? the AutoIncrementPreparedStatement doesn't have special handling for HANA anyway.

pStmt = new AutoIncrementPreparedStatement(this.dbType, conn.prepareStatement(stmt.getSQL()));
}
// Everyone else can use the regular getGeneratedKeys() method
Expand Down
191 changes: 191 additions & 0 deletions src/com/oltpbenchmark/benchmarks/tpcc/ddls/tpcc-hana-ddl.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,191 @@
-- TODO: C_SINCE ON UPDATE CURRENT_TIMESTAMP,

-- woonhak, turn off foreign key check, reference tpcc-mysql and tpcc specification
-- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
-- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

-- DROP TABLE IF EXISTS CUSTOMER;
-- DROP TABLE IF EXISTS DISTRICT;
-- DROP TABLE IF EXISTS HISTORY;
-- DROP TABLE IF EXISTS ITEM;
-- DROP TABLE IF EXISTS NEW_ORDER;
-- DROP TABLE IF EXISTS OORDER;
-- DROP TABLE IF EXISTS ORDER_LINE;
-- DROP TABLE IF EXISTS STOCK;
-- DROP TABLE IF EXISTS WAREHOUSE;
call existstable('CUSTOMER', 'SYSTEM');
call existstable('DISTRICT', 'SYSTEM');
call existstable('HISTORY', 'SYSTEM');
call existstable('ITEM', 'SYSTEM');
call existstable('NEW_ORDER', 'SYSTEM');
call existstable('OORDER', 'SYSTEM');
call existstable('ORDER_LINE', 'SYSTEM');
call existstable('STOCK', 'SYSTEM');
call existstable('WAREHOUSE', 'SYSTEM');

CREATE ROW TABLE CUSTOMER (
C_W_ID INT NOT NULL,
C_D_ID INT NOT NULL,
C_ID INT NOT NULL,
C_DISCOUNT DECIMAL(4,4) NOT NULL,
C_CREDIT VARCHAR(2) NOT NULL,
C_LAST VARCHAR(16) NOT NULL,
C_FIRST VARCHAR(16) NOT NULL,
C_CREDIT_LIM DECIMAL(12,2) NOT NULL,
C_BALANCE DECIMAL(12,2) NOT NULL,
C_YTD_PAYMENT FLOAT NOT NULL,
C_PAYMENT_CNT INT NOT NULL,
C_DELIVERY_CNT INT NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(20) NOT NULL,
C_CITY VARCHAR(20) NOT NULL,
C_STATE VARCHAR(2) NOT NULL,
C_ZIP VARCHAR(9) NOT NULL,
C_PHONE VARCHAR(16) NOT NULL,
C_SINCE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
C_MIDDLE VARCHAR(2) NOT NULL,
C_DATA VARCHAR(500) NOT NULL,
PRIMARY KEY (C_W_ID,C_D_ID,C_ID)
);


CREATE ROW TABLE DISTRICT (
D_W_ID INT NOT NULL,
D_ID INT NOT NULL,
D_YTD DECIMAL(12,2) NOT NULL,
D_TAX DECIMAL(4,4) NOT NULL,
D_NEXT_O_ID INT NOT NULL,
D_NAME VARCHAR(10) NOT NULL,
D_STREET_1 VARCHAR(20) NOT NULL,
D_STREET_2 VARCHAR(20) NOT NULL,
D_CITY VARCHAR(20) NOT NULL,
D_STATE VARCHAR(2) NOT NULL,
D_ZIP VARCHAR(9) NOT NULL,
PRIMARY KEY (D_W_ID,D_ID)
);

-- TODO: H_DATE ON UPDATE CURRENT_TIMESTAMP

CREATE ROW TABLE HISTORY (
H_C_ID INT NOT NULL,
H_C_D_ID INT NOT NULL,
H_C_W_ID INT NOT NULL,
H_D_ID INT NOT NULL,
H_W_ID INT NOT NULL,
H_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
H_AMOUNT DECIMAL(6,2) NOT NULL,
H_DATA VARCHAR(24) NOT NULL
);


CREATE ROW TABLE ITEM (
I_ID INT NOT NULL,
I_NAME VARCHAR(24) NOT NULL,
I_PRICE DECIMAL(5,2) NOT NULL,
I_DATA VARCHAR(50) NOT NULL,
I_IM_ID INT NOT NULL,
PRIMARY KEY (I_ID)
);


CREATE ROW TABLE NEW_ORDER (
NO_W_ID INT NOT NULL,
NO_D_ID INT NOT NULL,
NO_O_ID INT NOT NULL,
PRIMARY KEY (NO_W_ID,NO_D_ID,NO_O_ID)
);

-- TODO: O_ENTRY_D ON UPDATE CURRENT_TIMESTAMP

CREATE ROW TABLE OORDER (
O_W_ID INT NOT NULL,
O_D_ID INT NOT NULL,
O_ID INT NOT NULL,
O_C_ID INT NOT NULL,
O_CARRIER_ID INT DEFAULT NULL,
O_OL_CNT DECIMAL(2,0) NOT NULL,
O_ALL_LOCAL DECIMAL(1,0) NOT NULL,
O_ENTRY_D TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (O_W_ID,O_D_ID,O_ID),
UNIQUE (O_W_ID,O_D_ID,O_C_ID,O_ID)
);


CREATE ROW TABLE ORDER_LINE (
OL_W_ID INT NOT NULL,
OL_D_ID INT NOT NULL,
OL_O_ID INT NOT NULL,
OL_NUMBER INT NOT NULL,
OL_I_ID INT NOT NULL,
OL_DELIVERY_D TIMESTAMP NULL DEFAULT NULL,
OL_AMOUNT DECIMAL(6,2) NOT NULL,
OL_SUPPLY_W_ID INT NOT NULL,
OL_QUANTITY DECIMAL(2,0) NOT NULL,
OL_DIST_INFO VARCHAR(24) NOT NULL,
PRIMARY KEY (OL_W_ID,OL_D_ID,OL_O_ID,OL_NUMBER)
);

CREATE ROW TABLE STOCK (
S_W_ID INT NOT NULL,
S_I_ID INT NOT NULL,
S_QUANTITY DECIMAL(4,0) NOT NULL,
S_YTD DECIMAL(8,2) NOT NULL,
S_ORDER_CNT INT NOT NULL,
S_REMOTE_CNT INT NOT NULL,
S_DATA VARCHAR(50) NOT NULL,
S_DIST_01 VARCHAR(24) NOT NULL,
S_DIST_02 VARCHAR(24) NOT NULL,
S_DIST_03 VARCHAR(24) NOT NULL,
S_DIST_04 VARCHAR(24) NOT NULL,
S_DIST_05 VARCHAR(24) NOT NULL,
S_DIST_06 VARCHAR(24) NOT NULL,
S_DIST_07 VARCHAR(24) NOT NULL,
S_DIST_08 VARCHAR(24) NOT NULL,
S_DIST_09 VARCHAR(24) NOT NULL,
S_DIST_10 VARCHAR(24) NOT NULL,
PRIMARY KEY (S_W_ID,S_I_ID)
);

CREATE ROW TABLE WAREHOUSE (
W_ID INT NOT NULL,
W_YTD DECIMAL(12,2) NOT NULL,
W_TAX DECIMAL(4,4) NOT NULL,
W_NAME VARCHAR(10) NOT NULL,
W_STREET_1 VARCHAR(20) NOT NULL,
W_STREET_2 VARCHAR(20) NOT NULL,
W_CITY VARCHAR(20) NOT NULL,
W_STATE VARCHAR(2) NOT NULL,
W_ZIP VARCHAR(9) NOT NULL,
PRIMARY KEY (W_ID)
);

-- INDEXES
CREATE INDEX IDX_CUSTOMER_NAME ON CUSTOMER (C_W_ID,C_D_ID,C_LAST,C_FIRST);

-- woohak, add constraints. MySQL/InnoDB storage engine is kind of IoT.
-- and add constraints and make indexes later aretoo slow when running a single thread.
-- so I just add create index and foreign key constraints before loading data.

-- already created
-- CREATE INDEX IDX_CUSTOMER ON CUSTOMER (C_W_ID,C_D_ID,C_LAST,C_FIRST);
-- CREATE INDEX IDX_ORDER ON OORDER (O_W_ID,O_D_ID,O_C_ID,O_ID);
-- tpcc-mysql create two indexes for the foreign key constraints, Is it really necessary?
-- CREATE INDEX FKEY_STOCK_2 ON STOCK (S_I_ID);
-- CREATE INDEX FKEY_ORDER_LINE_2 ON ORDER_LINE (OL_SUPPLY_W_ID,OL_I_ID);

-- add 'ON DELETE CASCADE' to clear table work correctly

ALTER TABLE DISTRICT ADD CONSTRAINT FKEY_DISTRICT_1 FOREIGN KEY(D_W_ID) REFERENCES WAREHOUSE(W_ID) ON DELETE CASCADE;
ALTER TABLE CUSTOMER ADD CONSTRAINT FKEY_CUSTOMER_1 FOREIGN KEY(C_W_ID,C_D_ID) REFERENCES DISTRICT(D_W_ID,D_ID) ON DELETE CASCADE ;
ALTER TABLE HISTORY ADD CONSTRAINT FKEY_HISTORY_1 FOREIGN KEY(H_C_W_ID,H_C_D_ID,H_C_ID) REFERENCES CUSTOMER(C_W_ID,C_D_ID,C_ID) ON DELETE CASCADE;
ALTER TABLE HISTORY ADD CONSTRAINT FKEY_HISTORY_2 FOREIGN KEY(H_W_ID,H_D_ID) REFERENCES DISTRICT(D_W_ID,D_ID) ON DELETE CASCADE;
ALTER TABLE NEW_ORDER ADD CONSTRAINT FKEY_NEW_ORDER_1 FOREIGN KEY(NO_W_ID,NO_D_ID,NO_O_ID) REFERENCES OORDER(O_W_ID,O_D_ID,O_ID) ON DELETE CASCADE;
ALTER TABLE OORDER ADD CONSTRAINT FKEY_ORDER_1 FOREIGN KEY(O_W_ID,O_D_ID,O_C_ID) REFERENCES CUSTOMER(C_W_ID,C_D_ID,C_ID) ON DELETE CASCADE;
ALTER TABLE ORDER_LINE ADD CONSTRAINT FKEY_ORDER_LINE_1 FOREIGN KEY(OL_W_ID,OL_D_ID,OL_O_ID) REFERENCES OORDER(O_W_ID,O_D_ID,O_ID) ON DELETE CASCADE;
ALTER TABLE ORDER_LINE ADD CONSTRAINT FKEY_ORDER_LINE_2 FOREIGN KEY(OL_SUPPLY_W_ID,OL_I_ID) REFERENCES STOCK(S_W_ID,S_I_ID) ON DELETE CASCADE;
ALTER TABLE STOCK ADD CONSTRAINT FKEY_STOCK_1 FOREIGN KEY(S_W_ID) REFERENCES WAREHOUSE(W_ID) ON DELETE CASCADE;
ALTER TABLE STOCK ADD CONSTRAINT FKEY_STOCK_2 FOREIGN KEY(S_I_ID) REFERENCES ITEM(I_ID) ON DELETE CASCADE;


-- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
-- SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
1 change: 1 addition & 0 deletions src/com/oltpbenchmark/types/DatabaseType.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -52,6 +52,7 @@ public enum DatabaseType {
CASSANDRA("com.github.adejanovski.cassandra.jdbc.CassandraDriver", true, true, false),
PELOTON("org.postgresql.Driver", false, false, true),
MEMSQL("com.mysql.jdbc.Driver", true, false, false),
HANA("com.sap.db.jdbc.Driver", true, false, true)
;

private DatabaseType(String driver,
Expand Down