1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • Ping Jerry Stuckle re: DB2 issues

    From DFS@21:1/5 to All on Fri Sep 3 18:56:29 2021
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jerry Stuckle@21:1/5 to DFS on Sat Sep 4 10:21:12 2021
    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
    ==================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to Jerry Stuckle on Mon Sep 6 12:50:19 2021
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)