From Newsgroup: comp.databases.mysql

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.


--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 9/3/2021 6:56 PM, DFS wrote:

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-a The transaction log for the database is full.-a SQLSTATE=57011

Recreated the tables with the NOT LOGGED INITIALLY option:

CREATE TABLE TBL (
-a-a-a ID-a-a-a INT-a-a-a-a-a-a-a-a-a-a NOT NULL,
-a-a-a TEXT-a VARCHAR(220)-a 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-a (commits are done at small intervals)

But now I'm getting error:

-aSQL1476N-a The current transaction was rolled back because of error "-964".-a 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)-a-a-a-a = 103297818
Maximum secondary log space used (Bytes)-a-a = 49038276
Maximum total log space used (Bytes)-a-a-a-a-a-a = 103297988
Secondary logs allocated currently-a-a-a-a-a-a-a-a = 12

Any advice will be appreciated.

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
jstucklex@attglobal.net
==================
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 9/4/2021 10:21 AM, Jerry Stuckle wrote:

On 9/3/2021 6:56 PM, DFS wrote:

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-a The transaction log for the database is full.-a SQLSTATE=57011


Recreated the tables with the NOT LOGGED INITIALLY option:

CREATE TABLE TBL (
-a-a-a-a ID-a-a-a INT-a-a-a-a-a-a-a-a-a-a NOT NULL,
-a-a-a-a TEXT-a VARCHAR(220)-a 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-a (commits are done at small intervals)

But now I'm getting error:

-a-aSQL1476N-a The current transaction was rolled back because of error
"-964".-a 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)-a-a-a-a = 103297818
Maximum secondary log space used (Bytes)-a-a = 49038276
Maximum total log space used (Bytes)-a-a-a-a-a-a = 103297988
Secondary logs allocated currently-a-a-a-a-a-a-a-a = 12


Any advice will be appreciated.

Your problem is the NOT LOGGED INITIALLY is only good for the
transaction.-a Once you issue a COMMIT logging starts again.-a Everything must be done in one transaction (no COMMIT or ROLLBACK and AUTOCOMMIT
must be disabled).

Thanks. I eventually got the code to run and the db to finish
populating by increasing the number of secondary logs to 24.

--- Synchronet 3.21d-Linux NewsLink 1.2