Unanswered: Peformance issue on DB2 9.5 FP 10 Prod.
Looking for an advise.
We have env DB2 ESE 9.5 FP 10 [AIX 6] on both prod & test. Customer facing performance problem on their batch job after 16.6.15. They are running important batch after business hours, it use to complete below 4 hours however nowadays it takes 8 hours to complete. Whereas the prod db was refreshed on test and tested the batch job with same schedule and data, it only takes 2 and half hour to complete. It just running 3 times faster than prod.
Production server is more powerful than test in terms of RAM,etc..We use to take tablespace partial backup during this batch on prod, since it was running long back..
3 instances on prod, db2inst1 - 6 DB,db2inst2 - 2 DB and db2inst3 - 2 DB. Recently, i mean after 27-6-15 there are 4 new dbs has been created on db2inst1 [2 + 4 = 6]. STMM was enabled in all databases in respective instances. Prod have 48 GB RAM but Test have 24 RAM.
Only one instance on test, but 15 databases [db2inst1 - 15 DB].
Customer concern is why job takes more time than test since prod have more capacity and capability? They need 90% of OS or db2 resource should assigned to only one database [ Critical DB] remaining 10% of resource can be assign to others instances and dbs.
What could be the reason here jobs takes 3 time slower than test suddenly. How to optimize the prod system back. Can anyone able to help. Thanks in advance.
Your customer should request (or pay for) a competent experienced performance troubleshooter to examine the prod system.
If you don't have the right mix of skills and experience, then posting on internet general questions that contain few facts just becomes a lottery and wastes everyone's time.
They can either undo the change(s) that increased the batch-time, or forward-fix to ensure the batch is optimized as much as it can be and ensure CPU/disk/RAM resource-allocation guarantees the batch completes within the desired window.