parallel select data from a db2 table

0
votes

I am trying to fetch equal number of records from 4 different clients from the same table in db2 database. I need to ensure that each sql fetches distinct set of rows with equal volume. the table has a 18 digit number as a primary key generated randomly. how do I ensure that the record getting select in first sql is not present in any of the other sqls.

3
sqldb2
You want to select disjoint sets. Are you limited to using only the primary-key as predicate? Why not use other columns in predicates? Is the table in Db2 partitioned-by-range, or distributed-by-hash, or both of those? Is the select a One-Time action, or must it run continually, each time selecting new data? Are rows being added, updated, deleted to the table continually? - mao

3 Answers

3
votes

This will affect performance but you can use modulus:

SELECT ...
FROM table
WHERE MOD(pkey, 4) = 1 --or 2, 3, 0

You also said something about having equal result set sizes, you can use FETCH FIRST x ROWS ONLY to limit to the same size.

There may be other options depending on the "why" of the question - OLAP functions, hidden columns, functional indexes, views, and other tools could possibly be better answers. MOD is a simple one though.

0
votes

May be you can do it in only query like this:

select * from (
select f1.*, rownumber() over(partition by clientid order by f1.pkey, f1.clientid) rang
from yourtable f1
where f1.clientid in (4, 8, 9, 5) --> put your client id here
) f2 
where f2.rang<5 --> 5 if you want only 5 rows by client
-1
votes

Db2 Federation could be the answer to your question. This would allow a single SQL statement running against four source databases which have been defined in one database. A UNION wold ensure the deduplication of data.

Details can be found here