Technical

List Registered DB Providers using PowerShell

Daniel Adeniji Microsoft, PowerShell, Technical

Background

As we prepare some of our machines for Database Application Server work, wanted to see how to programmatically discover which Database Providers are installed.

 

Code

Outline

  1. .Net Class
    • DbProviderFactories
      • Methods
        • GetFactoryClasses
          • Returns DataTable

Powershell

Script


Set-StrictMode -Version Latest;

function listProviders()
{
	Param(

			  [parameter(Mandatory=$true)]
			  [Object]
			  $objDBFactories

    )
	[int] $iPropertyIndex = 0;

	foreach($objDBFactory in $objDBFactories)
	{

	   # increment property counter
	   $iPropertyIndex = $iPropertyIndex + 1;

	   $strLog = $objDBFactory;

	   # display data
	   $strLog;			

	} 

}	

#Get Database Provider Factory
$objDBFactories = [System.Data.Common.DbProviderFactories]::GetFactoryClasses();

#listProviders $objDBFactories

$objDBFactories | Out-GridView;

"Please press any key to continue"

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

Output

Output – Sample

listDBProviders_20181019_0525PM

References

  1. Microsoft
    • DbProviderFactories
      • Docs / NET / .NET API Browser / System.Data.Common / DbProviderFactories / Methods / GetFactoryClasses
        • GetFactoryClasses
          Link

 

PowerShell – Error – “Cannot process argument transformation on parameter ”. Reference type is expected in argument.

Daniel Adeniji Technical, Microsoft, PowerShell ,

Error

Error – Text

readConfigurationFile : Cannot process argument transformation on parameter 'loadDuration'. Reference type is expected in argument.

Error – Image

PowerShell_Error_20181018_0558PM

Reproduce

Code


function readConfigurationFile
{

	Param(

			  [parameter(Mandatory=$true)]
			  [String]
			  $scriptName

			, [parameter(Mandatory=$true)]
			  [Object]
			  $cstrbld		

			, [parameter(Mandatory=$true)]
			  [ref] [int]
			  $loadDuration
    )

	###########################################################
	# Get Current Folder
	###########################################################
	$currentFolder=Convert-Path .

	###########################################################
	# Ini - Get Current File
	###########################################################
	$fileIni = "dbsettings.ini"

}

Remediation

Outline

  1. The third argument is an int and for changes to persists it needs to be passed as ref
  2. Current Entry
    • [ref] [int]
      • We are defining it as both ref and int
  3. Revision
    • [ref]
      • Please remove [int] and have system pass along as reference
      • reference obviously means a pre-recorded address in memory

Code



function readConfigurationFile
{

	Param(
	
			  [parameter(Mandatory=$true)]
			  [String]
			  $scriptName
			  
			, [parameter(Mandatory=$true)]
			  [Object]
			  $cstrbld		
			  
			, [parameter(Mandatory=$true)]
			  [ref] #[int]
			  $loadDuration
    )
	
	###########################################################
	# Get Current Folder
	###########################################################
	$currentFolder=Convert-Path .
}

Db/2 LUW – Listening Port Using OS Tools

Daniel Adeniji DB/2, DB/2 - Products, DB/2 - Products - LUW, lsof ( Linux ), netstat, Uncategorized

Background

Wanted to quickly review network ports that are being used by our DB/2 Instance.

Outline

  1. netstat
  2. lsof

Tools

netstat

Sample


netstat -anp | grep LISTEN | grep -i DB2

Output

netstat_linux_20181006_1141AM

lsof

Syntax


lsof

Sample – Usage


lsof -i | grep -i 'TCP' | grep 'LISTEN'

Output

lsof.linux.20181006.1201PM

Sample – Get Title


lsof 2>&1 | head -n 1

Output

lsof.linux.head.20181006.1159AM

PowerShell – By Reference

Daniel Adeniji Microsoft, Pass By Reference ( PowerShell - Tasks ), PowerShell, Technical ,

Background

It is always so easy to talk about things we know so little of.

Code

Outline

  1. Calling Routine
    • Each variable to pass by ref
      • Syntax
        • argument-in-called function ([ref] $local-variable)
      • Sample
        • strNew ([ref] $newCompanyName)
  2. Called Routine
    • Add Param Clause
      • Syntax
        • [ref] $variable-name
      • Example
        • [ref] $companyName
    • Explicitly state that value is being changed
      • Syntax
        • $variable.Value=[new-value]
      •  Example
        •   $strNew.Value=”Inprise Corp”

PowerShell


Set-StrictMode -Version Latest

function passByRef
{

    Param($str, [ref] $strNew, [ref] $loadDuration, [ref] $ratio)
    $str="Inprise Corp"
    $strNew.Value="Inprise Corp"
    $loadDuration.Value=100
    $ratio.Value=10

    $result = $loadDuration.Value / $ratio.Value
}

$company = "Borland"
$companyNameChange = ""
$loadDuration=0
$ratioLocal=-1

passByRef $company -strNew ([ref]$companyNameChange) -loadDuration ([ref]$loadDuration) -ratio ([ref]$ratioLocal)

"Company is $company"
"Company Name Change is $companyNameChange"
"loadDuration is $loadDuration"
"ratioLocal is $ratioLocal"

"Press Enter to complete"

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

Output

passByRef_20181015_0721PM

Listening

Listening to Paul Simon..

Something so right
Link

Lyrics

When the fever runs high
You’ve got the look of love light
In your eyes
And I was in crazy motion
‘Til you calmed me down
It took a little time
But you calmed me down
When something goes wrong
I’m the first to admit it
I’m the first to admit it
But the last one to know
When something goes right
Well it’s likely to lose me
It’s apt to confuse me
It’s such an unusual sight
I can’t get used to something so right
Something so right

 

Oliver Lipkau :- Get Ini File Contents in PowerShell

Daniel Adeniji Inifile ( PowerShell - Tasks), Microsoft, PowerShell, Tasks ( PowerShell ), Technical

Background

In a previous post ended up hard coding database connectivity information in the the actual PowerShell code.

Not a good look.

Let us move that into an INI File.

Oliver Lipkau

Ini File Management

PowerShell

Outline

  1. PowerShell
    • PowerShell Function File
      • Get-IniContent.ps1
    • Powershell PSM1
      • PSIni.psm1
    • Sample Invocation
      • readIniFile.ps1

PowerShell Function File

Get-IniContent.ps1
Set-StrictMode -Version Latest
Function Get-IniContent {
    $FileContent = Get-IniContent "c:\settings.ini"
        C:\PS>$FileContent["Section"]["Key"]
        -----------
        Description
        Returns the key "Key" of the section "Section" from the C:\settings.ini file  

    .Link
        Out-IniFile
    #>  

    [CmdletBinding()]
    Param(
        [ValidateNotNullOrEmpty()]
        [ValidateScript({(Test-Path $_) -and ( ( (Get-Item $_).Extension -eq ".ini") -or ( (Get-Item $_).Extension -eq ".txt") ) })]
        [Parameter(ValueFromPipeline=$True,Mandatory=$True)]
        [string]$FilePath
    )  

    Begin
        {Write-Verbose "$($MyInvocation.MyCommand.Name):: Function started"}  

    Process
    {
        Write-Verbose "$($MyInvocation.MyCommand.Name):: Processing file: $Filepath"  

        $ini = @{}
        switch -regex -file $FilePath
        {
            "^\[(.+)\]$" # Section
            {
                $section = $matches[1]
                $ini[$section] = @{}
                $CommentCount = 0
            }
            "^(;.*)$" # Comment
            {
                if (!($section))
                {
                    $section = "No-Section"
                    $ini[$section] = @{}
                }
                $value = $matches[1]
                $CommentCount = $CommentCount + 1
                $name = "Comment" + $CommentCount
                $ini[$section][$name] = $value
            }
            "(.+?)\s*=\s*(.*)" # Key
            {
                if (!($section))
                {
                    $section = "No-Section"
                    $ini[$section] = @{}
                }
                $name,$value = $matches[1..2]
                $ini[$section][$name] = $value
            }
        }
        Write-Verbose "$($MyInvocation.MyCommand.Name):: Finished Processing file: $FilePath"
        Return $ini
    }  

    End
        {Write-Verbose "$($MyInvocation.MyCommand.Name):: Function ended"}
}

PowerShell Application File

PSIni.psm1



$PsIniModuleHome = Split-Path -Path $MyInvocation.MyCommand.Path -Parent

# Name of the Section, in case the ini file had none
# Available in the scope of the module as `$script:NoSection`
$script:NoSection = "_"

# public functions
. "$PsIniModuleHome\Functions\Get-IniContent.ps1"
readIniFile.ps1

Set-StrictMode -Version Latest

###########################################################
# Import Module
###########################################################
Import-Module .\PSIni.psm1  -NoClobber -ErrorAction Stop

###########################################################
# Get Current Folder
###########################################################
$currentFolder=Convert-Path .

###########################################################
# Ini - Get Current File
###########################################################
$fileIni = "dbsettings.ini"

###########################################################
# Ini - Get Current File ( fullname)
###########################################################
$fileIniFullname = $currentFolder + [IO.Path]::DirectorySeparatorChar + $fileIni;

$fileContent = Get-IniContent ($fileIniFullname);

###########################################################
# Ini File - Read
###########################################################
$section="Database";

$key="server";
$dbServerHost = $fileContent[$section][$key]

$key="port";
$dbServerPort = $fileContent[$section][$key]

$key="database";
$dbDatabase = $fileContent[$section][$key]

$key="userid";
$dbUserID = $fileContent[$section][$key]

$key="password";
$dbUserPassword = $fileContent[$section][$key]

###########################################################
# Ini File - Display
###########################################################
"Database Server :- $dbServerHost"

"Database Server Port :- $dbServerPort"

"Database :- $dbDatabase"

"Database UserId :- $dbUserID"

"Database User Password :- $dbUserPassword"

Configuration Files

Outline

  1. Configuration Files
    • Ini File
      • dbsettings.ini

Configuration File

dbsettings.ini

[Database]
server=localhost
port=50000
database=WIDEWRLD
userid=db2user
password=simplepassword

Control Files

Outline

  1. Invoke
    • PowerShell
      • pass file name to Powershell file

Powershell

Invoke

powershell -file ./readIniFile.ps1
Output

invoke_2018104_0447PM

Dedicated

Dedicated to Oliver Lipkau :-

Work with INI files in PowerShell using hashtables
Link

 

Db/2 – PowerShell – Lab Exercises – 01

Daniel Adeniji DB/2, DB/2 - Products - LUW, PowerShell

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

Karim Zidan

Daniel Adeniji Conor McGregor, GeoPolitics, Karim Zidan, Khabib Nurmagomedov, Mixed Martial Arts ( MMA ), MMA On Point ( YouTube - Channel ), MMAFightingonSBN ( YouTube - Channel ), Off The Ball ( YouTube - Channel ), Sports , , , , ,

Background

I am very touched and impressed with Karim Zidan.

The depth he goes to try to illuminate tough and deep subject matters.

 

Profile

karimZidan

 

Videos

  1. MMA On Point
    • A Deep Analysis of What Conor Said to Khabib
      • Profile
      • Videos
        • Video #1
          Channel :- MMA On Point
          Published On :- 2018-Sept-29th
          Link
  2. MMAFightingonSBN
    • Journalist Karim Zidan Unpacks Accusations Made By McGregor Against Nurmagomedov | The MMA Hour
      • Profile :- Journalist Karim Zidan speaks to Luke Thomas on The MMA Hour about the statements and accusations made by Conor McGregor against Khabib Nurmagomedov and manager Ali Abdelaziz at the UFC 229 press conference in NYC.
      • Videos
        • Video #1
          Channel :- MMAFightingonSBN
          Date Published On :- 2018-Sept-26th
          Link
  3. Off The Ball
    • The dark politics behind McGregor and Khabib’s feud | Karim Zidan on UFC 229’s real ‘Bad Blood’
      • Profile :- Journalist Karim Zidan has spent years reporting on the links between dictators and regional ‘strongmen’ and MMA. He joined OTB AM to discuss why UFC 229 reached the boiling point that it did on Saturday night, and how the UFC should approach promoting McGregor vs Khabib II if/when it happens.
      • Videos
        • Video #1
          Channel :- Off The Ball
          Date Published On :- 2018-Oct-11th
          Link

 

Articles

  1. Bloody Elbow
    • The Dubliner and the Dagestani: The distinct and divergent paths that conspired to create the biggest fight in UFC history.
      • Profile :- Karim Zidan and Tim Bissell delve into the two sides of the biggest fight in UFC history, examining the Family, Faith, and Fighting of Khabib Nurmagomedov and Conor McGregor.
      • Authors :- Karim Zidan and Tim Bissell
      • Dated :- Oct 4, 2018, 12:00 pm EDT
      • Links

Summary

Sports inspire people.  It makes everyone money.  We all get to choose sides.

Easy patriotism or shall we say glee.

When that inspiration is seen as cheap, it can come with un-containable costs.

Others feel slighted and not everyone has the depth to reason it out nor afford others the choice they themselves cherish.