Getting group of data for each member of primary key

[Arun Srinivasan]

I have a table that is based on a client_cd,
client_cd, client_nm ..........
It has 1.7 billion rows with varying cardinality for each client.

All I want to do is to set up a sample table having exactly 100 rows
for each client_cd s. I have tried recursion but have problems with
sets (cannot use fetch first 100 rows only and union all inside a
CTE).
Has anyone attempted this? if yes, please help.

[Dave Hughes]

Re: Getting group of data for each member of primary key

Arun Srinivasan wrote:

I have a table that is based on a client_cd,
client_cd, client_nm ..........
It has 1.7 billion rows with varying cardinality for each client.
>
All I want to do is to set up a sample table having exactly 100 rows
for each client_cd s. I have tried recursion but have problems with
sets (cannot use fetch first 100 rows only and union all inside a
CTE).
Has anyone attempted this? if yes, please help.

Hmm, you /can/ use FETCH FIRST and UNION ALL within CTEs as the former
is a clause of subselect and the latter of full-select on DB2 for LUW
(and DB2 9 for z/OS; although in DB2 8 for z/OS you couldn't as FETCH
FIRST is a clause of select-statement there).

Still, I think there's a simpler way with OLAP functions. Assuming your
table is called BIGTABLE ...

WITH T1 AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CLIENT_CD) AS ROWNUM,
T.*
FROM BIGTABLE T
)
SELECT * FROM T1
WHERE ROWNUM <= 100;


Cheers,

Dave.