How to Get a List of Local DB2 Databases

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!

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

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