TBLSpace No more extent 8GB limit

Hi ! Is there any way to defragment and shrink TBLSpace in some dbspace (NOT in tables in database) ? I have message "No more extent" when I try to create any table in any database in this dbspace ... I think there is a limit in size of 8GB on TBLSpace in one dbspace. With command oncheck -pe | grep TBLSpace- I see this extents and I wonder hot to defragment them ? This is develop server, with more than 50 databases and every database has more than 4.000 tables, 15.000 indexes and so on ... I know that solution is create new dbspace and export/drop/import database from one to another dbspace ... but I wonder there is a way to defragment existing TBLSpace ? Version is 12.10.FC4W1WE on Linux. ------------------------------ Ivan Zavis ------------------------------ #Informix

Not that I am aware of, I think there is a RFE for this though Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Could be number of fragments (in which case unavailable defrag might be helpful), could be number of pages - the error number/message doesn't really differentiate the two cases. Pls. post 'oncheck -pt $(((X*0x100000)+1))', with X being the dbspace number. Should number of allocated/used pages be those 16M, then you'd be hitting the max-pages-per-partition limit. ::DISCLAIMER:: The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.

Thank you, here it is: Problem is limit of 8GB size. Maybe is better to create smaller extent, not so big like I created ... I can't believe that all extent is full of data ? Physical Address 7:16 Creation date 09/09/2014 13:02:25 TBLspace Flags 2801 Page Locking TBLspace use 4 bit bit-maps Permanent System TBLspace Maximum row size 136 Number of special columns 0 Number of keys 0 Number of extents 14 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 8 First extent size 128000 Next extent size 128000 Number of pages allocated 1048575 Number of pages used 1048575 Number of data pages 0 Number of rows 1005927 Partition partnum 7340033 Partition lockid 7340033 Extents Logical Page Physical Page Size Physical Pages 0 7:12 128000 512000 128000 8:20665564 64000 256000 192000 9:29339208 64000 256000 256000 10:40782128 64000 256000 320000 7:12483676 64000 256000 384000 10:48732344 64000 256000 448000 12:11775328 64000 256000 512000 12:18949252 64000 256000 576000 7:35648364 64000 256000 640000 8:35558324 64000 256000 704000 10:4182188 64000 256000 768000 10:18211932 128000 512000 896000 12:27456972 128000 512000 1024000 8:10882736 24575 98300 ------------------------------ Ivan Zavis ------------------------------

... and when I try to defragment TBLspace by his partnum, it is impossibile: -21531 Defragment: A partition that contains a partition header page table cannot be defragmented. ACTION Do not attempt to run the defragmenter on the partition header page table - this is not allowed. ------------------------------ Ivan Zavis ------------------------------

I am very confused. You are getting the "no more extents" error which either means that a) you have exceeded the maximum number of extents which is 1024, or b) the maximum number of pages which is 16,777,216. My confusion is that according to the oncheck output header you have only 14 extents and about 1 million pages in this tablespace. The only thing I can think of is that the dbspace is full? I might think that maybe the tablespace doesn't support the extended extent page, but even so the old partition header pages diastole about 600 extents on a single page, so ... still confused. Anyone from HCL around to comment?

My only logical conclusion is that there is a limit in size on TBLspace for one dbspace and this is 8GB which is enough on normal work ... but i have to many databases on that dbspace. ------------------------------ Ivan Zavis ------------------------------

The limit that's effective here is 2**20, i.e. 1M (1048575 = 0xfffff) pages per TBLSpace TBLSpace. (The 16M limit applies mentioned earlier applies to all other non-index partitions - while pure index partitions allow up to 2B(illion) pages.) Keep in mind how a 'partnum' (partition identifier) is constructed: Hex: 0xDDDLLLLL, with DDD being the dbspace number and LLLLL being the logical position within a dbspace's TBLSpace TBLSpace. The LLLLL determine the 0xFFFFF limit or partitions per dbspace. BR, Andreas ------------------------------ Andreas Legner ------------------------------

Seems that I reached the limit because I have 8k page size, and 8GB size of extents. 1Milions page * 8Kb = 8GB That is. Thank you Andreas. ------------------------------ Ivan Zavis ------------------------------

Hi Ivan, As far as I understood, Developer Edition, starting V14.10FC2, has now only one restriction, which is "No use in any production system". All the other limitations seem to have gone, according to the license document, including the storage limitations. http://www-03.ibm.com/software/sla/sladb.nsf/lilookup/0F4D5A657DDAB16D85258472005339CE?OpenDocument Although this won't directly solve your problem due to the max number of extents according to the dbspace page size, it will indirectly because you now have more space to create other dbspaces and fragment your tables. So I would say upgrade to developer Edition 14.10 FC2 you can find it here https://www-01.ibm.com/marketing/iwm/iwm/web/pickUrxNew.do?source=ifxids&transactionid=455937504 Hope this helps Eric ------------------------------ [eric] [Verceletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] ------------------------------

Could be number of extents (in which case 'defragment' would help), could be number of pages - the error doesn't really differentiate between the two. Can you post 'oncheck -pt $(((X*0x100000)+1))', with X being the dbspace number? Should number of pages allocated be those 16mio pages, you'd simply be hitting the pages-per-partition limit. ------------------------------ Andreas Legner ------------------------------

Hello Eric, Interested in your comment "all the other limitations seem to have gone". Can you (or someone else who may know) please clarify: Is the maximum data pages per table/fragment limit of 16,775,134 gone in 14.10.FC2? (not 100% related but it is important for us) is the "maximum output row size" limit of 32767 bytes (error -528) gone in 14.10.FC2? Thank you, Hal Maner M Systems International, Inc. www.msystemsintl.com

Hi Hal, Sorry I will rephrase: all the resource limitations specific to the Developer Edition have gone, as per reading the license document of Developer Edition 14.10 FC2. This means no more storage limit ( that was restricted to 8 or 10 Gb), no more CPU VP limitations and no more ShMEM limitations. This means now we can use Dev Edition for significant test with significant data sets ... as long as it is not in a production environment. Max data pages per fragment has not been increased AFAIK, only in our wildest dreams for now. Eric Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting Board of Directors, International Informix Users group IBM Champion 2013,2014,2015,2016,2017,2018,2019 Ne manquez pas Informix Roadshow Paris 2019 Evènement technique gratuit chez IBM à Bois Colombes 17 et 18 Octobre 2019 inscrivez vous à https://www.eventbrite.com/e/informix-roadshow-paris-2019-tickets-74598755887 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org

Hal: No, neither of the systemic limits you have mentioned have been removed. IB that Eric was referring to several of the resource usage limits placed specifically on Developer Edition having been removed as of the 14.10.FC2 release. There is an RFE to eliminate or at least expand the 16million page limit on all Informix servers. You might want to vote for that RFE. As far as I know there has been no request to expand the maximum in-row data limit. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Here is the RFE in question: very weirdly, it only has 4 votes... https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-31 just vote for it and share! ------------------------------ [eric] [Verceletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] ------------------------------

Thank you Art, will do. I created an RFE recently for lifting the maximum output row size 32767 limit. After I created mine (and even though I looked for a similar one before creating mine and did not find), I found that another similar request was already there with several votes and I voted for that one also. I also found quite a bit of complaints online, similar to mine, that databases such as Postgresql, Cloudscape, etc. do not have such a limit (or have a much higher one) and Informix does. Hal

As for " can't believe that all extent is full of data": these TBLSpace TBLSpace extents are filled with so called partition header pages which come in multiple flavors: - primary partition header pages - one for every partition/fragment in this dbspace - secondary partition header pages, mainly for storing 'in-place alter' (IPA) info - can be multiple per DATA partition/fragment, depending on how much ALTER TABLE activity these tables undergo (and how much of that can be handled the IPA way.) ------------------------------ Andreas Legner ------------------------------