Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Unanswered: alternates to Cusrsors to handle huge volume of data

    Hi all,

    I'm new to DB2 and I my proc has to process some 3 million records. Can I use cursors for this. Will cursors handle such large volumes of data efficiently or will it hang coz it need to store all 3 million rec in temp memory. Do we have any other alternative in DB2 other than using cursors to process such huge data? Pls advise.


    Thanks,
    Kayal.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all rows will not be loaded into memory
    result sets will be created in temp. tablespace
    I don't see how to handle this in other way..
    it will take some time if the rows are all handled one by one ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    8
    Hi
    Thanks for the reply. In my cursor I dont have any condition to get the result set, so entire table has to be handled by the cursor. Is there any spl keywords in cursors to fetch the result set every one lakh record and process and again fetch 1 lakh into memory?

    Thanks,
    Kayal

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if there is no predicate it will fetch from base table i presume and lock according the isolation level...
    db2 will always bufferpools to transit the data
    have you already run this process ?
    is there a specific problem ?
    be as precise as possible and add some additional info if so..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What do you want to do with those 3 mio rows? Maybe you can push the processing into the database system and just retrieve the final result (or something close to it).

    However, if you want to do the processing in your application yourself, this has a high chance to be simply slow.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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