DB2 8.2 is the backend to our Vignette Imaging System. I am using the Vignette Admin Console to simply create a new Table. When I go to Commit the table through their tool, I get the SQL0964C error.

My current setup:
LOGFILSIZ=4096
LOGPRIMARY=10
LOGSECOND=100

I tried updating LOGSECOND to 200, stop/start DB2, but I don't see it changing. Also, LOGRETAIN and USEREXIT are both OFF.

Is there a better way to clean this up?

Thanks

check if the drive have enough space left

Yes, plenty of disk space on the drive. Not sure why the parameter will not increase, although those values seem pretty large considering nothing ever happens on this system. It's Test, and used for mostly inquiry. We increased them when we did a mass load of data from our Production system into this one.

Now a simple table create bombs. Can the logs be purged or simply turned off?

Thanks

Is the tool tyring to load some data in the same unit of work as creating the table? Why don't you create the table directly in DB2. There is no way that a simple table create without data can cause the logs to fill up.

I'm waiting for the vendor to get me more details about what it is doing. There's no data being loaded, which like you said, should not fill up the logs. I'll also check with them about creating it through DB2. This subsystem may not see, or be able to manage it if not created in the tool. It's very proprietary.

Until I get more info from them, all I know is when I commit the table, even before adding the fields, just the ones they put in, it fails and i get the SQL0964C error in the error logs.

Do you know if they use CLI or JDBC? If so, you could collect a CLI trace and see which operations are to be executed besides the CREATE TABLE statement.

I could use an ODBC trace. There are several table updates going on behind the scenes, but nothing very big, and I cannot change how that process works anyway.

I guess what I really need to know is just how can I clear out this transaction log, or give it more space? I tried to increase LOGSECOND but it does not seem to take.

The log files are released for reuse when all transactions (units of work) have been commited that are contained on that particular log.

As far as I can tell all Commits have been completed. I have stopped and started the DB2 database, and still get the transaction log full error when trying to run this process that creates a couple of tables, and updates a couple more.

Is there no way to purge them?

No, the logs are needed for crash recovery (in case of system failure). But only uncommited transactions are needed, and DB2 will release the logs for reuse as soon as everyone has commited (or a parituclar log file has no uncommited transactions on it).

Anytime you stop the intance and restart it, the logs are released (unless crash recovery is in progress).

You should double check to see if there is sufficient disk space on the path were the logs are located. Also, check the dates on the log files to see when the last one created is.

BTW, do you have anything set for LOGARCHMETH1 ? If so, then you are not using circular logging.

That parameter is set to OFF. Should it be on? Seems like based on what I'm reading it should work, but doesn't, and there's not too much I can do besides increase the size of the logs to help it.

I will check the disk space out too, but last I looked we had plenty available.

Thanks

Just a small correction: During crash recovery, DB2 needs the log records from uncommitted and committed transactions. The log records from uncommitted transactions are needed to rollback those transactions and undo any changes done so far. For committed transactions, DB2 must ensure that any changes are made persistent in the database (which may not yet have happened). Thus, it is necessary to re-apply those log records.

Back to the original question: Circular or archival logging doesn't make any difference here - assuming that enough disk space (1GB for logfilsiz = 4096 and logprimary+logsecond ~= 250) is available. What do you mean with "plenty" being available (output of "df -m ." in the log directory would help us)?

Something is occupying all the log space. Maybe some other applications are running concurrently and do not commit/rollback? How did you determine that all transactions have committed?

Do you do anything else besides using this proprietary tool? What does "db2 list applications" show you? Is there anything interesting in the db2diag output (set the diaglevel to 4 before collecting this information)?

Space stats:

$ df -m
Filesystem MB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 128.00 89.70 30% 2449 11% /
/dev/hd2 5376.00 609.73 89% 48317 26% /usr
/dev/hd9var 128.00 111.79 13% 476 2% /var
/dev/hd3 384.00 373.27 3% 235 1% /tmp
/dev/hd1 128.00 35.01 73% 74 1% /home
/proc - - - - - /proc
/dev/hd10opt 128.00 69.00 47% 949 6% /opt
/dev/fslv00 39424.00 31827.48 20% 8961 1% /db2
/dev/fslv01 2048.00 1118.69 46% 2430 1% /home/runtime
/dev/fslv02 1024.00 1022.71 1% 6 1% /home/runtime/is/log
/dev/fslv03 5120.00 5118.90 1% 4 1% /home/runtime/db/dbfiles
/dev/fslv05 3072.00 3043.89 1% 115 1% /policy
/dev/fslv06 1024.00 1023.52 1% 4 1% /home/backup
/dev/fslv04 269312.00 15982.66 95% 2608465 42% /home/runtime/is/files
$ pwd
/db2/db2inst1/db2inst1/NODE0000/SQL00002
$ ls
SQLBP.1 SQLDBCONF SQLOGDIR SQLSPCS.2 SQLT0002.0 db2rhist.asc
SQLBP.2 SQLINSLK SQLOGMIR.LFH SQLT0000.0 SQLTMPLK db2rhist.bak
SQLDBCON SQLOGCTL.LFH SQLSPCS.1 SQLT0001.0 db2event

$ db2 list applications

Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
IDMDB mmc.exe 1175 C0A82546.J607.00F9C7173124 TOWERDB 1
DB2INST1 db2bp 966 *LOCAL.db2inst1.071227160145 TOWERDB 1
IDMDB ODBC4DB2 14 2010C9D8.C35020.F1F1F0F8C5F4 TOWERDB 1
IDMDB ODBC4DB2 15 2006C398.C35020.F0F7F0F2C1F4 TOWERDB 1
IDMDB ODBC4DB2 12 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1
IDMDB ODBC4DB2 13 2010C9D8.C35020.F1F1F0F8C5F4 TOWERDB 1
IDMDB ODBC4DB2 11 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1
IDMDB ODBC4DB2 10 2010CEA8.C35020.F1F1F0C4C2F4 TOWERDB 1

Being a Test system, there is not much running except the processes started by the third party application. The db2diag has a lot of good information that I'll look through, including the log full errors.

I may force off all applications and then try the process again. And on the Commits, does stopping DB2 force all commits to take place?

Thanks for all the information!

Sorry for the ugly output above.....

db2stop force will not cause any commits, but it will do a rollback on any uncommited transactions.