Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015

    Unanswered: Performance with db2 stored proc with cursor

    I'm working in a client and I don't have a lot experience qwith database.

    We have a stored proc with a cursor , that have a simple logic , that can treat something like 40.000 an records an hour.
    But this performance is very low because we have to treat in production 500.000 records.
    Then , I don't know if the db2 server is "under" sizing or if this volume, 40.000/hour is normal for a stored proc with cursor.
    They gave me the following data's

    Partition Number : 22
    Type : Shared-SMT-4
    Mode : Uncapped
    Entitled Capacity : 1.00
    Partition Group-ID : 32790
    Shared Pool ID : 1
    Online Virtual CPUs : 2
    Maximum Virtual CPUs : 4
    Minimum Virtual CPUs : 1
    Online Memory : 16384 MB

    Thank you -

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    this information only will not lead to any info about your problem (db2 level - platform - config - sp code ...)
    what is the sp doing : select / update ??
    concurrency ? many conditions can change the behavior
    have you run a testcase to measure the time ?
    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

  3. #3
    Join Date
    May 2012
    Canberra, Australia
    Provided Answers: 6
    As Guy said, the info provided is not really relevant. Did you consult the DBA responsible for the server?
    How about providing the structure of the related tables, details of indexes and the proc code. And the version and fixpack level of the db2 server. Is locktimeout set in the db cfg? If you don't have this info then you need to get it from the DBA
    Last edited by tafster; 09-07-15 at 19:10.

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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