Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    76

    Unanswered: Runstats problem

    I have a script that runs nightly and does runstats on some select tables. It sporatically fails on some of the tables with the following error:

    SQL2310N The utility could not generate statistics. Error -911 was returned.


    The stats command was:

    runstats on table db2admin.s_srv_req and detailed indexes all shrlevel reference


    Has anyone seen this or know why it would work sometimes but not always. Sounds like contention but should the shrlevel take care of that?

  2. #2
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Runstats problem

    Hey Akratz

    here´s the transcription of the error message:

    SQL0911N The current transaction has been rolled back because of
    a deadlock or timeout. Reason code "<reason-code>".

    You´re trying to run statistics for a table that´s being locked in another transaction. If those are select only table, make sure that the Isolation Level of the application accessing those tables are NOT Repeatable Read, if they are, change them to Cursor Stability (Read Commited), or at least issue a commit after every Select Statement.

    HTH
    Fernando Ohana.

    Originally posted by akratz
    I have a script that runs nightly and does runstats on some select tables. It sporatically fails on some of the tables with the following error:

    SQL2310N The utility could not generate statistics. Error -911 was returned.


    The stats command was:

    runstats on table db2admin.s_srv_req and detailed indexes all shrlevel reference


    Has anyone seen this or know why it would work sometimes but not always. Sounds like contention but should the shrlevel take care of that?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since the Runstats utility only reads the user data (and updates the catalog) the contention may be because of another utility running which has exclusive access to the user data or the catalog.

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    Take snapshots for locks periodically while the runstats run as well as a list applications show detail - that will help you idnetify where the contention is.

    Not sure how the applications works and why the runstats would need a sharelevel of reference. If it works for you, try a sharelevel of change and see if it stops failing.

  5. #5
    Join Date
    Aug 2002
    Posts
    76
    Great! Thanks for pointing that out, I'm not sure why I was using REFERENCE , had just 'inherited' that when I started DBA and should have researched that, SHRLEVEL CHANGE has solved the issue.

    Thanks
    Al

Posting Permissions

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