Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Finland
    Posts
    22

    Unanswered: What exactly is a database snapshot?

    Ok, this is most likely a really stupid guestion... but I have been trying to search all documents and internet about information what the data returned by a snapshot (database or any other) actually means.

    Everywhere it is said that a snapshot gives back the data at the moment when the snapshot it taken. Ok nice, but what does that exacly mean then? Does it mean that it returns only the data related to actions happening on a database on the exact same moment the snapshot is taken... I guess not since then the numbers would be close to zero almost every time.

    In many places I have seen a mention that the data returned by snapshot would be a running sum kind of data... so it is collected to some hidden place and the number there will be given back to me when I ask. Ok that makes sense. But in that case I would expect that these number would grow and grow... now however I can get varying values when I run snapshots weekly. So it can not be this also or at least if it is then something is resetting the counter at some point... but at least I don't do that intentionally.

    We are running db2 udb 8.1.15 on windows 2003. We take off-line backups every friday and I take snapshots every sunday... but now please someone tell me... what does it actually mean when database snapshot last sunday says there is 5126 direct reads or lock_wait_time is 827... SINCE WHEN?

    It is nice to have much information, but it is less usufull if you have no idea how it is collected...

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mitait
    We take off-line backups every friday and I take snapshots every sunday... but now please someone tell me... what does it actually mean when database snapshot last sunday says there is 5126 direct reads or lock_wait_time is 827... SINCE WHEN?
    In your situation the cumulative counters would contain data from no earlier than Friday. The cumulative counters are reset when the database is deactivated, which is the case if you take an offline backup.

    You can see the time when the counters were reset on the database snapshot:
    Code:
    First database connect timestamp           = 07/11/2008 09:40:51.732151
    Last reset timestamp                       =
    Last backup timestamp                      = 07/13/2008 22:00:05.000000
    Snapshot timestamp                         = 07/14/2008 09:00:04.592647
    I guess it would be the latest of the two highlighted timestamps.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have all the right ideas about snapshots. The cumulative counters are from the time the database is activated and continues to increase until it is deactivated. Databases are activated either by having a connection to an inactivated DB or explicitly by the ACTIVATE DATABASE command. They are deactivated by either the last connection being disconnected or explicitly by the DEACTIVATE DATABASE command. When you take the offline backup, you have to do that to an inactive database. That is why your snapshot counters seem to reset.

    Andy

  4. #4
    Join Date
    Mar 2004
    Location
    Finland
    Posts
    22
    Thanks a lot guys! I was thinking it must be something like this, but knowing it is much more usefull. I guess I will move the snapshots to be taken prior to backups so that I get the action between the backups... that will make much more sense than just having the snapshot information from weekends only (when nobody is working). Thanks again.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not sure what useful information can be obtained from snapshots taken once a week. Even if you see 827 (or may be 827000) ms of lock wait time, you wouldn't know whether it all happened on Monday morning during peak user load or on Sunday afternoon at the time of the weekly batch run, so that information will be useless for diagnostic purposes.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2004
    Location
    Finland
    Posts
    22
    Mostly I was thinking to get some usage statistics and bufferpools ratios etc. But even so your point is very valid and I may need to think it a little more how to get the kind of information I want.

    The database is used for data warehousing. So the daytime use is the "real" use of the database as nighttime is the time for updating. So in weekly results these two get mixed and that does not exactly tell much then as you mentioned. Well maybe I should think about collecting information only during the daytime (or separately for day and night) and maybe for only some selected days...

    I agree that for example lock_wait_time does not tell much

Posting Permissions

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