1.介绍
TPC-C是专门针对联机交易处理系统(OLTP系统)的规范,一般情况下我们也把这类系统称为业务处理系统。
2.解压
unzip benchmarksql-4.1.1.zip
3.拷贝数据库驱动到lib文件夹下,配置文件
vi /home/dmdba/benchmarksql-4.1.1/run/props.dm7
4创建数据库测试用户
create user benchmarksql identified by 123456789;
grant dba to benchmarksql;
5.定义表
create table benchmarksql.warehouse (
w_id int not null,
w_ytd float,
w_tax float,
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
cluster primary key(w_id)
)storage(fillfactor 2,without counter);
create table benchmarksql.district (
d_w_id int not null,
d_id int not null,
d_ytd float,
d_tax float,
d_next_o_id int,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
cluster primary key(d_w_id,d_id)
)storage(fillfactor 3,without counter);
create table benchmarksql.customer (
c_w_id int not null,
c_d_id int not null,
c_id int not null,
c_discount float,
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim float,
c_balance float,
c_ytd_payment float,
c_payment_cnt int,
c_delivery_cnt int,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
cluster primary key(c_w_id,c_d_id,c_id)
)storage(without counter);
create sequence benchmarksql.hist_id_seq;
create table benchmarksql.history (
hist_id int default benchmarksql.hist_id_seq.NEXTVAL,
h_c_id int,
h_c_d_id int,
h_c_w_id int,
h_d_id int,
h_w_id int,
h_date timestamp,
h_amount float,
h_data varchar(24)
)storage(branch(16,16),without counter);
create table benchmarksql.oorder (
o_w_id int not null,
o_d_id int not null,
o_id int not null,
o_c_id int,
o_carrier_id int,
o_ol_cnt float,
o_all_local float,
o_entry_d timestamp,
cluster primary key(o_w_id,o_d_id,o_id)
)storage(without counter);
create table benchmarksql.new_order (
no_w_id int not null,
no_d_id int not null,
no_o_id int not null,
cluster primary key(no_w_id,no_d_id,no_o_id)
)storage(without counter);
create table benchmarksql.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,
ol_amount float,
ol_supply_w_id int,
ol_quantity float,
ol_dist_info char(24),
cluster primary key(ol_w_id,ol_d_id,ol_o_id,ol_number)
)storage(without counter);
create table benchmarksql.stock (
s_w_id int not null,
s_i_id int not null,
s_quantity float,
s_ytd float,
s_order_cnt int,
s_remote_cnt int,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
cluster primary key(s_i_id,s_w_id)
)storage(without counter);
create table benchmarksql.item (
i_id int not null,
i_name varchar(24),
i_price float,
i_data varchar(50),
i_im_id int,
cluster primary key(i_id)
)storage(without counter);
6.装载数据库
cd benchmarksql-4.1.1\run
./runLoader.sh props.dm7 numWAREHOUSES 10
7.创建索引和存储过程
create index ndx_customer_name
on benchmarksql.customer (c_w_id, c_d_id, c_last, c_first);
create or replace procedure benchmarksql.createsequence
as
n int;
stmt varchar(100);
begin
stmt:='drop sequence benchmarksql.hist_id_seq';
EXECUTE IMMEDIATE stmt;
select max(hist_id) + 1 into n from benchmarksql.history ;
stmt:='create sequence benchmarksql.hist_id_seq start with '||n;
EXECUTE IMMEDIATE stmt;
end;
call createsequence;
8.预读表数据
select count(*) from "BENCHMARKSQL"."CUSTOMER" union all
select count(*) from "BENCHMARKSQL"."DISTRICT" union all
select count(*) from "BENCHMARKSQL"."ITEM" union all
select count(*) from "BENCHMARKSQL"."NEW_ORDER" union all
select count(*) from "BENCHMARKSQL"."OORDER" union all
select count(*) from "BENCHMARKSQL"."ORDER_LINE" union all
select count(*) from "BENCHMARKSQL"."STOCK" union all
select count(*) from "BENCHMARKSQL"."WAREHOUSE" union all
select count(*) from "BENCHMARKSQL"."HISTORY" union all
select count("C_PAYMENT_CNT") from "BENCHMARKSQL"."CUSTOMER";
commit;
9.开始测试
[dmdba@localhost run]$ ./runBenchmark.sh props.dm7