Confused by "Database" concept in Informix

My background is in Oracle. I've been working on some Informix DB's for about 18 months but without any formal training. I've been asked by a user to work out how much space is left in a database, and in trying to do this I've discovered that I don't exactly know what Informix calls a database. In Oracle, a database is a collection of processes and various files. If there are 2 databases on the same machine then their processes and files are independent of each other. But it seems to me that on Informix everything is mixed together. All the processes are called oninit (are there any others?). And when I do onstat -d I get information about chunks and dbspaces (I've got aworking grasp of these concepts). So to me, all these processes and all these chunks constitute a single database. However, when I get into DBACCESS it offers me a whole lot of "databases" to work with. Are these the equivalent of ORACLE "schemas"? The various scripts that I've looked at tell me how much free space there is in all the chunks or dbspaces (and I know that onstat gives similar information). How do I determine how much space a particular "database" has left? Does a "database" live in only 1 dbspace? Can the "database" use all the space in all the chunks on which it resides? Any help on this would be greatly appreciated.

IDS has the concept of instance, databases within instance, schemas within database, tables within schemas. However, the schema level is optional. The instance consists of the shared memory, logging system, chunks, and other resources that are used by the engine to manage the databases within that instance. All of the databases within an instance share the same shared memory, oninit processes, and other resources. When a database is created, it can be placed within a default dbspace... create database ..... in some_dbspace.. If the dbspace is not specified, then the default dbspace for the database will be the initial root dbspace. When a table is created, you can specify the dbspace by ... create table ... in some_dbspace. If the dbspace for the table is not specified, then the table will be placed in the database dbspace. Probably the easiest thing for you to do is to first examine the output of dbschema so that you can determine which dbspace each of the tables are located in. M.Pruet "Chris Bullivant" <cbullivant@orange.net.au> wrote in message news:21e62a09.0402151750.2766c12d@posting.google.com... > My background is in Oracle. I've been working on some Informix DB's > for about 18 months but without any formal training. > > I've been asked by a user to work out how much space is left in a > database, and in trying to do this I've discovered that I don't > exactly know what Informix calls a database. > > In Oracle, a database is a collection of processes and various files. > If there are 2 databases on the same machine then their processes and > files are independent of each other. > > But it seems to me that on Informix everything is mixed together. All > the processes are called oninit (are there any others?). And when I do > onstat -d I get information about chunks and dbspaces (I've got a> working grasp of these concepts). So to me, all these processes and > all these chunks constitute a single database. > > However, when I get into DBACCESS it offers me a whole lot of > "databases" to work with. Are these the equivalent of ORACLE > "schemas"? > > The various scripts that I've looked at tell me how much free space > there is in all the chunks or dbspaces (and I know that onstat gives > similar information). How do I determine how much space a particular > "database" has left? Does a "database" live in only 1 dbspace? Can the > "database" use all the space in all the chunks on which it resides? > > Any help on this would be greatly appreciated.

Chris Bullivant wrote: > My background is in Oracle. I've been working on some Informix DB's > for about 18 months but without any formal training. Congratulations for surviving without the formal training. A little bit of it would help you a lot, though. I recommend an administrator's course if you can possibly wangle it -- it will make your life easier. > I've been asked by a user to work out how much space is left in a > database, and in trying to do this I've discovered that I don't > exactly know what Informix calls a database. Madison outlined the way Informix works: you have an IDS instance, which manages all the disk space it uses, and within that you have what Informix calls a database, and within a database there are tables and so on. Madison mentioned schemas - technically, they just about exist in Informix, but that existence is more than a tad marginal. Informix normally calls the analogue of an ISO SQL schema the 'owner', mainly because by default, the schema for a table is the login name of the user who creates it (Informix uses o/s-defined users, rather than DB-defined users, in general - another difference between Informix and Oracle). > In Oracle, a database is a collection of processes and various files. > If there are 2 databases on the same machine then their processes and > files are independent of each other. So, you're Oracle database corresponds to an Informix (IDS) instance. > But it seems to me that on Informix everything is mixed together. All > the processes are called oninit (are there any others?). In modern versions of IDS (meaning anything you can lay hands on), the processes are all called oninit. In OnLine 5.x, the processes were called 'tbinit' instead, and in some historical o/s (SunOS 3, 4), some of those processes would rename themselves 'tbundo' and 'tbpgcl' (for the process that rolled back incomplete transactions and the page cleaners), and the clients each ran a process, sqlturbo, that actually processed the queries. The chances are you'll never need to know that again. (Modern o/s do not allow you to achieve the process renaming effect; in current systems, you have possibly a few tbinit processes running a single OnLine instance.) > And when I do > onstat -d I get information about chunks and dbspaces (I've got a> working grasp of these concepts). So to me, all these processes and > all these chunks constitute a single database. Within your Oracle terms of reference, that's correct. We have the inverse problem when we work with other DBMS - Oracle or DB2 for example. > However, when I get into DBACCESS it offers me a whole lot of > "databases" to work with. Are these the equivalent of ORACLE > "schemas"? Roughly, yes. It's a good enough analogy to work with. > The various scripts that I've looked at tell me how much free space > there is in all the chunks or dbspaces (and I know that onstat gives > similar information). How do I determine how much space a particular > "database" has left? Does a "database" live in only 1 dbspace? Can the > "database" use all the space in all the chunks on which it resides? > > Any help on this would be greatly appreciated. The question 'how much space is left in a database' is a little diffuse. It could mean a number of different things. However, you are probably being asked how long before you will need to allocate new disk space, more or less indirectly - or how much data can I add to a particular table, or set of tables, before I need to allocate new space. The answer is complicated in full detail. The disk space is allocated in three classes of 'space' - dbspaces, blob spaces, and smart blob spaces or sbspaces. If you are using IDS 7.x, then you don't have sbspaces to worry about - they can only exist in IDS 9.x. Blob spaces can be used to store BYTE or TEXT blobs (but the data can also be stored 'IN TABLE' - in the same dbspace as the regular data in a table). Sbspaces are used to store BLOB or CLOB blobs. All other data, and indexes and control information and so on is stored in regular dbspaces. You are probably primarily concerned about regular data and hence the dbspaces. All the 'spaces' can be built up of multiple chunks; a single chunk belongs to a single dbspace or sbspace or blob space, but each dbspace (etc) can have one or many chunks in it. Tables can be either fragmented or non-fragmented. Non-fragmented tables, by definition, live in a single dbspace; fragmented tables live in more than one dbspace. Indexes can be attached to a non-fragmented table, in which case they live in the same dbspace as the table, or they can be detached, in which case they can live in other dbspaces (and there are semi-detached indexes too, but I'm going to ignore them). Any given table and its associated indexes is limited in size by the sum of the sizes of the dbspaces in which it is stored - but you can always (nearly always) increase the size of a dbspace by adding new chunks to it. It is also constrained, of course, by the size of the other tables that share the same dbspaces. When you create a database (Informix style), you can specify the dbspace that will contain the system catalog; this dbspace will also be used for tables and indexes when you don't specify an alternative dbspace. When you create a table, you can specify the dbspace or dbspaces in which it will be stored. Similarly with an index. There is nothing that prevents several Informix databases from sharing the same dbspaces (though there are certainly advantages to enforcing rules that a single dbspace is used by a single database). Using 'onstat -d' to determine amount of disk space available in each of the chunks and dbspaces gives you a large part of the answer. You might also need to use 'dbschema -ss dbase' to find out which dbspaces are actually used by the database whose growth you are interested in. OTOH, if your IDS system was not set up with multiple dbspaces, then you don't need to worry about this - you only have a single dbspace to be concerned with. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/