Using PowerShell to Access DB2


Yes, PowerShell can be used to access DB2 using The Microsoft Host Integration Server Data Providers.


 


Not a tutorial, but here is the script, ran using HIS 2004’s data provider:


$cn = new-object system.data.OleDb.OleDbConnection(“Provider=DB2OLEDB;User ID=<userid>;Password=<password>;Initial Catalog=<catalog>;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=DB2V82;Network Port=50000;Package Collection=<collection>;Default Schema=<schema>;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;”);


$ds = new-object “System.Data.DataSet” “dsTest”


$q = “SELECT ID_NUMBER”


$q = $q + ”      ,FIRST_NAME”


$q = $q + ”      ,LAST_NAME”


$q = $q + ”      ,CITY”


$q = $q + ”      ,STATE”


$q = $q + ”  FROM <schema>.ADELINS”


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


$da.Fill($ds)


 


$dtPerson = new-object “System.Data.DataTable” “dtPersonData”


$dtPerson = $ds.Tables[0]


$dtPerson | FOREACH-OBJECT { ” ” + $_.ID_NUMBER + “: ” + $_.FIRST_NAME + “, ” + $_.LAST_NAME + “, ” + $_.CITY + “, ” + $_.STATE }


 


Output (the values are dummy ones in the table and don’t match the column names, but everything is correct):


PS C:\scripts> c:\scripts\db2query.ps1


15


 1         : 1, 2, 3, 4


 1         : 2, 3, 4, 5


 1         : 3, 4, 5, 6


 1         : 4, 5, 6, 7


 1         : 5, 6, 7, 8


 x         : 6, 7, 8, 9


 x         : 7, 8, 9, 10


 x         : 8, 9, 10, 11


 x         : 9, 10, 11, 12


 x         : 10, 11, 12, 13


 1         : 6, 7, 8, 9


 1         : 7, 8, 9, 10


 1         : 8, 9, 10, 11


 1         : 9, 10, 11, 12


 1         : 10, 11, 12, 13


 


A more useful script is this one, that pings an AS400 (in this case), then tries to open a socket to the DDM port (446) to see if it’s listening:


$ip =”172.29.136.200″


$ping = new-object System.Net.NetworkInformation.Ping


$rslt = $ping.send($ip)


if ($rslt.status.tostring() -eq “Success”)


{


       write-host “ping worked”


       # if the ping works, try opening a socket to the DDM port


       $port = 446


       $socket = new-object System.Net.Sockets.TcpClient($ip, $port)


       if ($socket -eq $null)


       {


              write-host “could not open DDM socket”


       }


       else


       {


              write-host “got socket to DDM”


              $socket = $null


       }


}


else


{


       write-host “ping failed”


}


$ping = $null


 


Output:


C:\scripts>powershell c:\scripts\portping.ps1


ping worked


got socket to DDM