Blog Post

Query DB2 From PowerShell


Story time:

A few months ago I was in a meeting where we were all asked how we could take on a complex, but very necessary task. As they went around to everyone on the call, everyone said it wasn’t possible to do without a major (dedicated personnel) effort. When they got to me, I said something like “well, maybe, if only I could do these two thigs in PowerShell, I might be able to achieve the end result you’re after”. (Please picture the scene in The Princess Bride where Inigo & Fezzik ask Westley to figure out how to raid the castle. Westley tells them it’s impossible. And then a moment later he asks for a wheelbarrow & cloak.) Well, that’s exactly where I was, I needed just 2 things to attempt the impossible.

Side Note: If you’re a regular reader here, before you say it, I know what you’re thinking. You’re thinking “wow Aaron, you’ll do *anything* to come up with a reason to blog about PowerShell, won’t you?”

Solution time:

One of the two things I needed to be able to do in PowerShell was to query a DB2 database. I had a look on the PowerShell Gallery and the search didn’t return anything for DB2. So, I did what I’m fairly well known for doing, I asked for help on twitter.

Just a few minutes later Tim replied and pointed me to some code on Ember Crooks’ blog.

<blockquote class=”twitter-tweet”><p lang=”en” dir=”ltr”>This might be handy.<a href=””></a></p>&mdash; Tim (@hantu0) <a href=””>November 17, 2020</a></blockquote> <script async src=”” charset=”utf-8?></script>

This was a huge help! It wasn’t quite what I needed, but it was close enough to get me going. I took the code from Ember Crooks’ GitHub and merged it with some code from way back when the old SQLPS module had a whopping 5 cmdlets. After I got it working, I turned it into a PowerShell function, to make it easier to use.

I’ve put this code in a Gist so that I can get to it easily. But since I don’t work with DB2 very often I figured the code would have a better home back with Ember, so I did a PR against her repo.

How to use it:

You easily can download a copy of the script using PowerShell:

PS C:temp> Invoke-RestMethod Uri OutFile Invoke-DB2Query.ps1

If you found this blog post because you work with DB2 and are new to PowerShell, I’ll give you a couple quick tips. I wrote this script to be a function, a function is like a baby cmdlet. To use the function you can either copy paste the code into your session and run it, or you can dot-source it like below.

PS C:temp>. .Invoke-DB2Query.ps1

After that, you can you the Get-Help cmdlet to get PowerShell to tell you a few examples of how you can use this function.

PS C:temp>Get-Help Invoke-DB2Query Full

OK, that’s about it for the quick tour.

Some other things:

You might notice I didn’t include parameters for username & password, and that’s because I included trusted_connection=true. If you need to use a username & password instead, just swap out that piece with User Id=;Password= from Ember’s code.

Finally, you might be wondering what was the other thing I needed in PowerShell, or what the ‘impossible’ task was. Those will take multiple blog posts to cover, but I will get around to them before too long, I promise.

The post Query DB2 From PowerShell first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating