Informix

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Thank you for the explanation, Art.

One more thought... Is the tablespace tablespace implemented as a linked list?  Could tech support login and hex edit the linked list to omit those pages?

Or maybe that is a huge effort that wouldn't be warranted for a non-emergency situation (and this is not an emergency).

DG

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Thank you, once again, Andreas.

Your comments are both comprehensive and understandable.

I will continue with your recipe when I find a nice "lull in the action" for this instance.  (As I mentioned, it is not production, but it is used regularly by several staff for some development work, or testing.)

If you are of a certain age, and are familiar with the USA cartoon characters, "Rocky & Bullwinkle"  you might appreciate the following:  :)

https://www.youtube.com/watch?v=qLteAm69hu4

DG

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

Not sure what you are asking with the last David?

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

------------------------------
David Grove

Original Message:
Sent: Tue August 01, 2023 05:46 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

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

Original Message:
Sent: Mon July 31, 2023 03:05 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 02:50 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

------------------------------
David Grove

Original Message:
Sent: Mon July 31, 2023 02:33 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Not sure what you are asking with the last David?

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 01:40 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 05:32 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 03:08 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 02:18 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

In summary:.. Is

EXECUTE FUNCTION task('fragment repack shrink', '<rootdbs_partition_number>');

appropriate/suitable/recommended?

EDIT: Wait, partition_number applies to table, not dbspace.  So, would I have to write a script that would do this for each table in each database?

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 02:45 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

------------------------------
David Grove

Original Message:
Sent: Tue August 01, 2023 05:46 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

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

Original Message:
Sent: Mon July 31, 2023 03:05 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 02:50 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

------------------------------
David Grove

Original Message:
Sent: Mon July 31, 2023 02:33 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Not sure what you are asking with the last David?

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 01:40 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 05:32 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 03:08 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 02:18 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

Or would it be a better idea to DROP and immediately rebuild the sys* databases?

EDIT: Or drop them, then do the "max fill" of both chunks, and then drop the "fills" from chunk 1, and then rebuild the sys* databases (which should get them all in chunk 1).

But, I've just talked myself into a circle here.

Just doing "stream of conscious" here.

Sorry.

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 05:22 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

In summary:.. Is

EXECUTE FUNCTION task('fragment repack shrink', '<rootdbs_partition_number>');

appropriate/suitable/recommended?

EDIT: Wait, partition_number applies to table, not dbspace.  So, would I have to write a script that would do this for each table in each database?

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 02:45 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

------------------------------
David Grove

Original Message:
Sent: Tue August 01, 2023 05:46 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

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

Original Message:
Sent: Mon July 31, 2023 03:05 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 02:50 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

------------------------------
David Grove

Original Message:
Sent: Mon July 31, 2023 02:33 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Not sure what you are asking with the last David?

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 01:40 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 05:32 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 03:08 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 02:18 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

I'd not go into any of those and instead simply run something like

for i in {001..200}; do
 echo "create table dummy_tab_$i (i int) in rootdbs extent size 33554430;" -- max. possible extent size in 2k space
done | dbaccess <some_database>

and hope this will consume all the gaps, first bigger ones, the smaller - if not, run more of the same.

Ultimately, once "oncheck -pe rootdbs" shows no more FREE extents (or only tiny ones below 4 pages), pick the biggest one residing in root chunk (#1) and drop it.

Once exercise is over:

for i in {001..200}; do
 echo "drop table dummy_tab_$i;"
done | dbaccess <same_database>

Or would it be a better idea to DROP and immediately rebuild the sys* databases?

EDIT: Or drop them, then do the "max fill" of both chunks, and then drop the "fills" from chunk 1, and then rebuild the sys* databases (which should get them all in chunk 1).

But, I've just talked myself into a circle here.

Just doing "stream of conscious" here.

Sorry.

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 05:22 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

In summary:.. Is

EXECUTE FUNCTION task('fragment repack shrink', '<rootdbs_partition_number>');

appropriate/suitable/recommended?

EDIT: Wait, partition_number applies to table, not dbspace.  So, would I have to write a script that would do this for each table in each database?

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 02:45 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

------------------------------
David Grove

Original Message:
Sent: Tue August 01, 2023 05:46 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

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

Original Message:
Sent: Mon July 31, 2023 03:05 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 02:50 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

------------------------------
David Grove

Original Message:
Sent: Mon July 31, 2023 02:33 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Not sure what you are asking with the last David?

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 01:40 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 05:32 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 03:08 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 02:18 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

I thank you, once again, Andreas for your helpful response.

I don't like to beleaguer this and drag it out (too late1), but now I have a more tutorial question, in response to your suggestion.

I had actually thought of scripting the creation of dummy tables, just as you suggested.  But, then, when I observed the disk fragmentation, my thinking went something like this:  If I start creating tables specifying the maximum allowed first extent, the process will (likely soon) run out of contiguous space that is large enough to permit creation of a new table with the specified first extent.  Then, I would have to manually intervene and determine the largest remaining contiguous free space, and specify that for a first extent.  After that table is created, I would (again) have to intervene manually and (again) determine the largest remaining contiguous free space, and create that table.  Then, the same again and again, until I use up all the space.  I figured that if there was insufficient contiguous free space to satisfy the specified first extent, it would just fail and not create a table at all.

I must not be understanding things correctly, because your example script doesn't seem to care whether there is sufficient contiguous free space to allocate a first extent of 33554420 KB.

Does Informix just plow on, allocating the largest amount of contiguous space it can when/if there is insufficient space to satisfy the specified first extent size?

I will just try it and see what happens...  But, I am curious about whether that is the way it is supposed to work.

Thank you.

DG

I'd not go into any of those and instead simply run something like

for i in {001..200}; do
 echo "create table dummy_tab_$i (i int) in rootdbs extent size 33554430;" -- max. possible extent size in 2k space
done | dbaccess <some_database>

and hope this will consume all the gaps, first bigger ones, the smaller - if not, run more of the same.

Ultimately, once "oncheck -pe rootdbs" shows no more FREE extents (or only tiny ones below 4 pages), pick the biggest one residing in root chunk (#1) and drop it.

Once exercise is over:

for i in {001..200}; do
 echo "drop table dummy_tab_$i;"
done | dbaccess <same_database>

Or would it be a better idea to DROP and immediately rebuild the sys* databases?

EDIT: Or drop them, then do the "max fill" of both chunks, and then drop the "fills" from chunk 1, and then rebuild the sys* databases (which should get them all in chunk 1).

But, I've just talked myself into a circle here.

Just doing "stream of conscious" here.

Sorry.

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 05:22 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

In summary:.. Is

EXECUTE FUNCTION task('fragment repack shrink', '<rootdbs_partition_number>');

appropriate/suitable/recommended?

EDIT: Wait, partition_number applies to table, not dbspace.  So, would I have to write a script that would do this for each table in each database?

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 02:45 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>

So, does that mean 6 per page?

DG

------------------------------
David Grove

Original Message:
Sent: Tue August 01, 2023 05:46 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi Art,

David hit the third possibility: second chunk in rootdbs received reserved pages extension of (primary + mirror) chunk reserved pages, and the task now is to get them moved back into root chunk.

@David: chunk descriptors are fixed size.  See "oncheck -pP 1 6 | head -20" to see their size (slot len) and how many of them fit on one page.

HTH,

 Andreas

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

Original Message:
Sent: Mon July 31, 2023 03:05 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Ahh, OK, got it. So, here is what I know:

• A rootdb chunk may be non-dropable for one of two reasons:

1. 1. The first being there are databases or tables defined in it or having extents there (but residing initially elsewhere). If you have verified (using oncheck -pe) that there are not database objects residing in that chunk, then
   2. The tablespace tablespace extents defined when the server was initialized (ONCONFIG parameters TBLTBLFIRST & TBLTBLNEXT) had been used up and the tablespace tablespace needed expanding but there was not free space in the initial chunk such that the new extents were added in the additional chunk (# 8?).
      

Obviously removing database objects from a chunk is easy and you seem to have done that. Unfortunately I do not think that Andreas's scheme will work because there is no way to delete the tablespace tablespace pages that were created for the dropped objects. You will just be adding even more unused partition pages to that overhead table. I agree with support that the only answer is to unload all of the data, drop the instance, and start all over again from scratch.

Alternatively, you could use ER to migrate everything to a new instance then drop the original, rename the new one, and poof.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 02:50 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

My apologies for lack of clarity.

Earlier, Andreas suggested the following:

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

Step 3 is to add chunks to use up all the extensions.

First, I wondered if there were a way to estimate how many chunks might be required.  (If a few, I'd do it manually; if a lot, I'd script it.)  Then, I recast the question to ask if there is a way to add the additional chunks that minimizes the number required.

DG

------------------------------
David Grove

Original Message:
Sent: Mon July 31, 2023 02:33 PM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

Not sure what you are asking with the last David?

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Mon July 31, 2023 01:40 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Let me ask a slightly different question...

Is there a way to add chunks that minimizes-- or at least reduces-- the number of additional chunks required to force a new extension?

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 05:32 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas.

Thinking some more, I believe I will not create a new database, but simply create tables with large initial extents in the existing db to fulfill step 1.

For step 3, is there a way to estimate how many additional chunks might be needed? (if a lot, I would script it.  If only a few, I would just do it manually.)  Is there a way to cause the addition of a chunk to require longer descriptors?

Thanks again,

DG

------------------------------
David Grove

Original Message:
Sent: Fri July 28, 2023 03:08 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Exactly, no need to even insert into those tables, the "EXTENT SIZE" spec should suffice to claim the space.

Your idea to do this in a newly created "dum" database might blur the picture, with all the new database's catalog tables, so you might want to place this new database into a non-root dbspace, then create your 'object' tables with "IN rootdbs".

For step 2, you'd add new chunks, observing oncheck -pe after each addition: if all goes by plan, the 4*4 RESERVED PAGES eventually should disappear from chunk #8 and you'd instead see new 4*5 such pages in chunk #1 at which point the goal is achieved and you can start dropping all other stuff left in chunk #8 and finally drop chunk #8.

Much luck with this!

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 02:18 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Thank you, Andreas, for your fast and helpful reply.  I believe you are the "surgeon" who assisted in my recent tech support case to get rid of the rsccompdict table.  Thank you, I really appreciated that.

To avoid possible disruption in availability of the instance (it isn't production, but it is used by development staff as a sort of scratchpad database, and for testing purposes), I think I'll do it over the weekend, when use of that instance is minimal.

Just a few questions, though:

1) I'm not 100% clear on what you mean when you suggest filling the dbspace with separate objects in each chunk.  I don't know how to make a table occupy a specific chunk.  Say I create a new database, "dum".  Then, a table, "dum_table", with a single column, "dum_pic" that would be a smart blob (in the dbspace).   I could then INSERT photos until the dbspace is filled.  But I don't know how to specify which chunk is used for the INSERTs.  Also, the dbspace would be filled, but not necessarily to that absolute last byte.  Just until there is no more room for the next photo.  (But, if I'm following you, that would be sufficient.)

2.) So, then start adding chunks to the rootdbs until I observe new reserve pages in chunk 1, is that right?

3.) Then drop all the dummy objects that are in chunk #8, and all should be good.  (I should then be able to DROP the chunk.)  Right?

Thank you

DG

EDIT:  Oops, I see already (after clicking "post") that this isn't good.  After both chunks are full, I need to be able to drop objects from chunk #8 only.  So, if I use a single table, I would need to distinguish which rows are in which chunk.  So, strike that approach.  Perhaps this is better: create a dummy table (it goes in whatever chunk Informix decides) and start INSERTing rows of photos, but stop when I see any new extent created in the other chunk.  Then I create another new table (hopefully it is created in the other chunk), and fill it.  The potential problem is that the second new table might still be created in the same chunk as the first one.  I guess I can script it, and keep creating tables until I see a new one in the other chunk.  Then proceed as before.

I'm still on my first cup of coffee, so if this isn't clear, I'm happy to try again.  :)

SECOND EDIT:  Ah, I could just specify a table with a large enough initial extent to fill up the entire remaining space in the larger of the two chunks, and then repeat for the other chunk, right?
------------------------------
David Grove

Original Message:
Sent: Thu July 27, 2023 01:13 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

How about this - maybe be a trick to do it without surgery (I've seen your evidence):

• fill both root chunks to the lid, with separate (dummy) objects in each chunk
• remove the dummy objects from root chunk (#1), so only this chunk offers room now
• add enough more dummy chunks to the system so those existing 4*4 pages extensions cannot accommodate their descriptors and had to be extended again - now in chunk #1
• remove remaining dummy objects from chunk #8 which should be free (of these reserved pages) now
• confirm by oncheck -pe after each step

HTH,

 Andreas

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

Original Message:
Sent: Thu July 27, 2023 12:42 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Informix 12.10.FC14

Solaris 10  1/13

Scenario: I have an instance that has a rootdbs with two chunks-- the initial and an added one.  The added one is empty, and I want to drop it.  Because, at one time, it did have contents, additional reserve pages exist in the chunk.  Therefore, it cannot be dropped.

Question:  Is there any (undocumented) way to forcibly drop the additional chunk.  Perhaps, some way to get rid of the additional reserve pages.  Or maybe just some way to force the empty chunk to be dropped.

I have asked tech support to login and work some magic to drop the chunk, but they decline.  They say Informix is working as intended, and there is no problem (because multiple chunks are allowed) and, further, that there is no documented solution, anyway; and even further, if I don't like it I should enter an enhancement request.

I suspect that they could do it if they really wanted to because just a few weeks ago, they logged in and did some hex editing to make a zombie "rsccompdict" object go away.  (Its existence prohibited me from dropping another rootdbs chunk [which was the third chunk in the rootdbs]).  Their edit was successful, and once that the object was gone (and chunk was empty) I could drop the chunk.

My goal, as you may have surmised, is to get the rootdbs back to its original single chunk existence, and to use the (former) additional rootdbs chunks for something else.

Might anyone have any magic pixie dust I could sprinkle to DROP the empty rootdbs chunk.

Grasping at straws.  Maybe (as tech support suggests) there really is nothing to do but reinitialize the instance from scratch.  This isn't a production instance, but there are a lot of databases that I would have to export (and then re-import).  Cue the violins.

Thank you.

DG

------------------------------
David Grove
------------------------------

I thank you, once again, Andreas for your helpful response.

I don't like to beleaguer this and drag it out (too late1), but now I have a more tutorial question, in response to your suggestion.

I had actually thought of scripting the creation of dummy tables, just as you suggested.  But, then, when I observed the disk fragmentation, my thinking went something like this:  If I start creating tables specifying the maximum allowed first extent, the process will (likely soon) run out of contiguous space that is large enough to permit creation of a new table with the specified first extent.  Then, I would have to manually intervene and determine the largest remaining contiguous free space, and specify that for a first extent.  After that table is created, I would (again) have to intervene manually and (again) determine the largest remaining contiguous free space, and create that table.  Then, the same again and again, until I use up all the space.  I figured that if there was insufficient contiguous free space to satisfy the specified first extent, it would just fail and not create a table at all.

I must not be understanding things correctly, because your example script doesn't seem to care whether there is sufficient contiguous free space to allocate a first extent of 33554420 KB.

Does Informix just plow on, allocating the largest amount of contiguous space it can when/if there is insufficient space to satisfy the specified first extent size?

I will just try it and see what happens...  But, I am curious about whether that is the way it is supposed to work.

Thank you.

DG

------------------------------
David Grove

Original Message:
Sent: Mon August 07, 2023 08:04 AM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

I'd not go into any of those and instead simply run something like

for i in {001..200}; do
 echo "create table dummy_tab_$i (i int) in rootdbs extent size 33554430;" -- max. possible extent size in 2k space
done | dbaccess <some_database>

and hope this will consume all the gaps, first bigger ones, the smaller - if not, run more of the same.

Ultimately, once "oncheck -pe rootdbs" shows no more FREE extents (or only tiny ones below 4 pages), pick the biggest one residing in root chunk (#1) and drop it.

Once exercise is over:

for i in {001..200}; do
 echo "drop table dummy_tab_$i;"
done | dbaccess <same_database>

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

Original Message:
Sent: Fri August 04, 2023 07:02 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Or would it be a better idea to DROP and immediately rebuild the sys* databases?

EDIT: Or drop them, then do the "max fill" of both chunks, and then drop the "fills" from chunk 1, and then rebuild the sys* databases (which should get them all in chunk 1).

But, I've just talked myself into a circle here.

Just doing "stream of conscious" here.

Sorry.

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 05:22 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

In summary:.. Is

EXECUTE FUNCTION task('fragment repack shrink', '<rootdbs_partition_number>');

appropriate/suitable/recommended?

EDIT: Wait, partition_number applies to table, not dbspace.  So, would I have to write a script that would do this for each table in each database?

------------------------------
David Grove

Original Message:
Sent: Fri August 04, 2023 02:45 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

Server Studio (see screen shot pasted below) shows a fair amount of fragmentation (disk fragmentation, not Informix fragmentation) in the rootdbs (i.e. chunks 1 & 8).  Since table extents are contiguous, to avoid having to create dozens or hundreds of small tables, should I repack and shrink all the tables, first?  Can I do an entire chunk (all the tables in a chunk) by using the fragment "flavor" of the command?  None of the tables are fragmented in the Informix sense of the word.  And, all the tables are from  sys* databases (sysadmin, sysutil, etc.)

------------------------------
David Grove

Original Message:
Sent: Thu August 03, 2023 04:44 PM
From: Andreas Legner
Subject: Want to DROP an empty rootdbs chunk

Hi David,

first to your oncheck output:

• this is page 1:6, so one of the 12 root reserved pages at the very start of the root chunk - not an extension (its extension resides in chunk #8 - high two bytes of 'prev', at offset 0x12e6f  -  'next'  -  and is 4 pages long - low two bytes of 'prev')
  If you also dump pages 1:7, 1:8 and 1:9 you'll find the pointers to those other three sets of 4 pages, all in chunk #8.
• each chunk descriptor consumes 304 bytes (plus 4 for the slot table entry)
• the page's free counter 'frcnt', 172, is significantly less than 304, so the page can be considered full
• so yes, up to six chunk descriptors would fit on one reserved page

Now back to my line of thought:

• some root reserved pages can have extensions - one contiguous set per root reserved page
• pages 1:2 .. 1:5, the logical log and dbspace descriptor pages, can have their extensions only in the root chunk itself while
• pages 1:6 .. 1:9, the primary and mirror chunk descriptor pages, can have their extensions in a different chunk of the rootdbs, and in fact the db server even favors a non-root chunk over the root chunk - which is your case, and also your problem atm.
• if an additional (log/dbspace/chunk) descriptor cannot be accommodated by the existing root rp + extension, the extension will have to grow, yet it will not grow in place, but will get reallocated with bigger size at a different location
• this is what we're trying to use: force a reallocation of those chunk descriptor extensions at a different location within the rootdbs
• by occupying all available space in the rootdbs, then freeing some in the root chunk, we'd hope to overcome said preference for non-root chunk

I guess you'll have to define no more than six tiny new dummy chunks to force this ext. rp. growth, and I'd hope this will then happen for all four such extensions at once (the pair for the primary chunk will always grow in sync, and the reserved pages space for mirror chunk descriptors, even if no mirroring is used, will also be kept in sync with primaries' - so you'd always be able to turn mirroring on for all your chunks.)
If, at this point in time, no space would be available in chunk #8, but sufficient (contiguous!) space exists in root chunk, the server had no choice but re-allocating/-locating those extensions out of chunk #8 into root chunk #1.

Use  "oncheck -pe rootdbs" frequently, after each step, to see when and how things change.

Hope this makes sense ;-)

BR,

 Andreas

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

Original Message:
Sent: Wed August 02, 2023 01:57 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

"...drop those six chunks..."

I am happy to do the extra work of exporting and importing, but...

Slot #1 is the initial chunk of rootdbs

Slot #2 is the chunk we are currently discussing, i.e., it is itself... chunk #8-- which is precisely the empty chunk that I opened this discussion to try to learn how to drop.

The remaining 4 slots-- No problem.  They are chunks that I am able to manipulate, so as to empty them and drop them.

It's looking more and more like "rebuild it from scratch" is the answer.  But, I am still not completely clear on Andreas' line of thinking.

Thank you, again.

DG

------------------------------
David Grove

Original Message:
Sent: Wed August 02, 2023 08:14 AM
From: Art Kagel
Subject: Want to DROP an empty rootdbs chunk

David:

From the oncheck output it looks like this is an extended chunk table page mapping your last six chunks. The only way that I know of to clear that would be to drop those six chunks, drop this chunk, then recreate those chunks which will hopefully be able to live on reserved pages in the first root chunk. 

Of course, if there are tables or other objects in those six chunks this will not be painless, but it may be as simple as exporting, dropping, and reloading those objects.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Tue August 01, 2023 07:05 PM
From: David Grove
Subject: Want to DROP an empty rootdbs chunk

I fully respect both Andreas and Art.  Both are at the very top of Informix resources.  But, it seems there is a difference of opinion.  Does Andreas' suggested sequence of operations result in the extensions being moved back to the initial root chunk?

The output from 'oncheck' is:

informix@ifmx-test-jnu>oncheck -pP 1 6|head -20
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:6              1559613954 8ef0   6      1800 ROOTRSV      1848  172   12e6f    80004
        slot ptr   len   flg
        1    24    304   0
        2    328   304   0
        3    632   304   0
        4    936   304   0
        5    1240  304   0
        6    1544  304   0
slot   1:
    0: ff ff ff f2  0  1  0  8  1 aa 3c 18  0  0  0  0   ...r.....*<.....
   16:  1 a1 d5 a5  0  0  0  0  0  0  0  0  0  0  0  0   .!U%............
   32:  0  3  0 40  8  0  0  1  0 26 2f 6f 70 74 2f 69   ...@.....&/opt/i
   48: 6e 66 6f 72 6d 69 78 2f 72 64 73 6b 2f 65 6e 63   nformix/rdsk/enc
   64: 68 69 6c 61 64 61 5f 64 62 73 31 2d 74 65 73 74   hilada_dbs1-test
   80:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
   96:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  112:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  128:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
  144:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0   ................
informix@ifmx-test-jnu>