Professional Web Applications Themes

Retrieving first N records from database - IBM DB2

Hi Experts, I am using DB2 as backend for my web application. Can any help me to retrieve the rows in batches. We have some 10000+ records in a table. We cannot afford to retrieve all those records once and keep them in cache. Can I write a query to retrieve the first 100 records first time and next 100 records second time and so on. Thanks in advance, Praveen....

  1. #1

    Default Retrieving first N records from database

    Hi Experts,
    I am using DB2 as backend for my web application.
    Can any help me to retrieve the rows in batches.
    We have some 10000+ records in a table. We cannot afford to retrieve
    all those records once and keep them in cache.
    Can I write a query to retrieve the first 100 records first time and
    next 100 records second time and so on.

    Thanks in advance,
    Praveen.
    Praveen Guest

  2. #2

    Default Re: Retrieving first N records from database

    Praveen,

    what you are asking for is the normal behavior of DB2: it retrieves in
    batches as you go, e.g. with each nth Fetch DB2 will ask the backend for a
    new batch, depending on your memory and batching method.
    Good enough?

    Regards Rolf
    Rolf Guest

  3. #3

    Default Re: Retrieving first N records from database

    This may help you

    To select a set of rows from within the result set, use the rownum
    function to number the rows, and then select from within the result set. For
    example, to select the employees with the 90th through 95th highest
    salaries:
    select * from
    (select EMPNO, FIRSTNME, LASTNAME, SALARY , rownumber() over
    (order by salary desc)
    AS rn FROM employee)
    AS tr WHERE rn between 90 and 95


    You just need to extrapolate this sql statement to suit your needs.

    fm (Praveen) wrote in message news:<google.com>... 
    anurag Guest

  4. #4

    Default Re: Retrieving first N records from database

    Thanks Rolf for explaining about how fetching of records is done.
    Thanks Anurag for that SQL query. I was'nt aware that such kind of a
    function existed in DB2...Are there any more such functions?..If yes,
    could you provide me the same or atleast a link.

    Praveen.

    com (anurag) wrote in message news:<google.com>... [/ref]
    Praveen Guest

  5. #5

    Default Re: Retrieving first N records from database

    Search on olap functions at this site:
    http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

    Praveen wrote:
     [/ref][/ref]

    Blair Guest

Similar Threads

  1. MySQL Database not retrieving the full database
    By geetha.veeraiah@gmail.com in forum MySQL
    Replies: 4
    Last Post: July 21st, 09:34 PM
  2. Retrieving images to flash from SQL database
    By JPCS in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: May 2nd, 07:43 AM
  3. Retrieving info from database
    By Geoff Wickens in forum ASP Database
    Replies: 1
    Last Post: August 30th, 10:04 AM
  4. Retrieving (Data + Fieldnames) from database to array?
    By Josep in forum PHP Development
    Replies: 3
    Last Post: July 15th, 09:17 PM
  5. Database record retrieving
    By Atrax in forum ASP
    Replies: 0
    Last Post: June 30th, 02:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139