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
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:
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:
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.
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
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:
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 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.
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:
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
rm and others. You can list the full list of aliases in your environment, including any custom ones, by issuing:
The standard redirection characters – |, >, < work in PowerShell, as does a favorite of mine – tee.
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.
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:
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.
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
Any ideas ?
Are you opening powerShell using “Run as Administrator”?
Thanks a lot! It worked fine here.
BTW … You mentioned you would like to have
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:]