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

3 comments

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.