Re: Ping Jerry Stuckle re: DB2 issues

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