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>— 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.
The post Query DB2 From PowerShell first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.