Need to connect to DB2 database with PowerShell

Question

I couldn't get the output for my query which I am trying to run thru PowerShell.

$connection = New-Object System.Data.OleDb.OleDbConnection("Provider=IBMDADB2;DSN=;User Id=;Password=");
$ds = New-Object "System.Data.DataSet"
$QuerySQL = "select * from omaa.status"
$da = New-Object System.Data.OleDb.OleDbDataAdapter($QuerySQL, $connection)
$da.Fill($ds)

I am getting this exception

Exception calling "Fill" with "1" argument(s): "Unspecified error"
At line:6 char:9
+ $da.Fill <<<< ($ds)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Duplicate of this --- Connecting to DB2 using powershell stackoverflow.com/questions/11862005/… — postanote
– postanote, Commented Jun 3, 2019 at 5:49
I am running as administrator ...still it's throwing this exception @postanote — Rmm
– Rmm, Commented Jun 3, 2019 at 6:42
Is your user member of one of the groups DB2Users or DB2Admns as the second answer to the other question suggests? — Ansgar Wiechers
– Ansgar Wiechers, Commented Jun 3, 2019 at 14:37

Tom-Inge Nilsen · Accepted Answer · 2022-03-18 10:46:18Z

I know this is an aging thread, but maybe this will help someone. I'm using this, and it works for Oracle and DB2 connections. (Not with acceleration for DB2 though). Exports CSV, but this can be modified to convert to xlsx afterwards.

function Get-OLEDBData ($connectstring, $sql) {            
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)            
   $OLEDBConn.open()            
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)            
   $readcmd.CommandTimeout = '300'            
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)            
   $dt = New-Object system.Data.datatable            
   [void]$da.fill($dt)            
   $OLEDBConn.close()            
   return $dt            
}

#For Oracle
$connString = "password=yourPassword;User ID=YourID;Data Source=YourServer;Provider=OraOLEDB.Oracle"            

#For DB2
$connString = "Provider=DB2OLEDB.1;Network Transport Library=TCPIP;Network Address=YourIPHere;Network Port=YourPortHere;Initial Catalog=YourDatabaseHere;Package Collection=NULLID;Default Schema=SameAsYourDatabaseHere;User ID=YourID;Password=YourPassword;"            

$qry= "select something from somewhere"

Get-OLEDBData $connString $qry | Export-Csv -Path C:\TargetFolder\Results.csv -Delimiter ";" -NoTypeInformation

Collectives™ on Stack Overflow

Need to connect to DB2 database with PowerShell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related