Try the following to delete all the rows in a DB2 table:
1. ALTER TABLE DB2ADMIN.EMPLOYEE ACTIVATE NOT LOGGED INITIALLY WITH
EMPTY TABLE;
2. COMMIT;
Note: The "COMMIT statement must be run immediately after the "ALTER
TABLE" statement.
Also, when loading data, you need to "COMMIT" more frequently.
Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: gcanedy
[mailto:db2udbdba-ezmlmshield-x87493518.[Email address protected]
Sent: Tuesday, June 26, 2007 11:05 AM
To: LazyDBA Discussion
Subject: [DB2] Transaction Logs
Hi everyone, First time post here. I'm guessing this is an old topic,
but
having trouble finding the right answer.
I have a small table with about 100,000 rows. If I do "delete from
table_name" it fails with the "TTSQL error -964:[DataDirect][ODBC DB2
Wire
Protocol driver][UDB DB2 for Windows, UNIX, and Linux] The transaction
log
for the database is full." error message.
I can selectively delete small groups of records until I get it down to
around 10.000 rows, then sometimes it will let me finish with the
'delete
from table'.
I've got the following cfg (which was just changed from values 1024, 3,
2):
Log file size (4KB) (LOGFILSIZ) = 4096
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 5
with logs files as shown:
/db2/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR
$ ls -l
total 40080
-rw------- 1 db2inst1 db2iadm1 4104192 Jun 26 09:52 S0000000.LOG
-rw------- 1 db2inst1 db2iadm1 4104192 Jun 26 09:42 S0000001.LOG
-rw------- 1 db2inst1 db2iadm1 4104192 Jun 26 09:43 S0000002.LOG
-rw------- 1 db2inst1 db2iadm1 4104192 Jun 26 09:43 S0000003.LOG
-rw------- 1 db2inst1 db2iadm1 4104192 Jun 26 09:43 S0000004.LOG
Does DB2 have to be bounced for the changes to take effect? Is there a
cleaner way to manage the transaction logs? I'm also having trouble
loading mass rows (around 200,000) into the tables.
Thanks!
Gary P. Canedy
Database Analyst
ProMutual Group
(617) 757-6775
-----------------------------------------------------------
This email and any files transmitted with it are intended only for the
person or entity to which it is addressed and may contain confidential
and/or privileged material. If you received this email in error,
please
contact the sender immediately and delete this email from your system.
If
you are not the named addressee, you should not disseminate, distribute,
print, or copy the email, or take any action in reliance on its
contents.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page