Db/2 – PowerShell – Lab Exercises – 01

Background

Wanted to start placing together sample Lab Exercises for working with Db/2 and PowerShell.

Prerequisites

Please make sure you have IBM’s Data Server Provider for .NET installed.

 

Code


#set provider invariant name
$dbProviderInvariantName = "IBM.Data.DB2";

#Get Database Provider Factory
$factory = [System.Data.Common.DbProviderFactories]::GetFactory($dbProviderInvariantName);

#Initialize Database ConnectionString
$cstrbld = $factory.CreateConnectionStringBuilder();

#Set Database ConnectionString
$cstrbld.Database = "WIDEWRLD"
$cstrbld.UserID = "dadeniji"
$cstrbld.Password = "pass2"
$cstrbld.Server = "localhost:50000"

#Initialize Database Connection Object
$dbconn = $factory.CreateConnection()

#Set Database Connection Object ConnectionString
$dbconn.ConnectionString = $cstrbld.ConnectionString

# Open Database Connection
$dbconn.Open()

# Create Database Command Object
$dbcmd = $factory.CreateCommand();

# Set Database Command Connection Property to DB Connection
$dbcmd.Connection = $dbconn

$sql = @"
		SELECT
			  tbl.CATALOG_PARTITION_NAME AS partition
			, tbl.DB_NAME AS db
			, tbl.SERVER_PLATFORM AS platform

		FROM TABLE (
				SNAP_GET_DB
				(
					''
					, 0
				)
			) AS tbl
"@

$dbcmd.CommandText = $sql;

#set Command Type Text
$dbcmd.CommandType = [System.Data.CommandType]::Text

# Create Data Adapter
$dataAdapter = $factory.CreateDataAdapter()

# Set Data Adapter :- Select Command
$dataAdapter.SelectCommand = $dbcmd

# Create Dataset Object
$dataSet = New-Object System.Data.DataSet

# Fill Data Adapter with Dataset Object
$dataAdapter.Fill($dataSet)

# Set Data Table
$dataTable = $dataSet.Tables[0];

# Show Data Table as Grid
$dataTable | Out-GridView;

# Close Database Connection
$dbconn.Close()

#Wait
$HOST.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") | OUT-NULL;
$HOST.UI.RawUI.Flushinputbuffer();

Output

db2_powershell_01

One thought on “Db/2 – PowerShell – Lab Exercises – 01

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s