888-685-3101, ext. 2

Introduction to Using the PowerShell Command Line for DB2 on Windows

by | Aug 6, 2015 | Blog, Data Management, Databases

A lot of DB2 LUW DBAs spend significantly more time working with DB2 on Linux and UNIX than they do working with DB2 on Microsoft Windows. This can lead to frustration when administering DB2 on Windows. Using a PowerShell command line for DB2 instead of a DB2 Command Window can ease the pain of switching back and forth, once you know some of the tricks.

This post focuses on the use of PowerShell at the command line with DB2, not scripting in PowerShell.

About PowerShell

In my job as a consultant, I have to go into a large number of environments, so it is important that I can work in the “lowest common denominator” of scripting languages. I may love Perl when I have the choice, but Perl requires actual installation for too many systems to be something I can rely upon having. My choices are generally then ksh for Linux/UNIX and in the past, batch for Windows. Starting with Windows 2008, PowerShell is available by default. You can also bolt it on to Windows 2003. This makes it qualify for my “lowest common denominator”.

Using PowerShell as Your Command Line

One of the frustrations on Windows for someone used to a command line has always been that you cannot easily just execute DB2 commands from any command line. You generally have to pull up a DB2 Command Window and use db2cmd.exe when executing batch scripts or DB2 commands from batch scripts. It is really easy to pull up and set up PowerShell to work with DB2.

All actions taken here are as the DB2 instance owner.

First to find PowerShell on a Windows server, you can use the search box on the start menu. This is what it looks like to do that on Windows 2008:

Right click on that top entry and select “Run as Administrator”. You will then have to configure PowerShell to run DB2 commands using this command:

set-item -path env:DB2CLP -value "**$**"

To set it up so that PowerShell automatically does this whenever you bring up PowerShell as this user (the DB2 instance owner), open up PowerShell. At the PowerShell prompt, do this:

notepad $profile

In the file opened, enter the same set-item command:

set-item -path env:DB2CLP -value "**$**"

Save and close the file. If you try opening a PowerShell window at this point, you will get this error:

To get around this error, execute the following in your PowerShell window:

Set-ExecutionPolicy remoteSigned

That will ask you a question and looks like this:

Obviously, you should understand the implications of changing this security-related parameter before changing it, particularly on a production system.

At this point, if you create an icon on the desktop for PowerShell by dragging it from your start menu search, and then right click and select “Run as administrator”, you will get a PowerShell prompt that works for DB2 commands.

Working with more than one DB2 Instance

Multiple DB2 instances are not as common on Windows as they are on Linux/UNIX. However, they are possible. Even more confusingly, on Windows there may not be a separate DB2 instance owner for each DB2 instance – so you may use the same ID on the server to administer more than one DB2 instance. This is in stark contrast to how multiple DB2 instances work on UNIX/Linux.

At the PowerShell command line, it is easy to switch which instance you’re working with – simply use this syntax:

set-item -path env:DB2INSTANCE -value instname

That assumes both instances are on the same copy of DB2. If they are on different DB2 copies, you must also set the path appropriately, so that all DB2 references in the path refer to the appropriate DB2 copy. An example of this is:

set-item -path env:PATH -value "%SystemRoot%system32WindowsPowerShellv1.0;C:Windowssystem32;C:Windows;C:Windowssystem32wbem;C:Windowssystem32windowspowershellv1.0;c:program filesibmgsk8lib64;C:PROGRA~1IBMSQLLIBBIN;C:PROGRA~1IBMSQLLIBFUNCTION;C:PROGRA~1IBMSQLLIBSAMPLESREPL"

The exact paths will depend on where you have DB2 installed for each DB2 copy.

Useful Basics

Once you have the PowerShell command line working for DB2 commands, knowing how to accomplish a few basics can go a long way.

List Files

Using ls works in PowerShell. This makes it easy for DBAs who are used to Linux and UNIX.

However, not all the options/flags of ls are available. To sort the files based on their date in ascending order, you can use:

ls | sort -property LastWriteTime

In PowerShell, ls is really just an alias for the Get-ChildItem PowerShell function. You can use man in PowerShell too, and it’s also helpful for using to look at aliases like ls to see what they actually point to, like so:

Grep

On Linux/UNIX, I use grep a lot, usually running some command and piping it to grep to get only the information I want. Grep doesn’t appear in the default PowerShell aliases, but the Select-String commandlet can emulate grep’s behavior:


PS E:DB2NODE0000> db2 get dbm cfg | select-string -pattern "DIAGPATH"

 Diagnostic data directory path               (DIAGPATH) =

Windows is not generally case sensitive, so using the syntax above is equivalent to the following command on Linux or UNIX:

db2 get dbm cfg | grep -i diagpath

Head and Tail

Fairly frequently, I want to see the first few or the last few lines of a file. I can do that fairly easily in PowerShell with the select comandlet:


PS D:xtivia> db2 get dbm cfg | select -first 15


          Database Manager Configuration



     Node type = Enterprise Server Edition with local and remote clients



 Database manager configuration release level            = 0x0b00



 Maximum total of files open               (MAXTOTFILOP) = 16000

Counting Lines

Counting lines of output can be quite useful in some scenarios, though I do more of this through SQL than I used to. Here’s one useful example:


PS> db2 list applications | Measure-Object -Line

                        Lines Words                         Characters                    Property
                        ----- -----                         ----------                    --------
                          473

You can obviously use Measure-Object for other purposes as well.

VI

I miss vi at the command line. The first command I issue after logging in to many Linux/UNIX servers is set -o vi. I love to be able to easily search through command history and am so used to vi editing commands that I use them automatically – it’s rather frustrating to do this at any other command line, because you end up typing ‘hhhhhhhhhhh’ when what you really wanted to do was scroll left.

I spent a bit of time searching and couldn’t find a way to get this functionality at the PowerShell command line. Please let me know if anyone knows a way.

On the other hand, I can use aliases in that same profile that I used to set up the automatic use of DB2 commands to make it so that instead of getting error messages every time my fingers type vim filename, I instead get notepad opening the file.

First, at a PowerShell prompt, I issue:

powershell_ise $profile

Then a scripting window pops up that helps me with syntax. With these aliases added, mine now looks like this:

Save and close, and the next time you open PowerShell, the aliases are available for use.

Other Useful Aliases

The default aliases include those for cp, mv, rm and others. You can list the full list of aliases in your environment, including any custom ones, by issuing:

Get-Alias

Redirection

The standard redirection characters – |, >, < work in PowerShell, as does a favorite of mine – tee.

Summary

With all this good stuff at the command line, I’m much more looking forward to scripting than I was with batch. I feel like it’s possible I won’t feel so lost coming from my Perl and ksh roots.