How to Get a List of Local DB2 Databases

Posted by

The database directory is something many of us use every day. It includes informaiton for connecting to databases, both local and remote, as well as additional information like alternate servers and filesystems where database directories reside. Getting just the local database names out of it may take a bit of practice.

Please use the code here cautiously, after thorough testing, and at your own risk.

By Eyeball

Before I script anything, I like to define how I’m determining the information when I look at it myself. Here’s the full process that I use when I’m looking at this by eye.

  1. List the database directory:
    $ db2 list db directory
    
     System Database Directory
    
     Number of entries in the directory = 3
    
    Database 1 entry:
    
     Database alias                       = SAMPL
     Database name                        = SAMPLE
     Local database directory             = /db2home/db2inst1
     Database release level               = 10.00
     Comment                              =
     Directory entry type                 = Indirect
     Catalog database partition number    = 0
     Alternate server hostname            =
     Alternate server port number         =
    
    Database 2 entry:
    
     Database alias                       = TEST
     Database name                        = TEST
     Local database directory             = /db2home/db2inst1
     Database release level               = 10.00
     Comment                              =
     Directory entry type                 = Indirect
     Catalog database partition number    = 0
     Alternate server hostname            =
     Alternate server port number         =
    
    Database 3 entry:
    
     Database alias                       = SAMPLE
     Database name                        = SAMPLE
     Local database directory             = /db2home/db2inst1
     Database release level               = 10.00
     Comment                              =
     Directory entry type                 = Indirect
     Catalog database partition number    = 0
     Alternate server hostname            =
     Alternate server port number         =
  2. Look at the ouput and find entries with a Directory Entry Type of Indirect – in the example here, that’s all three of them
  3. Of the Indirect entries, look for the unique values for Database Name
  4. If there is more than one Database alias for a given Database name, determine which alias I want to use for that database
  5. From the list above that would give me:
    SAMPLE
    TEST

This isn’t hard when you have a single screen of output for the database directory. But when the output is longer, this is harder to parse with just your eyes. I have clients who have up to 15 production databases on a single instance. While I wouldn’t recommend that, sometimes it happens. Also, sometimes you need to get this list of databases programmatically to work with each database in turn.

Shell Scripting on Linux

The quick and dirty form of this for Linux is one of the following:

db2 list db directory | grep Indirect -B 5 |grep "Database alias" |awk {'print $4'} |sort -u | uniq

or

db2 list db directory | grep Indirect -B 5 |grep "Database name" |awk {'print $4'} |sort -u | uniq

The first has the risk of giving you the same actual database by two different aliases. The second has the risk of giving you a database name for which there is no exact match in database alias, and that you therefore cannot work with. I’ve worked in environments that could have either issue, but never one that could have both.

Of course, that’s not good enough for me. I want to get only one alias back for each local database, and make sure I can use it. To do that, I can use this:

#!/bin/bash
typeset -A db_hash
while read a n; do
  db_hash["${a}"]=${n}
done < <( db2 list db directory | grep Indirect -B 5 | grep -E 'alias|name' | awk {'print $4'} | sed 'N ; s+\n+ +g' )

while read v; do
  n=0
  echo "val:  $v"
  for i in "${!db_hash[@]}"
  do
    if [[ ${db_hash[$i]} == $v ]]
    then
      (( ${n} > 0 )) && unset db_hash[$i] 
      ((n++))
    fi
  done
done < <( printf '%s\n' "${db_hash[@]}"|awk '!($0 in seen){seen[$0];next} 1' )

echo "${!db_hash[@]}"

That's not quite what I'd call complete code - there's a bit too little error checking for my liking, but hopefully it is logic that others can build on. It takes the first database alias it finds for a given local database.

Shell Scripting on AIX

I'm a big fan of the -p option for grep on AIX. The quick and dirty versions for AIX are:

db2 list db directory | grep -p Indirect |grep "Database alias" |awk {'print $4'} |sort -u | uniq

or

db2 list db directory | grep -p Indirect |grep "Database name" |awk {'print $4'} |sort -u | uniq

And here's the duplicate eliminating version without error checking on AIX:

#!/bin/ksh93
typeset -A db_hash
db2 list db directory | grep -p Indirect | grep -E 'alias| name' | awk {'print $4'} | sed 'N;s/\n/ /' | while read a n; do
  db_hash["${a}"]=${n}
done

printf '%s\n' "${db_hash[@]}"|awk '!($0 in seen){seen[$0];next} 1' | while read v; do
  n=0
  echo "val:  $v"
  for i in "${!db_hash[@]}"
  do
    if [[ ${db_hash[$i]} == $v ]]
    then
      (( ${n} > 0 )) && unset db_hash[$i]
      ((n++))
    fi
  done
done

echo "${!db_hash[@]}"

PowerShell on Windows

Here are the quick and dirty versions on PowerShell (no way I'm even attempting this in batch):

db2 list db directory |select-string Indirect -context 10,1 | Select -Expand context | Select -Expand PreContext |select-string 'alias' | Select -Expand Line | foreach-object { $_.split(" = ")[-1]}

or

db2 list db directory |select-string Indirect -context 10,1 | Select -Expand context | Select -Expand PreContext |select-string 'name' | Select -Expand Line | foreach-object { $_.split(" = ")[-1]}

And here is the more complicated version in PowerShell:

set-item -path env:DB2CLP -value "**$$**"
$db_hash = @{}
clear-variable alias
clear-variable name
db2 list db directory |select-string Indirect -context 10,1 | Select -Expand context | Select -Expand PreContext |select-string 'alias|name' | convertfrom-stringdata | foreach-object { 
  if ($_."Database alias") { $alias=$_."Database alias" }
  if ($_."Database name") { $name=$_."Database name" }
  if ( $alias -ne $null -and $name -ne $null ) {
    $db_hash.Add($alias, $name)
    clear-variable alias
    clear-variable name
  } #end if on alias and name 
} # end foreach-object
$ht = @{} 
#$db_hash.keys | 
foreach ($key in $($db_hash.keys)) {
  $ht[$db_hash["$key"]] += 1
  if ( $ht[$db_hash["$key"]] -gt 1 ) { $db_hash.remove("$key") }
}
$db_hash

I'm sure that there might be more elegant or more efficient solutions out there - I would love to hear about any in the comments below!

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

10 comments

  1. 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. 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

    1. 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.

    1. 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.
      
  3. Hi Ember,

    Thanks for the wonderful information.

    In Windows, what is the source file from which the “db2 list node directory” gets the protocol , hostname and Service name(port number).

  4. I worked on the solution. In this way I am able to convert the output to an hashmap, and convert the hashmap to objects.

    db2 list db directory|Select-String “Directory entry type” -Context 10,1|ConvertFrom-StringData|% { [PsCustomObject]@{alias=$_.”Database alias”;name=$_.”Database name”;directory=$_.”Local database directory”;release=$_.”Database release level”;comment=$_.Comment;type=$_.”> Directory entry type” }}

    There is also a local directory (unfortunately one key differs)
    db2 list db directory on d:|Select-String “Directory entry type” -Context 10,1|ConvertFrom-StringData|% { [PsCustomObject]@{alias=$_.”Database alias”;name=$_.”Database name”;directory=$_.”Database directory”;release=$_.”Database release level”;comment=$_.Comment;type=$_.”> Directory entry type” }}

  5. For the German version of the PowerShell One-liner you can use:
    db2 list db directory |select-string Indirekt -context 10,1 | Select -Expand context | Select -Expand PreContext |select-string ‘Alias | Select -Expand Line | foreach-object { $_.split(” = “)[-1]}

  6. An one liner with sample output:
    $ echo `db2 list db directory | sed 1,5d | sed ‘s/Database .* entry:/DB:/g’ | egrep -v ‘^ [AC]|^ .*rel’ | sed ‘s/ .*= //g’`| sed ‘s/DB: /\n/g’ | sed 1d | awk ‘{printf “%9s %9s %18s %9s \n”, $1,$2,$3,$4}’
    ALIAS1 SAMPLE NODE1 Remote
    GSDB GSDB /db2/data Indirect
    SAMPLE SAMPLE /home/db2inst1 Indirect

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.