Hello, Db2 World! PowerShell

Posted by

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.