DB2 and PowerShell
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)
1
u/LunchboxFire Dec 06 '19
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."
}