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.
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.
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.