posh.evt-drv | query sql async

Import-Module SQLDATA -Force -DisableNameChecking

function EXwConn {
    param(
        $targetHost
        , $targetcmd
    )
     Exec-MSSQLData -CONNECTION "Data Source=$targetHost;Initial Catalog=master;Persist Security Info=True;Integrated Security=SSPI;" `
        -sql $targetcmd
}

function GTwConn {
    param(
        $targetHost
        , $targetcmd
        , $RTNIDX = 0..0
    )
    $DS = Get-MSSQLData -CONNECTION "Data Source=$targetHost;Initial Catalog=master;Persist Security Info=True;Integrated Security=SSPI;" `
        -sql $targetcmd
    RETURN $DS.TABLES[$RTNIDX]
}

function Qlog {
    param(
        $str
    )
    Write-Host $str -BackgroundColor Yellow -ForegroundColor Red
    $str >> $global:logfile
}

function getItemQ {
    param(
        $qArr = $global:tblBatch
        , [ref] $itmQ 
    )
    $X = 0; $Y = 0
    WHILE($qArr.Count -NE 0){
        try{
            $itmQ.value.EnQueue($(,@($y, $qArr[$y].Dequeue()))) 
        } catch [exception]{
            $qArr.RemoveAt($y)            
        }
        IF($Y -EQ ($qArr.count - 1)){$y = 0}
        else {$y++}
    }
    $itmQ.value = [collections.queue]::Synchronized($itmQ.value)
}

function init{
    $global:logfile = "U:\PA2013\PA2013_ERR.TXT"
    $logfi = New-Object io.fileinfo $logfile
    if($logfi.Exists){
        $logfi.Delete()
        $logfi.Create()
    } else {
        $logfi.Directory.Create()
        $logfi.Create()
    }
    
    $global:instances = "dc20gtdb02.colocation.channelwave.local\POD2",
        "10.20.9.169\DEV_POD", #dc20gtdevdb03.Colocation.ChannelWave.local
        "DC20GTODS.colocation.channelwave.local\GTODS"
    
    $global:instNM= "[POD2@dc20gtdb02].",
        "[DEV_POD@dc20gtdevdb03].",
        "[GTODS@DC20GTODS]."

    $global:tblBatch = New-Object collections.arraylist

    $instances | %{
        $ins = $_
        $q = New-Object collections.queue
        $dbs = GTwConn -targetHost $ins -targetcmd "select name from sys.databases where database_id > 4"
        $dbs.rows | %{
            $r = $_.name
            TRY{            
                $schs = GTwConn -targetHost $ins -targetcmd "
                    select '[' + sch.name + '].[' + obj.name + ']' obj from $r.sys.schemas sch 
                   inner join $r.sys.tables obj
                   on sch.schema_id = obj.schema_id"
                try{
                    $schs.rows | %{
                        try{
                            $q.Enqueue($("[$r]." + $_.OBJ))
                        } catch {
                            Qlog "Q '$($_.OBJ)' Failed!"
                        }
                    }
                } catch {
                    Qlog "Q '$r' Failed!"
                }
            
            } CATCH {
                Qlog "Q '$INS' Failed!"
            }
        }
        #$sfq = [collections.queue]::Synchronized($q)
        [void] $tblBatch.add($q) #$sfq)
    }
    $global:iq = New-Object collections.queue
    getItemQ -itmQ $([ref] $global:iq)

    Register-EngineEvent -SourceIdentifier qnext -Action {
        $cid = $args[0]
        doConc $cid
    }

}

$sample = {
    param(
        $insID
        , $QRY
    )
    try{
        $INST = $instances[$insID]
        $INST2 = $instNM[$insID]
        $flnm = "U:\PA2013\$INST2$QRY.csv"
        $TBLDATA = GTwConn -targetHost $INST -targetcmd "select TOP(200) * FROM $QRY"
        if($TBLDATA -ne $null){
            $csv = $TBLDATA | ConvertTo-Csv -Delimiter "," -NoTypeInformation                
            [System.IO.File]::WriteAllLines($flnm, $csv)
        } else {
            $cnt = (GTwConn -targetHost $INST -targetcmd "select count(*) cnt FROM $QRY").ROWS[0].CNT
            QLOG "$QRY in $INST2 has no data, count $cnt"
        }
    } catch [exception]{
        write-host $($_ | out-string) -ForegroundColor Yellow 
        QLOG "$QRY in $INST2 not exists"
    }    
}

function doConc {
    param(
        $concID
        , $DESC
    )
    try{
        $qitm = $Global:iq.Dequeue()
    } catch {break}

    Write-Host "$concID : $($qitm[1]) start"
    $jb = Start-Job -ArgumentList $sample, $concID, $qitm, $instances, $instNM, $global:logfile, 
            $Function:Qlog, $Function:GTwConn -ScriptBlock {
        param(
            $sample, $concID, $qitm, $instances, $instNM, $global:logfile, $Qlog, $GTwConn
        )
        Import-Module SQLDATA -Force -DisableNameChecking
        iex "function Qlog {$($Qlog.ToString())}"
        iex "function GTwConn {$($GTwConn.ToString())}"
        . $([scriptblock]::Create($sample)) $qitm[0] $qitm[1]
    }
    $posh = "Register-ObjectEvent -InputObject `$jb -EventName StateChanged -SupportEvent -Action {
        Write-Host `"$concID : $($qitm[1]) done`"
        New-Event -SourceIdentifier qnext -EventArguments $concID
    }"
    iex $posh
}

init


doConc 00
doConc 01
doConc 02
doConc 03
doConc 04
doConc 05
doConc 06
doConc 07
doConc 08
doConc 09
doConc 10
doConc 11
doConc 12
doConc 13
doConc 14
doConc 15
doConc 16
doConc 17
doConc 18
doConc 19
doConc 20

Comments