Working with Db2 copies …on Windows

Posted by

How to investigate Db2 copies installed on a Windows system

According to one of the definitions from the UrbanDictionary, adding the words “in bed” to any sentence can make that sentence “more interesting”. I like to think that the Db2 LUW world equivalent of that is taking a task – such as working with Db2 copies – and adding words “…on Windows”. This also seems to make the task “more interesting”.

Why is it more interesting “on Windows”? Take for example the basic task of listing the Db2 copies installed on a system. On Linux, it is pretty simple – you simply use the db2ls command:

$ db2ls

Install Path                       Level   Fix Pack   Special Install Number   Install Date                  Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V10.5               10.5.0.8        8                            Mon Feb 18 17:17:40 2019 EST             0
/opt/ibm/db2/V11.1               11.1.3.3        3b                           Wed Mar 13 11:44:19 2019 EDT             0

or, for more in-depth information, the db2greg command:

$ db2greg -dump
S,TSA,4.1.0.3,/opt/IBM/tsamp,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1531416065,0
S,RSCT,3.2.3.1,/usr/sbin/rsct,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1531416065,0
S,DB2,11.1.3.3,/opt/ibm/db2/V11.1,,,3,0,,1531416136,0
V,DB2GPRF,DB2SYSTEM,ubuntu,/opt/ibm/db2/V11.1,
I,DB2,11.1.3.3,db2inst1,/db2home/db2inst1/sqllib,,1,0,/opt/ibm/db2/V11.1,,
I,DB2,11.1.3.3,db2,/home/db2/sqllib,,1,0,/opt/ibm/db2/V11.1,,
...

The output from db2greg has the additional benefit of listing the instances running under a given copy of Db2. Be careful with db2greg though:

Incorrect usage of this command can damage the global registry.


https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/r0020699.html

However, if one reads the KC entries for the two commands carefully, the “interesting” part is soon revealed – neither of these commands exist (at least in Db2 11.1) on Windows systems.

Instead, on Windows, one could use the db2swtch (switch default Db2 copy and database client interface copy). Invoked without any command line parameters, the utility will start a GUI where the copies installed will be listed and which, optionally, allows for switching which copy should be considered by the system as the default one. In addition to the GUI mode, the command can be also invoked with several different command line parameters, like “-l” to get a list of the copies in the command line environment.

PS N:\> db2swtch.exe -l

DB2_11_1_4_4A    D:\IBM\DB2_11_1_4_4A\SQLLIB    (Default DB2 and IBM Database Client Interface Copy)

DB2_TEST         D:\IBM\DB2_TEST\SQLLIB

There does not seem to be a command equivalent to db2greg that would also show the instances. Nevertheless, Db2 on Windows also has a need to persist global registry setting, and that place is the Windows registry, in the HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2 registry hive. While there are command line utilities that can be used to retrieve information stored inside Windows registry (for further use for scripting purposes, for example), using these utilities is redundant if one is using PowerShell. PowerShell treats Windows registry just as if it was another file system, so the commands that one would use for navigating a file system work with Windows registry as well. It is very simple to investigate the installed Db2 copies using this method:

PS N:\> $DB2Copies = @(gci HKLM:\SOFTWARE\IBM\DB2\InstalledCopies\);
PS N:\> $DB2Copies
    Hive: HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies
Name                           Property
----                           --------
DB2_11_1_4_4A                  DB2 Folder Name         :
                               DB2 Update Service Code : {B57F10E6-8A32-48F0-899C-70C6FD02ED7A}
                               DB2 Path Name           : D:\IBM\DB2_11_1_4_4A\SQLLIB\

DB2_TEST                       DB2 Folder Name         :
                               DB2 Update Service Code : {B57F10E6-8A32-48F0-899C-70C6FD02ED7A}
                               DB2 Path Name           : D:\IBM\DB2_TEST\SQLLIB\

Since this is PowerShell, we can expect the data returned to be in form of objects or collections of objects rather than just plain text as would be the case with the output from db2swtch -l. This gives us the advantage of not having to parse the text output in order to retrieve the installation paths of the copies. With PowerShell, it is as simple as referencing the properties of an object or calling one of its methods. For example:

PS N:\> $DB2Copies | Where-Object {$_.PSChildName -eq "DB2_11_1_4_4A"} | ForEach-Object {$_.GetValue("DB2 Path Name")}

D:\IBM\DB2_11_1_4_4A\SQLLIB\

PS N:\>

So what about the instance information? It is there as well. We can iterate over all of the Db2 copy objects stored in the Windows registry and retrieve the instances installed in each copy stored under the Profiles subkey of each Db2 copy.

PS N:\> foreach ($DB2Copy in $DB2Copies) {gci HKLM:$DB2Copy\Profiles\ | Select-Object -ExpandProperty Name};

HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\DB2_11_1_4_4A\Profiles\INST_01
HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\DB2_11_1_4_4A\Profiles\INST_02
HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\DB2_11_1_4_4A\Profiles\INST_03
HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\DB2_11_1_4_4A\Profiles\INST_04

HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\DB2_TEST\Profiles\INST_T

NOTE:

The code examples provided above will work as long as IBM keep storing the Db2 global registry metadata in the HKLM:\SOFTWARE\IBM\DB2\InstalledCopies Windows registry hive, and the instance information in the Profiles subkey of the individual instances. IBM could change this at any time without prior warning.

ACKNOWLEDGEMENTS:

Many thanks to Ember Crooks and Ian Bjorhovde for showing me how this is done on Linux and for providing text captures of outputs from Linux Db2 utilities – you guys rock!

Luke Numrych has been employed in various IT fields ranging from mainframe operations to software development since 1995. He has been involved in supporting DB2 LUW on Windows since the beginning of 2011, transitioning fully to a production support DBA for DB2 databases in an OLTP environment in March of 2012. Currently Luke is a Senior DB Engineer for a company in the Financial Services sector.
Luke is a member and, since 2015, Secretary of the Wisconsin DB2 Users’ Group.

Leave a Reply

Your email address will not be published.

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