Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2016
    Posts
    29

    Unanswered: sysibmadm.db_history querying slow on 10.5

    Hello DB2 DBA's,

    In one of our script there is a db query to get last archive log as below .

    db2 -x "select location from sysibmadm.db_history where eid = (select max(eid) from sysibmadm.db_history where operation = 'X' and operationtype = '1')"

    And in only one of the environment after migrating from 9.7 to 10.5 this query was running dam slow, the result of the query in 12 min, Which should be in seconds.

    I thought its prune issue and checked but it is not.

    what might be ?

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Check db2diag entries. Locking of the db-history file may be the cause. Which fixpack of V10.5 is present on this instance? Is it purescale or DPF ?

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    What are the sizes of the db2rhist.asc files on both your "quick" and "slow" systems?
    Have you compared the io characteristics of the file systems containing this file on both systems?

    Try this:
    db2 -x "select location from sysibmadm.db_history where operation = 'X' and operationtype = '1' order by eid desc fetch first 1 row only"
    Regards,
    Mark.

  4. #4
    Join Date
    Apr 2016
    Posts
    29
    Hello Mark,

    on Quick : 88.77 MB
    on Slow : 61.34 MB

    Also the size of the database ( Quick is bigger than Slow)
    And i have tried your query but still no luck.

    I suspect, did i miss anything while upgrade from 9.7 to 10.5. Out of 20 environments only 2 environments has this issue.

  5. #5
    Join Date
    Apr 2016
    Posts
    29
    Thanks for your help/information.

    Finally i found a solution for my problem.

    1) before DB upgrade i have set diaglevel to 4 and i forget it to move to 3.
    3) And in one of the environment REC_HIS_RETENTN is 360 i have changed it to 20 ( but as i dont have downtime. I did not restarted db, so I prune the history file)

    Not sure exactly which one is the solution but my issue got resolved. I suspect this issue is because of diaglevel 4 .

  6. #6
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    prune of history file should have resolved the issue,

    bigger history file has issues on backup as well, V 10.5 there is apar for same,

Tags for this Thread

Posting Permissions

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