Extraordinary problem with max() on AIX DB2 EEE v6.1.0.50 
Author Message
 Extraordinary problem with max() on AIX DB2 EEE v6.1.0.50

Hi all,

I got an extraordinary problem with DB2 6.1.0.50 on AIX :

I run a :
select c1, c2, c3, c4, max(c5) from t1, ..., t10 where ... group by c1,
c2, c3, c4.
c5 is a NULLable DOUBLE. No NULL value stored. No intermediate function,
simple joins in the where clause.

Result : 0 rows (event monitor shows no row read)

I replace max(c5) by max(value(c5,0)) : 2262 rows
I replace max(c5) by count(c5) : 2262 rows
I replace max(c5) by min(c5), max(c5) : 0 rows
I replace max(c5) by count(c5), max(c5) : 2262 rows
I replace max(c5) by max(c5)+0 : 2262 rows
I replace nothing : 2262 rows !
(Event monitor shows about 300000 lines read/written)

Data in the t1 to t10 tables has not changed since.

Did someone got this behavior ?

/Rgis



Sun, 15 Aug 2004 19:16:44 GMT
 Extraordinary problem with max() on AIX DB2 EEE v6.1.0.50

Quote:

> Hi all,

> I got an extraordinary problem with DB2 6.1.0.50 on AIX :

> I run a :
> select c1, c2, c3, c4, max(c5) from t1, ..., t10 where ... group by c1,
> c2, c3, c4.
> c5 is a NULLable DOUBLE. No NULL value stored. No intermediate function,

> simple joins in the where clause.

> Result : 0 rows (event monitor shows no row read)

> I replace max(c5) by max(value(c5,0)) : 2262 rows
> I replace max(c5) by count(c5) : 2262 rows
> I replace max(c5) by min(c5), max(c5) : 0 rows
> I replace max(c5) by count(c5), max(c5) : 2262 rows
> I replace max(c5) by max(c5)+0 : 2262 rows
> I replace nothing : 2262 rows !
> (Event monitor shows about 300000 lines read/written)

> Data in the t1 to t10 tables has not changed since.

> Did someone got this behavior ?

I did not experience this, but it sounds very odd. What happens if you
change the query to:

select c1, c2, c3, c4, max(c5) from t1, ..., t10 where ...and c5 is
not null group by c1, c2, c3, c4

?

/Lennart



Mon, 16 Aug 2004 04:02:32 GMT
 Extraordinary problem with max() on AIX DB2 EEE v6.1.0.50
I think you can contact IBM service.
BTW, What's result(read/written) for the same query from table snapshot?
Quote:

> Hi all,

> I got an extraordinary problem with DB2 6.1.0.50 on AIX :

> I run a :
> select c1, c2, c3, c4, max(c5) from t1, ..., t10 where ... group by c1,
> c2, c3, c4.
> c5 is a NULLable DOUBLE. No NULL value stored. No intermediate function,
> simple joins in the where clause.

> Result : 0 rows (event monitor shows no row read)

> I replace max(c5) by max(value(c5,0)) : 2262 rows
> I replace max(c5) by count(c5) : 2262 rows
> I replace max(c5) by min(c5), max(c5) : 0 rows
> I replace max(c5) by count(c5), max(c5) : 2262 rows
> I replace max(c5) by max(c5)+0 : 2262 rows
> I replace nothing : 2262 rows !
> (Event monitor shows about 300000 lines read/written)

> Data in the t1 to t10 tables has not changed since.

> Did someone got this behavior ?

> /Rgis



Mon, 16 Aug 2004 11:41:40 GMT
 Extraordinary problem with max() on AIX DB2 EEE v6.1.0.50
First of all, do you get the right answer from the db?

Secundo, if i do get snapshot for all before and afther this, i get no
difference on rows read stuff.

DYNEXPLN -d DSAM -u DSAMA DSAMA -g -q
SELECT TS.TBSPACEID, MAX(TABLEID+0)
FROM SYSCAT.TABLES TB, SYSCAT.TABLESPACES TS
WHERE TS.TBSPACEID=TB.TBSPACEID
GROUP BY TS.TBSPACEID
FOR READ ONLY

A MAX() using index only access may not actually read rows. (using the
index)

I don't understand either why you would get 0 in one case and x in the
other.
Maybe the optimizer choose another access plan at run time?

PM



Mon, 16 Aug 2004 23:23:51 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. DB2 EEE v7.1 AIX: force application problem.

2. DB2 UDB 5.0 EEE on AIX 4.2.1 Snapshot Monitor Problem

3. db2 v6.1 EEE in datawarehouse environment

4. DB2 EEE / Linux cluster, DB2 instance creation problem...

5. v6.50, v2000, update and COLLATE error and SQLSERVERAGENT

6. Error -50 in FileMaker Web Companion v6

7. Error DBI1517E - DB2 v6.1 admin client on AIX v5.1

8. DB2 V6 and AIX 4.3.3

9. DB2 for AIX V6.1 Evaluation CD

10. Problem using DBD::DB2 with DB2 V6 for Linux

11. SQL 7.0b3 on Dual Pii400 / 50% load max

12. max 50 FM databases on one machine


 
Powered by phpBB® Forum Software