Oninit cdi / Archive
Talk to an expert →

Maximum amount number of pages reached

Posted in 2018

Topics: Storage & Space Management, Data Types & Schema Design, Migration, Import/Export & Data Conversion, Versions, Editions & End-of-Life

JORGE GALAN — — source: IIUG Forums & Mailing Lists

Hello every body¡ I have reached the limit of pages to table in informix, and my application stops. I unload data from the table and so delete it. So, my application works again. I appreciate any help oncheck output: TBLspace Report for efectivalefe:summalogin.venmovimientosfd Physical Address 3:6701958 Creation date 11/21/2017 17:25:28 TBLspace Flags d01 Page Locking TBLspace contains VARCHARS TBLspace contains TBLspace BLOBs TBLspace use 4 bit bit-maps Maximum row size 1065 Number of special columns 6 Number of keys 0 Number of extents 7 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 1024 Next extent size 2097152 Number of pages allocated 16220159 <------------------ Number of pages used 15407009 <------------------ Number of data pages 750995 Number of rows 1766611 Partition partnum 3155669 Partition lockid 3155669 [informix@usatlinfo1 ~]$ onstat - IBM Informix Dynamic Server Version 11.70.FC7GE -- On-Line -- Up 169 days 04:30:30 -- 2764472 Kbytes

↪ replying to JORGE GALAN
Habichtsberg, — — source: IIUG Forums & Mailing Lists

Hi, you could fragment the table by round robin or by expression. "Alter fragment on table..." or create a new table with fragments or partitions and move the content from old to new table. Or you compress the table. Or create a dbspace with pagesize greater then 2 (4, 8 or 16) and recreate the table in that dbspace. HTH Reinhard. -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von JORGE GALAN Gesendet: Donnerstag, 11. Januar 2018 18:07 An: ids@iiug.org Betreff: Maximum amount number of pages reached [40487] Hello every body¡ I have reached the limit of pages to table in informix, and my application stops. I unload data from the table and so delete it. So, my application works again. I appreciate any help oncheck output: TBLspace Report for efectivalefe:summalogin.venmovimientosfd Physical Address 3:6701958 Creation date 11/21/2017 17:25:28 TBLspace Flags d01 Page Locking TBLspace contains VARCHARS TBLspace contains TBLspace BLOBs TBLspace use 4 bit bit-maps Maximum row size 1065 Number of special columns 6 Number of keys 0 Number of extents 7 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 1024 Next extent size 2097152 Number of pages allocated 16220159 <------------------ Number of pages used 15407009 <------------------ Number of data pages 750995 Number of rows 1766611 Partition partnum 3155669 Partition lockid 3155669 [informix@usatlinfo1 ~]$ onstat - IBM Informix Dynamic Server Version 11.70.FC7GE -- On-Line -- Up 169 days 04:30:30 -- 2764472 Kbytes *******************************************************************************Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Habichtsberg,
Alexandre Marini — — source: IIUG Forums & Mailing Lists

Sorry, he is not able to fragment or compress (growth edition). His version is 11.70.FC7GE. Hope it helps. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de Habichtsberg, Reinhard <RHabichtsberg@arz-emmendingen.de> Enviado: quinta-feira, 11 de janeiro de 2018 15:37 Para: ids@iiug.org Assunto: AW: Maximum amount number of pages reached [40488] Hi, you could fragment the table by round robin or by expression. "Alter fragment on table..." or create a new table with fragments or partitions and move the content from old to new table. Or you compress the table. Or create a dbspace with pagesize greater then 2 (4, 8 or 16) and recreate the table in that dbspace. HTH Reinhard. -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von JORGE GALAN Gesendet: Donnerstag, 11. Januar 2018 18:07 An: ids@iiug.org Betreff: Maximum amount number of pages reached [40487] Hello every body¡ I have reached the limit of pages to table in informix, and my application stops. I unload data from the table and so delete it. So, my application works again. I appreciate any help oncheck output: TBLspace Report for efectivalefe:summalogin.venmovimientosfd Physical Address 3:6701958 Creation date 11/21/2017 17:25:28 TBLspace Flags d01 Page Locking TBLspace contains VARCHARS TBLspace contains TBLspace BLOBs TBLspace use 4 bit bit-maps Maximum row size 1065 Number of special columns 6 Number of keys 0 Number of extents 7 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 1024 Next extent size 2097152 Number of pages allocated 16220159 <------------------ Number of pages used 15407009 <------------------ Number of data pages 750995 Number of rows 1766611 Partition partnum 3155669 Partition lockid 3155669 [informix@usatlinfo1 ~]$ onstat - IBM Informix Dynamic Server Version 11.70.FC7GE -- On-Line -- Up 169 days 04:30:30 -- 2764472 Kbytes *******************************************************************************Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to JORGE GALAN
John Miller — — source: IIUG Forums & Mailing Lists

=0A=0AI would do a few things,=0A=0A1. Set the onconfig MAX_FILL_DATA_PAGE= S because you have varchars=0Athis will spend more time finding free spac= e. You can run an oncheck=0A-pT dbs:table to see how much free space is n= ow used.=0A=0A2. Because this is pages you can double or more the storage = space by=0Agoing to a larger page size for this table. =0A=0ADoing a littl= e math Num Pages Used / Number of Rows =3D 15407009 /=0A1766611 =3D 87 P= ages/ Row=0A=0ABy moving to a large page size you can reduce the I/O GREATL= Y beside=0Aallowing more rows. Currently you are doing 44 IO per insert, y= ou can=0Adrop that to less than 6 by increasing your page size to 16KB=0A= =0A=0AHope it helps,=0AJohn=0A=0A=0A=0A-------- Original Message --------= =0ASubject: Maximum amount number of pages reached [40487]=0AFrom: "JORGE G= ALAN" <jorge.galan@efectivale.com.mx>=0ADate: Thu, January 11, 2018 9:06 am= =0ATo: ids@iiug.org=0A=0AHello every body? =0A=0AI have reached the limit o= f pages to table in informix, and my=0Aapplication =0Astops. =0A=0AI unload= data from the table and so delete it. =0A=0ASo, my application works again= . =0A=0AI appreciate any help =0A=0Aoncheck output: =0A=0ATBLspace Report f= or efectivalefe:summalogin.venmovimientosfd =0A=0APhysical Address 3:670195= 8 =0A=0ACreation date 11/21/2017 17:25:28 =0A=0ATBLspace Flags d01 Page Loc= king =0A=0ATBLspace contains VARCHARS =0A=0ATBLspace contains TBLspace BLOB= s =0A=0ATBLspace use 4 bit bit-maps =0A=0AMaximum row size 1065 =0A=0ANumbe= r of special columns 6 =0A=0ANumber of keys 0 =0A=0ANumber of extents 7 =0A= =0ACurrent serial value 1 =0A=0ACurrent SERIAL8 value 1 =0A=0ACurrent BIGSE= RIAL value 1 =0A=0ACurrent REFID value 1 =0A=0APagesize (k) 2 =0A=0AFirst e= xtent size 1024 =0A=0ANext extent size 2097152 =0A=0ANumber of pages alloca= ted 16220159 <------------------ =0A=0ANumber of pages used 15407009 ><----= -------------- =0A=0ANumber of data pages 750995 =0A=0ANumber of rows 17666= 11 =0A=0APartition partnum 3155669 =0A=0APartition lockid 3155669 =0A=0A[in= formix@usatlinfo1 ~]$ onstat - =0A=0AIBM Informix Dynamic Server Version 11= .70.FC7GE -- On-Line -- Up 169=0Adays =0A04:30:30 -- 2764472 Kbytes =0A=0A= =0A************************************************************************= *******=0A=0A Forum Note: Use "Reply" to post a response in the discussion = forum. =0A=0A>

Glossary