09-07-15, 05:04 #1Registered User
- 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 -
09-07-15, 05:16 #2Registered User
Provided Answers: 11
- Join Date
- Apr 2006
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
09-07-15, 18:56 #3Registered User
Provided Answers: 6
- Join Date
- May 2012
- Canberra, Australia
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 20:10.Andy
"All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you cant get them together again, there must be a reason. By all means, do not use hammer. IBM maintenance manual, 1975 "