Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: declare temp tables

    When a temp table is declared with not logging, the dml will not be logged in the transaction logs. However, the creation and dropping of the temp table will. This sound like the declare clause begins a transaction. My question is will a commit end the transaction or does it have to be a drop table statement? I know ending the connection or application will also end the transaction and drop the temp table.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Temp tables stay around until either they are explicitly dropped (DROP TABLE SESSION.x) or the connection is terminated.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Transactions are always ended by a COMMIT or ROLLBACK - nothing else. Both operations can occur implicitly (auto-commit or terminating a connection, which triggers an implicit ROLLBACK).

    And as Andy said, the existence of a temp table is tied to the connection (not the transaction).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2004
    Posts
    190
    I have two scenarios below. Which one(s) will caused a transaction full issue.

    1. User runs an app which declares a temp table, but does not do a commit or drop table in its code. The user leaves the app open over night. There is enough activity from other apps to fill all the transaction logs. Will this result in a transaction full issue.

    2. User runs an app which declares a temp table, but does a commit but not a drop table in its code before returning the result set to the user. The user leaves the app open over night. There is enough activity from other apps to fill all the transaction logs. Will this result in a transaction full issue.


    Thanks

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Filling up the transaction log is not dependent on whether you use temp tables or not. You will fill up the log by having enough open transactions performing work to fill them up. This can occur with just one transaction if it is large enough. Transactions left open for long periods of time will definitely lead to filling up the transaction log.

    Andy

Posting Permissions

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