Wednesday, July 24, 2013

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


columndefinition
databasethe name of the database
spacethe storage space this database was created in
ownername of the user who created or owns the database
createddate that the database was created
log1=database has logging, 0=no logging
buff1=database has buffered logging
ansi1=database has ANSI mode logging
alloc mbthe total number of mb allocated to this database
usedthe total mb used by the database
datathe amount of mb used by the data in the tables
indexthe amount of mb used by the indices
scansthe 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

No comments:

Post a Comment