Informix

 View Only
Expand all | Collapse all

Drop table with no-logging mode

  • 1.  Drop table with no-logging mode

    Posted Sun March 26, 2023 10:36 PM
    Edited by nasiha zailan Sun March 26, 2023 10:47 PM

    Hi, i have situation where i try to drop table contains of time-series data in production environment with HDR mode. The data is about 200GB. Is there anyone who knows how to do this?

    I try to delete table with no-logging since my logical logs are insufficient to carry the transaction causing the server to hang.



    ------------------------------
    nasiha zailan
    ------------------------------



  • 2.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 02:18 AM

    Hello Nasiha, you are aware that no-logging tables are not transferred to the secondary server, so HDR does not work for no-logging tables? Regards the hanging HDR server you should open a case.
    Regards, Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 2022 2023
    IIUG Board Member
    ------------------------------



  • 3.  RE: Drop table with no-logging mode

    Posted Mon March 27, 2023 03:06 AM
    Edited by nasiha zailan Mon March 27, 2023 03:10 AM

    Hi Henri, i thought that no-logging tables could not be done on HDR, thanks for the clarification anyway. Anyhow, you have any idea or reference to use no-logging mode. Couldn't find any resources on Google.

    Also, i try to drop physical table, not the temp one.



    ------------------------------
    nasiha zailan
    ------------------------------



  • 4.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 03:21 AM

    Hi Nasiha, the use of no-logging databases or tables depends on your applications. Don't recommend no-logging databases but see use case for RAW Tables (https://www.ibm.com/docs/en/informix-servers/14.10?topic=informix-raw-tables) in special situations (e.g. temporary data store). 

    Regards

    Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 2022 2023
    IIUG Board Member
    ------------------------------



  • 5.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 04:12 AM
    Hi, 
    if I understand you correctly, you want to delete a huge amount of data but you would run in a long transaction
    while doing the delete ?

    There are a number of options, which would allow you to delete the data.
    However it depends if you only want to delete parts of the data or purge the complete table.


    -> doing the deletion in non-logging mode, you need to cut the replication (set primary server in standard mode),
    set the table or the database to non-logging mode and then delete. 
    Afterwards take a full backup and reinit the secondary server by restoring the backup (only physically, ontape -p). 
    Then reestablish the HDR (onmode -d primary <sec_server>, onmode -d secondary <pri_server>).
    That way, the deletion will take a very small time (in non-logging mode, everything like that is faster), but this will be 
    interrupting the HDR and maybe make the application unusable while the deletion is executed because
    it might rely on transactions.

    -> doing the deletion in logging mode will transfer the deletions to the secondary server. You need to do this
    in a transaction. You either do it in steps small enough not to fill your logical logs, which would maybe result in a long transaction
    or create enough logical logs which are covering the deletions in one run.
    In order not to produce a high number of locks during deletion, lock the table in exclusive mode before the delete
    statement is executed (in same transaction). (otherwise one lock per row is created, assuming you have lock mode set to row, 
    which could result in a lock table overload, depending on the memory limits of your machine).

    -> In case you want to delete all data, maybe truncate table is the better choice. (or drop and re-create the table)

    -> In case you want to preserve only a small number of records, another approach can be to rename the table,
    create a new one with the same table layout (and foreign keys, if present) and copy the data to be preserved
    using insert into ... select from. Then drop the original table at the end.


    Best,

    Marcus Haarmann





  • 6.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 04:57 AM

    How many rows does this table have (select count(*) from <tab_name>;) ?

    How do you know / did you measure "200GB"?

    How big are those timeseries (on average, min/max)?

    And: non-logging is not possible with timeseries, even 'raw' looks to not be playing well with a 'drop table', so you'll have to find a way to delete those rows in batches. probably.



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



  • 7.  RE: Drop table with no-logging mode

    Posted Mon March 27, 2023 10:52 PM

    Rows about 939,064,588 rows, there are 4 tables in which each table consists of 16777215 pages with 4kb. So, i assumed the storage size is about 200gb.



    ------------------------------
    nasiha zailan
    ------------------------------



  • 8.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Tue March 28, 2023 04:10 AM

    Oh wow, this sounds like a lot, esp. if these only are 'home rows' with time series hanging off of these, but being stored elsewhere and consuming even more space?

    Is this your only table containing timeseries?

    What would this query return (same database):  select count(*) from tsinstancetable;



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



  • 9.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Tue March 28, 2023 07:23 AM
    How many elements in the TS?

    Cheers
    Paul
    --  Paul Watson Oninit www.oninit.com Tel: +1 913 364 0360 Cell: +1 913 387 7529  Oninit?? is a registered trademark of Oninit LLC  If you want to improve, be content to be thought foolish and stupid Failure is not as frightening as regret





  • 10.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 07:11 AM

    Nasiha:

    Once you resolve the log-hang issue (backup the logs and wait for the rollback to complete), you can use my dbdelete utility to delete the data from the table. That will delete the data in batches of 8192 rows which will not cause the logs to hang, even if you leave the table in logged mode and it will therefore also delete from the HDR secondary at the same time. Dbdelete is included in my open source utils2_ak package which you can download from my web site at:

    https://www.askdbmgt.com/my-utilities.html

    Art



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



  • 11.  RE: Drop table with no-logging mode

    Posted Tue March 28, 2023 12:35 AM

    Thanks Art for the response, but may i know how to use your utility or you have documentation that i can refer?



    ------------------------------
    nasiha zailan
    ------------------------------



  • 12.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 08:14 AM

    You should not try and delete timeseries (including dropping tables with timeseries) without logging, as it can lead to corruption.  I believe that Informix now stops you from getting yourself into trouble now by preventing changing the tables to raw.  That wasn't always the case though and would cause some serious problems, so don't try working around that restriction.

    When dropping a table with TimeSeries, the timeseries catalogs will be updated for each element removed, just like if you were deleting the timeseries elements.  You will see lots of updates to the tsinstance table, and it will be slow, and you'll have all of the additional logging.  The best thing to do is to delete the rows in batches so that you don't fill the logs and get a long transaction.  Art mentions his dbdelete script which will do the job nicely, or you may have another way to break up the data into manageable chunks for deletion.



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



  • 13.  RE: Drop table with no-logging mode

    IBM Champion
    Posted Mon March 27, 2023 08:44 AM
    AFAIK you can't have a non-logged TS table.

    If you are hitting a LTX then just run table row by row, or small batches.?? There a lot of background tasks underway when you drop TS tables

    --  Paul Watson Oninit www.oninit.com Tel: +1 913 364 0360 Cell: +1 913 387 7529  Oninit?? is a registered trademark of Oninit LLC  If you want to improve, be content to be thought foolish and stupid Failure is not as frightening as regret