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 Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 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 for XTIVIA, leading a team of Db2 DBAs.

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.