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.
- 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 =
- Look at the ouput and find entries with a
Directory Entry Type
ofIndirect
– in the example here, that’s all three of them - Of the
Indirect
entries, look for the unique values forDatabase Name
- If there is more than one
Database alias
for a givenDatabase name
, determine which alias I want to use for that database - 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!
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} ‘
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
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.
Hi is there way to retrieve the list of active databases from a query inside one of the databases?
Well, it is not a query, but you can use list active databases:
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:
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).
You can’t edit the file, only manipulate it with catalog commands. Technically, it’s stored in DB2\SQLNODIR and on linux/UNIX in sqllib/sqlnodir.
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” }}
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]}
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