DM8 tpcc的测试

要求JDK1.7及以上运行环境

我这里默认是安装好的

jdk链接:https://pan.baidu.com/s/1LESlGJw_X0o8p9nUSQIsmw 
提取码：dd0w

tpcc工具链接自行下载上传:

链接：https://pan.baidu.com/s/1vWZZCNYfTYp7YLPrJa6ZZw 
提取码：nnx5

一、tpcc简介

事务处理性能委员会（ Transaction Processing Performance Council ），简称TPC，它的功能是制定商务应用基准程序（Benchmark）的标准规范、性能和价格度量，并管理测试结果的发布。

TPC－C是在线事务处理(OLTP)的基准程序，用于衡量数据库系统OLTP性能的指标。

二、测试软件

1.上传软件与sql脚本

2.复制驱动文件到lib下

cp /dm8/drivers/jdbc/DmJdbcDriver18.jar .

3.编辑配置文件

4.创建用户表空间

CREATE TABLESPACE BENCHMARKSQL1 DATAFILE 'BENCHMARKSQL1.dbf' SIZE 20000;

CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "BENCHMARKSQL1";

GRANT DBA TO BENCHMARKSQL;

5.创建测试所需表

执行此脚本

./runSQL.sh props.dm tableCreates

或者手动执行

create table bmsql_config (

cfg_name    varchar(30) primary key,

cfg_value   varchar(50)

);

create table bmsql_warehouse (

w_id        integer   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 bmsql_district (

d_w_id       integer       not null,

d_id         integer       not null,

d_ytd        float,

d_tax        float,

d_next_o_id  integer,

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 bmsql_customer (

c_w_id         integer        not null,

c_d_id         integer        not null,

c_id           integer        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  integer,

c_delivery_cnt integer,

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 bmsql_hist_id_seq;

create table bmsql_history (

hist_id  integer,

h_c_id   integer,

h_c_d_id integer,

h_c_w_id integer,

h_d_id   integer,

h_w_id   integer,

h_date   timestamp,

h_amount float,

h_data   varchar(24)

)

storage(branch(16,16),without counter);

create table bmsql_new_order (

no_w_id  integer   not null,

no_d_id  integer   not null,

no_o_id  integer   not null,

cluster primary key (no_w_id, no_d_id, no_o_id)

)

storage(without counter);

create table bmsql_oorder (

o_w_id       integer      not null,

o_d_id       integer      not null,

o_id         integer      not null,

o_c_id       integer,

o_carrier_id integer,

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 bmsql_order_line (

ol_w_id         integer   not null,

ol_d_id         integer   not null,

ol_o_id         integer   not null,

ol_number       integer   not null,

ol_i_id         integer   not null,

ol_delivery_d   timestamp,

ol_amount       float,

ol_supply_w_id  integer,

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 bmsql_item (

i_id     integer      not null,

i_name   varchar(24),

i_price  float,

i_data   varchar(50),

i_im_id  integer,

cluster primary key (i_id)

)

storage(without counter);

create table bmsql_stock (

s_w_id       integer       not null,

s_i_id       integer       not null,

s_quantity   float,

s_ytd        float,

s_order_cnt  integer,

s_remote_cnt integer,

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);

6.装载数据

./runLoader.sh props.dm

7.创建索引

./runSQL.sh props.dm indexCreates

8. 创建存储过程

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;

9.预热数据让测试更加准确

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;

10.开始测试

./runBenchmark.sh props.dm

http://t.csdn.cn/v9RMw