zOs/SQL/ALIASMK

$*(--- wsh script um  Alias abzugleichen -------------------------------

     1. $=fun=list: eine Liste der Alias erstellen, ins dsn $aliasDsn
           ==> auf dem quellSystem, connect auf die richtige ssid|
     2. $=fun=comp: Alias vergleichen: die fehlenden
                          oder unterschiedlichen aliase rausschreiben
           ==> auf dem zielSystem, connect auf die richtige ssid|
     3. $=fun=crea: create fehlende Alias (sonst wie comp)
-------------------------------------------------------------------- $*)

call sqlConnect dbtf
$=fun = comp
$=aliasDsn=A540769.WK.TEXW(ALIAS)
eq = 0
nn = 0

if $fun == 'list' then $@[
    $>$aliasDsn
    call sqlPreOpen 1, "select strip(creator) || '.' || strip(name),",
                          "strip(location), createdTs, ",
                          "strip(tbCreator) || '.' || strip(tbName)",
                        "from sysibm.sysTables" ,
                        "where type = 'A'",
                        "order by 1, 2"
    do while sqlFetchInto(1, ":tb, :loc, :cr, :to")
        $$- 'tb' tb 'loc' loc 'to' to 'cr' cr
        nn = nn + 1
        end
    say nn 'alias gefunden'
    call sqlClose 1
$] else $@[
    call sqlExec "set current sqlId = 'S100447'"
    $@proc prep $@[
    call sqlPrepare 1, "select strip(creator) || '.' || strip(name),",
                              "strip(location), createdTs, type, ",
                              "strip(tbCreator) || '.' || strip(tbName)",
                            "from sysibm.sysTables" ,
                            "where  creator = ? and name = ?"
                $]
    $@prep()
    $;
    $<$aliasDsn
    $@for line $@[
        parse value $line with 'tb' cr '.' tb 'loc' loc 'to' to 'cr' tst .
        if sqlOpAllCl(1, st, ":fTb, :fLo, :fTst, :fTy, :fTo",
               , strip(cr), strip(tb)) = 1 then do
            if strip(loc) = fLo & strip(to) = fTo & fTy == 'A' then do
                eq = eq+1 /* say 'equal' strip(fTb) '=' */
                end
            else do
                m = 'notEq' strip(fTb)':' strip(loc)'.'strip(to) ,
                                      '<>' fTy fLo'.'fTo
                say m
                $$- m
                end
            end
        else if m.st.0 > 0 then do
            say tb 'with' m.st.0 'rows'
            end
        else do
            $$- 'missing' strip(cr)'.'strip(tb) 'loc' strip(loc) $*+
                  'to' strip(to) tst
            if loc = '' & $fun == 'crea' then do
                call sqlExec 'create alias' strip(cr)'.'strip(tb) ,
                             'for' strip(to)
                nn = nn + 1
                if nn // 100 = 1 then do
                    say 'created' nn  strip(cr)'.'strip(tb)
                    call sqlCommit
                    $@prep()
                    end
                end
            end
        $]
    call sqlCommit
    say 'total equal' eq
$]
call sqlDisconnect
$#out                                              20100615 16:50:30
$#out                                              20100615 16:48:52
$#out                                              20100615 16:47:50
$#out                                              20100615 16:16:57
notEq OA1P.TTP011A1RZ4: CHSKA000DBOL.OA1P.TTP011A1 <> A .OA1P.TTP011A1
notEq OA1P.TTP012A1RZ4: CHSKA000DBOL.OA1P.TTP012A1 <> A .OA1P.TTP012A1
notEq OA1P17.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P17.TAV415A1: .OA1P03.TAV415A1 <> T ..
notEq OA1P18.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P18.TAV415A1: .OA1P03.TAV415A1 <> T ..
notEq OA1P19.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P19.TAV415A1: .OA1P03.TAV415A1 <> T ..
notEq OA1P20.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P20.TAV415A1: .OA1P03.TAV415A1 <> T ..
notEq OA1P21.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P21.TAV415A1: .OA1P03.TAV415A1 <> T ..
notEq OA1P22.TAV413A1: .OA1P03.TAV413A1 <> T ..
notEq OA1P22.TAV415A1: .OA1P03.TAV415A1 <> T ..
$#out                                              20100615 16:16:42