dbexport/dbimport question

Hi, We are running informix 12.10 FC8 and setting up a dbexport/dbimport for database backups would like to ask th following. 1. Does dbexport includes the dbspace where the db resides? We have multiple dbs that are created on different dbspaces. 2. If #1 doesn't include the dbspace of the db then how can I query the dbspace where my db was created? I know it can be viewed using "dbaccess dbname" but is there a query to view this detail? 3. For example I have dbspace1, dbspace2 and dbspace3 if I use dbimport and not specify the dbspace (-d) where would the database be created? Thanks and more power to IIUG

Hi, We are running informix 12.10 FC8 and setting up a dbexport/dbimport for database backups would like to ask th following. 1. Does dbexport includes the dbspace where the db resides? We have multiple dbs that are created on different dbspaces. 2. If #1 doesn't include the dbspace of the db then how can I query the dbspace where my db was created? I know it can be viewed using "dbaccess dbname" but is there a query to view this detail? 3. For example I have dbspace1, dbspace2 and dbspace3 if I use dbimport and not specify the dbspace (-d) where would the database be created? Thanks and more power to IIUG Hi, Firstly I should say that dbimport and dbexport are not backup tools although I suppose they can be used as such for a small instance than can stand downtime and for which you can rebuild an instance quickly and efficiently. You should really look at using onbar. dbexport includes where tables and indices are stored if the storage schema option, "-ss", is specified. However if the object resides in the default dbspace for its database and no storage option was specified at creation "dbexport -ss" may not include an "in <dbspace>" clause. Can I suggest you look at the dbexport documentation: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.mig.doc/ids_mi g_127.htm The answer to your second question is there: -d dbspace Specifies the dbspace where the database is created. If this is omitted, the default location is the root dbspace Ben.

[This is a sidebar rant] "Firstly I should say that dbimport and dbexport are not backup tools although I suppose they can be used as such for a small instance than can stand downtime and for which you can rebuild an instance quickly and efficiently. You should really look at using onbar." Database backup is a issue that has annoyed us for many years: Other than dbexport/dbimport, Informix does not provide a general means to backup a database. (The various tools provided have limitations that prohibit their use in backing up a single database: some cannot backup a database that contains large objects; and, some can backup only at the instance or dbspace level. None [except dbexport/dbimport] exactly backup a database.) We have asked several times over the past 20 years. Always the same answer: Informix is designed NOT to be able to do backups at the database level. Maybe we've not asked clearly; or, maybe we've misunderstood the answer-- If so, I apologize for the false assertion, and would pay money to be set straight! I want to be able to backup a database just like ontape backs up an instance-- fast, and without needing exclusive access. DG

David, I'd be interested to know what your expectations would be from such a backup. I see two main options: 1. Something similar to onbar/ontape which backs up all the dbspaces and chunks (and would restore the disk layout) but only the objects belonging to system databases and the databases you name. I imagine this could run into trouble with cross-database transactions and database recovery so could be a non-starter. 2. It doesn't seem possible to back up a "database" any other way without it being an export of a specific user database so I guess you're asking for something similar to dbexport but with "consistent read" and no locking, which would provide a set of files with data and schema consistent at a certain point in time. I guess output could be in SQL or in some kind of binary format. Any export begs the question of how you recover it: would it require all the same dbspaces to be present in the target system or would it need to support some kind of remap file? Ben.

We want to back up a complete database. The catalog and all the tables and stored procedures. We want to be able to restore it on another machine (same configuration). We want it to function as quickly and as consistently as invisible-to-the-user as ontape. In the past, we used to use onunload. But, that is no longer possible because onunload does not function when Informix is used as intended (e.g., with smart large objects). If onunload worked when a database contained smart large objects (a feature of Informix for a decade or two), we could do what we need to do much more easily than we do now. It would be even better if onunload could work like ontape, and provide a consistent archive without taking the database down (or needing to lock it). I know. This is impossible for Informix. Things like this. And other errors (such as an error in execution of legal SQL-- which lead to an APAR-- on the very first day that a new developer started) make it increasingly difficult to ignore in-house calls to explore other options. We just need some basic conveniences and functionality that seem to be unavailable in Informix. I say this with a heavy heart, after working with Informix for more than 20 years. DG

I am going to try reworking the dbspace layout of our databases, on a test server. I'm thinking that, if each database is in its own set of dbspaces and sbspaces, maybe we can use ontape to backup individual databases. Up to now, we have been using the S.A.M.E. strategy that Oracle advanced and advocated a long time (maybe 15 years ago???). Because of the extreme simplicitly of administering that kind of space layout, we have near zero time required for space layout architecture issues. It worked for us. But, maybe it's time for a change, and maybe we can get what we want by doing this (and stay with Informix, which has great advantage in stability and low DBA time demands). DG