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=”https://t.co/0WoDdPvMFF”>https://t.co/0WoDdPvMFF</a></p>&mdash; Tim (@hantu0) <a href=”https://twitter.com/hantu0/status/1328793837003812866?ref_src=twsrc%5Etfw”>November 17, 2020</a></blockquote> <script async src=”https://platform.twitter.com/widgets.js” 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 https://gist.githubusercontent.com/SQLvariant/e9bede8a6bf4e65408da1f0a7f7faffc/raw/65a3f934e3d14223173d56edcb2d079d8ec58441/Invoke-DB2Query.ps1 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.

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

3 Responses

  1. Hi Aaron,
    I am trying to use Powershell to obtain the results (success or failure) of all the maintenance jobs (i.e. runstats, reorg etc.) on a DB2 server running on Windows 2012. I have read where the file called db2diag.log might contain this information. I also read where a tool called db2diag can help find this information but again I am not sure. Could you point me to someone who has Powershell experience and what to file to parse and what tool to use to find this information. I plan to run this as a report so we can monitor the DB2 instances we have in house.
    Thanks,
    mark

    1. Mark,

      The easiest way to get that information is by querying for it. Some of it is in the diag log, and some in the database history, but really when using powershell, it’s easiest to query for it. There are a number of places querying for it makes sense. For runstats, check out:https://datageek.blog/en/2017/03/16/db2-administrative-sql-cookbook-when-were-runstats-done-beyond-stats_time/https://datageek.blog/en/2015/02/26/db2-administrative-sql-cookbook-looking-at-how-current-statistics-are/
      Reorgs/backups:https://datageek.blog/en/2018/07/17/the-db2-recovery-history-file/
      You can also query the diag log:https://datageek.blog/en/2013/07/02/sql-access-to-the-db2-diagnostic-log/

      I have a few random queries lying around for such things that I mostly execute from jupyter notebooks, but that would work fine via powershell if you can’t find or figure out what you need. I have a fair amount on monitoring overall in blog entries over the years, including:https://datageek.blog/en/2017/06/22/embers-take-on-monitoring-db2-luw/

      1. Thank you Ember…The links were very helpful. I did visit your blogs but wasn’t connecting the dots until I went through the links you supplied.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner

%d bloggers like this: