Hi,
in my db2 database, in a table i have 80,000 rows, when i try to delete programmatically

"delete from table"

This end up with error - "SQL0964C The transaction log for the database is full"

In my config files,
i have LOGRETAIN = OFF and
USEREXIT = OFF

already i have used enough space to allocate log path.

What else i need to do, to make log off...?

any idea..?

2 options
alter table xx not logged initially
delete from table
both operation in the same uow
or try to call db2admin stored proc to execute import from /dev/null of del replace into xx
from application

another option:
increase log file size or/and log file number....

I tried the first option,
"Alter table xxx activate not logged initially" executed this command, But didnt work....

second option is like changing the query... if i do this..i need to make alot of changes in my app. so envt config settings would be better to do... and which handle future scenarios as well

Increase the log file size is allowing to write more log...
But this could impact in Hard disk space... i dnt want to to do that....

as indicated before :
both operation in the same uow
the commit command (explicit or implicit) will remove this setting
Reducing logging with the NOT LOGGED INITIALLY parameter

"Alter table" and "delete records" these both operation should we use in same unit of work... ? is this what you meant...?

I am not sure how to make it same unit of work....?

Could you give me the comments in this....

if started from a file in command line - look at command options and autocommit
LIST COMMAND OPTIONS
if from an application : no commit in between

basically that means "Alter table" and "delete records" should be in the same transaction.....
Do not commit work between the operation of alter table and delete records。
PLZ turn off the autocommit option/attributes in your program if it is on....

Hope this would work.

alter table XXX activate not logged initially;
delete from XXX;
commit work;

Hi,
i have turned off my autocommit options, and in program i applied the above queries.....
alter table xxxx activate not logged initially;
and then
delete from xxxx

But it throws exception during delete query execution..... says Transaction log is full.....

am i missing any thing.. ?

have you started the script with +c option
otherwise it is autocommitted and alter table is committed and not used

This change adding +c with query , will be like modifying queries everywhere i needed... If i have 200+ places, changing it to all the area is not good way of solution. I am trying to make it config level itself.... So that in future the same problem will not happen in any other areas.....

You have not specified your environment. If you have DB2 9.7, then you could use truncate!
TRUNCATE statement - IBM DB2 9.7 for Linux, UNIX, and Windows

if you are on db2 9.7 try below command to delete all the rows from the table...hope you already have the backup, just in case...

db2 "truncate table schema.table reuse storage immediate"

If you want to reclaim the space try the below command on the tablespace under which this table resides.

db2 "alter tablespace TSNAME reduce max"