Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

"Nuclear war can ruin your whole compile." -- Karl Lehenbauer


devel / comp.databases.mysql / Ping Jerry Stuckle re: DB2 issues

SubjectAuthor
* Ping Jerry Stuckle re: DB2 issuesDFS
`* Re: Ping Jerry Stuckle re: DB2 issuesJerry Stuckle
 `- Re: Ping Jerry Stuckle re: DB2 issuesDFS

1
Ping Jerry Stuckle re: DB2 issues

<xKxYI.9774$Dr.1102@fx40.iad>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=54&group=comp.databases.mysql#54

 copy link   Newsgroups: comp.databases.mysql
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!news.uzoreto.com!news-out.netnews.com!news.alt.net!fdc2.netnews.com!peer02.ams1!peer.ams1.xlned.com!news.xlned.com!peer03.iad!feed-me.highwinds-media.com!news.highwinds-media.com!fx40.iad.POSTED!not-for-mail
Newsgroups: comp.databases.mysql
X-Mozilla-News-Host: news://usnews.blocknews.net:119
From: nos...@dfs.com (DFS)
Subject: Ping Jerry Stuckle re: DB2 issues
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
Thunderbird/78.13.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Lines: 51
Message-ID: <xKxYI.9774$Dr.1102@fx40.iad>
X-Complaints-To: abuse@blocknews.net
NNTP-Posting-Date: Fri, 03 Sep 2021 22:56:29 UTC
Organization: blocknews - www.blocknews.net
Date: Fri, 3 Sep 2021 18:56:29 -0400
X-Received-Bytes: 1963
 by: DFS - Fri, 3 Sep 2021 22:56 UTC

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.

Re: Ping Jerry Stuckle re: DB2 issues

<sgvvd2$l0$1@jstuckle.eternal-september.org>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=55&group=comp.databases.mysql#55

 copy link   Newsgroups: comp.databases.mysql
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!jstuckle.eternal-september.org!.POSTED!not-for-mail
From: jstuck...@attglobal.net (Jerry Stuckle)
Newsgroups: comp.databases.mysql
Subject: Re: Ping Jerry Stuckle re: DB2 issues
Date: Sat, 4 Sep 2021 10:21:12 -0400
Organization: A noiseless patient Spider
Lines: 65
Message-ID: <sgvvd2$l0$1@jstuckle.eternal-september.org>
References: <xKxYI.9774$Dr.1102@fx40.iad>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Sat, 4 Sep 2021 14:21:22 -0000 (UTC)
Injection-Info: jstuckle.eternal-september.org; posting-host="438fd97344f33c262336a6806a8d13b0";
logging-data="672"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18V8+X7+IhJJlKZWyfHphh8cnh3gx52m18="
User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:78.0) Gecko/20100101
Thunderbird/78.13.0
Cancel-Lock: sha1:MMheuijmNPqL4ZJY4nOwyMQYPlk=
In-Reply-To: <xKxYI.9774$Dr.1102@fx40.iad>
Content-Language: en-US
 by: Jerry Stuckle - Sat, 4 Sep 2021 14:21 UTC

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

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

Re: Ping Jerry Stuckle re: DB2 issues

<gFrZI.16862$rsCb.4465@fx01.iad>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=56&group=comp.databases.mysql#56

 copy link   Newsgroups: comp.databases.mysql
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!4.us.feeder.erje.net!2.eu.feeder.erje.net!feeder.erje.net!feeder1.feed.usenet.farm!feed.usenet.farm!peer01.ams4!peer.am4.highwinds-media.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!fx01.iad.POSTED!not-for-mail
Subject: Re: Ping Jerry Stuckle re: DB2 issues
Newsgroups: comp.databases.mysql
References: <xKxYI.9774$Dr.1102@fx40.iad>
<sgvvd2$l0$1@jstuckle.eternal-september.org>
From: nos...@dfs.com (DFS)
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
Thunderbird/78.13.0
MIME-Version: 1.0
In-Reply-To: <sgvvd2$l0$1@jstuckle.eternal-september.org>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Lines: 64
Message-ID: <gFrZI.16862$rsCb.4465@fx01.iad>
X-Complaints-To: abuse@blocknews.net
NNTP-Posting-Date: Mon, 06 Sep 2021 16:50:20 UTC
Organization: blocknews - www.blocknews.net
Date: Mon, 6 Sep 2021 12:50:19 -0400
X-Received-Bytes: 2708
 by: DFS - Mon, 6 Sep 2021 16:50 UTC

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.

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor