How to Connect to a Local DB2 Database Without Specifying a Password in PowerShell

Posted by

One of the awesome things about running scripts locally on a DB2 server is that if they’re run as a privileged user, you do not have to specify the password. This makes for easier scripting without storing or encrypting passwords. When I first connected to a database with PowerShell, it took me a bit to figure out how to do the password-less local connection, so I thought I would share.

Please note that I started with the connection information and script available here: http://myblog4fun.com/archive/2012/01/14/using-powershell-to-access-db2.aspx

To connect to the SAMPLE database and run a simple query, this syntax works:

$cn = new-object system.data.OleDb.OleDbConnection("Provider=IBMDADB2;DSN=SAMPLE;User Id=;Password=;");
$ds = new-object "System.Data.DataSet" "dsEmployee"

$q = "SELECT EMPNO"
$q = $q + ",FIRSTNME"
$q = $q + ",LASTNAME"
$q = $q + " FROM ECROOKS.EMPLOYEE"

$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $cn)

$da.Fill($ds)

$dtPerson = new-object "System.Data.DataTable" "dtEmployee"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { " " + $_.EMPNO + ": " + $_.FIRSTNME + ", " + $_.LASTNAME }

I found the connection string syntax that would work here: http://129.33.205.81/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.apdv.sample.doc/doc/vb/s-ReadMe-txt.html?lang=en

While that sample is directed at Visual Basic, the connection strings are the same, so I was able to add the syntax in to the PowerShell script from the other blog.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

5 comments

  1. Ember,
    I have been working with PowerShell and DB2 together for some time. I figured out how to Carry out DB2 using Powershell with no need for Password sometime ago. Unfortunately, I feel like the way I am doing it is causing me some other issues when it comes to creating reports from my SQL. I have extensive DB2 knowledge, but my PowerShell is very weak.
    Basically, All I do to execute DB2 commands is to run them at the Command Line Level
    $DB2CMD = “C:\IBM\DB2\SQLLIB\BIN\DB2.exe”
    $Instance = “DEVINST1”
    Set-Item -Path env:DB2CLP -Value ‘**$$**’
    Set-Item -Path env:db2instance -value $Instance
    & $DB2CMD “Connect to devdatabase”
    & $DB2CMD “Select * from syscat.tables”
    & $DB2CMD “Terminate”

    I do have some issues, however where I would like to put those results (or other SQL) into an array, and it does not quite work out the way I want.

    Please feel free to email me if you need any more information.

    1. Yeah, this is always a workaround, but then you’re not using the drivers for that database really, which looses you benefits like things going into the proper data structures for you. You’ll have to manually parse the output to get access to fields by name, which can be quite time consuming to figure out.

      I used to use perl in a similar manner, and it wasn’t bad there once I got use to regular expressions.

      If it works for you, that’s fine, and there are actually some advantages with the way you’re doing it. This methodology will allow you to use non-SQL commands in addition to normal SQL.

Leave a Reply

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

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