Ping Jerry Stuckle re: DB2 issues

[DFS]

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]

Post by DFS
Is there an easy way to disable transaction logging altogether in DB2?
SQL0964C  The transaction log for the database is full.  SQLSTATE=57011
CREATE TABLE TBL (
    ID    INT           NOT NULL,
    TEXT  VARCHAR(220)  NOT NULL
) NOT LOGGED INITIALLY;
ALTER TABLE TBL ACTIVATE NOT LOGGED INITIALLY;
then
DELETE FROM TABLE;
then
repopulate with bulk INSERTs  (commits are done at small intervals)
 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.
$ db2 get snapshot for all on database > logsnapshot.txt
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.

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).

[DFS]

Post by Jerry Stuckle

Post by DFS
Is there an easy way to disable transaction logging altogether in DB2?
SQL0964C  The transaction log for the database is full.  SQLSTATE=57011
CREATE TABLE TBL (
     ID    INT           NOT NULL,
     TEXT  VARCHAR(220)  NOT NULL
) NOT LOGGED INITIALLY;
ALTER TABLE TBL ACTIVATE NOT LOGGED INITIALLY;
then
DELETE FROM TABLE;
then
repopulate with bulk INSERTs  (commits are done at small intervals)
  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.
$ db2 get snapshot for all on database > logsnapshot.txt
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.

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).

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