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!

You may also like...

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

Leave a Reply

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