Fragmentation problem

Good morning! in Informix IDS 10 (windows 32 bits), an error occurred while fragmenting a large table. The error was: 776: Alter fragmented error: unable to move row(s) to new fragmentation schema. 772: Record/key doesn't qualify for any table/index fragment.I know what I missed in the fragmentation specification. As we should continue working (production environment) we made the database transactional and we continued. But I don't know what is the state of the table, how do I check if it was fragmented? what happened with the rows that it didn't qualify, how do I alter the fragmentation specification and check if it qualifies the missing rows? Thank you very much in advance ------------------------------ guillermo villanueva ------------------------------

You can run either or both of the following: dbschema -ss -d <database> -t <table> $ dbschema -ss -d art -t with_date -q{ TABLE "art".with_date row size = 8 number of columns = 2 index size = 0 } create table "art".with_date ( one serial not null , two date ) fragment by round robin partition indexdbs_1 in indexdbs, partition indexdbs_2 in indexdbs extent size 8 next size 16 lock mode row; That will show the DDL for the table including any partitioning. oncheck -pT <database>:<table>That will show the storage details of the table including each partition/fragment and indexes: $ oncheck -pT art:with_dateTBLspace Report for art:art.with_date Table fragment partition indexdbs_1 in DBspace indexdbs Physical Address 9:17 Creation date 09/22/2023 07:35:45 TBLspace Flags 100802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 8 Number of special columns 0 Number of keys 0 Number of extents 5 Current serial value 69 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 128 Number of pages allocated 124 Number of pages used 105 Number of data pages 104 Number of rows 17416 Partition partnum 9437198 Partition lockid 9437198 Last DML time Fri Sep 22 07:35:45 2023 Extents Logical Page Physical Page Size Physical Pages 0 9:34372 4 4 4 9:34380 8 8 12 9:34396 16 16 28 9:34428 32 32 60 9:34492 64 64 TBLspace Usage Report for art:art.with_date Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 19 Bit-Map 1 Index 0 Data (Home) 104 ---------- Total Pages 124 Unused Space Summary Unused data slots 56 Unused bytes per data page 4 Total unused bytes in data pages 416 Home Data Page Version Summary Version Count 0 (current) 104 Table fragment partition indexdbs_2 in DBspace indexdbs Physical Address 9:18 Creation date 09/22/2023 07:35:45 TBLspace Flags 100802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 8 Number of special columns 0 Number of keys 0 Number of extents 5 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 128 Number of pages allocated 124 Number of pages used 105 Number of data pages 104 Number of rows 17400 Partition partnum 9437199 Partition lockid 9437198 Last DML time Fri Sep 22 07:35:45 2023 Extents Logical Page Physical Page Size Physical Pages 0 9:34376 4 4 4 9:34388 8 8 12 9:34412 16 16 28 9:34460 32 32 60 9:34556 64 64 TBLspace Usage Report for art:art.with_date Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 19 Bit-Map 1 Index 0 Data (Home) 104 ---------- Total Pages 124 Unused Space Summary Unused data slots 72 Unused bytes per data page 4 Total unused bytes in data pages 416 Home Data Page Version Summary Version Count 0 (current) 104 ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thank you very much Art ------------------------------ guillermo villanueva ------------------------------

Was your expression fragmentation missing an expression range for certain rows, or simply a REMAINDER fragment? ------------------------------ Andreas Legner ------------------------------

Inadvertently, we omitted a range. We checked how the table is now, and apparently with the error, it did a rollback, the table is not fragmented. ------------------------------ guillermo villanueva ------------------------------

Hi Guillermo, I agree with Art's workflow. dbschema -ss -d dbname -t tabname > to check the current schema definition and maybe the fragmentation strategyand oncheck -pt dbanem:tabname > to check the amount of data and pages in the data and index fragmentsThen you have a status and can start your own/new fragmentation strategy (e.g. with "INIT" ). But think about a newer release - e.g. 12.10 should be available for Windows 32 too (but I'm not sure). Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 2022 2023 IIUG Board Member henri.cujass@leolo.com ------------------------------