Informix

 View Only

  • 1.  Long transaction aborted

    Posted Mon December 11, 2023 07:34 AM

    Hello,

    Adding a field on a 24 millions rows table fails on 

    [Code: -458, SQL State: IX000]  Long transaction aborted.  [Script position: 1108 - 1110]

     [Code: -12204, SQL State: IX000]  RSAM error: Long transaction detected.  [Script position: 1108 - 1110]

    I could add logical logs but I don't know how much is needed, 

    so is it possible to momentarily disable transactions, just during the alter table  ?

    Thanks,

    Sam 



    ------------------------------
    Samuel To
    ------------------------------


  • 2.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 07:54 AM
    Hi,

    fastest way would be to disable transactions on the database. 
    (you can do this with ontape or onbar, both have a switch which allows you to take a fake backup
    and modifiy the database mode to no logging.
    E,g. ontape -s -F -N <database_name> -t /dev/null
    apply the table change ...
    ontape -s -F -U <database_name> -t /dev/null)
    -N = no logging
    -B = buffered logging
    -U = unbuffered logging

    The database would be unusable for the time of the modification, because lack of transactions,
    which your application probably make use of. But this will work significantly faster than with transactions.

    You need to take a full backup afterwards.

    And any HDR/RSS setup needs to be re-initialized afterwards by taking a full backup and restore,
    because the table modifications have not been put in the logical logs and so any secondary instance
    did not get the information about the change.

    BUT, normally an alter table statement, just appending columns should work in-place
    without running into a long transaction, because only the schema is changed and the
    data is left untouched.
    However, this is not true if you are populating the new column directly with non-null values for some reason.

    Also, this might occur if you are modifying data types (like converting a date to a datetime). 
    The database needs to copy the column internally while executing the modification, which might
    take very long and possibly will fail with long transaction.
    We would do this in multiple steps in order to prevent long transactions or downtime.
    So, add a new column without assigning values, populate the column with an external script 
    (in transactions each 100000 records for example, use tx_split for this), then rename columns and afterwards drop the old one,
    MARCUS HAARMANN




    Original Message


  • 3.  RE: Long transaction aborted

    Posted Mon December 11, 2023 08:14 AM

    So maybe there is another problem

    The dev guy add the field without populating the new field

    alter table scan_ans add times  LVARCHAR(300);

    I added 50 LL (200 > 250) and i saw that they are filled immediately after running the statement



    ------------------------------
    Samuel To
    ------------------------------

    Original Message


  • 4.  RE: Long transaction aborted

    Posted Mon December 11, 2023 08:40 AM
    BUT, normally an alter table statement, just appending columns should work in-place
    without running into a long transaction, because only the schema is changed and the
    data is left untouched.

    alter table scan_ans add times  LVARCHAR(300);

    You have any idea why apparently this runs into a long transaction ?



    ------------------------------
    Samuel To
    ------------------------------

    Original Message


  • 5.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 09:03 AM

    Unfortunately, adding a LVARCHAR will not use the in-place alter.  From the manual:

    When the table contains an opaque data type, a user-defined data type, an LVARCHAR data type, a BOOLEAN data type, or a smart large object (BLOB or CLOB), the database server does not use the in-place alter algorithm, even when the column that is being altered is of a data type that can support in-place alter operations.



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message


  • 6.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 11:11 AM

    Unfortunately, adding an LVARCHAR is not performed as in-place alter, hence the entire table gets copied into a new one - which, one could argue and were it not for HDR/RSS/SDS, wouldn't even have to be logged with a regular (non-raw) table, but there we are.



    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 7.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 12:47 PM

    Just musing on another option for this:

    Create a new table with the new scheme, copy the data using my dbcopy utility which will make the copy avoiding the long transaction problem (dbcopy commits every N rows- 10,000 by default), then rename the original table, rename the new table to the original name, do the due diligence to validate the copy, then drop the original table. All logged and lovely but no long transactions.

    Another option. unload the table to an external table with multiple files, truncate and alter the table, reload the files one at a time using an external table to minimize the transaction size.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 8.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 08:10 AM

    Sh:

    If your alter is not being processed in-place, then you can disable logging on that table for the alter like this:

    ALTER TABLE sometable  TYPE( RAW );

    ALTER TABLE sometable ADD (newcol newtype NOT NULL);

    ALTER TABLE sometable  TYPE( STANDARD);

    Then, as soon as it is practical, take a level 0 archive so that if you have to restore the new schema and new column(s) is included in the restored table.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 9.  RE: Long transaction aborted

    Posted Mon December 11, 2023 08:16 AM

    OK

    Also In this way I'll need to re-initiate the HDR/RSS servers isn't it ?



    ------------------------------
    Samuel To
    ------------------------------

    Original Message


  • 10.  RE: Long transaction aborted

    IBM Champion
    Posted Mon December 11, 2023 08:21 AM
    yes, anytime you are doing something without transactional logging.
    MARCUS HAARMANN




    Original Message