Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    27

    Unanswered: db2 performance issue - large number of IOPS how to determine problem?

    DB2 database is on storage. we are using 9.7 Storage administrators reported that today database was doing 5000 IOPS (during past days average number was 300 IOPS). Those are disk operations from server to disk system. db2data is currently on 89%. Today we did not do any large upload. How is this possible what happned to cause this problem?

    In db2diag log only one ERROR is seen today:
    2014-02-21-12.07.26.669877+060 E91992479E1392 LEVEL: Error
    PID : 22867 TID : 1084623168 PROC : db2acd 0
    INSTANCE: ctginst1 NODE : 000
    FUNCTION: DB2 UDB, Health Monitor, HealthIndicator::update, probe:500
    MESSAGE : ADM10500E Health indicator "Database Automatic Storage Utilization"
    ("db.auto_storage_util") breached the "upper" alarm threshold of "90
    %" with value "94 %" on "database" "ctginst1.MAXDB71 ". Calculation:
    "((db.auto_storage_used/db.auto_storage_total)*100);" =
    "((30921187328 / 32821358592) * 100)" = "94 %". History (Timestamp,
    Value, Formula): " (02/21/2014 11:57:26.209607, 89, ((29312966656 /
    32821358592) * 100)), (02/21/2014 11:47:26.676942, 89, ((29311541248
    / 32821358592) * 100)), (02/21/2014 11:37:26.426393, 89,
    ((29324247040 / 32821358592) * 100)), (02/21/2014 11:27:26.984178, 90,
    ((29492969472 / 32821358592) * 100)), (02/21/2014 11:17:26.520087, 90,
    ((29499133952 / 32821358592) * 100)), (02/21/2014 11:07:26.945333, 93,
    ((30552121344 / 32821358592) * 100)), (02/21/2014 10:57:26.657101, 92,
    ((30317146112 / 32821358592) * 100)), (02/21/2014 10:47:26.264625, 92,
    ((30147538944 / 32821358592) * 100)), (02/21/2014 10:37:26.876803, 91,
    ((29968994304 / 32821358592) * 100))"

    Any help is welcome. How to determine what happened? should we restart database to solve this?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    One bad query can do wonders, don't you think? What was the most recently changed process?

  3. #3
    Join Date
    May 2011
    Posts
    27
    Hi,
    thank you for response.
    I am the administrator and few other users but no additional queries were done.
    Can I somehow determine the query which caused the problem?
    db2top is useful only for current situation.
    I am interested in the past.
    Can I see it somehow? I know I can turn the snapshot on but that is also for future use.
    Is there some other easiest way to find long running queries?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sure. Let me google that for you.
    https://www.google.com/#q=db2+luw+ca...QL+statements+

Posting Permissions

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