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:
Screenshot_020615_053418_PM

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:
Screenshot_020615_055013_PM

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:
Screenshot_020615_055157_PM

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:
Screenshot_020615_061727_PM

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:
Screenshot_020615_063821_PM

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.

You may also like...

6 Responses

  1. Andres Gomez says:

    Very interesting article. I know it is not easy to get into PowerShell. I started a year ago by creating scripts in PowerShell to install my programs. I wanted to share them with you:

    * https://github.com/angoca/log4db2/blob/master/src/main/scripts/install.ps1
    * https://github.com/angoca/db2unit/blob/master/src/main/scripts/install.ps1

    • sabndeep says:

      Nice one Andres,I am new to powershell and to DB2.And have been assign to write a powershell script to install DB2 client in WIN& machine.Can you help me on this.

  2. Samuel Pizarro says:

    Hi Ember
    Nice article.

    Trying to use the powershell session, but facing issue with db2pd command.

    Or the output is empty for some parameters, or it complains that the database is not ACTIVE.
    But both instance and DB are active and running, and same commands works on regular DB2CLP window.

    Windows 2008 and/or 2012, and win-7
    Db2 10.5
    Any ideas ?

  3. Dejan says:

    Hi Ember!

    Thanks a lot! It worked fine here.

    BTW … You mentioned you would like to have
    grep
    ls -lart

    on WIN?
    How about tar bzip2 gzip dd diff du egrep fgrep head tail less pwd wc touch …?

    I don’t use PowerShell. So, this HowTo works for CMD
    Feel free to adapt it for PowerShell and please give me a hint in this case!
    Otherwise just run CMD!

    1. Visit https://sourceforge.net/projects/unxutils/
    2. Download Unix_Utils and feed your USB pen drive with Unix Utils – it’s just a ZIP Archive. There’s no need to install anything.
    3. In a WIN environment – just UNZIP or COPY Unix Utils into an arbitrary directory
    4. Normally, you should be allowed to change %PATH% … so just add the directory \UnxUtils\usr\local\wbin to %PATH%
    5. Finally … try
    ls -latr | grep [:digit:]

    Should you NOT be allowed to change %PATH% … Then:
    set UU=\UnxUtils\usr\local\wbin to %PATH%
    %UU%\ls -latr | %UU%\grep [:digit:]

    Have fun!
    Dejan

  1. August 18, 2015

    […] “command line environment not set up”.   There are several good posts on the internet on how to set up the db2 command line environment in PowerShell, and how to make the changes stick in the user profile so that they do not have to be re-done every […]

Leave a Reply

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