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






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



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



  • 5.  RE: Long transaction aborted

    IBM Champion