Introduction to Using the PowerShell Command Line with DB2 on Windows

I have worked with DB2 on Windows on and off over the years and have largely not enjoyed it all that much. Most likely because the vast majority of my time is spent on UNIX and Linux systems, so when I end up at a windows command line, my fingers type things like “ls” and “grep” before I can even stop them. I think this is a common condition for DB2 consultants and DBAs – most of us spend the majority of our time on Linux or UNIX or even both and then have to jump into a Windows system and still be proficient.

After a week of spending more time with a Windows database server, I was ready to try just about anything. My husband happens to work with VMWare, and is always talking about PowerShell, so I thought I’d give it a try. I didn’t even realize until I looked into it that I could use a PowerShell command line. This post focuses on the use of PowerShell at the command line with DB2. I’m sure I’ll have follow-on posts about actually scripting in PowerShell

About PowerShell

I am by no means an expert. 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 Windows2008, PowerShell is available by default. You can also bolt it on to Windows 2003. This makes it qualify for my “lowest common denominator” for new scripts moving forward.

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 command window and use db2cmd 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:

Clicking on that top entry will get you into PowerShell. You will then have to configure PowerShell to run DB2 commands using this command:

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

Better yet, set it up so that PowerShell automatically does this whenever you bring up PowerShell as this user (the DB2 instance owner). To do this, 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 double click it, you will get a PowerShell prompt that you can execute DB2 commands at.

Useful Basics

List Files

One thing that has really driven me nuts in the past is that to list files, my fingers type “ls -latr” before my brain is even in the loop. In a traditional DB2 command window, this leads to a minor error and a minor swear from me as I engage my brain in the process to remember what the command is on Windows.

I cannot tell you how happy I was the first time I typed just “ls” at the power shell prompt and got approximately what I was expecting. It really made my whole evening.

But the fact remains that I still type “ls -latr” which still gets me an error in PowerShell. I haven’t yet figured out how/if I can alias that out, but aliases do exist in PowerShell. 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

One of the things I’ve always missed at a Windows command line was grep. I use grep a lot, usually running some command and piping it to grep to get only the information I want. Well, grep doesn’t appear in the default PowerShell aliases, but I can at least generally emulate its behavior with the select-string commandlet:

PS E:\DB2\NODE0000> db2 get dbm cfg | select-string -pattern "DIAGPATH"

 Diagnostic data directory path               (DIAGPATH) =

Remember also, that 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

I’m sure there are many more cool things that can be done with select-string.

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.