Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    343

    Unanswered: 1000 rows per dbp

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    Last edited by Marcus_A; 04-17-06 at 19:15.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2003
    Posts
    343
    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?

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    ok. using row_number() worked. This is wonderful! Thank you very much.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I had actually meant RANK ... But if your sale_sys_id is unique, then RANK or ROW_NUMBER should not make any difference

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Mar 2003
    Posts
    343
    It is unique. But I'll try rank is well. Thanks again.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What does "dbp" mean? I searched the on-line DB2 manual, but only found it in dbp[assword].
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Mar 2003
    Posts
    343
    dbp : database partition
    dbpg : database partition group

  11. #11
    Join Date
    Mar 2003
    Posts
    343
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •