create table PART (
 P_PARTKEY integer not null,
 P_NAME  varchar( 55 ),
 P_MFGR  char( 25 ),
 P_BRAND  char( 10 ),
 P_TYPE  varchar( 25 ),
 P_SIZE  integer,
 P_CONTAINER char( 10 ),
 P_RETAILPRICE numeric( 18 , 2 ),
 P_COMMENT varchar( 23 )
);
alter table PART add constraint PART_PK primary key (P_PARTKEY);

create table SUPPLIER (
 S_SUPPKEY integer not null,
 S_NAME  char( 25 ),
 S_ADDRESS varchar( 40 ),
 S_NATIONKEY integer not null,
 S_PHONE  char( 15 ),
 S_ACCTBAL numeric( 18 , 2 ),
 S_COMMENT varchar( 101 )
);
alter table SUPPLIER add constraint SUPPLIER_PK primary key (S_SUPPKEY);

create table PARTSUPP (
 PS_PARTKEY integer not null,
 PS_SUPPKEY integer not null,
 PS_AVAILQTY integer,
 PS_SUPPLYCOST numeric( 18 , 2 ),
 PS_COMMENT varchar( 199 )
);
alter table PARTSUPP add constraint PARTSUPP_PK primary key (PS_PARTKEY, PS_SUPPKEY);

create table CUSTOMER (
 C_CUSTKEY integer not null,
 C_NAME  varchar( 25 ),
 C_ADDRESS varchar( 40 ),
 C_NATIONKEY integer not null,
 C_PHONE  char( 15 ),
 C_ACCTBAL numeric( 18 , 2 ),
 C_MKTSEGMENT char( 10 ),
 C_COMMENT varchar( 117 )
);
alter table CUSTOMER add constraint CUSTOMER_PK primary key (C_CUSTKEY);

create table ORDERS (
 O_ORDERKEY integer not null,
 O_CUSTKEY integer not null,
 O_ORDERSTATUS char( 1 ),
 O_TOTALPRICE numeric( 18 , 2 ),
 O_ORDERDATE date,
 O_ORDERPRIORITY char( 15 ),
 O_CLERK  char( 15 ),
 O_SHIPPRIORITY integer,
 O_COMMENT varchar( 79 )
);
alter table ORDERS add constraint ORDERS_PK primary key (O_ORDERKEY);

create table LINEITEM (
 L_ORDERKEY integer not null,
 L_PARTKEY integer not null,
 L_SUPPKEY integer not null,
 L_LINENUMBER integer not null,
 L_QUANTITY numeric( 18 , 2 ),
 L_EXTENDEDPRICE numeric( 18 , 2 ),
 L_DISCOUNT numeric( 18 , 2 ),
 L_TAX  numeric( 18 , 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 )
);
alter table LINEITEM add constraint LINEITEM_PK primary key (L_ORDERKEY, L_LINENUMBER);

create table NATION (
 N_NATIONKEY integer not null,
 N_NAME  char( 25 ),
 N_REGIONKEY integer not null,
 N_COMMENT varchar( 125 )
);
alter table NATION add constraint NATION_PK primary key (N_NATIONKEY);

create table REGION (
 R_REGIONKEY integer not null,
 R_NAME  char( 25 ),
 R_COMMENT varchar( 152 )
);
alter table REGION add constraint REGION_PK primary key (R_REGIONKEY);

load from c:\tpc-r\db2\PART.tbl     of del modified by coldel| datesiso REPLACE into vvm.PART;
load from c:\tpc-r\db2\SUPPLIER.tbl of del modified by coldel| datesiso REPLACE into vvm.SUPPLIER;
load from c:\tpc-r\db2\PARTSUPP.tbl of del modified by coldel| datesiso REPLACE into vvm.PARTSUPP;
load from c:\tpc-r\db2\CUSTOMER.tbl of del modified by coldel| datesiso REPLACE into vvm.CUSTOMER;
load from c:\tpc-r\db2\ORDERS.tbl   of del modified by coldel| datesiso REPLACE into vvm.ORDERS;
load from c:\tpc-r\db2\LINEITEM.tbl of del modified by coldel| datesiso REPLACE into vvm.LINEITEM;
load from c:\tpc-r\db2\NATION.tbl   of del modified by coldel| datesiso REPLACE into vvm.NATION;
load from c:\tpc-r\db2\REGION.tbl   of del modified by coldel| datesiso REPLACE into vvm.REGION;

create index lineitem_shipdate  on lineitem(l_shipdate);
create index lineitem_partkey_s on lineitem(l_partkey, l_suppkey);
create index part_brand_contain on part(p_brand, p_container, p_size);
create index lineitem_quantity_ on lineitem(l_quantity, l_shipmode, l_shipinstruct);
create index lineitem_shipmode_ on lineitem(l_shipmode, l_receiptdate);
create index part_name          on part(p_name);
create index supplier_nationkey on supplier(s_nationkey);
create index partsupp_suppkey   on partsupp(ps_suppkey);
create index customer_nationkey on customer(c_nationkey);
create index orders_custkey     on orders(o_custkey);
create index orders_orderdate   on orders(o_orderdate);

runstats on table vvm.PART with distribution and detailed indexes all;
runstats on table vvm.SUPPLIER with distribution and detailed indexes all;
runstats on table vvm.PARTSUPP with distribution and detailed indexes all;
runstats on table vvm.CUSTOMER with distribution and detailed indexes all;
runstats on table vvm.ORDERS with distribution and detailed indexes all;
runstats on table vvm.LINEITEM with distribution and detailed indexes all;
runstats on table vvm.NATION with distribution and detailed indexes all;
runstats on table vvm.REGION with distribution and detailed indexes all;

UPDATE DATABASE CONFIGURATION FOR TPCR USING APP_CTL_HEAP_SZ  128 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING APPGROUP_MEM_SZ         10462 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING CATALOGCACHE_SZ  260 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING CHNGPGS_THRESH   70 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING DBHEAP           600 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING LOCKLIST         50 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING LOGBUFSZ         131 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING LOGFILSIZ        1024 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING LOGPRIMARY       3 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING LOGSECOND        0 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING MAXAPPLS         40    MAXLOCKS     15 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING MINCOMMIT        1 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING NUM_IOCLEANERS   1 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING NUM_IOSERVERS    5 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING PCKCACHESZ       1114 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING SOFTMAX          150 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING SORTHEAP         682 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING STMTHEAP         2048 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING DFT_DEGREE      ANY;
UPDATE DATABASE CONFIGURATION FOR TPCR USING DFT_PREFETCH_SZ  64 ;
UPDATE DATABASE CONFIGURATION FOR TPCR USING UTIL_HEAP_SZ     24837 ;
UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES          20478 ;
UPDATE DATABASE MANAGER CONFIGURATION USING INTRA_PARALLEL     OFF;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_QUERYDEGREE     1 ;
UPDATE DATABASE MANAGER CONFIGURATION USING MAXAGENTS           400 ;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS      400 ;
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_INITAGENTS      0 ;
UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_BUFFERS     4096 ;
UPDATE DATABASE MANAGER CONFIGURATION USING PRIV_MEM_THRESH         32767 ;
CONNECT TO TPCR;
ALTER BUFFERPOOL IBMDEFAULTBP SIZE  74513 ;
SET CURRENT QUERY OPTIMIZATION =  7 ;
COMMIT;
CONNECT RESET;

db2stop force
db2start
db2batch -d tpcr -f  2 - 2 - 2 .sql  -r  2 - 2 - 2 .log1, 2 - 2 - 2 .log2 -v on -t ;

select
        s_acctbal,
        s_name,
        n_name,
        p_partkey,
        p_mfgr,
        s_address,
        s_phone,
        s_comment
from
        part p,
        supplier,
        partsupp,
        nation,
        region
where
        p_partkey = ps_partkey
        and s_suppkey = ps_suppkey
        and p_size =  15 
        and p_type like '%BRASS'
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'EUROPE'
        and ps_supplycost = (
                select
                        min(ps_supplycost)
                from
                        partsupp,
                        supplier,
                        nation,
                        region
                where
                        p.p_partkey = ps_partkey
                        and s_suppkey = ps_suppkey
                        and s_nationkey = n_nationkey
                        and n_regionkey = r_regionkey
                        and r_name = 'EUROPE'
        )
order by
        s_acctbal desc,
        n_name,
        s_name,
        p_partkey
FETCH FIRST  100  rows ONLY
OPTIMIZE FOR  100  ROWS
;

Summary of Results
==================
                Elapsed             Agent CPU         Rows      Rows
Statement #     Time (s)            Time (s)          Fetched   Printed
 1                       7 , 540        Not Collected        100         100 
 2                       0 , 401        Not Collected        100         100 
 3                       0 , 400        Not Collected        100         100