Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Unanswered: System Temporary Tables

    I was seeing some not insignificant write activity on our database, even though it's mostly used for queries, not updates... (about a 500-1 ratio)

    I figured I'd investigate, and found that db2 is creating temporary tables for some very simple queries. Some of them return only a single row, require no sorting, and explain shows a straightforward low-cost index access.

    From the Table snapshot, I see some really small tables that blinked into existence:

    Table Schema = <56><DBUSER >
    Table Name = TEMP (00004,00007)
    Table Type = Dropped
    Rows Read = 1
    Rows Written = 1
    Overflows = 0
    Page Reorgs = 0

    I thought temporary tables like this were only created when there were sorts or joins being done, does anyone have any insight on this? The docs are somewhat lacking.

    (I did read that temporary tables require a write to physical disk when DMS is being created, even if there is space in the bufferpool... but not SMS! Interesting)
    --
    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I should add this is DB2 UDB V8.1 / Linux.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think "WHERE something IN (something, something, ..)" may be creating temp.tables.

  4. #4
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    IF you are doing updates, inserts or deletes: triggers may also store old & new rows in a TEMP tables.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by hurmavi
    IF you are doing updates, inserts or deletes: triggers may also store old & new rows in a TEMP tables.
    We don't really use triggers, and I'm seeing these on SELECT statements.

    (No IN list, either).

    Here's an example:
    Number of executions = 2
    Number of compilations = 1
    Worst preparation time (ms) = 4
    Best preparation time (ms) = 4
    Internal rows deleted = 0
    Internal rows inserted = 0
    Rows read = 6
    Internal rows updated = 0
    Rows written = 4
    Statement sorts = 0
    Total execution time (sec.ms) = 0.000000
    Total user cpu time (sec.ms) = 0.000000
    Total system cpu time (sec.ms) = 0.000000
    Statement text = SELECT PRODUCT_NUMBER, PRODUCT_NAME FROM APP.PRODUCT WHERE PRODUCT_ID = ?

    This returns one row... (product_id is the pk)... 4 "rows written" caught my eye.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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