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.
- Certifications and Badges
3 comments