Informix

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Original Message------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique
------------------------------

Original Message:
Sent: Sun May 17, 2020 11:09 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

You do not have to go through that old procedure, nor do you have to export and import the table (nor the whole database) if you up grade to a modern release of Informix! First, in v7.31, while you cannot change the FIRST extent size, you can change the NEXT SIZE (later releases do let you change the first extent size which is helpful when using the built-in defragmentation features in later releases.

That said, since you are currently stuck in v7.31, let me address you direct issues:

Your questions are the following:
1 - Have you successfully carried out this procedure?

Many times in various different versions of the operation.

2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.

Yes. You have to drop all constraints and indexes OR create the new ones on the new table with different names.

3 - Do you have any alternative solution to this problem? 

Yes, you can use:

ALTER TABLE <tablename> NEXT SIZE <large extent size>;
ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspace>; 

The dbspace can be any dbspace, even the same space that the table currently resides in. However, if you use an new empty dbspace then the new table will have a single large contiguous extent because of extent coalescing (contiguous extents are merged into a single extent), or at least one for each chunk that the table has to span if it is bigger than a single chunk in the dbspace. Since this is completely carried out inside the engine's code, it is faster than copying the data from the original table to a new one. It will be even a bit faster if you first drop the constraints and make the table RAW before the reorg then alter it back to STANDARD afterwards, recreate the constraints, then take an archive (RAW tables are not logged, so you will need the archive in order to be able to recover.

Oh, and DO NOT TRY THE oncheck -me option, it doesn't always work and can corrupt the table!

If you really want to improve performance, you may want to try fragmenting the table across several dbspaces so that the engine can ignore the fragment(s) that contain older data that you no longer need access to.

Again, the current release, version 14.10, offers several other options and you can get a VERY inexpensive computer to run it on that will be far faster than what you have now. Feature-wise you can probably get away with using the Workgroup Edition or maybe even the Express Edition.

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------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Original Message------

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique
------------------------------

#Informix

Thanks again Art!

I did a test with one of the big tables in a pre-test environment, and the engine stopped the process by "long transaction", with "onstat -s -L 0 -N" I passed it to "no logging", and there it was possible complete the process.

Does this ALTER keep the Foreign Keys of the other tables intact ?, because I did it without deleting and re-creating them.

Will it help speed up the base dbexport process? I ask you this because it is the only way to back up the information that I have, and I do not have a sufficient time window with the current performance of the engine.

A hug!

------------------------------
Gustavo Echenique
------------------------------

Original Message:
Sent: Mon May 18, 2020 07:49 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

Addressing your follow-up questions:

1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.

You COULD do that, but you do not have to. The ALTER FRAGMENT command can reorg the table into the same dbspace that it already lives in. The only downsides of doing that (instead of using another dbspace) are: a) the dbspace has to have at least enough free space to hold the new copy of the table concurrently with the original copy during the ALTER operation, and b) that if the free space in the current dbspace is fragmented (look at the oncheck -pe report to see whether this will be a problem) then the ALTER may recreate the table with worse fragmenting that it already had. If there is a large contiguous chunk of free space then if you make the NEXT SIZE big enough then the table may have as few as one or two extents.

2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?

See #1, but if you do temporarily move the table to another dbspace then move it back, the result may not be any better than you originally had. Again see #1.

3 - Does this process also copy the indexes of the tables ?.

The indexes are rebuilt after the table as part of the ALTER if that's what you are asking. If the indexes were created with an IN dbspace clause then they will stay where they are (you could ALTER FRAGMENT on the indexes too if you want to actually move them to another dbspace). But if the index was created ATTACHED to the table (either create before 7.30 without an IN clause or created with the IN TABLE clause) then they will follow the table to its new location.

4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

OK, no. You want to make the NEXT SIZE big enough to hold all of the data that is normally accessed together (so a full year's worth if you typically report on a whole year of data for example) so that only one or two extents are actually accessed for those big reports no matter how many extents you actually end up with. When the table is rebuild with ALTER FRAGMENT the engine will create an initial extent based on the current EXTENT SIZE which you cannot change in v7.31 (as I mentioned you can in modern releases) then the NEXT SIZE will be used for all of the following extents. If there are blocks of free space that are at least as large as the NEXT SIZE then that's good. If two extents happen to be contiguous the engine will concatenate them into one bigger extent. So, setting NEXT SIZE is crucial to reducing the number of extents during the ALTER FRAGMENT processing.

I hope that helps.

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------

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique
------------------------------

#Informix

Original Message------

Thanks again Art!

I did a test with one of the big tables in a pre-test environment, and the engine stopped the process by "long transaction", with "onstat -s -L 0 -N" I passed it to "no logging", and there it was possible complete the process.

Does this ALTER keep the Foreign Keys of the other tables intact ?, because I did it without deleting and re-creating them.

Will it help speed up the base dbexport process? I ask you this because it is the only way to back up the information that I have, and I do not have a sufficient time window with the current performance of the engine.

A hug!

------------------------------
Gustavo Echenique
------------------------------

#Informix

Gustavo:

The app in question doesn't happen to be SAP, does it?

------------------------------
TOM GIRSCH
------------------------------

Original Message:
Sent: Mon May 18, 2020 07:17 PM
From: Gustavo Echenique
Subject: Table resizing queries

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique

Original Message:
Sent: Sun May 17, 2020 11:09 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

You do not have to go through that old procedure, nor do you have to export and import the table (nor the whole database) if you up grade to a modern release of Informix! First, in v7.31, while you cannot change the FIRST extent size, you can change the NEXT SIZE (later releases do let you change the first extent size which is helpful when using the built-in defragmentation features in later releases.

That said, since you are currently stuck in v7.31, let me address you direct issues:

Your questions are the following:
1 - Have you successfully carried out this procedure?

Many times in various different versions of the operation.

2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.

Yes. You have to drop all constraints and indexes OR create the new ones on the new table with different names.

3 - Do you have any alternative solution to this problem? 

Yes, you can use:

ALTER TABLE <tablename> NEXT SIZE <large extent size>;
ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspace>; 

The dbspace can be any dbspace, even the same space that the table currently resides in. However, if you use an new empty dbspace then the new table will have a single large contiguous extent because of extent coalescing (contiguous extents are merged into a single extent), or at least one for each chunk that the table has to span if it is bigger than a single chunk in the dbspace. Since this is completely carried out inside the engine's code, it is faster than copying the data from the original table to a new one. It will be even a bit faster if you first drop the constraints and make the table RAW before the reorg then alter it back to STANDARD afterwards, recreate the constraints, then take an archive (RAW tables are not logged, so you will need the archive in order to be able to recover.

Oh, and DO NOT TRY THE oncheck -me option, it doesn't always work and can corrupt the table!

If you really want to improve performance, you may want to try fragmenting the table across several dbspaces so that the engine can ignore the fragment(s) that contain older data that you no longer need access to.

Again, the current release, version 14.10, offers several other options and you can get a VERY inexpensive computer to run it on that will be far faster than what you have now. Feature-wise you can probably get away with using the Workgroup Edition or maybe even the Express Edition.

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------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Hello Tom!

No, it is not SAP. It is a development of an Argentine company.


------------------------------
Gustavo Echenique
------------------------------

Original Message:
Sent: Tue May 19, 2020 09:53 AM
From: TOM GIRSCH
Subject: Table resizing queries

Gustavo:

The app in question doesn't happen to be SAP, does it?

------------------------------
TOM GIRSCH

Original Message:
Sent: Mon May 18, 2020 07:17 PM
From: Gustavo Echenique
Subject: Table resizing queries

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique

Original Message:
Sent: Sun May 17, 2020 11:09 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

You do not have to go through that old procedure, nor do you have to export and import the table (nor the whole database) if you up grade to a modern release of Informix! First, in v7.31, while you cannot change the FIRST extent size, you can change the NEXT SIZE (later releases do let you change the first extent size which is helpful when using the built-in defragmentation features in later releases.

That said, since you are currently stuck in v7.31, let me address you direct issues:

Your questions are the following:
1 - Have you successfully carried out this procedure?

Many times in various different versions of the operation.

2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.

Yes. You have to drop all constraints and indexes OR create the new ones on the new table with different names.

3 - Do you have any alternative solution to this problem? 

Yes, you can use:

ALTER TABLE <tablename> NEXT SIZE <large extent size>;
ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspace>; 

The dbspace can be any dbspace, even the same space that the table currently resides in. However, if you use an new empty dbspace then the new table will have a single large contiguous extent because of extent coalescing (contiguous extents are merged into a single extent), or at least one for each chunk that the table has to span if it is bigger than a single chunk in the dbspace. Since this is completely carried out inside the engine's code, it is faster than copying the data from the original table to a new one. It will be even a bit faster if you first drop the constraints and make the table RAW before the reorg then alter it back to STANDARD afterwards, recreate the constraints, then take an archive (RAW tables are not logged, so you will need the archive in order to be able to recover.

Oh, and DO NOT TRY THE oncheck -me option, it doesn't always work and can corrupt the table!

If you really want to improve performance, you may want to try fragmenting the table across several dbspaces so that the engine can ignore the fragment(s) that contain older data that you no longer need access to.

Again, the current release, version 14.10, offers several other options and you can get a VERY inexpensive computer to run it on that will be far faster than what you have now. Feature-wise you can probably get away with using the Workgroup Edition or maybe even the Express Edition.

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------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

OK. The reason I asked is because despite it not officially being supported by SAP, I was successfully able to upgrade our back-end Informix database to 12.10, and not only is functionality still okay, performance is a fair bit better in some cases.

In my case, I was able to take the risk because both the SAP version we were using and the DB version were end of life and unsupported anyway. Even very new versions of the database engine retain backward compatibility with older ones, except in some extreme edge cases. So it's something you may want to at least consider testing.

------------------------------
TOM GIRSCH
------------------------------

Original Message:
Sent: Tue May 19, 2020 10:29 AM
From: Gustavo Echenique
Subject: Table resizing queries

Hello Tom!

No, it is not SAP. It is a development of an Argentine company.


------------------------------
Gustavo Echenique

Original Message:
Sent: Tue May 19, 2020 09:53 AM
From: TOM GIRSCH
Subject: Table resizing queries

Gustavo:

The app in question doesn't happen to be SAP, does it?

------------------------------
TOM GIRSCH

Original Message:
Sent: Mon May 18, 2020 07:17 PM
From: Gustavo Echenique
Subject: Table resizing queries

Hello Art!

I sensed that you were going to respond quickly to my queries. It is something I always appreciate.

Regarding migration to a new version of Informix, in this case 14.10, it will be impossible, since the provider of our application has a new version of its application that works with Oracle. For this reason, until the new system is implemented, I must keep the current one running with Informix.

This is something that frustrates me since Informix seems to me to be a much better database engine than Oracle. But unfortunately, there is nothing I can do to change the situation.

Returning to the topic of my query, I am interested in the idea of ​​making an ALTER to the tables and creating them in another DBSPACE. Related to this, I have some doubts:
1 - After creating the tables in the new DBSPACE, can I recreate the tables in the original DBSPACE ?. I suppose so, using the same command that was used to move them from DBSPACE. I ask this because all my tables are in a single DBSPACE. I understand this would involve doing the job 2 times for each table, but I would like to keep all the tables together.
2 - Assuming that when you return the table to its original DBSPACE, it runs out of space, how could I solve it?
3 - Does this process also copy the indexes of the tables ?.
4 - The NEXT SIZE value could be indicated as a percentage ratio of the size of each table ?. This because the table would already be compacted by calling it somehow.

Forgive me for so many questions, but I am extremely suspicious and cautious when working in a production environment.

Thanks for your reply again !!!

------------------------------
Gustavo Echenique

Original Message:
Sent: Sun May 17, 2020 11:09 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

You do not have to go through that old procedure, nor do you have to export and import the table (nor the whole database) if you up grade to a modern release of Informix! First, in v7.31, while you cannot change the FIRST extent size, you can change the NEXT SIZE (later releases do let you change the first extent size which is helpful when using the built-in defragmentation features in later releases.

That said, since you are currently stuck in v7.31, let me address you direct issues:

Your questions are the following:
1 - Have you successfully carried out this procedure?

Many times in various different versions of the operation.

2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.

Yes. You have to drop all constraints and indexes OR create the new ones on the new table with different names.

3 - Do you have any alternative solution to this problem? 

Yes, you can use:

ALTER TABLE <tablename> NEXT SIZE <large extent size>;
ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspace>; 

The dbspace can be any dbspace, even the same space that the table currently resides in. However, if you use an new empty dbspace then the new table will have a single large contiguous extent because of extent coalescing (contiguous extents are merged into a single extent), or at least one for each chunk that the table has to span if it is bigger than a single chunk in the dbspace. Since this is completely carried out inside the engine's code, it is faster than copying the data from the original table to a new one. It will be even a bit faster if you first drop the constraints and make the table RAW before the reorg then alter it back to STANDARD afterwards, recreate the constraints, then take an archive (RAW tables are not logged, so you will need the archive in order to be able to recover.

Oh, and DO NOT TRY THE oncheck -me option, it doesn't always work and can corrupt the table!

If you really want to improve performance, you may want to try fragmenting the table across several dbspaces so that the engine can ignore the fragment(s) that contain older data that you no longer need access to.

Again, the current release, version 14.10, offers several other options and you can get a VERY inexpensive computer to run it on that will be far faster than what you have now. Feature-wise you can probably get away with using the Workgroup Edition or maybe even the Express Edition.

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------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Hello Andreas!

First, I want to thank you for your prompt response.

Your explanation about "Fragmentation" is completely correct, since the meaning that Informix gives to this term is to indicate a technique for optimizing data access and would not reflect my problem.

I should have chosen better the term, something like "my tables have a lot of fragment dispersion in the database", I think this would better explain the situation.

I assume that this dispersion of fragments of large tables is causing the performance problems, because they are tables that have had the same EXTENT and NEXT for a long time, while tens of thousands or hundreds of thousands of records. I understand that such dispersion must have negative effects on their performance, even if they are indexed.

Tell me if I'm wrong, but what do you suggest is a reassembly of each of the indices?

Again, thanks for your reply.

Gustavo

------------------------------
Gustavo Echenique
------------------------------

Original Message:
Sent: Sun May 17, 2020 11:09 PM
From: Art Kagel
Subject: Table resizing queries

Gustavo:

You do not have to go through that old procedure, nor do you have to export and import the table (nor the whole database) if you up grade to a modern release of Informix! First, in v7.31, while you cannot change the FIRST extent size, you can change the NEXT SIZE (later releases do let you change the first extent size which is helpful when using the built-in defragmentation features in later releases.

That said, since you are currently stuck in v7.31, let me address you direct issues:

Your questions are the following:
1 - Have you successfully carried out this procedure?

Many times in various different versions of the operation.

2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.

Yes. You have to drop all constraints and indexes OR create the new ones on the new table with different names.

3 - Do you have any alternative solution to this problem? 

Yes, you can use:

ALTER TABLE <tablename> NEXT SIZE <large extent size>;
ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspace>; 

The dbspace can be any dbspace, even the same space that the table currently resides in. However, if you use an new empty dbspace then the new table will have a single large contiguous extent because of extent coalescing (contiguous extents are merged into a single extent), or at least one for each chunk that the table has to span if it is bigger than a single chunk in the dbspace. Since this is completely carried out inside the engine's code, it is faster than copying the data from the original table to a new one. It will be even a bit faster if you first drop the constraints and make the table RAW before the reorg then alter it back to STANDARD afterwards, recreate the constraints, then take an archive (RAW tables are not logged, so you will need the archive in order to be able to recover.

Oh, and DO NOT TRY THE oncheck -me option, it doesn't always work and can corrupt the table!

If you really want to improve performance, you may want to try fragmenting the table across several dbspaces so that the engine can ignore the fragment(s) that contain older data that you no longer need access to.

Again, the current release, version 14.10, offers several other options and you can get a VERY inexpensive computer to run it on that will be far faster than what you have now. Feature-wise you can probably get away with using the Workgroup Edition or maybe even the Express Edition.

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------

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Hi Gustavo,

I generally tend to question that table "fragmentation" (i.e. table partitions having more that just a few extents, so data physically scattered across chunks  -  whereas the real Informix "fragmentation" term is reserved for something entirely different) generally has a detrimental effect on performance:

• any indexed access to data should be pretty much unaffected from how contiguously your data is stored
• only really large disk scans, sequetially through a table's data, would (possibly) sense an effect from many small (vs. few larger) extents - yet should this be affecting your OLTP then a whole set of different questions had to be asked.

So what evidence to you have for "this is surely due to the fragmentation of the mentioned tables"? Before spending a lot of time and headache on such reorg, only on the assumption that this might be the reason behind longer session (query?) duration, an assessment of the real reason behind those performance problem should be conducted.  And even if such reorg should turn out to help your application, in terms of performance, it might have been something other than "defragmentation" that did the trick (e.g. indices re-built).

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Sun May 17, 2020 07:22 PM
From: Gustavo Echenique
Subject: Table resizing queries

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix

Reading this thread I was going to write something similar but Andreas beat me to it. I have never seen any measurable benefit from reducing the number of extents tables. What evidence do you have that this is the cause?

Ben.

P.S. I dislike "alter fragment": it can be slow, difficult to measure its progress and matters get even worse if it aborts for some reason (DBA gets fed up of waiting for it or long transaction) and rolls back. Roll backs are single threaded and on versions prior to 12.10 read-ahead is not used so it can take many times longer to roll back than it took to get to that point.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: Mon May 18, 2020 03:59 AM
From: Andreas Legner
Subject: Table resizing queries

Hi Gustavo,

I generally tend to question that table "fragmentation" (i.e. table partitions having more that just a few extents, so data physically scattered across chunks  -  whereas the real Informix "fragmentation" term is reserved for something entirely different) generally has a detrimental effect on performance:

• any indexed access to data should be pretty much unaffected from how contiguously your data is stored
• only really large disk scans, sequetially through a table's data, would (possibly) sense an effect from many small (vs. few larger) extents - yet should this be affecting your OLTP then a whole set of different questions had to be asked.

So what evidence to you have for "this is surely due to the fragmentation of the mentioned tables"? Before spending a lot of time and headache on such reorg, only on the assumption that this might be the reason behind longer session (query?) duration, an assessment of the real reason behind those performance problem should be conducted.  And even if such reorg should turn out to help your application, in terms of performance, it might have been something other than "defragmentation" that did the trick (e.g. indices re-built).

------------------------------
Andreas Legner

Original Message:
Sent: Sun May 17, 2020 07:22 PM
From: Gustavo Echenique
Subject: Table resizing queries

Dear colleagues:
I have the following situation: my database consists of almost a thousand tables, of which a couple of dozen exceed 30 million records. I even have one with 170 million records.

I have noticed that many processes accessing the database have increased their time significantly, and this is surely due to the fragmentation of the mentioned tables.

Now, when I took a database administration course taught by Informix Argentina, the instructor told me that the only way to change the Extent was to do a dbexport and dbimport (with the Extent and Next data calculated again) from the database .

This is impractical due to the size of the database, which occupies 104GB in flat files (dbexport), since it would take a long weekend of 3 or 4 days. It is as a consequence of the old age of the database version (IDS 7.31TD6) running on a Windows 2000 Advanced Server, and the server (IBM x366 with a 2GB transfer optical disk storage).

But researching, I found the following link: https://www.ibm.com/support/pages/how-reorganize-table-effectively

I did the test in a test environment with a single table, as the page indicates, but instead of dropping it, I renamed it and when I wanted to create the indexes and constraints, they still existed.

My questions to you are the following:
1 - Have you successfully carried out this procedure?
2 - Should I take into account the elimination of all foreign key constraints that refer to the modified table and its subsequent recreation? At this point I assume yes.
3 - Do you have any alternative solution to this problem?
4 - Can you add something to what is exposed by the article exposed in the link?

In advance, I appreciate your kind attention and the time dedicated to my consultation.
#Informix