Hello Gurus,

Quick question - my application is DB2 'partitioning-aware'. It initiates 2 application partitions to connect to each of the 2 physical database partitions. I can see messages in the application log indicating each of the application connections connected to individual database partitions.

This particular ETL routine is performing a join between 2 tables A1 and A2 - A1 is hash partitioned on AK1 and A2 is hashed on AK2.

When I look at the queries generated by the application partitions, I see -

Application Partition 1:
Select * from A1 , A2 where A1.AK1 = A2.AK1 and Nodenumber(A1.AK1) = 1

Application Partition 2:
Select * from A1 , A2 where A1.AK1 = A2.AK1 and Nodenumber(A1.AK1) = 2


While I can confirm if there will be data lost due to the above partitioning (application) by reviewing the data, the data load is in progress, and is going to take quite a while,

I was wondering if you guys could tell by just reviewing the description above that my application would miss data because of how it is connecting to the data nodes and performing a non-collocated join.

Thanks in advance for your help!

- G

too complicated - too uninteresting????

You will miss data, not because of which partition the applicaiton is connected to, but because of Nodenumber specification in the query. I have no idea why you would do that.

In all probability (without me being able to see the entire db design) both tables should be hashed on column AK2, since it appears to be the column that both table have in common for their PK's.

Always hashing on the full PK's is usually a mistake if done automatically without thinking about it. You should have as many tables as possible (that might participate in joins) be hashed on the same column.

Marcus,

Thanks for the review and response! Although, I am seeing I got all my records back. I guess my question is -

if the application says its logically connected to node number 2 and also applies nodenumber(col) = 2 predicate, does that prevent it from retrieving non collocated records for another table?

The output I am seeing from this test run seems to suggest the database actually pulled other tables' non collocated records and included in the result set of the respective application partition.

- G