System Integration mit Xpert.Ivy: Event Start über Dateien

Xpert.Ivy bringt von Haus aus bereits Möglichkeiten mit, andere Systeme über eine Datei basierte asynchrone Kommunikation in Prozesse einzubinden. Die Datei basierte Integration hat den Vorteil, dass sie einfach und ohne zusätzliche Software umsetzbar ist. In einem Prozess Showcase, bei dem drei verschiedene Systeme integriert wurden, haben wir daher auf diese Variante gesetzt für die asynchrone Kommunikation.


Wenn man einen Prozess in Xpert.Ivy durch eine Datei starten oder weiterlaufen lassen möchte, kann man dies über ein "Event Start" oder ein "Intermediate Event" realisieren.

Schauen wir zuerst das Event "Event Start" an. Xpert.Ivy wird bereits mit drei Java Klassen (auch StartEvenBeans genannt) ausgeliefert.

Im Projekt IvyAddOns gibt es darüber hinaus noch weitere EventBeans. Um diese zu finden wechselt man am besten in die Java Perspektive (Menu Window => Open Perspective => Other => Java). Wenn man nun das Projekt IvyAddOns aufmacht, findet man im Ordner src zwei Packages mit weiteren Start- und Intermediate-EventBeans:


Die Klasse XMLFileStartEventBean ist dabei ein guter Startpunkt, wenn man eine eigene Klasse implementieren möchte. Ich werde später ein Beispiel dazu zeigen.



Hier eine kurze Beschreibung der StartEventBeans.

FileStartEventBean

Startet für jede Datei in einem bestimmten Verzeichnis ein Prozess und kopiert die Werte in der Datei in die Prozessdaten. Die Datei muss dafür folgendes Format haben:

Attribute1=Wert1,Attribute2=Wert2,...

Die Pollzeit ist fest auf eine Minute eingestellt. Die Datei wird gelöscht. Attribute die nicht übereinstimmen werden ignoriert.

Beispiel mit Debug Ausgabe der Prozess Attribute:

FilePickupStartEventBean

Startet für jede Datei in einem bestimmten Verzeichnis ein Prozess und kopiert den Dateinamen in ein Prozess-Attribut vom Typ String.

Die Pollzeit ist fest auf eine Minute eingestellt. Die Datei wird wenn das Setzen des Dateinamens funktioniert gelöscht, im Fehlerfall stehen gelassen.

Beispiel mit Debug Ausgabe:

XMLFileStartEventBean (aus IvyAddOns)

Startet für jede Datei in einem Verzeichnis ein Prozess und kopiert die XML Elemente in die Prozess Attribute (analog FileStartEventBean aber mit einer XML Datei).

Die Pollzeit kann eingestellt werden. Das Verzeichniss kann aus den globalen Variablen von Ivy gelesen werden. Im Fehlerfall wird die Datei in ein Verzeichnis "error" verschoben.

Beispiel:

Fazit

Mit den in Ivy integrierten EventStartBeans ist es möglich, schnell eine Datei basierte Kommunikation zwischen dem Prozess und verschiedenen Systemen umzusetzen. Allerdings sind sie für einen produktiven Einsatz nur bedingt brauchbar, da:

• Die Pollzeit (ausser bei XMLFileStartEventBean) nicht konfigurierbar ist
• Das Verzeichnis (ausser bei XMLFileStartEventBean) nicht über eine globale Ivy Variable konfigurierbar ist
• Das Fehler-Handling zu einfach ist
• Fehler beim gleichzeitigen Schreiben (vom externen System) und Lesen (durch Ivy) nicht ausgeschlossen sind
• Kein Datei-Filter angewendet werden kann

Wie man selber eine StartEventBean schreiben kann, welche diese Punkte umsetzt, zeige ich in einem späteren Post.

Xpert.Ivy HTML UI based on JSF?

Looks like Soreco decided to take JSF to replace the JSP based HTML dialogs in Xpert.Ivy:

http://www.ivyteam.ch/flatpress/?entry=entry120917-133839

That would be interessting for us, because we already have some JSF knowledge.

http://fzkvision.ch/

SQLServer Plan Info

I was debugging today a SQL statement which took about 40 seconds in one app, but only 200 ms in SSMS. I learned that SQLServer creates different execution plans depending on the SET options.

Here is a SQL statement to print out some infos about cached execution plans for a specific statement:

SELECT
    query_info.sql_handle,
    query_info.plan_handle,
    plan_options.usecounts,
    query_info.total_elapsed_time,
    CASE WHEN ((1 & set_options) = 1) THEN 'ON' ELSE 'OFF' END AS 'ANSI_PADDING',
    CASE WHEN ((4 & set_options) = 4) THEN 'ON' ELSE 'OFF' END AS 'FORCEPLAN',
    CASE WHEN ((8 & set_options) = 8) THEN 'ON' ELSE 'OFF' END AS 'CONCAT_NULL_YIELDS_NULL',
    CASE WHEN ((16 & set_options) = 16) THEN 'ON' ELSE 'OFF' END AS 'ANSI_WARNINGS',
    CASE WHEN ((32 & set_options) = 32) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULLS',
    CASE WHEN ((64 & set_options) = 64) THEN 'ON' ELSE 'OFF' END AS 'QUOTED_IDENTIFIER',
    CASE WHEN ((128 & set_options) = 128) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULL_DFLT_ON',
    CASE WHEN ((256 & set_options) = 256) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULL_DFLT_OFF',
    CASE WHEN ((512 & set_options) = 512) THEN 'ON' ELSE 'OFF' END AS 'NoBrowseTable',
    CASE WHEN ((4096 & set_options) = 4096) THEN 'ON' ELSE 'OFF' END AS 'ARITH_ABORT',
    CASE WHEN ((8192 & set_options) = 8192) THEN 'ON' ELSE 'OFF' END AS 'NUMERIC_ROUNDABORT',
    CASE WHEN ((16384 & set_options) = 16384) THEN 'ON' ELSE 'OFF' END AS 'DATEFIRST',
    CASE WHEN ((32768 & set_options) = 32768) THEN 'ON' ELSE 'OFF' END AS 'DATEFORMAT',
    CASE WHEN ((65536 & set_options) = 65536) THEN 'ON' ELSE 'OFF' END AS 'LanguageID',
    query_info.text,
    plan_info.query_plan
FROM (
    SELECT plan_handle, usecounts, CAST(pvt.set_options AS INT) AS set_options
    FROM (
        SELECT plan_handle, usecounts, epa.attribute, epa.value
        FROM sys.dm_exec_cached_plans
            OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
        WHERE cacheobjtype = 'Compiled Plan') AS ecpa
    PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
) as plan_options
JOIN (
    SELECT qs.sql_handle AS sql_handle, qs.plan_handle AS plan_handle, qs.total_elapsed_time, st.text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
) AS query_info ON query_info.plan_handle = plan_options.plan_handle
CROSS APPLY  sys.dm_exec_query_plan(query_info.plan_handle) plan_info
WHERE text LIKE 'SELECT ...%';

Thanks to:

• http://www.benjaminnevarez.com/2011/09/parameter-sniffing-and-plan-reuse-affecting-set-options 
• http://www.sqlserverinternals.com/companion/QTO_Chapter5code.txt

Generate SQLServer Scripts with PowerShell

I needed a new script to generate SQL scripts for tables, views etc. The old script did export all tables as files with extension "TAB", all views with extension "VIW", etc. I wanted to keep this, but needed all the files in correct order for dependencies. So here we go: my first PowerShell script.

In order to connect to a SQLServer we must first load some assemblies:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null

Now it is possible to connect to the server:

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "NAME_OF_SERVER"
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Login = "USER_NAME"
$srv.ConnectionContext.Password ="PASSWORD"

Get a reference to the database:

$dbName = "DATABASE_NAME"
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbName]

Get a scripter instance and set options:

$scripter = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scripter.Server = $srv
$scrOpts = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$scrOpts.Encoding = [System.Text.Encoding]::GetEncoding(1252)
$scrOpts.AllowSystemObjects = $false
$scrOpts.AnsiFile = $true
$scrOpts.AppendToFile = $false
$scrOpts.ClusteredIndexes = $true
$scrOpts.Default = $true
$scrOpts.DriAll = $true
$scrOpts.Indexes = $true
$scrOpts.NonClusteredIndexes = $true
$scrOpts.ToFileOnly = $true
$scrOpts.WithDependencies = $false
$scrOpts.Triggers = $true
$scripter.Options = $scrOpts

See MSDN for all Options .

Export all tables:

# Read all tables into an array, filter out system tables
$Objects = @()
$Objects += $db.Tables | where {$_.IsSystemObject -eq $false}
# Find dependencies
$dependencyTree = $scripter.DiscoverDependencies($Objects, $true)
# Create a list of all needed objects in correct order
$depCollection = $scripter.WalkDependencies($dependencyTree);

Unfortunatelly this did nor work the first time, because there was a trigger on a table which called a stored procedure. DiscoverDependencies also returned this procedure and all views and other objects which this procedure was dependent of. Filtering out the procedure helped. To get this done one can set a callback function which gets called by DiscoverDependencies:

function ScriptingFilter([Microsoft.SqlServer.Management.Sdk.Sfc.Urn]$urn) {
    $item = $srv.GetSmoObject($urn)
    if($item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        Write-Host "  Filter: " $item " => " $type
        return $true
    } else {
        return $false
    }
}

$scripter.FilterCallbackFunction = get-content Function:\ScriptingFilter

Now create a script file for each object:

# Create a UrnCollection of all objects
$urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
foreach($dep in $depCollection) { $urns.add($dep.Urn) }

$onlyOne = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
$i = 0
$max = $urnCollection.Count

foreach($urn in $urnCollection)
{
    $onlyOne.clear()
    $item = $srv.GetSmoObject($urn)     $ext = "SQL"
    if($item -is [Microsoft.SqlServer.Management.Smo.Table])
    {
        $ext = "TAB"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.View])
    {
        $ext = "VIW"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        $ext = "PRC"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.UserDefinedFunction])
    {
        $ext = "UDF"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.UserDefinedDataType])
    {
        $ext = "UDT"
    }


    # Filter out some more objects we do not need     if($item.Name.StartsWith("spt_") -or $item.IsSystemObject -eq $true)
    {
        Write-Host " Ignored: " $item.Name
     } else {
            $count = $count + 1
            $scripter.Options.FileName = $scriptPath + $count.ToString("0000") + '.' + $item.Schema + "." + $item.Name + "." + $ext
            $scripter.Script($onlyOne)
            $i = $i + 1
    }
}

Finally I added some status information using Write-Progress commandlet and created some functions so that it was possible to script first all tables, then all views and procedures.

Some helpfull links:

• http://sqlblog.com/blogs/ben_miller/archive/2007/10/18/scripting-tables-views-and-data-using-smo-part-3.aspx
• http://www.yaldex.com/sql_server_tutorial_3/ch11lev1sec4.html
• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scripter.aspx

The full script:

#Set-ExecutionPolicy RemoteSigned

#
# Extension fuer ein bestimmtes SmoObject ermitteln
#
function GetTypeExt([Microsoft.SqlServer.Management.Smo.SqlSmoObject] $Item)
{
    $ext = "SQL"
    if($Item -is [Microsoft.SqlServer.Management.Smo.Table])
    {
        $ext = "TAB"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.View])
    {
        $ext = "VIW"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        $ext = "PRC"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.UserDefinedFunction])
    {
        $ext = "UDF"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.UserDefinedDataType])
    {
        $ext = "UDT"
    }
    return $ext
}

#
# Function um bestimmte Elemente aus den Abhaengigkeiten auszuschliessen
#
function ScriptingFilter([Microsoft.SqlServer.Management.Sdk.Sfc.Urn]$urn) {
    $item = $srv.GetSmoObject($urn)
    $type = GetTypeExt($item)
    if(-not $Filter -eq "" -and $type.StartsWith($Filter))
    {
        Write-Host "  Filter: " $item " => " $type
        return $true
    } else {
        return $false
    }
}

#
# Generiert Scripts ohne Abhaengigkeiten fuer Objekte in $Objects
#
function GenScripts()
{
    $urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
    foreach($dep in $Objects) { $urns.add($dep.Urn) }
    GenScriptsForObjects $urns
}

#
# Generiert Scripts mit Abhaengigkeiten fuer Objekte in $Objects
#
function GenScriptsWithDependencies()
{
    $scripter.FilterCallbackFunction = get-content Function:\ScriptingFilter
    if($Objects.Length -ne 0)
    {
        $dependencyTree = $scripter.DiscoverDependencies($Objects, $true)
        $depCollection = $scripter.WalkDependencies($dependencyTree);
        $urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        foreach($dep in $depCollection) { $urns.add($dep.Urn) }
        GenScriptsForObjects $urns
    }
}

#
# Generiert Scripts fuer eine Collection von SmoObjects
#
function GenScriptsForObjects([Microsoft.SqlServer.Management.Smo.UrnCollection]$urnCollection)
{
        $onlyOne = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        $i = 0
        $max = $urnCollection.Count

        foreach($urn in $urnCollection)
        {
            $onlyOne.clear()
            $item = $srv.GetSmoObject($urn)
           
            Write-Progress -Activity "Script objects..." -status $item.Name -percentComplete ($i / $max * 100)
           
            $ext = GetTypeExt($item)
           
            if($item.Name.StartsWith("spt_") -or $item.IsSystemObject -eq $true)
            {
                Write-Host "  Ignored: " $item.Name
            } else {
                if(-not $UrnsDone.Contains($item.Urn))
                {
                    $onlyOne.add($item.Urn)
                    $global:count = $UrnsDone.Count + 1
                    $scripter.Options.FileName = $scriptPath + $count.ToString("0000") + '.' + $item.Schema + "." + $item.Name + "." + $ext
                    $scripter.Script($onlyOne)
                    $UrnsDone.add($item.Urn)
                    $i = $i + 1
                }
            }
        }
}

#
# Hauptfunktion: Stellt Verbindung mit SQLServer her, ermittelt die Objekte und Scripted diese
#
function GenerateDBScript([string]$dbServer, [string]$dbName, [string]$dbUser, [string]$dbPsw, [string]$scriptPath)
{
    if ($scriptPath[-1] -ne "\")
    {
        $scriptPath = $scriptPath + "\"
    }
    if (!(Test-Path -path $scriptPath))
    {
       New-Item $scriptPath -type directory
    }
    if (Test-Path -path ($scriptPath + "0*.*"))
    {
        $msg = "Verzeichnis """ + $scriptPath + """ enthält bereits Script-Dateien. Zuerst alle Dateien löschen."
        [System.Windows.Forms.MessageBox]::Show($msg) | out-null
        $msg
        explorer $scriptPath
    } else {
        "Generate scripts for server: " + $dbServer + " database: " + $dbName
        "  to Path: " + $scriptPath

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
        [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null

        $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $dbServer
        $srv.ConnectionContext.LoginSecure = $false
        $srv.ConnectionContext.Login = $dbUser
        $srv.ConnectionContext.Password = $dbPsw
        $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

        $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
        $db = $srv.Databases[$dbName]

        $scripter = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
        $scripter.Server = $srv

        $scrOpts = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
        #$scrOpts.Encoding = [System.Text.Encoding]::GetEncoding(1252)
        $scrOpts.AllowSystemObjects = $false
        $scrOpts.AnsiFile = $true
        $scrOpts.AppendToFile = $false
        $scrOpts.ClusteredIndexes = $true
        $scrOpts.Default = $true
        $scrOpts.DriAll = $true
        $scrOpts.Indexes = $true
        $scrOpts.NonClusteredIndexes = $true
        $scrOpts.ToFileOnly = $true
        $scrOpts.WithDependencies = $false
        $scrOpts.Triggers = $true
        $scripter.Options = $scrOpts

        $UrnsDone = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        $Filter = ""
        $global:count = 0

        try {

            $Objects = @()
           
            Write-Progress -Activity "Script Objects..." -status "User Defined Data Types" -percentComplete 0
            $Res = $db.UserDefinedDataTypes | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }
       
            Write-Progress -Activity "Script Objects..." -status "Tables" -percentComplete 0
            $Res = $db.Tables | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = "PRC"
            GenScriptsWithDependencies
           
           
            $Objects = @()
       
            Write-Progress -Activity "Script Objects..." -status "User Defined Functions" -percentComplete 0
            $Res = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            Write-Progress -Activity "Script Objects..." -status "Views" -percentComplete 0
            $Res = $db.Views | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = "PRC"
            GenScriptsWithDependencies

           
            $Objects = @()
           
            Write-Progress -Activity "Script Objects..." -status "DB Triggers" -percentComplete 0
            $Res = $db.Triggers | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            Write-Progress -Activity "Script Objects..." -status "Procedures" -percentComplete 0
            $Res = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = ""
            GenScriptsWithDependencies

        } catch {
            "EXCEPTION: " + $_.Exception.GetBaseException().Message
            throw $_.Exception
        }

        "Done. " + $count + " Dateien erstellt in " + $scriptPath
        explorer $scriptPath
    }
}

# GenerateDBScript $args[0] $args[1] $args[2] $args[3] $args[4]
GenerateDBScript "SERVER" "DATABASE" "USER" "PASSWORD" "DIRECTORY"