close database; drop database jj_tpch_external; create database jj_tpch_external in datadbs_01 with buffered log; create table "informix".nation_std ( n_nationkey integer not null , n_name char(25) not null , n_regionkey integer not null ); create table "informix".region_std ( r_regionkey integer not null , r_name char(25) not null ); create table "informix".part_std ( 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 ); create table "informix".supplier_std ( 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 ); create table "informix".partsupp_std ( ps_partkey integer not null , ps_suppkey integer not null , ps_availqty integer not null , ps_supplycost decimal(15,2) not null ); create table "informix".customer_std ( 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 ); create table "informix".orders_std ( 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 ); create table "informix".lineitem_std ( 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 ); create external table "informix".nation sameas nation_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/nation.pip")); create external table "informix".region sameas region_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/region.pip")); create external table "informix".part sameas part_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/part.pip")); create external table "informix".supplier sameas supplier_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/supplier.pip")); create external table "informix".partsupp sameas partsupp_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/partsupp.pip")); create external table "informix".customer sameas customer_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/customer.pip")); create external table "informix".orders sameas orders_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/orders.pip")); create external table "informix".lineitem sameas lineitem_std using ( datafiles("pipe:/home/informix/tpch/jj_tpch_external/lineitem.pip")); create unique index nation_pk on nation (n_nationkey) disabled; create unique index region_pk on region (r_regionkey) disabled; create unique index part_pk on part (p_partkey) disabled; create unique index supplier_pk on supplier (s_suppkey) disabled; create unique index partsupp_pk on partsupp (ps_partkey, ps_suppkey) disabled; create unique index customer_pk on customer (c_custkey) disabled; create unique index orders_pk on orders (o_orderkey) disabled; create unique index lineitem_pk on lineitem (l_orderkey, l_linenumber) disabled; execute function ifx_dropmart('JJDWA1','jj_tpch_mart_ext'); 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_external_mart; create database jj_tpch_external_mart in datadbs_01 with buffered log; database jj_tpch_external_mart; execute procedure ifx_probe2mart('jj_tpch_external','jj_tpch_mart_ext'); database jj_tpch_external; execute function ifx_createmart('JJDWA1','jj_tpch_mart_ext','jj_tpch_external_mart'); execute function ifx_loadmart('JJDWA1','jj_tpch_mart_ext','NONE'); execute function lotofile(ifx_getMartdef('JJDWA1', 'jj_tpch_mart_ext'), 'jj_tpch_mart_??.xml','client');