CODEWITHSUNDEEP
sqldb2
user1063108
user1063108

Reputation: 712

db2 cant stop getting transaction log is fill error

I am having a serious issue.. I have store procedure which updates, couple of million records. It all happens in a cursor. I am having issues that whatever I have done so far, it fails with transaction log full error SQL0964C The transaction log for the database is full. SQLSTATE=57011

Over the past couple of days I have, one by one, made following changes to db configuration in hopes that log file will STOP getting filled I have set the following

LOGRETAIN = NO
LOGARCHMETH1 = OFF
LOGILESIZE=20000
LOGPRIMARY=50
LOGSECONDARY=200

Up to this morning ( Just changed LOGARCHMETH1 = OFF and sp is running) I have no luck. For now I just don't want to see this darn error, I wish this to stop so that I can move forward with my testing. I can deal with these performance issues later. Can some kind soul shed some light how in the heck I force db2 to NOT use logs other than what I have already tried? DB2 version is 8.2.4

Upvotes: 0

Views: 1088

Answers (1)

user1063108
user1063108

Reputation: 712

Ok found the answer, Since I am not well versed with DB2 syntax, apparently the way I was using cursor was causing an "infinite loop"(?) Anyways I changed my code to follow this format 

FOR v AS cur1 CURSOR FOR 
            SELECT firstnme, midinit, lastname FROM employee
DO 
  SET fullname = v.lastname || ',' || v.firstnme 
                 ||' ' || v.midinit;
  INSERT INTO tnames VALUES (fullname);
END FOR;

and all worked perfectly..:-)

Hope it will help some other newbie like me with db2 syntax

Upvotes: 1

Related Questions