Ping Jerry Stuckle re: DB2 issues

0 views
Skip to first unread message

DFS

unread,
Sep 3, 2021, 6:56:32 PMSep 3
to
Maybe you can help:

Is there an easy way to disable transaction logging altogether in DB2?


I was populating DB2 tables and started getting:

SQL0964C The transaction log for the database is full. SQLSTATE=57011


Recreated the tables with the NOT LOGGED INITIALLY option:

CREATE TABLE TBL (
ID INT NOT NULL,
TEXT VARCHAR(220) NOT NULL
) NOT LOGGED INITIALLY;


Code to populate data includes:

ALTER TABLE TBL ACTIVATE NOT LOGGED INITIALLY;
then
DELETE FROM TABLE;
then
repopulate with bulk INSERTs (commits are done at small intervals)

But now I'm getting error:

SQL1476N The current transaction was rolled back because of error
"-964". SQLSTATE=40506

As far as I can tell it's another 'transaction log full' issue.



I saved a snapshot:

$ db2 get snapshot for all on database > logsnapshot.txt

It contains:

Log space available to the database (Bytes)= 1047782
Log space used by the database (Bytes) = 103297818
Maximum secondary log space used (Bytes) = 49038276
Maximum total log space used (Bytes) = 103297988
Secondary logs allocated currently = 12


Any advice will be appreciated.


Jerry Stuckle

unread,
Sep 4, 2021, 10:21:24 AMSep 4
to
Your problem is the NOT LOGGED INITIALLY is only good for the
transaction. Once you issue a COMMIT logging starts again. Everything
must be done in one transaction (no COMMIT or ROLLBACK and AUTOCOMMIT
must be disabled).

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

DFS

unread,
Sep 6, 2021, 12:50:24 PMSep 6
to
Thanks. I eventually got the code to run and the db to finish
populating by increasing the number of secondary logs to 24.

Reply all
Reply to author
Forward
0 new messages