Simple infx commands: infx dbs

It has always seemed strange to me, that a database management system, such as Informix, has no command to list all the databases.

You need to use SQL to get this information out.

Of course, if you use infx you can easily list out all the databases in your instance using the infx dbs command.

infx dbs command

Here is an example of the infx dbs command output:

database      space  owner     created    log  buff  ansi  alloc mb    used   data  index  scans
bartest       dat01  informix  07/14/2012   1     0     0      3.08    2.11   1.43   0.69     17
monitor_jgh1  dat01  informix  07/14/2012   1     1     0    201.92  146.91  53.82  93.10     17
mtest         dat02  informix  07/15/2013   1     0     0  1,154.65    2.05   1.27   0.78     14
schema_test   dat01  informix  07/18/2013   1     0     0      2.85    1.90   1.23   0.66     13
stores_demo   dat03  informix  07/20/2013   1     1     0     14.70   12.32  10.50   1.81     30
sysadmin      root   informix  07/14/2012   1     0     0     12.36   10.15   4.84   5.31  3,701
sysmaster     root   informix  07/14/2012   1     0     0      3.66    2.58   1.57   1.01     19
sysuser       root   informix  07/14/2012   1     0     0      3.18    2.19   1.44   0.75     17
sysutils      root   informix  07/14/2012   1     0     0      5.78    3.35   1.71   1.64     17

the output described

column	definition
database	the name of the database
space	the storage space this database was created in
owner	name of the user who created or owns the database
created	date that the database was created
log	1=database has logging, 0=no logging
buff	1=database has buffered logging
ansi	1=database has ANSI mode logging
alloc mb	the total number of mb allocated to this database
used	the total mb used by the database
data	the amount of mb used by the data in the tables
index	the amount of mb used by the indices
scans	the total number of sequential scans executed in this database, since last statistics reset

other options

This command, as with all infx commands, has filtering options for each column.

Example: only databases in the dat02 storage space

demo1@bobii:/home/informix>infx dbs space=dat02

database  space  owner     created     log  buff  ansi  alloc mb  used  data  index  scans
mtest     dat02  informix  07/15/2013    1     0     0  1,154.65  2.05  1.27   0.78     14

Example: change the order so that the largest database is first

demo1@bobii:/home/informix>infx dbs order=alloc

database      space  owner     created     log  buff  ansi  alloc mb    used   data  index  scans
mtest         dat02  informix  07/15/2013    1     0     0  1,154.65    2.05   1.27   0.78     14
monitor_jgh1  dat01  informix  07/14/2012    1     1     0    201.92  146.91  53.82  93.10     17
stores_demo   dat03  informix  07/20/2013    1     1     0     14.70   12.32  10.50   1.81     30
sysadmin      root   informix  07/14/2012    1     0     0     12.36   10.15   4.88   5.27  3,735
sysutils      root   informix  07/14/2012    1     0     0      5.78    3.35   1.71   1.64     17
sysmaster     root   informix  07/14/2012    1     0     0      3.66    2.58   1.57   1.01     19
sysuser       root   informix  07/14/2012    1     0     0      3.18    2.19   1.44   0.75     17
bartest       dat01  informix  07/14/2012    1     0     0      3.08    2.11   1.43   0.69     17
schema_test   dat01  informix  07/18/2013    1     0     0      2.85    1.90   1.23   0.66     13

Any column can be used for filtering and ordering. For more information about the infx command line utilities see: infx command line reference