Using PowerShell to Access DB2

 

Charles's Host Integration Server Ramblings

The intent is to share some knowledge about Host Integration Server (HIS) that are not well documented, not well known, as well as various tidbits that come up when supporting HIS and all the various components.

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

Published Friday, October 31, 2008 12:44 PM by Charles Ezzell

Filed under: DB2, ping, Powershell

Charles's Host Integration Server Ramblings : Using PowerShell to Access DB2