Hello, Db2 World! PowerShell

I have published several articles on PowerShell. But the “Hello, Db2 World!” series is designed to be a quick and fairly simple way of properly connecting to a Db2 database. Check out the GitHub repository folder for this blog entry.

Db2 Drivers

Note that before starting, you should have a Db2 Client installed on the server you wish to connect from. Follow IBM’s directions for installing IBM Data Server Clients and Drivers. You usually do NOT want the runtime client.

PowerShell and Db2

I use PowerShell most often when I need to administer Db2 databases on MS Windows severs. PowerShell offers several things that make it an excellent command line environment for working with Db2, some commands that are more similar to ksh/Bash, and a sophisticated object orientation as your work gets more advanced.

The Code

I am splitting all code in this series into two files. One that contains private information like passwords and host names, and one that contains the actual working code. For the PowerShell “Hello, Db2 World”, that file looks like this (names and passwords changed to protect the innocent):

$dbName="SAMPLE"
$userID="db2admin"
$PW="db2admin"
$server="localhost"
$port=50000

Likely all of these variables will have to be changed for you. “localhost” for the server name means that your script is running on the same server where the database is, which is rare unless you are a DBA.

The code looks like this:

# Source File with connection variables set
$Path = $PSScriptRoot
. "$Path\HelloDb2World_PowerShell_variables.ps1"

#Define connection string for the database
$cn = new-object system.data.OleDb.OleDbConnection("Provider=IBMDADB2;DSN=$dbName;User Id=;Password=;");
#Define data set for first query
$ds = new-object "System.Data.DataSet" "ds"
#Define query to run
$q = "select * from hello_world"
# Define data object given the specific query and connection string
$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $cn)
# Fill the data set - essentially run the query. 
$da.Fill($ds) | Out-Null
# Print the result
foreach ($Row in $ds.Tables[0].Rows)
        {
        write-host  "$($Row.C1)"  
        }
# Close the Connection
$cn.close()

I don’t claim to be an expert in Powershell. Check out Luke Numrych’s different ideas for using PowerShell with Db2.

Also check out my other PowerShell related blog entries. Most are from the point of view of a DBA, but they may give you some ideas.

All of the code used in the “Hello, Db2 World!” is available on GitHub.