zOs/REXX/SQLCAT
tstCatTb:
/*
$=/tstCatTb/
### start tst tstCatTb ############################################
..
select * from sysibm.SYSDUMMY1 .
IBMREQD
I .
Y .
I .
IBMREQD
$/tstCatTb/
*/
call sqlConnect
call tst t, 'tstCatTb'
call sqlCatTb 'sysDummy1'
call sqlCatTb 'SYSTableSpaceStats',
, "name = 'A403A1' and dbName = 'DA540769'"
call tstEnd t
return
endProcedure tstCatTb
sqlCatIni: procedure expose m.
if m.sqlCat_ini == 1 then
return
m.sqlCat_ini = 1
m.sqlCat_rbaF = '%-20H'
return
endProcedure sqlCatIni
sqlCatTb: procedure expose m.
parse arg ty gOnly, wh, ord, fTab, paPlus
tb = tkrTable(, ty)
if gOnly == 1 then
edFun = ''
else
edFun = tkrTable(, ty, 'e')
cx = 1
ft = 'ft'm.tb.alias
call sqlFTabReset ft, cx, 'c 1', '1 c', 12, if(fTab, , 2000)
call sqlFTabDef ft, 492, '%7e'
call FTabSet ft, 'CONTOKEN' , '%-16H'
call FTabSet ft, 'DCONTOKEN' , '%-16H'
call FTabSet ft, 'DBNAME' , '%-8C', 'db'
call FTabSet ft, 'DSNAME' , '%-44C'
call FTabSet ft, 'DSNUM' , '%5i'
call FTabSet ft, 'PARTITION' ,'%5i' , 'part'
call FTabSet ft, 'PIT_RBA' , m.sqlCat_rbaF
call FTabSet ft, 'RBA1' , m.sqlCat_rbaF
call FTabSet ft, 'RBA2' , m.sqlCat_rbaF
call FTabSet ft, 'START_RBA' , m.sqlCat_rbaF
call FTabSet ft, 'TSNAME' , '%-8C', 'ts'
call FTabSet ft, 'VERSION' , '%-28C'
if edFun \== '' then do
interpret 'sq =' edFun'(ft, tb, wh, ord)'
end
else do
cl = sqlColList(m.tb.table, m.ft.blobMax)
sq = 'select' cl tkrTable( , tb, 'f') wh ,
'order by' if(ord=='', m.tb.order, ord)
call sqlPreOpen cx, sq
call sqlFTabOthers ft
call sqlCatTbVl ft, tb
end
if fTab then
call sqlFTab ft
else
call sqlFTabCol ft
call sqlRxClose cx
call sqlCatTbTrailer space(m.TKR.path paPlus, 1), sq
return 0
endProcedure sqlCatTb
sqlCatTbVlsep:
return '+++'
sqlCatTbVl: procedure expose m.
parse arg ft, tb, sep
if sep == '' then
sep = sqlCatTbVLsep()
if m.tb.vlKey == '' then
return
ky = m.tb.vlKey
ff = ''
tt = ''
do kx=1 to m.ky.0
tt = tt || sep || m.ky.kx.col
ff = ff || sep'@'m.ky.kx.col'%S'
end
call fTabAdd ft, substr(tt,length(sep)+1), substr(ff,length(sep)+1)
return
endProcedure sqlCatTbVl
sqlCatTbTrailer: procedure expose m.
parse arg pa, sq
ox = lastPos(' order by ', sq)
if ox < 1 then
call err 'order by not found in' sq
ord = substr(sq, ox+10)
sq = left(sq, ox-1)
sqUp = translate(sq)
call out ''
call out 'dbSys:' m.sql.conDbSys
call out 'path:' pa
int = ''
iNx = ' '
br = ''
cx = 1
lx = 1
plus = 0
stops = '/*-*/ (select from where'
do while cx < length(sq)
nx = -1
do sx=1 to words(stops)
n2 = pos(word(stops, sx), sq, cx+1)
if n2 > cx & (nx < 1 | n2 < nx) then
nx = n2
end
if nx < 0 then
leave
if substr(sq, nx, 5) == '/*-*/' then do
sq = delStr(sq, nx, 5)
plus = plus + 1
cx = nx
iterate
end
call out int || substr(sq, lx, nx-lx)
int = iNx
if substr(sq, nx, 3) = '(se' then do
iNx = iNx' '
br = left(br, length(int))')'
end
cx = nx
lx = nx
end
ll = strip(substr(sq, cx))
bq = strip(br)
do while bq <> ''
if right(bq, 1) \== ')' | right(ll, 1) \== ')' then
call err 'missing ) bq:' bq', ll:' ll
ll = strip(left(ll, length(ll) - 1))
bq = strip(left(bq, length(bq) - 1))
end
call out int || ll
if br <> '' then
call out br
if ord <> '' then
call out ' order by' ord
return
endProcedure sqlCatTbTrailer
sqlCatCopy: procedure expose m.
parse arg ft, tb, wh, ord
al = m.tb.alias
sq = "select substr('' ||" al".instance || case" ,
"when" al".instance = 1 and s.clone = 'N' then ''" ,
"when s.clone = 'N' then 'only'" ,
"when s.instance =" al".instance then 'base'" ,
"else 'clone' end, 1, 6) insTxt" ,
", icType || case icType" ,
"when 'A' then '=alter'" ,
"when 'B' then '=rebuiIx'" ,
"when 'C' then '=create'" ,
"when 'D' then '=checkData'" ,
"when 'E' then '=recovToCu'" ,
"when 'F' then '=fulCopy'" ,
"when 'I' then '=incCopy'" ,
"when 'J' then '=comprDict'" ,
"when 'L' then '=sql'" ,
"when 'M' then '=modifyRec'" ,
"when 'P' then '=recovPIT'" ,
"when 'Q' then '=quiesce'" ,
"when 'R' then '=loaRpLog'" ,
"when 'S' then '=loaRpLoNo'" ,
"when 'T' then '=termUtil'" ,
"when 'V' then '=repairVer'" ,
"when 'W' then '=reorgLoNo'" ,
"when 'X' then '=reorgLog'" ,
"when 'Y' then '=loaRsLoNo'" ,
"when 'Z' then '=loaLog'" ,
"else '=???' end icTyTx" ,
',' al'.*' ,
'from' tkrTable( , tb, 't') 'join sysibm.sysTableSpace s' ,
'on' al'.dbName = s.dbName and' al'.tsName = s.name' ,
'where' wh 'order by' if(ord == '', m.tb.order, ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, dbName , '%-8C', 'db'
call sqlFTabAdd ft, tsName , '%-8C', 'ts'
call sqlFTabAdd ft, dsNum , '%4i', 'part'
call sqlFTabAdd ft, insTxt , '%6C', 'instan'
call sqlFTabAdd ft, icTyTx , '%-11C', 'icType'
call sqlFTabAdd ft, sType
call sqlFTabAdd ft, oType
call sqlFTabAdd ft, jobName
call sqlFTabAdd ft, timestamp
call sqlFTabAdd ft, dsName
call sqlFTabOthers ft
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatCOPY
sqlCatIxKeys: procedure expose m.
parse arg ft, tb, wh, ord
sq = 'select ikK.colSeq, ikK.colName, ikK.ordering, ikK.period' ,
', ik.creator, ik.name, ik.tbCreator, ik.tbName, ikC.*' ,
tkrTable(, tb ,'f') wh,
'order by' if(ord == '', m.tb.order, ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, CREATOR, '%-8C', 'creator'
call sqlFTabAdd ft, NAME , '%-16C','index'
call sqlFTabAdd ft, colSeq , '%5i', 'coSeq'
call sqlFTabAdd ft, colName, '%-16C', 'column'
call sqlFTabAdd ft, ordering
call sqlFTabAdd ft, period
call sqlFTabAdd ft, COLNO
call sqlFTabAdd ft, COLTYPE
call sqlFTabAdd ft, LENGTH
call sqlFTabAdd ft, SCALE
call sqlFTabAdd ft, NULLS
call sqlFTabOthers ft, 'COL9 COL10 COL11 COL47'
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatIxKeys
sqlCatIXStats: procedure expose m.
parse arg ft, tb, wh, ord
sq = 'select *' tkrTable( , tb, 'f') wh ,
'order by' if(ord == '', m.tb.order, ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, CREATOR, '%-8C', 'creator'
call sqlFTabAdd ft, NAME , , 'index'
call sqlFTabAdd ft, INSTANCE , '%1i' , 'i'
call sqlFTabAdd ft, PARTITION , , 'part'
call sqlFTabOthers ft
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatIXStats
sqlCatRec: procedure expose m.
parse arg ft, tb, pWh, ord
wh = sqlWhereResolve(pWh)
al = m.tb.alias
vw = catRecView('cat')
if m.recView.unl then
sq = "select fun, recover, lok || ' ' || load loadText"
else
sq = "select case when left(recover, 2) = 'ok'",
"then 'r' else '?' end fun" ,
", '' stage, 'noXDocs' loadText" ,
", '' unlTst, '' unl, '' punTst, '' pun"
sq = sq", lPad(strip(basPa), 4) || basTy|| char(basTst) basPTT",
", ( select case when count(*) <> 1" ,
"then '|' || count(*) || 'tables'",
"else max(strip(creator) ||'.'|| name) end",
"/*-*/from sysibm.sysTables t" ,
"/*-*/where t.dbName =" al".db" ,
"and t.tsName="al".ts and type not in ('A', 'V')) tb",
"," al".*",
"from" vw al,
'where' m.tb.cond wh ,
'order by' if(ord == '', m.tb.order, ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, db , '%-8C', 'db'
call sqlFTabAdd ft, ts , '%-8C', 'ts'
call sqlFTabAdd ft, pa , '%4i', 'part'
call sqlFTabAdd ft, insTxt , '%-5C', 'insta'
call sqlFTabAdd ft, fun , '%-2C', 'fun'
call sqlFTabAdd ft, stage , '%-2C', 'sta'
call sqlFTabAdd ft, recover , '%-7C', '?recov?'
call sqlFTabAdd ft, basPTT , '%-18C','part copytime'
call sqlFTabAdd ft, loadText , '%-70C', '?load?'
call sqlFTabAdd ft, unlTst , '%-19C', 'unloadTime'
call sqlFTabAdd ft, unl , '%-44C', 'unloadDSN'
call sqlFTabAdd ft, punTst , '%-19C', 'punchTime'
call sqlFTabAdd ft, pun , '%-44C', 'punch'
call sqlFTabAdd ft, 'TB' , '%-40C', 'table'
call sqlFTabOthers ft
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatRec
sqlWhereResolve: procedure expose m.
parse arg wh
wh = strip(wh)
l1 = pos('(', wh)
l2 = pos('(', wh, l1+1)
l3 = pos('(', wh, l2+1)
r1 = pos(')', wh)
r2 = pos('FROM', translate(wh))
if r2 <= 0 then
if pos('SELECT', translate(wh)) < 1 then
return wh
else
call err 'select without from in where:' wh
if l1 <= 0 | l2 <= 0 | r1 <= 0 then
call err 'bad missing first 2 brackets where:' wh
if l1 <> 1 | r1 > l2 then
call err 'bad first bracket pair in where:' wh
if l2 >= r2 | (l3 <= r2 & l3 > 0) then
call err 'bad second bracket / from in where:' wh
if translate(strip(substr(wh, r1+1, l2-r1-1))) \== 'IN' then
call err 'in missing in where:' wh
li = translate(substr(wh, 2, r1-2), ' ', ',')
ci = substr(wh, l2+1, r2-l2-1)
if translate(word(ci, 1)) \== 'SELECT' then
call err 'missing select in where:' wh
ci = subWord(ci, 2)
cj = translate(ci, ' ', ',')
c0 = words(cj)
if c0 <> words(li) then
call err 'list 1&2 not equal len in where:' wh
do cx=1 to words(cj)
lA = word(cj, cx)
c.cx = translate(substr(lA, pos('.', lA) + 1))
l.cx = word(li, cx)
end
call sql2St substr(wh, l2+1, length(wh)-l2-1),
'group by' ci 'order by' ci, rr
c1 = c.1
c2 = c.2
r = ''
do rx=1 to m.rr.0
if rx = 1 then
ex = 0
else do
ry = rx - 1
do ex=1 to c0
cA = c.ex
if m.rr.rx.cA <> m.rr.ry.cA then
leave
end
ex = ex-1
if ex < c0 - 1 then
r = r copies(')', c0-ex)
end
do dx=ex+1 to c0
cA = c.dx
if dx = ex + 1 then
r = r 'or' left('(', dx < c0)
else
r = r 'and ('
r = r l.dx "= '"m.rr.rx.cA"'"
end
end
return substr(r, 4) copies(copies(')', c0), c0>1)
endProcedure sqlWhereResolve
catRecView: procedure expose m.
parse arg m
m.recView.unl = wordPos(m.m.dbSy, 'DBOF DVBP') > 0
if \ m.recView.unl then
return 'oa1p.vqz005Recover'
call sql2St "select punTst tst, err" ,
", case when punTst < current timestamp - 1 hour" ,
"then 1 else 0 end att" ,
"from oa1p.tQZ005TecSvUnload" ,
"where stage = '-r'", recView
call out ' '
t = 'Recovery Unloads aus oa1p.tQZ005TecSvUnload'
if m.m.dbSy = 'DVBP' then
call out ' ELAR XB' t
else
call out ' EOS und eRet (XC, XR)' t
t = 'refresh='m.recView.1.tst 'err='m.recView.1.err
if m.recView.0 < 1 then
call out ' Achtung: ist leer'
else if m.recView.0 > 1 then
call out ' Achtung: zuviele ('m.recView.0') -r rows'
else if m.recView.1.att = 1 then
call out ' Achtung: älter 1h:' t
else
call out ' ' t
call out ' cx -ru ... für refresh unload'
call out ' '
return 'oa1p.vqz005RecovLoad'
endProcedure catRecView
sqlCatTables: procedure expose m.
parse arg ft, tb, wh, ord
al = m.tb.alias
sq = 'select' al'.*, tsX.type tsType, tsX.partitions',
', tsX.pgSize, tsX.dsSize' ,
',' sqlLrsn2tst('rba1') 'rba1Tst' ,
',' sqlLrsn2tst('rba2') 'rba2Tst' ,
'from' m.tb.table 'left join sysibm.sysTablespace tsX',
'on' al'.dbName = tsx.dbName and' al'.tsName = tsX.name',
'where' m.tb.cond wh ,
'order by' if(ord == '', m.tb.order, ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, creator , '%-8C', 'creator'
call sqlFTabAdd ft, NAME , '%-24C', 'table'
call sqlFTabAdd ft, type
call sqlFTabAdd ft, dbNAME , '%-8C', 'db'
call sqlFTabAdd ft, tsNAME , '%-8C', 'ts'
call sqlFTabAdd ft, tsType
call sqlFTabAdd ft, partitions, , 'parts'
call sqlFTabAdd ft, pgSize
call sqlFTabAdd ft, dsSize
call sqlFTabOthers ft, 'RBA1 RBA1TST RBA2 RBA2TST'
call sqlFTabAdd ft, rba1 , m.sqlCat_rbaF
call sqlFTabAdd ft, rba1Tst , , 'rba1Timestamp:GMT'
call sqlFTabAdd ft, rba2 , m.sqlCat_rbaF
call sqlFTabAdd ft, rba2Tst , , 'rba2Timestamp:GMT'
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatTables
sqllrsn2tst: procedure expose m.
parse arg f /* sql fails in v10 without concat | */
return "timestamp(case when length("f") = 6 then" f "|| x'0000'" ,
"when substr("f", 1, 4) = x'00000000' then" ,
"substr("f" || X'000000000000', 5, 8)" ,
"else substr("f" || X'00000000', 2, 8) end)"
sqlCatTSStats: procedure expose m.
parse arg ft, tb, wh, ord
sq = 'select' m.tb.alias'.*' ,
tkrTable( , tb, 'f') wh ,
'order by' if(ord == '', m.tb.order , ord)
call sqlPreOpen m.ft.sqlX, sq
call sqlFTabAdd ft, DBNAME, '%-8C', 'db'
call sqlFTabAdd ft, NAME , '%-8C', 'ts'
call sqlFTabAdd ft, INSTANCE , '%1i' , 'i'
call sqlFTabAdd ft, PARTITION , , 'part'
call sqlFTabAdd ft, NACTIVE , , 'nActive'
call sqlFTabAdd ft, NPAGES , , 'nPages'
call sqlFTabAdd ft, SPACE , , 'spaceKB'
call sqlFTabAdd ft, TOTALROWS , , 'totRows'
call sqlFTabAdd ft, DATASIZE , , 'dataSz'
call sqlFTabAdd ft, LOADRLASTTIME , , 'loadRLasttime'
call sqlFTabAdd ft, REORGLASTTIME , , 'reorgLasttime'
call sqlFTabAdd ft, REORGINSERTS , , 'inserts'
call sqlFTabAdd ft, REORGDELETES , , 'deletes'
call sqlFTabAdd ft, REORGUPDATES , , 'updates'
call sqlFTabAdd ft, REORGUNCLUSTINS , , 'unClIns'
call sqlFTabAdd ft, REORGDISORGLOB , , 'disorgL'
call sqlFTabAdd ft, REORGMASSDELETE , , 'massDel'
call sqlFTabAdd ft, REORGNEARINDREF , , 'nearInd'
call sqlFTabAdd ft, REORGFARINDREF , , 'farInd'
call sqlFTabAdd ft, REORGCLUSTERSENS , , 'cluSens'
call sqlFTabAdd ft, REORGSCANACCESS , , 'scanAcc'
call sqlFTabAdd ft, REORGHASHACCESS , , 'hashAcc'
call sqlFTabAdd ft, STATSLASTTIME , , 'statsLasttime'
call sqlFTabAdd ft, STATSINSERTS , , 'inserts'
call sqlFTabAdd ft, STATSDELETES , , 'deletes'
call sqlFTabAdd ft, STATSUPDATES , , 'updates'
call sqlFTabAdd ft, STATSMASSDELETE , , 'massDel'
call sqlFTabAdd ft, COPYLASTTIME , , 'copyLasttime'
call sqlFTabAdd ft, COPYUPDATETIME , , 'copyUpdatetime'
call sqlFTabAdd ft, COPYUPDATELRSN , m.sqlCat_rbaF ,
, 'updateLRSN'
call sqlFTabAdd ft, COPYUPDATEDPAGES , , 'updaPgs'
call sqlFTabAdd ft, COPYCHANGES , , 'changes'
call sqlFTabOthers ft
call sqlCatTbVl ft, tb
return sq
endProcedure sqlCatTSStats