Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: surprised by snapshot data

    This afternoon I detected a dimension table in the DWH not being updated during the night. The cause was a query which had been running for almost 48 hours (user used ctrl+alt+delete on the reporting tool) and which locked the table.

    (the query had read 11.6 billion records in the process)

    Before forcing it off I issued the snapshot command for the agentid. Surprisingly It gave the exact query that was running as well.
    I never noticed this before when issuing a snapshot. Am I getting old, or is this a special occurence?

    Still looking for ways to prevent these runaway queries too......

    DB2 UDB on NT 7.1 Workgroup Edition
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check out "Chapter 9. Using the Governor," in the Administration Guide: Performance Version 7

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    The dbm switch uow_mon_statement must be set to ON. That will give the sql statement.

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Wink

    Thanks cchattoraj/marcus,

    I had a look at the passage from performance guide as Marcus suggested. Looks like this aspect of DB2 is even more complicated than DB2's concurrency/locking scheme.
    Best to see it as an intellectual challenge though, it's a bit like the thermodynamics course I once set through at university,feeling very stupid I can tell you...........................
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Mar 2003
    Posts
    343
    I agree. Setting up the Governor is a pain in the neck. And then you have to answer on why the DBA picked on someone and killed their queries. But it does help stopping the runaway query. One thing to keep in mind if this is EEE is that the limits that are set in the gov cfg are per logical node - so if rowsel is 10000 that means 10000 rows per logical.

Posting Permissions

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