How to Get a List of Local DB2 Databases

You may also like...

5 Responses

  1. Luiz da Silva says:

    A similar way using AWK only:

    db2 list db directory | awk ‘/Database alias/{db_alias=$4}/Directory entry type/{db_type=$5;if(db_type == “Indirect”)print db_alias} ‘

  2. Eric Sheridan says:

    Ember,

    An option I prefer to use is “db2 list active databases”. (This command requires SYSADM, SYSCTRL, SYSMAINT, or SYSMON.) Granted this command will only list the active databases, which in a production environment I would think you would want all of the databases active. The output will list only the locally catalog databases and stripe out any aliases.

    On another note, the “db2 list db directory” will only list the catalog databases. There may be databases that were uncataloged that reside in the same directory. To see if there are any uncataloged databases use “db2 list db directory on “. For example “db2 list db directory on /db2home/db2inst1”

    Thanks,
    Eric Sheridan

    • Ember Crooks says:

      Good points. I’ve never been a fan of listing active databases because I often care about inactive databases. And I rarely completely uncatalog databases I care about in any way – only in edge cases for very short amounts of time.

  3. Ronny says:

    Hi is there way to retrieve the list of active databases from a query inside one of the databases?

    • Ember Crooks says:

      Well, it is not a query, but you can use list active databases:

      $ db2 list active databases
      
                                 Active Databases
      
      Database name                              = SAMPLE
      Applications connected currently           = 0
      Database path                              = /db2home2/db2inst2/db2inst2/NODE0000/SQL00001/MEMBER0000/
      
      Database name                              = TEST
      Applications connected currently           = 0
      Database path                              = /db2home2/db2inst2/db2inst2/NODE0000/SQL00002/MEMBER0000/
      

      Of course, any database that is not active at that moment will not show up in that list. As long as you only care about active databases, you can cheat and use mon_get_memory_pool like this:

      $ db2 "select distinct(db_name) from table(mon_get_memory_pool('','',-2))"
      
      DB_NAME                                                                                                                         
      --------------------------------------------------------------------------------------------------------------------------------
      SAMPLE                                                                                                                          
      TEST                                                                                                                            
      -                                                                                                                               
      
        3 record(s) selected.
      

Leave a Reply

Your email address will not be published. Required fields are marked *