184 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
Sample database
The database table used for the tests described in this redbook was ITEM_FACT. Its layout is
shown in Table B-1. The table contained 6,001,215 rows and occupied approximately 1.6 GB.
Indexes were created and dropped as needed to perform specific tests. A modified copy of
the table, ITEM_FACT_SHORT, was created with some VARCHAR columns. This was used
specifically to test performance of queries referencing variable length character columns.
The table sizes are:
򐂰 Table PART_DIM has 200,000 records.
򐂰 Table SUPPLIER_DIM has 10,000 records.
򐂰 Table CUST_DIM has 150,000 records.
򐂰 Table TIME_DIM has 1,450 records.
򐂰 Table ITEM_FACT has 6,001,215 records.
Table B-1 ITEM_FACT: Database table layout
Column name Type Length
ORDERKEY DECIMAL 16,0
PARTKEY INTEGER
SUPPKEY INTEGER
LINENUMBER INTEGER
QUANTITY DECIMAL 15,2
EXTENDED PRICE DECIMAL 15,2
DISCOUNT DECIMAL 15,2
TAX DECIMAL 15,2
RETURNFLAG CHARACTER 1
LINESTATUS CHARACTER 1
SHIPDATE DATE
COMMITDATE DATE
RECEIPTDATE DATE
SHIPMODE CHARACTER 10
SUPPLYCOST DECIMAL 15,2
CUSTKEY INTEGER
ORDERDATE DATE
ORDERPRIORITY CHARACTER 15
SHIPPRIORITY INTEGER
REVENUE_WO_TAX DECIMAL 15,2
REVENUE_W_TAX DECIMAL 15,2
PROFIT_WO_TAX DECIMAL 15,2
PROFIT_W-TAX DECIMAL 15,2

Get Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.