3. 创建IQ数据(以IQ 15.2为例)
dbgen工具为TPC-H 1GB数据库生成的数据文件的总尺寸大约有1045M。由于IQ数据库具有出色的压缩能力，IQ数据库存放数据的iq user store空间大约是原始数据的50~100%。在这里我们这样安排存储：
(1) IQ_SYSTEM_MAIN 324M 使用2个尺寸为192M的裸设备
从IQ 15开始建议不再存放用户数据，用来存放系统使用的数据存储空间大约是时的dbspace
(2) IQ_SYSTEM_TEMP 494M 使用1个裸设备
(3) IQ USER DBSPACE 988M 使用2个尺寸为494M的裸设备
下面是创建数据库的脚本：
CREATE DATABASE '/sybiqdb/tpchdb/tpchdb.db'
JAVA ON
JCONNECT ON
CASE RESPECT
BLANK PADDING ON
COLLATION 'ISO_BINENG'
IQ PAGE SIZE 131072
PAGE SIZE 4096
IQ PATH '/dev/raw/raw40'
TEMPORARY PATH '/dev/raw/raw50';
具体如何创建数据库的方法，请参见本人在“IQ 基础入门”分类中的博文。
4. 配置IQ Server启动参数文件
用文本编辑器编辑一个参数文件tpchdb.cfg
-n tpch_srv
-x tcpip{port=3638}
-cl 32m
-ch 128m
-gc 20
-gd dba
-gk dba
-gl all
-gm 20
-gp 4096
-ti 4400
-tl 300
-iqmc 256
-iqtc 256
5. 配置数据库
使用命令 start_iq @tpchdb.cfg tpchdb.db 启动建好的IQ数据库，然后以DBA用户登录IQ执行如下的命令配置IQ数据库：
--为IQ_SYSTEM_MAIN增加空间
ALTER DBSPACE IQ_SYSTEM_MAIN ADD FILE IQ_SYSTEM_MAIN_FILE02 '/dev/raw/raw41';
--创建存放用户数据的dbspace
CREATE DBSPACE TPCH_USER_MAIN USING FILE TPCH_USER_MAIN_FILE01 '/dev/raw/raw51',FILE TPCH_USER_MAIN_FILE02 '/dev/raw/raw52';
--禁止用户在IQ_SYSTEM_MAIN dbspace中创建用户数据
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;
--设置基本的IQ数据库选项
set option public.NOTIFY_MODULUS=200000;
set option public.force_no_scroll_cursors='on';
SET OPTION PUBLIC.ALLOW_READ_CLIENT_FILE='ON';
6. 创建用户，并对用户进行设置
sp_iqaddlogin tpch_user,tpchpwd;
grant resource to tpch_user;
GRANT CREATE ON TPCH_USER_MAIN TO tpch_user; --允许用户在TPCH_USER_MAIN dbspace上创建对象
SET OPTION tpch_user.DEFAULT_DBSPACE='TPCH_USER_MAIN'; --指定用户的缺省dbspace
7. 创建TPC-H模型的8个表
使用tpch_user登录建好的IQ数据库，执行下面的create_tables.sql脚本.执行方法如下：
dbisql -c "uid=tpch_user;pwd=tpchpwd;eng=tpch_srv" -nogui create_tables.sql
load_tables.sql内容如下：
--create_tables.sql脚本
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'lineitem') then
drop table lineitem;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'orders') then
drop table orders;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'partsupp') then
drop table partsupp;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'supplier') then
drop table supplier;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'part') then
drop table part;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'customer') then
drop table customer;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'nation') then
drop table nation;
end if;
if exists (select a.table_name from systable a , sysobjects b where a.server_type='IQ'
and b.type='U' and a.table_name = b.name
and a.table_name = 'region') then
drop table region;
end if;
create table part
(p_partkey int not null,
p_name varchar(55),
p_mfgr char(25),
p_brand char(10),
p_type varchar(25),
p_size int,
p_container char(10),
p_retailprice numeric(10,2),
p_comment varchar(23),
primary key(p_partkey)
);
create table region
(r_regionkey int not null,
r_name char(25),
r_comment varchar(152),
primary key(r_regionkey)
);
create table nation
(n_nationkey int not null,
n_name char(25),
n_regionkey int,
n_comment varchar(152),
primary key(n_nationkey),
foreign key (n_regionkey) references region(r_regionkey)
);
create table supplier
(s_suppkey int not null,
s_name char(25),
s_address varchar(40),
s_nationkey int, --Foreign key to N_NATIONKEY
s_phone char(15),
s_acctbal numeric(15,2),
s_comment varchar(101),
primary key(s_suppkey),
foreign key (s_nationkey) references nation(n_nationkey)
);
create table partsupp
(ps_partkey int not null, --Foreign key to P_PARTKEY
ps_suppkey int not null, --Foreign key to S_SUPPKEY
ps_availqty int,
ps_supplycost numeric(10,2),
ps_comment varchar(199),
primary key(ps_partkey,ps_suppkey),
foreign key (ps_partkey) references part(p_partkey),
foreign key (ps_suppkey) references supplier(s_suppkey)
);
create table customer
(c_custkey int not null,
c_name varchar(25),
c_address varchar(40),
c_nationkey int, --Foreign key to N_NATIONKEY
c_phone char(15),
c_acctbal numeric(15,2),
c_mktsegment char(10),
c_comment varchar(117),
primary key(c_custkey),
foreign key (c_nationkey) references nation(n_nationkey)
);
create table orders
(o_orderkey int not null,
o_custkey int, --Foreign key to C_CUSTKEY
o_orderstatus char(1),
o_totalprice numeric(15,2),
o_orderdate date,
o_orderpriority char(15),
o_clerk char(15),
o_shippriority int,
o_comment varchar(79),
primary key(o_orderkey),
foreign key (o_custkey) references customer(c_custkey)
);
create table lineitem
(l_orderkey int not null,
l_partkey int,
l_suppkey int,
l_linenumber int not null,
l_quantity numeric(10,2),
l_extendedprice numeric(10,2),
l_discount numeric(10,2),
l_tax numeric(10,2),
l_returnflag char(1),
l_linestatus char(1),
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment varchar(44),
primary key(l_orderkey,l_linenumber),
foreign key (l_orderkey) references orders(o_orderkey),
foreign key (l_partkey,l_suppkey) references partsupp(ps_partkey,ps_suppkey)
);
