I have a situation where a developer's code does the following:

1. Declare temp table
2. Insert data into temp table (no commit).
3. Using common table expressions and lots of joins, a long running query is run.

Now the part I'm not sure about. Several hours into the long running query, I get a SQL0965C transaction full msg pointing to this process. This process is only running a select statement at this point. My question is would this process be pointed to by db2 as the culprit, even though it only had a small number of inserts before the select statement, but didn't do a commit for about 2 hours? If this is now the case, why would I get a SQL0965C on a process performing a select statement (a long running query). I would expect to run out of temp space in this case.

DB21085I Instance "db2inst3" uses "64" bits and DB2 code release "SQL09053"
with level identifier "06040107".
Informational tokens are "DB2 v9.5.0.3", "special_22945", "U818976_22945", and
Fix Pack "3".
Product is installed at "/cs/IBM/db2/V9.5".


Thanks

Here is what may be happening. As an example, I will use 3 Primary and 2 Secondary logs (and assume you have archive logging and it is not infinite).

Start with Log 1.
Problem user does Insert without a Commit. There is an open transaction in the Log 1.
Other processes do Changes and commits and the Log 1 fills up.
DB2 goes to Log 2.
Other processes do Changes an commits and Log 2 fills up.
DB2 goes to Log 3.
Other processes do Changes an commits and Log 3 fills up.
At this point DB2 would close Log 1 as it should have 3 Primary logs open but it can't since there is still an uncommitted transaction in it.
DB2 goes Log 4 (as Secondary 1).
Other processes do Changes an commits and Log 4 (Secondary 1) fills up.
DB2 still can't close Log 1.
DB2 goes to Log 5 (as Secondary 2).
Other processes do Changes an commits and Log 5 (Secondary 2) fills up.
DB2 still can not close Log 1 because of the uncommitted transaction.
But it has run out of Primary and Secondary logs.

You now have a Transaction logs full situation.

I have encountered the scenario stealth_dba describes a number of times.
You can use the database snapshot to understand which application is holding the oldest transaction etc.

db2 get snapshot for database on <dbname>

Look up for
Log space used by the database (Bytes)
Secondary logs allocated currently
Appl id holding the oldest transaction

HTH

Sathyaram

to be more sure on ur question you can post the user temp tablespace describe
like why the temp space was not full and when you got this error related to transaction log was there enought space in your temp tablespace file system.
regds
Paul

Will this scenario happen as described, if using a temp table created with the NOT LOGGED statement.