8.1 fp 5 64 bit aix5.2, 50 dbps
moving to 8.1 fp 10 64 bit on aix 5.2, 70 dbps

Looking for a way to select 1000 rows per dbp for an archive/purge application which will then go across 10+ tables and purge data.

I am trying the following and getting the error shown

select sale_sys_id from xyz.sale where sale_dt < date('2006-03-01') - 70 months group by nodenumber(sale_sys_id) having count(nodenumber(sale_sys_id)) <=1000

SQL0119N An expression starting with " SALE_SYS_ID" specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified. SQLSTATE=42803

Any thoughts and recommendations highly appreciated.

Not sure if this is what you want, but it is the standard syntax for a group by with count:

select nodenumber(sale_sys_id), count(*) from xyz.sale where sale_dt < date('2006-03-01') - 70 months group by nodenumber(sale_sys_id) having count(*) <=1000

I appreciate the response. I'm really looking to select 1000 (or n) sys_id's per dbp into a staging table and then use the staging table to colocated delete from 10+ base tables.

Also, I'd like to make the number of partitions transparent and jsut make it n rows per dbp across the board regardless of the number of dbps.

If you are selecting say, just 1000 records or even a few thousands, then the optimizer should be doing a good job by sending these ids (a small data transfer) and execute the delete without moving large tables ...

If you want to get 1000 rows per dbp, the option may be to use the RANK OLAP Function and partition by the nodenumber ..

select * from
(select c1,c2,rownum() over (partition by nodenumber(id)) as r from xyz ) as temp where r<=1000

HTH

Sathyaram

This is the SQL I ran

select * from (select sale_sys_id, rownum() over (partition by nodenumber(sale_sys_id))as r from xyz.sale where sale_dt < date('2006-03-01') - 70 months ) as temp where r<=100;

SQL0440N No authorized routine named "ROWNUM" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Did you mean to use the row_number function?

ok. using row_number() worked. This is wonderful! Thank you very much.

I had actually meant RANK ... But if your sale_sys_id is unique, then RANK or ROW_NUMBER should not make any difference

Sathyaram

It is unique. But I'll try rank is well. Thanks again.

What does "dbp" mean? I searched the on-line DB2 manual, but only found it in dbp[assword].

dbp : database partition
dbpg : database partition group

The select works fine as a select. However, when embedded as a subselect of an insert statement, it does not work. Not quite sure how to get around it.