close database; database jj_tpch_internal; drop table nation; drop table region; drop table part; drop table supplier; drop table partsupp; drop table customer; drop table orders; drop table lineitem; close database; drop database jj_tpch_internal; create database jj_tpch_internal in datadbs_02 with buffered log; create raw table "informix".nation ( n_nationkey integer not null , n_name char(25) not null , n_regionkey integer not null ) lock mode row; create raw table "informix".region ( r_regionkey integer not null , r_name char(25) not null ) lock mode row; create raw table "informix".part ( p_partkey integer not null , p_name varchar(55) not null , p_mfgr char(25) not null , p_brand char(10) not null , p_type varchar(25) not null , p_size integer not null , p_container char(10) not null , p_retailprice decimal(15,2) not null ) lock mode row; create raw table "informix".supplier ( s_suppkey integer not null , s_name char(25) not null , s_address varchar(40) not null , s_nationkey integer not null , s_phone char(15) not null , s_acctbal decimal(15,2) not null ) lock mode row; create raw table "informix".partsupp ( ps_partkey integer not null , ps_suppkey integer not null , ps_availqty integer not null , ps_supplycost decimal(15,2) not null ) lock mode row; create raw table "informix".customer ( c_custkey integer not null , c_name varchar(25) not null , c_address varchar(40) not null , c_nationkey integer not null , c_phone char(15) not null , c_acctbal decimal(15,2) not null , c_mktsegment char(10) not null ) lock mode row; create raw table "informix".orders ( o_orderkey integer not null , o_custkey integer not null , o_orderstatus char(1) not null , o_totalprice decimal(15,2) not null , o_orderdate date not null , o_orderpriority char(15) not null , o_clerk char(15) not null , o_shippriority integer not null ) lock mode row; create raw table "informix".lineitem ( l_orderkey integer not null , l_partkey integer not null , l_suppkey integer not null , l_linenumber integer not null , l_quantity decimal(15,2) not null , l_extendedprice decimal(15,2) not null , l_discount decimal(15,2) not null , l_tax decimal(15,2) not null , l_returnflag char(1) not null , l_linestatus char(1) not null , l_shipdate date not null , l_commitdate date not null , l_receiptdate date not null , l_shipinstruct char(25) not null , l_shipmode char(10) not null ) lock mode row; create external table "informix".nation_ext sameas nation using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/nation.pip")); create external table "informix".region_ext sameas region using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/region.pip")); create external table "informix".part_ext sameas part using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/part.pip")); create external table "informix".supplier_ext sameas supplier using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/supplier.pip")); create external table "informix".partsupp_ext sameas partsupp using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/partsupp.pip")); create external table "informix".customer_ext sameas customer using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/customer.pip")); create external table "informix".orders_ext sameas orders using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/orders.pip")); create external table "informix".lineitem_ext sameas lineitem using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/lineitem.pip")); insert into nation select * from nation_ext; insert into region select * from region_ext; insert into part select * from part_ext; insert into supplier select * from supplier_ext; insert into partsupp select * from partsupp_ext; insert into customer select * from customer_ext; insert into orders select * from orders_ext; insert into lineitem select * from lineitem_ext; alter table nation type (standard); alter table region type (standard); alter table part type (standard); alter table supplier type (standard); alter table partsupp type (standard); alter table customer type (standard); alter table orders type (standard); alter table lineitem type (standard); create unique index nation_pk on nation (n_nationkey); create unique index region_pk on region (r_regionkey); create unique index part_pk on part (p_partkey); create unique index supplier_pk on supplier (s_suppkey); create unique index partsupp_pk on partsupp (ps_partkey, ps_suppkey); create unique index customer_pk on customer (c_custkey); create unique index orders_pk on orders (o_orderkey); create unique index lineitem_pk on lineitem (l_orderkey, l_linenumber); -- update statistics high; execute function ifx_dropmart('JJDWA1','jj_tpch_mart_int'); set environment use_dwa 'probe cleanup'; set environment use_dwa 'probe start'; set explain on avoid_execute; set optimization environment fact 'lineitem'; select * from lineitem l, orders o, supplier s, part p, customer c, nation n1, nation n2, region r1, region r2 where l.l_orderkey = o.o_orderkey and l.l_suppkey = s.s_suppkey and l.l_partkey = p.p_partkey and o.o_custkey = c.c_custkey and s.s_nationkey = n2.n_nationkey and c.c_nationkey = n1.n_nationkey and n1.n_regionkey = r1.r_regionkey and n2.n_regionkey = r2.r_regionkey; set optimization environment fact 'partsupp'; select * from partsupp ps, part p, supplier s, nation n, region r where ps.ps_partkey = p.p_partkey and ps.ps_suppkey = s.s_suppkey and s.s_nationkey = n.n_nationkey and n.n_regionkey = r.r_regionkey; set explain off; set environment use_dwa 'probe stop'; drop database jj_tpch_internal_mart; create database jj_tpch_internal_mart in datadbs_01 with buffered log; database jj_tpch_internal_mart; execute procedure ifx_probe2mart('jj_tpch_internal','jj_tpch_mart_int'); database jj_tpch_internal; execute function ifx_createmart('JJDWA1','jj_tpch_mart_int','jj_tpch_internal_mart'); execute function ifx_loadmart('JJDWA1','jj_tpch_mart_int','NONE'); execute function lotofile(ifx_getMartdef('JJDWA1', 'jj_tpch_mart_int'), 'jj_tpch_mart_int_??.xml','client');