Hi Serge,
Let me explain my situation.
I am working on OLAP project and my datamart is located on Microsoft SQL
Server.
I have moved this datamart to DB2 (UNIX) and Iwas impressed about
performance for ETL which loads data to DB2.
But  my second step was to LOAD my OLAP database which is for DB2 means:

SELECT a,b,c,d,.....FROM View

- 60 millions rows, about 15 GB of data , takes forever!
( this process takes about 1hour 20 min with MS SQL Server.)

View =  SELECT  A (55millions row table)  JOIN  B (3 millions row tabel)  ON
A.B_PK_CLUSTER = B.B_PK_CLUSTER
                   JOIN C (1 million rrow table) ON A.C_PK_CLUSTER =
C.C_PK_CLUSTER
                   LEFT JOIN D (small)  ON PK_CLUSTER.......

Plan shows that DB2  TABLSCAN table A --> SORT it by B_PK_CLUSTER field and
HASH it to TABLESCANED table B!!
even when I add FETCH FIRST 1 ROW ONLY - takes forever!!

I changed my View like this:
SELECT a,b,c,d,.....FROM tableA  ( just fact table)
and I loaded my OLAP in 15 min !!  ( 60 millions rows, about 15 GB of
data ) - Fantastic.
What does it mean? I think it means I have no problem with OLAP,
network,drivers, etc.

So I tryed this ( only 2 tables) :
SELECT a,b,c, ......
       FROM  A (55millions row table)  JOIN  B (3 millions row tabel)  ON
A.B_PK_CLUSTER = B.B_PK_CLUSTER

Plan is still ugly - 2 TABLESCANS - very slow. I think you may create this
situation very easy.

IF I add WHERE clause to narrow table A to let say 1 million rows - plan is
good, DB2 accept indexes, works fast.
What does it mean? I think it means I do have indexes and they are good.

After RANSTATS on just B_PK_CLUSTER  field on table A .
My next SELECT selects ONLY fields from table A and ONLY PK from table B.:

SELECT A.f1, A.f2,  A.B_PK_CLUSTER , B.B_PK_CLUSTER
FROM  A (55millions row table)  JOIN  B (3 millions row tabel)  ON
A.B_PK_CLUSTER = B.B_PK_CLUSTER

Plan is perfect, DB2 uses indexes , very fast.

But If I add to SELECT above some another  fields from table B - plan again
becomes TABLESCAN type, very slow.

I tryed use Functions instead of JOINs -- it improves situation but still
very slow - about 6 hour to load my cube.
I have to know what to expect when I will need to load 100 million row, 200,
300.
Limits, thresholds, buffer sizes, pools, memory, CPUs, etc...$$$$?
How to hint to DB2 to use this particular index or that type of Join?
Please advice.
Thanks,
Timur.