Using PowerShell to Access DB2

  • Article

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