Automate DB2 Integration Tasks from PowerShell

The CData Cmdlets for DB2 are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to DB2.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to DB2, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete DB2 data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for IBM DB2. To access DB2 data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for IBM DB2.

Once you have acquired the necessary connection properties, accessing DB2 data in PowerShell can be enabled in three steps.

Set the following properties to connect to DB2:

• Server: Set this to the name of the server running DB2.
• Port: Set this to the port the DB2 server is listening on.
• Database: Set this to the name of the DB2 database.
• User: Set this to the username of a user allowed to access the database.
• Password: Set this to the password of a user allowed to access the database.

You will also need to install the corresponding DB2 driver:

• Windows: Install the IBM Data Server Provider for .NET.

  On Windows, installing the IBM Data Server Provider is sufficient, as the installation registers it in the machine.config.

• Java: Install the IBM Data Server Driver for JDBC.

  In the Java version, place the IBM Data Server Driver JAR in the www\WEB-INF\lib\ folder for this application.

PowerShell

1. Install the module:

   Install-Module DB2Cmdlets

2. Connect:

   $db2 = Connect-DB2 -Server "$Server" -Port "$Port" -User "$User" -Password "$Password" -Database "$Database"
   

3. Search for and retrieve data:

   $shipcity = "New York"
   $orders = Select-DB2 -Connection $db2 -Table "Orders" -Where "ShipCity = `'$ShipCity`'"
   $orders
   

   You can also use the Invoke-DB2 cmdlet to execute SQL commands:

   $orders = Invoke-DB2 -Connection $db2 -Query 'SELECT * FROM Orders WHERE ShipCity = @ShipCity' -Params @{'@ShipCity'='New York'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for IBM DB2\lib\System.Data.CData.DB2.dll")
    

2. Connect to DB2:

    
   $conn= New-Object System.Data.CData.DB2.DB2Connection("Server=10.0.1.2;Port=50000;User=admin;Password=admin;Database=test;")
   $conn.Open()
   

3. Instantiate the DB2DataAdapter, execute an SQL query, and output the results:

   $sql="SELECT OrderName, Freight from Orders"
   
   $da= New-Object System.Data.CData.DB2.DB2DataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.ordername $_.freight
   }
    

Update DB2 Data

PowerShell

Update-DB2 -Connection $DB2 -Columns @('OrderName','Freight') -Values @('MyOrderName', 'MyFreight') -Table Orders -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("UPDATE Orders SET ShipCity='New York' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert DB2 Data

PowerShell

Add-DB2 -Connection $DB2 -Table Orders -Columns @("OrderName", "Freight") -Values @("MyOrderName", "MyFreight") 

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("INSERT INTO Orders (ShipCity) VALUES (@myShipCity)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myShipCity","New York")))
$cmd.ExecuteNonQuery()

Delete DB2 Data

PowerShell

Remove-DB2 -Connection $DB2 -Table "Orders" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("DELETE FROM Orders WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()