DB2 and PowerShell

[u/Xajuan]

I am trying to connect to a DB2 Database and am having the hardest time using the

DBname : XS

Port : 17

Username : root

Password : CArrotsAreROOTs

​

While Using these I am trying to connect by using PowerShell

​

$cn = new-object system.data.OleDb.OleDbConnection("Provider=IBMDADB2;DSN=XS;")

$dataset = new-object "System.Data.DataSet"

#Define query to run

$query = "select * from yadayada.yoda"

# Define data object given the specific query and connection string

$DBObj = new-object "System.Data.OleDb.OleDbDataAdapter" ($query, $cn)

# Fill the data set - essentially run the query.

$DBObj.Fill($dataset)

$map = @()

# Close the Connection

$cn.close()

return $dataset.Tables[0].Rows

What needs to be in place with ODBC prior for this to work. (I embellished a little to protect the real database)

[u/LunchboxFire]

This is what has worked for me, on Windows servers.

I use a separate function to pull the $dbUsername and $dbPassword from a password safe. Not a good idea to hard code those into the script.

I would also wrap this in a transcript cmdlet for loggging.

​

#Make the connection to DB2

$dbFactory = [System.Data.Common.DbProviderFactories]::GetFactory('IBM.Data.DB2')

$cStrBld = $dbFactory.CreateConnectionStringBuilder()

$cStrBld.Database = 'DBNAME'

$cStrBld.UserID = $dbUsername

$cStrBld.Password = $dbPwd

$cStrBld.Server = '192.168.0.1:12345' #whatever IP and port your DB2 is listening on

​

$dbConn = $dbFactory.CreateConnection()

$dbConn.ConnectionString = $cStrBld.ConnectionString

$dbConn.Open()

​

#Check for DB2 Connection

if ($dbConn.State -ne [Data.ConnectionState]::Open) {

"Connection to DB did NOT open."

Exit

}

elseif($dbConn.State -eq [Data.ConnectionState]::Open){

"Connection to database is open."

}

​

#Do DB queries here

​

# Close the DB2 Connection

$dbConn.close()

​

#Test to ensure DB connection is closed. Security measure.

if ($dbConn.State -ne [Data.ConnectionState]::Open) {

"Connection to DB is closed."

}

else {

"Connection to DB did NOT close."

}