sbspace size insufficient after dbexport/dbimport

Solaris 10 Informix 12.10 I am trying to dbexport a database, and then dbimport it into an Informix instance on another machine. The database currently has a sbspace that is 8GB. I do the dbexport successfully. I dbimport it into the new instance, said instance having an sbspace of 16GB. Problem: The sbspace fills before the dbimport completes, thus halting the dbimport. Question: Why won't 8GB of blobs fit into a 16GB sbspace? So far, I have invented the following as a possible answer: The blobs consist of photos, in two different tables. All of the photos in one of the tables are already stored in the other table. So, I'm thinking that the same LO Handle is stored in each table, but the actual photo is stored only once in the sbspace. Now, maybe dbexport exports each table, without using the knowledge about the same photo being in two tables. So, then, dbimport would just merrily import those blobs, and maybe the photos are actually physically present twice in the new database-- once for each of the tables. (Instead of the same LO Handle being in the two tables, each table might have a distinct LO Handle, each pointing to its own blob.) Could this be the case? If not, might anyone suggest why twice the sbspace in a new instance is insufficient to contain all the blobs from an sbspace half the size in the old instance? Thank you for any comments. Regards, DG

Hi, David. I suppose you ran your dbexport command with -ss argument, so... I would recommend you to check the page size of the sbspace, I suppose that your new sbspace is in a smaller value that the exported one. Compare the two onstat -d and you probabily see the difference between them. Hope it helps. Best regards. Em 2 de nov de 2016 9:52 PM, DAVID GROVE <david.grove@alaska.gov> escreveu: Solaris 10 Informix 12.10 I am trying to dbexport a database, and then dbimport it into an Informix instance on another machine. The database currently has a sbspace that is 8GB. I do the dbexport successfully. I dbimport it into the new instance, said instance having an sbspace of 16GB. Problem: The sbspace fills before the dbimport completes, thus halting the dbimport. Question: Why won't 8GB of blobs fit into a 16GB sbspace? So far, I have invented the following as a possible answer: The blobs consist of photos, in two different tables. All of the photos in one of the tables are already stored in the other table. So, I'm thinking that the same LO Handle is stored in each table, but the actual photo is stored only once in the sbspace. Now, maybe dbexport exports each table, without using the knowledge about the same photo being in two tables. So, then, dbimport would just merrily import those blobs, and maybe the photos are actually physically present twice in the new database-- once for each of the tables. (Instead of the same LO Handle being in the two tables, each table might have a distinct LO Handle, each pointing to its own blob.) Could this be the case? If not, might anyone suggest why twice the sbspace in a new instance is insufficient to contain all the blobs from an sbspace half the size in the old instance? Thank you for any comments. Regards, DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you for your helpful and quick response. 1) Yes, ran dbexport with -ss option. 2) I will investigate as you suggested. Thank you. DG

The page size of the sbspace in both instances is identical. DG

David: I think that you nailed it. Since each BLOB is referenced by two rows, the export contains two copies of each BLOB so on import it needs twice as much space. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 2, 2016 at 7:51 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Solaris 10 > Informix 12.10 > > I am trying to dbexport a database, and then dbimport it into an Informix > instance on another machine. The database currently has a sbspace that is > 8GB. > > I do the dbexport successfully. > > I dbimport it into the new instance, said instance having an sbspace of > 16GB. > > Problem: The sbspace fills before the dbimport completes, thus halting the > dbimport. > > Question: Why won't 8GB of blobs fit into a 16GB sbspace? > > So far, I have invented the following as a possible answer: The blobs > consist > of photos, in two different tables. All of the photos in one of the tables > are > already stored in the other table. So, I'm thinking that the same LO > Handle is > stored in each table, but the actual photo is stored only once in the > sbspace. > Now, maybe dbexport exports each table, without using the knowledge about > the > same photo being in two tables. So, then, dbimport would just merrily > import > those blobs, and maybe the photos are actually physically present twice in > the > new database-- once for each of the tables. (Instead of the same LO Handle > being in the two tables, each table might have a distinct LO Handle, each > pointing to its own blob.) > > Could this be the case? > > If not, might anyone suggest why twice the sbspace in a new instance is > insufficient to contain all the blobs from an sbspace half the size in the > old > instance? > > Thank you for any comments. > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b05fcf239fa054062ead6

Thank you, so much, Art. I had hoped to be wrong, but feared I was right. It just makes us use more space. Oh well. But, it does raise the question in my (alleged) mind, why doesn't IBM provide a way to backup/copy/clone/export (any or all) a database? We have struggled for years with Informix's inability to backup a database. "ontape" doesn't work, because it backs up the whole instance. "onunload" doesn't work because it doesn't permit blobs (which always surprised me, considering how [at least several years ago] one of Informix's big marketing points was, you know, OBJECTS and how Informix was the [only] "object relational" database, etc.). "dbexport" doesn't actually produce a true copy of a database, as we just see in the immediate example. Besides, sometimes it fails-- i.e., sometimes an export produced by running dbexport cannot be imported by dbimport. (I admit it's been several years since I ran into this, but it sure is real and repeatable, when it occurs.) What we really need is a way to copy/extract/archive/backup a database, and, so far as we know, IBM doesn't have a way to do that. Also, it would be REALLY nice if such a feature could operate while online (just like ontape). So, I guess the bottom line is that to copy/backup/clone a database that contains blobs, dbexport is our only option. At least we can avoid downtime (this is for a production database) by doing a temporary STOP_APPLY on the RSS, and run dbexport from there. Of course, we lose redundancy for that brief period, but we don't really see any alternative. Thank you, again. Regards, David Grove

Using onbar, if a database is completely and uniquely contained in a given subset of your dbspaces, you can restore only those dbspaces and therefore, in effect, a single database. The Multi-Tenancy features in v12.10 make that easier because it enforces the ownership relationship between databases and dbspaces preventing objects from other databases from being created in dbspaces belonging to each tenant database. FYI: You CAN use my dbexport replacement utility, myexport, to create a dbexport data set from either the primary or the RSS secondary without having to lock the database or freeze the secondary. Myexport uses other methods to reduce the possibility of inconsistent data being exported and the myimport utility has features to further eliminate such problems on import. Myexport and myimport are fully compatible with dbexport and dbimport (though using dbimport to import a myexport dataset that was created with the -m option that splits indexes and constraints to a separate schema file requires the index schema to be run manually after a dbimport). As a bonus, if you run myexport/myimport in external table mode, it is significantly faster than dbexport/dbimport. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 3, 2016 at 12:22 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you, so much, Art. > > I had hoped to be wrong, but feared I was right. > > It just makes us use more space. Oh well. > > But, it does raise the question in my (alleged) mind, why doesn't IBM > provide > a way to backup/copy/clone/export (any or all) a database? > > We have struggled for years with Informix's inability to backup a database. > "ontape" doesn't work, because it backs up the whole instance. "onunload" > doesn't work because it doesn't permit blobs (which always surprised me, > considering how [at least several years ago] one of Informix's big > marketing > points was, you know, OBJECTS and how Informix was the [only] "object > relational" database, etc.). "dbexport" doesn't actually produce a true > copy > of a database, as we just see in the immediate example. Besides, sometimes > it > fails-- i.e., sometimes an export produced by running dbexport cannot be > imported by dbimport. (I admit it's been several years since I ran into > this, > but it sure is real and repeatable, when it occurs.) > > What we really need is a way to copy/extract/archive/backup a database, > and, > so far as we know, IBM doesn't have a way to do that. Also, it would be > REALLY > nice if such a feature could operate while online (just like ontape). > > So, I guess the bottom line is that to copy/backup/clone a database that > contains blobs, dbexport is our only option. At least we can avoid downtime > (this is for a production database) by doing a temporary STOP_APPLY on the > RSS, and run dbexport from there. Of course, we lose redundancy for that > brief > period, but we don't really see any alternative. > > Thank you, again. > > Regards, > > David Grove > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3aa3e7a10110540682927

Thank you, Art. One of the advantages of having one huge dbspace, with chunks on SSDs for performance, is zero time spent on managing dbspaces (we probably will do some partitioning at some point, but there are no complaints [or even requests] regarding performance). A disadvantage is that databases are not isolated to their own dbspaces. Oh well. I still think it would be useful for IBM to provide a way to backup/archive/copy/clone/export/whatever individual databases. It's been many years, and still IBM puts forth a migration product (onunload) that cannot be used with Informix, when Informix is used as intended (with smart large objects). Nor do they provide any solution at all to handle individual databases with smart large objects, while online. It seems strange to me that we must be the only customer that has need for this. We have been asking for a solution for almost 10 years. Thank you also for the steer to your utility. That is a fantastic improvement over the IBM-provided utility. Again, I have to wonder why IBM declines to incorporate such functionality, with such wide application, in their product. <Whine mode off> Regards, David Grove

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } The issue with doing database archives is with the restore. Unless a given database is restricted to a exclusive set of displaces which is not used by other databases, then you can not ensure that you can restore that database. Why? Well if multiple databases reside in overlapping do spaces, then it is entirely possible that pages backed up in the archive of that database could be subsequently used by a table belonging to some other database. For instance suppose that you had a table in database 'A' and dropped that table. That would make those pages available for use by other databases. And that would then make it impossible to restore database 'A' because some of the pages in the archive now belong to database 'B'. If you tried to restore database 'A', then you would corrupt database 'B' because you over wrote those pages during the restore. Sent from Yahoo Mail for iPad On Friday, November 4, 2016, 11:26 AM, DAVID GROVE <david.grove@alaska.gov> wrote: Thank you, Art. One of the advantages of having one huge dbspace, with chunks on SSDs for performance, is zero time spent on managing dbspaces (we probably will do some partitioning at some point, but there are no complaints [or even requests] regarding performance). A disadvantage is that databases are not isolated to their own dbspaces. Oh well. I still think it would be useful for IBM to provide a way to backup/archive/copy/clone/export/whatever individual databases. It's been many years, and still IBM puts forth a migration product (onunload) that cannot be used with Informix, when Informix is used as intended (with smart large objects). Nor do they provide any solution at all to handle individual databases with smart large objects, while online. It seems strange to me that we must be the only customer that has need for this. We have been asking for a solution for almost 10 years. Thank you also for the steer to your utility. That is a fantastic improvement over the IBM-provided utility. Again, I have to wonder why IBM declines to incorporate such functionality, with such wide application, in their product. <Whine mode off> Regards, David Grove ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you Madison, for informative reply. In my (alleged) mind, I'm still a little confused, though. First, I'm not sure what you mean by "overlapping dbspaces". Although we have multiple databases in a single dbspace (in one of our instances, not our production instance), every dbspace is distinct from every other dbspace. Also, 'onunload' and 'onload' permit database backup and restore, right? Why wouldn't they have the problem you describe? And, why does database backup work (with 'onload'/'onunload') for databases that have no smart blobs, but not work if smart blobs exist? Perhaps this would be a good topic for a session or two at the annual conference. Thank you. DG

HI David: I hope you can upgrade to version 12 and utilize the new database level restore features which you have been asking for. This feature was released last year and allow a user to restore a single database to a running system. If the restore is to a system which has secondary servers, then the restore is automatically propagated to those secondary servers without interruption and without a DBA lifting a finger. As Madison pointed out we do have to ensure you do not put multiple databases in the same dbspace or smart blob space. How this is done is by creating a tenant database. The tenant database has properties which associates a database with a defined set of storage spaces. If you try and access or create a table/index outside of these defined set of space(s) you will receive an error. If a user of another database tries to access your spaces, they will receive an error. This ensures that no two database can share their storage space(s). The restore becomes very simple as you can just say restore a database to a specific point in time an the restore process figures out which space are required and restore the images rolling forward the required logs and stopping at your desired point in time. While I will admit that the intent of this feature was not for migration, but there are many ways one can utilize this technology for migration of a database. John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 11/04/2016 09:26:32 AM: > From: "DAVID GROVE" <david.grove@alaska.gov> > To: ids@iiug.org > Date: 11/04/2016 09:27 AM > Subject: Re: sbspace size insufficient after dbexport/dbimp [38085] > Sent by: ids-bounces@iiug.org > > Thank you, Art. > > One of the advantages of having one huge dbspace, with chunks on SSDs for > performance, is zero time spent on managing dbspaces (we probably > will do some > partitioning at some point, but there are no complaints [or even requests] > regarding performance). A disadvantage is that databases are not isolated to > their own dbspaces. Oh well. > > I still think it would be useful for IBM to provide a way to > backup/archive/copy/clone/export/whatever individual databases. It'sbeen many > years, and still IBM puts forth a migration product (onunload) that cannot be > used with Informix, when Informix is used as intended (with smart large > objects). Nor do they provide any solution at all to handle individual > databases with smart large objects, while online. It seems strange to me that > we must be the only customer that has need for this. We have been > asking for a > solution for almost 10 years. > > Thank you also for the steer to your utility. That is a fantastic improvement > over the IBM-provided utility. Again, I have to wonder why IBM declines to > incorporate such functionality, with such wide application, in theirproduct. > > <Whine mode off> > > Regards, > > David Grove > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } By overlapping dbspaces I mean that your dbspaces overlap multiple databases. The problem with what you propose is that in order for a backup to be online AND that a recovery be consistent with anything other than the archive checkpoint, you must apply the logical logs as well as the archive. When the archive is restored, the restore process has no idea what that page that it is restoring, it is a physical restore only. That means that the given page could not be reused because the restore will restore that page. And then when the logical recovery is done, it must have a physically consistint starting point. That is why any database level archive/restore would require distinct files for given database, and that is what is done by default when multi-tenancy is deployed. In order for import/export to be used successfully as a means of archive/restore, the database must be in a static state for the duration of the export. That is why it is required that the export do an exclusive lock mode. Yes - there are tools which allow an export/unload of the data on an active system, but in order to produce a consistent unload which can be used as a restore, there can be no changes to the tables or to any associated tables while the unload is being done. Oh I guess that it would be possible to combine a form of active unload plus CDC to create a point of consistency as of the end of the unload, but that can get rather messy. We use that technique with ifxclone. Sent from Yahoo Mail for iPad On Friday, November 4, 2016, 12:32 PM, DAVID GROVE <david.grove@alaska.gov> wrote: Thank you Madison, for informative reply. In my (alleged) mind, I'm still a little confused, though. First, I'm not sure what you mean by "overlapping dbspaces". Although we have multiple databases in a single dbspace (in one of our instances, not our production instance), every dbspace is distinct from every other dbspace. Also, 'onunload' and 'onload' permit database backup and restore, right? Why wouldn't they have the problem you describe? And, why does database backup work (with 'onload'/'onunload') for databases that have no smart blobs, but not work if smart blobs exist? Perhaps this would be a good topic for a session or two at the annual conference. Thank you. DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I do thank you for your explanatory comments. "The problem with what you propose is that in order for a backup to be online AND that a recovery be consistent with anything other than THE ARCHIVE CHECKPOINT..." (my emphasis) BTW, Just being able to restore to the archive checkpoint would be worth gold to us. DG