Informix

 View Only
Expand all | Collapse all

alter table modify lvarchar column length report -136 ' no more extents' error

  • 1.  alter table modify lvarchar column length report -136 ' no more extents' error

    Posted Sun March 24, 2024 11:44 PM

    Hi, experts:

         I execute the 'alter table table-name modify pexp lvarchar(4096) ' ,the table has about 80M rows, and informix report below errors.

        222: cannot write to temporary file for new table

        136: ISAM error : no more extents

       my IDS version is 11.5, the dbspace page size is 4K;

        How to avoid the problem? any suggestion are appreciation

    lu chuan

        



    ------------------------------
    chuan lu
    ------------------------------


  • 2.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    IBM Champion
    Posted Mon March 25, 2024 03:55 AM
    Hi,

    we came upon a similar situation, although not with lvarchar but with a number of varchar columns.
    The point is, that the server reserves for each varchar column a portion of additional space in order to
    allow updates with slightly longer content within the same row (preventing a copy).
    Also, when your table has been modified multiple times, earlier versions might have had a smaller
    row size.
    When you now alter a lvarchar, which does not work in place, the server internally constructs a new table
    with the new structure and inserts all the records from the old table.
    Then the following happens:
    1. all rows which have fields which have not been there before (because of a in-place alter, which produced null values)
      are now populated
    2. all varchar rows suddenly consume more space than before. You might be able to get around this effect by tuning the
      MAX_FILL_DATA_PAGES parameter to 1 , which reduces the additional reserved space for varchar columns.
      But I am not sure in which release it was introduced.

    Both of these might lead to no more extents, because the needed space for the new table is significantly higher than the old one.
    You need to check if you are at the level that "only" the number of extents is exceeded (you can trick this out
    by modifying the extent sizes) or if you are about to reach the maximum storage limit.
    (which is 16mio pages of (in your case 4k)).
    You should check with oncheck -pT if you are about to touch the limits of if only extends are your issue.
    If you are touching the limits, you should either distribute the table over different dbspaces using partitioning 
    or put it in a 8k volume instead, because the number of consumed pages would decrease significantly, getting you
    away from the 16mio restriction. In case of a table which is growing quite fast, even a 16k volume might make sense.
    You just need to watch your buffer pool (you should have a buffer pool for each page size in use).
    (Licensing might limit you: distribution requires enterprise edition, 8k spaces can be done in any edition).

    I am not sure if they changed anything in alter table of lvarchar columns in later versions, so your issue won't be
    vanishing probably in a newer release.
    The storage limit is about to be extended in 15.x version, planned hopefully sometime this year, but this
    is perhaps not an option for your needs, because the effort of migration from 11.50 to 15.x is of course
    not what you were expecting when doing this simple alter table.

    11.50. is way out of support, you should consider upgrading to 14.10, but I know of some customers sticking to a very
    old version for some legacy systems, which are not meant to be upgraded any more but somehow needed.
    Most of them do not want to invest any more in these systems.

    Hope this helps,

    Marcus Haarmann



    Original Message


  • 3.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    IBM Champion
    Posted Mon March 25, 2024 07:38 AM

    Chuan:

    As Marcus pointed out, MAX_FILL_DATA_PAGES will pack the data more tightly when your 4K LVARCHAR column only contains a much shorter string. However, prior to v14.10 having MAX_FILL_DATA_PAGES set during production processing in a system with many deletes or updates to a variable length table can seriously impact performance. That is because older versions did not cache lists of pages that have space for new small rows so any insert would scan the existing non-full pages until it found one with enough free space to hold the new or updated and longer row. Deletes on such a table exacerbate the problem by leaving free space holes in pages scattered across older pages requiring frequent reorganization of the table to regain insert/update performance.

    So, what you could do is to set MAX_FILL_DATA_PAGES before the ALTER then clear it afterwards. You will still have to reorg the table periodically to compress empty space on pages, but performance will not be affected in as great a way.

    Another thing to do, would be to make sure that the EXTENT SIZE and NEXT SIZE of the table is set to minimize the number of extents, though you are more likely to be hitting the 6million page limit on the table. If so, which is likely without MAX_FILL_DATA_PAGES set, you will have to partition the table before altering it to expand that LVARCHAR column.

    Art



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

    Original Message


  • 4.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    Posted Mon March 25, 2024 01:24 PM

    If you have Perl installed as well as Jonathan Leffler's magnum opus module DBD::Informix:

    Here's my $0.02(US), as well as a shameless plug:

    1. Rather than try to do an in-place alter table, create a new table with the identical schems as the current table, except with that one column typed as your lvarchar(4096). And an initial extent of at least the size of the current table, next size maybe 25% of that.
    2. Download my fcopy-table.pl from the IIUG repository in SourceForge.  (It has a big help page).
    3. Copy the old table to the new one using fcopy-table.  I think the default commit interval is about 10,000 rows but you can check on that.
    4. Rename the old table to table_orig
    5. Rename the new table to the original name of the old table.

    You will have to recreate the PK and FK constraints manually.  Get this information from dbaccess/table/info/constraints before starting this procedure.  Might even be a good idea to set those up before you start the copy these will slow down the copy.  Besides, that's probably not needed for a mere 80 million rows.

    Let us know if you were able to pull this off.

    If you don't have Perl or the necessary modules, pout and throw a tantrum at your admins until they give in and install these ultra-useful facilities.. <Wicked grin>  If that fails, I believe Art Kagel's table-copy utility will do the job quite nicely.



    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 5.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    Posted Mon March 25, 2024 10:35 PM

    Hi,

        I check the onconfig, the parameter MAX_FILL_DATA_PAGES exist in version 11.5 also. 

     below are the table schema.

    { TABLE "epcc".epcc_paysrl_attach row size = 2217 number of columns = 29 index size 
                  = 54 }
    create table "epcc".epcc_paysrl_attach 
      (
        instgid varchar(14) not null ,
        trxid varchar(31) not null ,
        rpflg varchar(1) not null ,
        ordrid varchar(40),
        ordrdesc lvarchar(1229),
        ordrexp varchar(19),
        mrchnttpid varchar(4),
        mrchntpltfrmnm varchar(20),
        trxtrmtp varchar(2),
        trxtrmno varchar(32),
        trxprps char(4),
        ipaddr varchar(45),
        mac varchar(12),
        imei varchar(15),
        imsi varchar(15),
        iccid varchar(20),
        wifimac varchar(12),
        gps varchar(24),
        cpext01 varchar(20),
        cpext02 varchar(60),
        cpext03 varchar(120),
        mkinfo1 varchar(20),
        mkinfo2 varchar(20),
        mkinfo3 varchar(60),
        mkinfo4 varchar(60),
        mkinfo5 varchar(120),
        mkinfo6 varchar(120),
        createts varchar(24),
        updatets varchar(24),
        primary key (instgid,trxid,rpflg)  constraint "epcc".pk_epcc_paysrl_attach
      ) extent size 10240 next size 16 lock mode row;
     
    revoke all on "epcc".epcc_paysrl_attach from "public" as "epcc";

      extent size 16K is a small number also, I use 'oncheck -pP' to verified that the table still have 368 free extent , but Why 'alter table X modify column-name' report 'no more extent' error.

       we do have plan to do the unload/load using the external table .



    ------------------------------
    chuan lu
    ------------------------------

    Original Message


  • 6.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    Posted Mon March 25, 2024 10:53 PM

    Chuan,

    fcopy-table.pl unloads to a FIFO and runs dbload reading from the same FIFO - the unload & load at the same time.



    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 7.  RE: alter table modify lvarchar column length report -136 ' no more extents' error
    Best Answer

    IBM Champion
    Posted Tue March 26, 2024 05:13 AM
    Hi,

    you are reaching the extents limits, because the system tries to duplicate the table internally,
    with the same structure that is already has.
    So the initial/next sizes are copied and all rows are duplicated.
    Since MAX_FILL_DATA_PAGES is not set, the resulting table will be bigger than the original.

    In case only extents are the issue here, you could maybe do it without copying data.
    First, calculate a reasonable extent size (see below)
    Then modify the table:
    alter table xxxx modify extent size xxxxx next size xxxx;
    and afterwards try to reorg the table:
    alter fragment on table xxxx init in datadbs; (or whatever your dbspace is named where table is stored)
    This would internally reorganize the table, reducing the extents.
    (takes some time and of course blocks the table).

    The extent size you have listed for this table is way too small, when it has 80mio records.
    Also, when looking at the schema, you have lots of varchar columns there, the MAX_FILL_DATA_PAGES
    parameter will for sure save a lot of space.
    I am not sure if setting MAX_FILL_DATA_PAGES requires an instance bounce in 11.50.
    so make sure it really applies, otherwise the table fragment init would result in an error.

    If the parameter is not active, you will run into a situation where the new constructed table
    is consuming even more space than before.

    In order to calculate the extents:
    oncheck -PT will tell you how many extents are consumed, not how many are free.
    You should take a look at the Number of pages allocated/used numbers.

    You should make a rough estimation of the extent size which will get you again to a reasonable number of extents
    for the current content of the table, this is where you would need the number of pages allocated for.
    no_pages_allocated * pagesize = actual size of table
    actual size of table / extent size = number of extents

    You should try to allocate the full space in only one initial extent in the new table, setting 10% of the initial size
    as next size (these should be smaller, unless you have unlimited storage).
    So when your table now consumes 3GB, you could allocate 3GB as initial size for the new table, with next size
    300 mb. If your table is not growing that fast, you could set 100mb as next size instead.

    When alter fragment does not work as expected, you could copy the table manually.
    (the column change you are intending, would internally reconstruct the table anyway, since lvarchar
    cannot be modified in-place).
    Maybe the alter fragment can be skipped and you can try the modify of the lvarchar, which would probably
    produce the same result. That way, you would not have to lock / internally reconstruct the table twice.

    The copy process will shrink the table space consumption because of the MAX_FILL_DATA_PAGES, so the result
    would be one extent only, with some space left.

    You could also create a mini stored procedure which will copy the data in steps:
    create procedure duplicate_table ()
        define v_instgid varchar (14);
        define v_trxid varchar (31);
        ......
        begin
          prepare stmt1 from "select instgid, trxid, rpflg ... from epcc_paysrl_attach";
          declare cursor1 cursor with hold for stmt1;
          begin work;
          set isolation to dirty read;
          open cursor1;
          fetch cursor1 into v_instgid, v_trxid, .....;
          let counter = 0;
          while (sqlcode = 0)
              insert into epcc_paysrl_new (instgid, trxid, rpflg ....) values (v_instgid, v_trxid);
              let counter = counter + 1;
              if counter > 50000 then
                  commit work;
                  begin work;

                  let counter = 0;
              end if
              fetch cursor1 into v_instgid, v_trxid ...;
          end while
          close cursor1;
          free cursor1;
          free stmt1;
          commit work;
        end
    end procedure;
    Marcus Haarmann



    Original Message


  • 8.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    Posted 27 days ago

    HI， Marcus：

            I want  to do the data reorg. we will try after fragment firstly.  Using the store procedure is the backup solution. 

           Dose the performance of the store procedure  is better than the  external table ? 



    ------------------------------
    chuan lu
    ------------------------------

    Original Message


  • 9.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    IBM Champion
    Posted 27 days ago
    Chuan,

    Use oncheck -pT, which shows you the currently allocated number of extends
    and the currently allocated number of pages.
    The free extends are not known, because the limit is depending on the table layout.
    Check the man page (finderr -136) for details on calculating the maximum number of extends
    and the table limits.

    As already stated, the error message can be an indicator that you either are running in an extends overflow
    or you are reaching the table size limit of 16m pages.
    When the table limit is reached (e.g. you are near to 16 mio pages), maybe setting MAX_FILL_DATA_PAGES and copying the data internally (within the same page size) might help, because you have so many varchar columns.
    We typically are monitoring the extend numbers and our warning mechanism starts at 100 extends. So your table
    structure would be exceeding our internal limits.
    We are also monitoring consumed number of pages, warning starts at 12m pages .
    This gives us enough time to react on such a situation.

    You have chosen the way to reorganize the table in a new tablespace which is created using
    larger page size (e.g. 16k instead of 2k or 4k). This would certainly get you in a situation that the number
    of extends is not in a warning state any more.
    So first create the new DBSpace if you do not have one already, you need to configure bufferpool for this page
    size as well.
    Make sure MAX_FILL_DATA_PAGES is set before populating the new table.
    Then create the new table (with a different name, including the new column you want to add) 
    in the DBSpace as empty table with appropriate extends.
    After that, copy data from old to new table. When copying is complete, rename old table to table_org and new table
    to the correct name.

    The use of an external table to copy data is a two step process. You copy data to the filesystem into the external
    table and then you insert it into the newly created table in the 16k page area.
    Also, apply any indexes AFTER the copying process. That results in better balanced indexes.

    Copying data within the server would be a one step solution. This was what the approach with the stored procedure
    was meant for, it is designed to be running in a logging environment and would not break an existing HDR setup.
    (but would generate a large number of logs maybe, check the logfile backup while the copying process is running).
    I would assume this would be faster than the external table approach, because data is only touched once.

    A solution to speed up the process could also be to set the new table to raw mode while copying data (remember to
    modify it before the rename.

    This would not require a stored procedure any more, taking care about transactions, but you could insert data with a simple 
    insert into ... select from ... (make sure you are enumerating the field names, leaving the newly created column as a null).

    However, if you have HDR in place, the use of a raw table would break the HDR and you would need to 
    do a full restore on the secondary server.

    Make sure you are pre-calculating the extend sizes (in KB, not in pages) for the new created table, in order
    to not run into a big number of extends again at a later timepoint.

    Good luck,
    MARCUS HAARMANN



    Original Message


  • 10.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    IBM Champion
    Posted Tue March 26, 2024 07:03 AM

    Chuan:

    You have asked "why?" and no one has given you a complete answer yet. So, here goes:

    • With MAX_FILL_DATA_PAGES not set the engine will not place a variable length row on a page that already has data on it unless the maximum length of the new row will fit on that page, even if the actual length of the row is only 33 bytes (the minimum disk footprint of one of this table's rows. So, while a 4K page can hold up to 50 minimum sized rows and still have room for another because that would leave enough room for a full length 2217 byte row (plus its slot table entry), only one row of even 33 bytes will fit on a page when the maximum row length is increased from 2217 bytes to 5084 bytes (assuming it is the ordrdesc column you are actually trying to expand - more if it is another column)! So, with the now 4096 byte length of that column you would need at least 80 million pages (more than 80 million pages if there are actually rows that are physically wider than 4068 bytes since the maximum number of data bytes that will fit on a page). So, it is actually not the number of extents that has maxed out during the table's rebuild/alter but rather the maximum number of pages (2^24 = 16,777,216) that was exceeded. I know that is not the error code you got, but that incorrect error code is a long standing bug in the error reporting code.
    • Since your EXTENT SIZE is only 10240 pages and your NEXT SIZE is only 16 pages, it is quite possible that even with MAX_FILL_DATA_PAGES set you will actually exceed the maximum number of extents for the table before extend compression and extent size doubling can make the experienced next size large enough. You need to set EXTENT SIZE and NEXT SIZE both to something closer to 1,000,000 pages something like at least 100,000 each.
    • With MAX_FILL_DATA_PAGES the engine will be allowed to place a new row on a page as long as at least 10% of the page will be free to allow the rows on the page to expand if they are updated with longer values without having to be moved to a "forwarding page". That might allow the new copy of the table to occupy nearly the same number of pages that it does now, or possibly many fewer pages.

    Art



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

    Original Message


  • 11.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    Posted Wed March 27, 2024 10:44 PM

    Hi, Art,Marcus,Jacob:

          I make sense about your suggestion, it's do help for me. 

          I do the double check, the real alter statement is "alter table epcc_paysrl_attach modify  ordrdesc lvarchar(2458)", customer double the column size. 

          Marcus , reorg is a good suggestion , but we want to do the fragment take advantage of this opportunity.

          I will suggest the customer to do the fragment , and move the table to 16K dbpspace , and after we summary the data pages used,  and will enlarge the first extent size and next extent size also ;   

          jacob， your suggestion can decrease the outage duration of the system,but both the customer and me don't familiar with IIUG website. I am the support team and I don't do the testing myself. 

          Thank you very much for your help



    ------------------------------
    chuan lu
    ------------------------------

    Original Message


  • 12.  RE: alter table modify lvarchar column length report -136 ' no more extents' error

    IBM Champion
    Posted Thu March 28, 2024 06:56 AM

    Chuan:

    A couple of notes:

    If you decide to take Jacob's advice to copy the data to a new table with the new column length and partitioned in a 16K dbspace, I would recommend that instead of unloading the data and reloading it that you copy it directly from the current table to the new one. The absolute fastest way to do that is using my dbcopy utility. Dbcopy is included in the open source package utils2_ak which you can download from my own website (free) at My Utilities The packate also includes several other very useful DBA tools.

    The IIUG (International Informix Users Group) is an advocate for Informix users worldwide providing a voice for us within IBM and HCL (the company that IBM contracts with the actually maintain the Informix code). You should join as a member (free) which will put you on our mailing list (I am a member of the IIUG Board of Directors) which includes our monthly newsletter The Insider. You can join at the IIUG web site: www.iiug.org.

    Note that IBM is running a webinar today at 11:00 AM US Eastern Time. Here is a description of the event and a link to the page on the IIUG site where you can register to attend:

    Hear from the IBM Informix Product Management team and IIUG about new capabilities in IBM Informix v14.10 and why you should upgrade to access better performance, scalability, operational efficiencies, and watsonx AI integrations in the upcoming major release, Informix v15.



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

    Original Message