Table and index defragment

Hi, I am having some problems with fragmented tables over several customers, as I have several indexes and constraints is there any way to achieve this problem without having to reconstruct everything? Is there a procedure in sysadmin for this? Has anyone had similar problems that can help me? Thanks for any help, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

So you want to turn a set of fragmented tables into non-fragmented tables, without a lot of interruption and without having to (spend the time to) rebuild everything (indices, constraints)? How about creating the new tables side by side with the old ones, with slightly different names, then moving the data and keeping it synchronized using ''loopback replication" (local ER, within same database) until everything's fine and you can switch new for old tables by means of renaming? (I'd hope RI constraints will survive this.) HTH, Andreas ------------------------------ Andreas Legner ------------------------------

This is an interesting solution, regarding the index propagation features that exist since version 12.10.xC12W1 ... I would like to have some details of the configuration for local ER, because all the attempts I made to configure the local ER so far, none worked .. !! SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

What are the problems ? ------Original Message------ Hi, I am having some problems with fragmented tables over several customers, as I have several indexes and constraints is there any way to achieve this problem without having to reconstruct everything? Is there a procedure in sysadmin for this? Has anyone had similar problems that can help me? Thanks for any help, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

Let us know about those 'loopback replication' problems. There was a thread recently on " Asynchronous post commit triggers & loopback replication ERROR " that started out with a loopback replication definition problem, maybe that can help too. Andreas ------------------------------ Andreas Legner ------------------------------

First of all a quick setup guide, considering the various information I found I was never able to establish a connection to the same server ... ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

First of all a quick setup guide, considering the various information I found I was never able to establish a connection to the same server ... ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

@Sergio Peres I haven't read the parallel ​thread, but doesn't "ALTER FRAGMENT <table name> INIT IN < dbspace name>;" not do it for you?​​​ ------------------------------ Kirit Rana ------------------------------

Sergio: Are you asking about changing a partitioned table or index (ie created with FRAGMENT BY ...) to a non-partitioned table/index or are you asking about tables with too many non-contiguous extents that need to be reorganized ie defragmented? 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. ------Original Message------ Hi, I am having some problems with fragmented tables over several customers, as I have several indexes and constraints is there any way to achieve this problem without having to reconstruct everything? Is there a procedure in sysadmin for this? Has anyone had similar problems that can help me? Thanks for any help, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

yes it works, but as in most instances I only have 1 dbspace, that limits the option. although I can always use external tables, my concern is the indexes and constraints. ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Hi Art, My problem are tables with too many non-contiguous extents and their reorganization. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

onconfig: --------- DBSERVERNAME serv_a1 DBSERVERALIASES serv_a2 CDR_QDATA_SBSPACE some_sbspace(s) sqlhosts: --------- er_serv group - - i=1serv_a1 onsoctcp myhost service_a1 g=er_serv er_loop group - - i=2 serv_a2 onsoctcp myhost service_a2 g=er_loop server definitions: -------------------- cdr define server er_serv -I cdr define server er_loop -I -S er_serv -N Important points: the port used for the 'loopback' pseudo ER server (serv_a2) can't be the same as needs to occur after the one used for the 'real' ER server (serv_a1) in DBSERVERNAME/DBSERVERALIASES list the pseudo server has to be defined second, i.e. into an existing ER domain (of at least one server, the local 'real' one), 'syncing' itself from the real local server (same instance): -S er_serv the pseudo server has to be defined as 'non-root' server under that real server (-N) From there on you can use the two ER nodes (er_serv and er_loop), e.g. in replicate definitions, (almost) the same way as any other ER nodes. ------------------------------ Andreas Legner ------------------------------

OK, so you have two good options. You can use the ALTER FRAGMENT ... INIT IN ... option which can be used to relocate a table of index EVEN into the same dbspace in which it already resides. But that requires some downtime to copy the table. The other is to use the DEFRAGMENT API function: execute function task( 'defragment', 'database:"owner".table' );or execute function task( 'defragment partnum', <partnum> );These are restartable and online tasks. It is safe even if the server crashes (and you can manually restart the defragment after the server returns) and does not lock anything for more than a fraction of a second. It does not always succeed in reducing a partition to a single extent, but it will usually reduce the number of extents if there is sufficient free space in the dbspace to do that. You can combine this with using REPACK SHRINK on other tables to coalesce fragmented free space between extents in other tables into a smaller number of larger free blocks and then defragment again. The onstat -g defragment command reports the status of all active defragment runs. 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. ------Original Message------ Hi Art, My problem are tables with too many non-contiguous extents and their reorganization. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

So look here... Attached indexes become non-fragmented indexes in the same dbspace as the new non-fragmented table. Constraints that do not use Detached indexes become non-fragmented indexes in the same dbspace as the new non-fragmented table. Fragmentation strategy of Detached indexes and Constraints that use Detached indexes remain unchanged. Don't worry about having just the one dbspace as you can re-organise into the same dbspace. Just ensure your temporary dbspace(s) are large enough to hold the entire table at least twice over. Hope it helps. ------------------------------ Kirit Rana ------------------------------

I have used this procedure, but only defragments tables and indexes with a name, what I cannot defragment constraints created automatically by the engine, a s they have space at the beginning. ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Thanks Andreas, I will test it, thats the information I was looking for :) SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Sergio: You can use the DEFRAGMENT PARTNUM option for auto-named indexes! Also, I don't remember what release you are using, but you can rename auto-named indexes in releases 12.10.xC8 and later: RENAME INDEX 100_1 TO idx1; That will work to rename the index named " 100_1" to "idx1". You can also get the partnum(s) for an index, even if it is an auto-generated index, from sysmaster:systabnames: > select * from systabnames where tabname = ' 111_23';partnum 5243076 dbsname timeseries owner informix tabname 111_23 collate en_US.819 dbsnum 5 partnum 5244283 dbsname art owner informix tabname 111_23 collate en_US.819 dbsnum 5 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. ------Original Message------ I have used this procedure, but only defragments tables and indexes with a name, what I cannot defragment constraints created automatically by the engine, a s they have space at the beginning. ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

My problem is that we have more than 3000 tables on each database and fragmentation levels are different over customers and tables. I am trying to find any way to do it over several on the same way. We have more than 150 instances and don't have direct access. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------