By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

TPC-H Query Results - 1GB DB

P: n/a
Hello,

I am trying to validate all the 22-Queries in the TPC-H Benchmark on
DB2 UDB 8.2, I am having a little bit of trouble with Query #8

The result should be:

YEAR MKT_SHARE
1995 .03
1996 .04

But when I run the query, I got the following result:

YEAR MKT_SHARE
1995 .0
1996 .0

I am not really sure what could be wrong, the query syntax is %100
correct. I would really appreciate any help.

Query #8 :

select
o_year,
sum(case
when nation = 'BRAZIL' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date('1995-01-01') and date('1996-12-31')
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year;

Regards,
Salem

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"UnixSlaxer" <un********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hello,

I am trying to validate all the 22-Queries in the TPC-H Benchmark on
DB2 UDB 8.2, I am having a little bit of trouble with Query #8

The result should be:

YEAR MKT_SHARE
1995 .03
1996 .04

But when I run the query, I got the following result:

YEAR MKT_SHARE
1995 .0
1996 .0

I am not really sure what could be wrong, the query syntax is %100
correct. I would really appreciate any help.


This is what IBM used in their benchmark (published on the TPC.org site). If
it is not right, then you probably have a data problem as a result of the
compile process of the datagen program:

select
o_year,
sum(case
when nation = 'BRAZIL' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
tpcd.part,
tpcd.supplier,
tpcd.lineitem,
tpcd.orders,
tpcd.customer,
tpcd.nation n1,
tpcd.nation n2,
tpcd.region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date('1995-01-01') and
date ('1996-12-31')
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year

O_YEAR MKT_SHARE
-----------------------------------
1995 0.034
1996 0.041

Number of rows retrieved is: 2
Nov 12 '05 #2

P: n/a
"UnixSlaxer" <un********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hello,

I am trying to validate all the 22-Queries in the TPC-H Benchmark on
DB2 UDB 8.2,

One more thing. Some of the TPC-H queries will run much faster with query
optimization level set to 7 (default is 5).
Nov 12 '05 #3

P: n/a
Hello,

I Also tried the queries published at TPC.org for IBM DB2, but I had
the same result:

1995 .0
1996 .0

All the other queries are running fine and with the expected results,
except for Query 8, it seems to me it has something to do with
fractions? decimal points? I really don't know.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.