Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Unanswered: Database snapshot

    DB2 UDB V7.2 FP 9 on Windows

    Hi: Recently I took a snapshot of database and following is the part of output:

    Update/Insert/Delete statements executed = 0
    DDL statements executed = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 24
    Rows read = 14565236
    Rows written = 5813427

    I am stupmed by the fact that no. of Update/Insert/Delete statements executed are 0 while Rows written= 5813427, how coud that be? Or I am interpreting it wrong? Please clarify...

    dollar

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Database snapshot

    An extract from DB2 manuals, explaining what 'Rows Written' means

    For application-connections and statements, this element includes the number of rows inserted, updated, and deleted in <Bold>temporary tables.</Bold>

    Here is a demonstation of what it could be , indicating that you need to tune your SQL queries/create indexes- clustered or otherwise/avoid sorts etc. I have used a meaningless and 'poorly' written query

    (This is a sample table on my home linux machine, with V8, with RUNSTATS done )

    Database Connection Information

    Database server = DB2/LINUX 8.1.4
    SQL authorization ID = DB2INST2
    Local database alias = SAMPLE

    $ db2 "select tabname,card from syscat.tables where tabname='Q2_2003'"

    TABNAME
    CARD
    -------------------------------------------------------------------------------
    ------------------------------------------------ --------------------
    Q2_2003
    178000

    1 record(s) selected.

    $ db2 get snapshot for applications on sample | grep -i rows
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 1
    Rows read = 17
    Rows written = 0
    Rows read = 0
    Rows written = 0
    Rows deleted = 0
    Rows updated = 0
    Rows inserted = 0
    Rows fetched = 0
    $ db2 "with temp as (select * from q2_2003 order by 1,2,3) select min(when_created) from temp "

    1
    --------------------------
    2003-03-01-23.16.37.850865

    1 record(s) selected.

    $ db2 get snapshot for applications on sample | grep -i rows
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 2
    Rows read = 356025
    Rows written = 178000
    Rows read = 0
    Rows written = 0
    Rows deleted = 0
    Rows updated = 0
    Rows inserted = 0
    Rows fetched = 0


    And a portion of the table snapshot:

    Table List
    Table Schema = <7><DB2INST2>
    Table Name = TEMP (00001,00002)
    Table Type = Dropped
    Rows Read = 178000
    Rows Written = 178000
    Overflows = 0
    Page Reorgs = 0

    Table Schema = DB2INST2
    Table Name = Q2_2003
    Table Type = User
    Rows Read = 178000
    Rows Written = 0
    Overflows = 0
    Page Reorgs = 0



    Cheers
    Sathyaram
    Last edited by sathyaram_s; 02-04-04 at 20:16.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: Database snapshot

    Big thanks man.

    dollar

    Originally posted by sathyaram_s
    An extract from DB2 manuals, explaining what 'Rows Written' means

    For application-connections and statements, this element includes the number of rows inserted, updated, and deleted in <Bold>temporary tables.</Bold>

    Here is a demonstation of what it could be , indicating that you need to tune your SQL queries/create indexes- clustered or otherwise/avoid sorts etc. I have used a meaningless and 'poorly' written query

    (This is a sample table on my home linux machine, with V8, with RUNSTATS done )

    Database Connection Information

    Database server = DB2/LINUX 8.1.4
    SQL authorization ID = DB2INST2
    Local database alias = SAMPLE

    $ db2 "select tabname,card from syscat.tables where tabname='Q2_2003'"

    TABNAME
    CARD
    -------------------------------------------------------------------------------
    ------------------------------------------------ --------------------
    Q2_2003
    178000

    1 record(s) selected.

    $ db2 get snapshot for applications on sample | grep -i rows
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 1
    Rows read = 17
    Rows written = 0
    Rows read = 0
    Rows written = 0
    Rows deleted = 0
    Rows updated = 0
    Rows inserted = 0
    Rows fetched = 0
    $ db2 "with temp as (select * from q2_2003 order by 1,2,3) select min(when_created) from temp "

    1
    --------------------------
    2003-03-01-23.16.37.850865

    1 record(s) selected.

    $ db2 get snapshot for applications on sample | grep -i rows
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 2
    Rows read = 356025
    Rows written = 178000
    Rows read = 0
    Rows written = 0
    Rows deleted = 0
    Rows updated = 0
    Rows inserted = 0
    Rows fetched = 0


    And a portion of the table snapshot:

    Table List
    Table Schema = <7><DB2INST2>
    Table Name = TEMP (00001,00002)
    Table Type = Dropped
    Rows Read = 178000
    Rows Written = 178000
    Overflows = 0
    Page Reorgs = 0

    Table Schema = DB2INST2
    Table Name = Q2_2003
    Table Type = User
    Rows Read = 178000
    Rows Written = 0
    Overflows = 0
    Page Reorgs = 0



    Cheers
    Sathyaram

  4. #4
    Join Date
    Sep 2002
    Posts
    456
    Okay, one question just to clarify, it is a sum of no. of rows written to a table + no. of rows written to a temporary table?

    dollar

Posting Permissions

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