Oninit cdi / Archive
Talk to an expert →

Re: How can I disable a foreign key???

Posted in 2006

Topics: Storage & Space Management, Triggers, Constraints & Referential Integrity, Migration, Import/Export & Data Conversion

Art S. Kagel — — source: Usenet: comp.databases.informix

Diego Morales wrote: > Hi to all. I have problems to unload/load a table to set extents > correctly (I have table with more than 500,000 records and more than 100 > extents). I need to disable foreign keys to unload and load the main > table but I don't know how. Please help me. Diego, you do not have to unload/reload the table to compress the extents. The following will do it. 1) ALTER TABLE mytable NEXT SIZE <newsize>; 2) ALTER FRAGMENT ON TABLE mytable INIT IN <dbspace or fragmentation expression>; The dbspace/expression can be the same one the table already uses as long as there is space for a 2nd copy of the table temporarily. You should end up with one or at most 2 extents - assuming the table is smaller than a chunk - or one per chunk if it is larger unless that dbspace is badly fragmented. In which case doing this 2x for each table in the dbspace or moving the table(s) to an empty dbspace will solve that. Art S. Kagel

↪ replying to Art S. Kagel
Diego Morales — — source: Usenet: comp.databases.informix

Interesting... this "alter fragment" works on IDS 7.31??? -----Mensaje original----- De: KAGEL@BLOOMBERG.NET [mailto:KAGEL@BLOOMBERG.NET] Enviado el: martes, 28 de noviembre de 2006 0:31 Para: Diego Morales CC: informix-list@iiug.org Asunto: Re: How can I disable a foreign key??? Diego Morales wrote: > Hi to all. I have problems to unload/load a table to set extents > correctly (I have table with more than 500,000 records and more than 100 > extents). I need to disable foreign keys to unload and load the main > table but I don't know how. Please help me. Diego, you do not have to unload/reload the table to compress the extents. The following will do it. 1) ALTER TABLE mytable NEXT SIZE <newsize>; 2) ALTER FRAGMENT ON TABLE mytable INIT IN <dbspace or fragmentation expression>; The dbspace/expression can be the same one the table already uses as long as there is space for a 2nd copy of the table temporarily. You should end up with one or at most 2 extents - assuming the table is smaller than a chunk - or one per chunk if it is larger unless that dbspace is badly fragmented. In which case doing this 2x for each table in the dbspace or moving the table(s) to an empty dbspace will solve that. Art S. Kagel

Glossary