Number of extents

Dears, We got issue for number of chunks exceed. Is this Informix bug or Informix can not read extent if over 200 extents? if we move the table in other dbspace where dbspace has much space left, will solve the problem without changing the table data and schema? Error Could not insert new row into the table. :ISAM[-136] Message Log: 03/01/21 03:36:46 WARNING: partition 'oltp:informix.cs_ptrg_trns': no more pages Cause: table "cs_ptrg_trns" is big size and cannot auto pages allocation due to number of extents exceed limitation at 200. Kindly revert. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------ #Informix

Amit: What version of Informix are you using? There used to be a soft limit on the number of extents a table could have which varied by pagesize and the number of attached (versus detached) indexes and "special" column (like blobs). That limit was about 240 for a 2K pagesize more for wider page dbspaces. However, that limit was removed in early v11.70 releases and today the number of extents is virtually unlimited (there is a hard limit of 32765 extents but with extent compression and extent size doubling there is no practical way to reach that limit before you hit the maximum pages in a partition limit of 2^24 or 16,777,215 data pages. That is more likely the limit that you have hit and the -136 error is spurious. You DO have a problem, just a different one than you think. oncheck -pT <table> will tell you the number of pages or you can look in sysmaster:sysptnhdr joined to sysmaster:systabnames for the same information. If I am correct, the solutions are one or more of the following approaches (they can be used together in various combinations): move the table to a dbspace with wider pages so that it takes up fewer pages partition the table so that each partition has fewer than 2^24 pages for the forseeable future shard the table across multiple server instances purge old unneeded data Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello, just have a look at the limits for a table fragment regarding the number of records and the number of pages. If one of these limits hit, then you hace to fragment or partition the table. Here a script to check: {##################################################################### ### Check limit for rows and pages ################################### #################################### gerd.kaluzinski@de.ibm.com ###### #####################################################################} database sysmaster; set isolation to dirty read; select first 5 n.tabname[1,20], dbsname[1,8] as db, 4277659295 - h.nrows as rows_left, h.nrows, (round(100 * (4277659295 - h.nrows)/4277659295,2))::char(3) as prc_left from sysptnhdr h, systabnames n where h.partnum = n.partnum and n.tabname[1] != ' ' and h.flags != -2147481599 order by 3; select first 5 n.tabname[1,24], dbsname[1,12] as db, 16777215 - h.nptotal as pages_left, h.nptotal, (round(100 * (16777215 - h.nptotal)/16777215,2))::char(3) as prc_left from sysptnhdr h, systabnames n where h.partnum = n.partnum and n.tabname[1] != ' ' and h.flags != -2147481599 order by 3; ------------------------------ Gerd Kaluzinski ------------------------------

Amit I take it you have never experienced the infamous -136 in a Production environment before. Once experienced, never forgotten. ------------------------------ Kirit Rana ------------------------------

Amith, What version of informix are you running ? Scott Pickett IBM Informix WW Technical Sales IBM Informix WW Cloud Technical Sales IBM Informix WW Cloud Technical Sales ICIAE IBM Informix WW Informix Warehouse Accelerator Sales Boston, Massachusetts USA spickett@us.ibm.com 617-899-7549 33 Years Informix User The current Informix Roadshow presentations are here: https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments All presentations and the agenda used by the Roadshow can be found there. The current internal ZACS Informix Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/

Thanks Art, Currently in my company IDS 11.7 using and may in few months we will upgrade with 14.10 We already using partition table but here each chunk size limited to 2GB only and whole DB size is around 1TB. If we create new dbspacess with higher size like 4K or 8 KB (current 2K only), Can this problem be reduced and will it impact to backup and Restoration as few dbspacess will have different page size? Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Amit: There is no problem or issue with having dbspaces with different pages sizes, no. There are advantages, including: Each page size gets its own buffer pool which can improve performance for busy tables and isolate them from affecting other tables. Indexes perform better on wider pages so some sights have moved all indexes to 16K dbspaces which, again, isolates the index IO from data IO. At checkpoint time IO threads are assigned to clean dirty data by dbspace. More dbspaces, more IO threads. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------