zOs/SQL/DDLCHDDL
$#@
$*(
17. 6.16 defaults for new UTS guidelines
10. 6.16 PBG mit 4GB und 10 maxParts
7. 3.16 pa fuer views geflickt
2. 2.16 fqz Funktionen
30.11.15 ELAR: primary key ignorieren, tb-, ixName von TS nicht DB
18.11.15 fix att, nur rTrim, wegen Sortierung von s, no part in path
13. 7.15 mit User ddlCheckt und 2. Elar Variante
17.12.14 datacapture changes, genSel und 1. Elar Variante
26.11.13 datacapture cat='o' (immer noch override auf none|)
19.11.13 order by ebcdic for rexx
25. 9.13 uts ok, kleine pctFree, locksize usw. Aenderung o statt oq
Achtung neues WS2|
19. 9.13 primary constraint reklamiert wenn vorhanden statt fehlend
19. 7.13 namingconvention aus current server, stabiler
17. 7.13 lobs angepasst
$*)
$=cr=A540769
$=cr=OA1P
$=gpCols= gp, gpCr, gpOpt
$=cols8 = ty, qu, nm, cat, att, val, std, pa
$=colE8 = ty, qu, nm, pa, cat, att, val, std
$=colO8 = ty, cat, qu, nm, att, val, std, pa
if 0 then $@[ $** execute ddl in rz4 ------------------------
$@%[genDdl OA1P $]
$>. fEdit()
$** $| $@ call sqlConnect DP4G ; call sqlStmts
$] else if 0 then $@[ $** distribute/exe ddl in all rz/dbSys -------
$>. fEdit()
$<=[
RZ4 DBOL DP4G
RZ2 DBOF DVBP DP2G
RZX DE0G DEVG DPXG DX0G
RZY DE0G DEVG DPYG
RZZ DE0G DEVG DPZG
RR2 DBOF DVBP DP2G
RQ2 DBOF DVBP DP2G
$]
$@genJobs
$] else if 1 , $** test dp4g ---------------------------------
& sysVar(sysnode) = 'RZ4' then $@=[
set current path = 'OA1P';
insert into $cr.tQZ120DdlSel (ty, qu, nm)
select 't', creator , Name
from sysibm.sysTables
where
name in ( 'TQZ120DDLSEL'
, 'TQZ006GBGRTSSTATS'
, 'TQZ050CMD'
, 'TQZ040BINDPGM'
) ;
insert into $cr.tQZ120DdlSel (ty, qu, nm)
values ('v', 'OA1P', 'VQZ040REBIND') ;
$$ -- ddlChDdl generated $-[f('%t s')$]
$@genSel
$** $>. fEdit('A540769.TMP.SQL(DDLCH)')
$| $@ call sqlConnect DP4G ; call sqlStmts
$] else if 0 then $@=[ $** test dbof --------------------------------
set current path = 'OA1P';
insert into $cr.tQZ120DdlSel (ty, qu, nm)
select 'ts', dbName , tsName
from sysibm.sysTables
where type = 'T'
and name in ( 'VTINSTRUMENT'
-- , 'TQZ050CMD'
);
insert into $cr.tQZ120DdlSel (ty, qu, nm)
valueS ('v', 'OA1P', 'VNGNOFER') ;
insert into $cr.tQZ120DdlSel (ty, qu, nm)
valueS ('db', 'XB007002', '') ;
$$ -- ddlChDdl generated $-[f('%t s')$]
$@genSel
$** $>. fEdit('A540769.TMP.SQL(DDLCH)')
$| $@ call sqlConnect DVBP ; call sqlStmts
$]
$proc $@=/genDdl/
$=path=- strip(arg(2))
set current sqlid = 'S100447';
set current path = '$path';
drop table $cr.tQZ120DDlSel;
create global temporary table $cr.tQZ120DdlSel
(ty char(3) not null, qu varchar(128) not null
, nm varchar(128) not null ) -- with default geht nicht ||||
;
create view $cr.vQZ120DdlChec8 as
$@cteC8
select * from uO
;
create view $cr.vQZ120DdlCheck as
$@txt
from $cr.vQZ120DdlChec8
;
create view $cr.vQZ120DdlCheSu8 as
with t as
(
select * from OA1P.vQZ120ddlChec8 t
)
, u ($cols8) as
(
select ty, '', '', '', 'count ' || ty
, right(' ' || count(*), 8)
, '', ' count=s-' || ty
from t
where cat = 's'
group by ty
union all select '', '', '', '', 'count err'
, right(' ' || count(*), 8)
, '', ' countErr=' || min(cat) || '-' || max(cat)
from t
where cat <> 's'
union all select $cols8
from t
)
$@cteUO
select * from UO
;
create view $cr.vQZ120DdlCheSum as
with t as
(
select t.*
, case when posStr(err, '=') = 0 then err
else left(err, posStr(err, '=')) end erSh
from $cr.vQZ120ddlCheck t
)
, u (ty, qu, nm, cat, err, pa) as
(
select ty, qu, nm, cat, err, pa
from t
union all select ty, '', '', '*s', count(*) || ' * ' || ty
|| ': ' || min(qu || '.' || nm) || ' - ' || min(qu || '.' || nm)
, '*ty ' || ty
from t
where cat = 's'
group by ty
union all select ty, '', '', '*' || cat, count(*) || ' * ' || erSh
, '*tyCatErr ' || ty || cat || erSh
from t
where cat <> 's'
group by ty, cat, erSh
)
select * from u
;
create view $cr.vQZ120DdlCheS8 as
$@cte
, rt (ty, qu, nm, pa, rows, space) as
(
select ty, qu, nm
, substr(min(right('00000' || length(strip(pa)), 6)
|| strip(pa)), 7) pa
, real(sum(totalRows)) rows
, sum(ts.pgSize * real(r.nActive) * 1024) space
from ts left join sysibm.sysTablespaceStats r
on r.dbName = ts.dbName and r.name = ts.name
and r.dbid = ts.dbid and r.psid = ts.psid
group by ty, qu, nm
)
, ri (ty, qu, nm, pa, rows, space) as
(
select ty, qu, nm
, substr(min(right('00000' || length(strip(pa)), 6)
|| strip(pa)), 7) pa
, real(sum(totalEntries))
, sum(case when i.pgSize = 4096 then 4
else i.pgSize end
* real(r.nActive) * 1024)
from i left join sysibm.sysIndexspaceStats r
on r.creator= i.creator and r.name = i.name
and r.dbid = i.dbid and r.isobid = i.isobid
group by ty, qu, nm
)
, u ($cols8) as
(
select $cols8 from eU
union all select ty, qu, nm,
case when space > 2e10 or rows > 1e8
then 'sb' else 's' end,
' rows,space', oa1p.fqzFmte7(rows), oa1p.fqzFmtE7(space),pa
from rt
union all select ty, qu, nm, 's', '', '', '', pa from t
union all select ty, qu, nm, 's',
' rows,space', oa1p.fqzFmte7(rows), oa1p.fqzFmtE7(space), pa
from ri
union all select ty, qu, nm, 's', '', '', '', pa from v
)
$@cteUO
select * from uO
;
create view $cr.vQZ120DdlCheSz as
$@txt
from $cr.vQZ120DdlCheS8
;
commit
;
$/genDdl/
$proc $@=/genSel/ $** generate a select without views
$@cteC8
$** $@outTxt
-- select * from uO ; x; by qu, nm ; x;
select ty, substr(qu || '.' || nm, 1, 30) qualifierName, cat
, substr(att, 1, 12) att
, substr(val, 1, 20) val
, substr(std, 1, 20) std
, pa
from uO -- outTxt
;
$*(
select qu, nm, db, pa
from vD;
select lv, uQ, uN, qu, nm, pa
from vUse;
$*)
$/genSel/
$@proc cte $@=/cte/
with std (std, mbrClu, app, pFree) as
(
select ' ', ' ', 'N', 5 from sysibm.sysDummy1
union all select 'x', 'Y', 'Y', 0 from sysibm.sysDummy1
)
, nmC1 as
(
select right(strip(current server), 4) dbSys
from sysibm.sysDummy1
)
, nmC2 as
( select case when posStr(dbSys, 'V') > 0 then 'V'
when dbSys = 'DBAF' then 'A'
when dbSys = 'DBTF' then 'T'
else 'P'
end nmCn, dbSys
from nmC1
)
, nmCn as
( select case when nmCn = 'V' then 'BUA' else 'OA1' || nmCn end cr
, case when nmCn = 'V' then '' else '99' end cr99
, case when nmCn = 'V' then 'BUA'
else 'OA1' || nmCn || ' 99?' end crStd
, nmCn, dbSys
from nmC2
)
, ts0 (qu, nm) as
(
select dbName, name
from sysibm.sysTablespace ts join $cr.tQZ120DdlSel s
on s.ty = 'db' and s.qu = ts.dbName
union all select qu, nm
from $cr.tQZ120DdlSel s
where s.ty = 'ts'
)
, tsU (qu, nm) as
(
select t.dbName , t.tsName
from sysibm.sysTables t
join $cr.tQZ120DdlSel s
on s.ty = 't' and s.qu = t.creator and s.nm = t.name
where t.dbName not like 'DSNDB%'
union all select * from ts0
)
, tsG as
(
select *
from tsU
group by qu, nm
)
, ts as
(
select 'ts' ty, qu, nm
$@%[db2gp qu$]
, 'db=' || strip(qu) || ' ts=' || strip(nm) pa
, s.*
, 33 pctFreeZiel
from tsG g join sysibm.sysTablespace s
on g.qu = s.dbName and g.nm = s.name
)
, tp as
(
select 'tp' ty, qu, nm, $gpCols, pa
, ts.type tsTy, ts.partitions parts
, tp.*
from ts join sysibm.sysTablePart tp
on ts.qu = tp.dbName and ts.nm = tp.tsName
)
, tN (qu, nm) as
(
select creator, name
from ts0 join sysibm.sysTables t
on t.dbName = ts0.qu and t.tsName = ts0.nm
and t.type not in ('A', 'V')
union all select qu, nm
from $cr.tQZ120DdlSel s
where ty = 't'
)
, tG as
(
select qu, nm
from tN
group by qu, nm
)
, t as
(
select 't' ty, qu, nm
, 'db=' || strip(t.dbName) || ' ts=' || strip(t.tsname)
|| ' t=' || strip(t.creator) || '.' || strip(t.name) pa
$@%[db2gp t.dbName$]
, t.*
from tG join sysibm.sysTables t
on t.creator = tG.qu and t.name = tG.nm
and t.type not in ('A', 'V')
)
, c as
(
select 'c' ty, '' qu, c.name nm, $gpCols
, pa || ' c=' || right('0000'||colNo, 5) ||'-'|| strip(c.name) pa
, c.*
from t join sysibm.sysColumns c
on qu = c.tbCreator and nm = c.tbName
)
, iN (qu, nm) as
(
select strip(i.creator), strip(i.name)
from tG join sysibm.sysIndexes i
on qu = i.tbCreator and nm = i.tbName
union all select qu, nm
from $cr.tQZ120DdlSel s
where s.ty = 'i'
)
, iG as
(
select qu, nm
from iN
group by qu, nm
)
, i as
(
select 'i' ty, qu, nm
, 'db=' || strip(t.dbName) || ' ts=' || strip(t.tsname)
|| ' t=' || strip(t.creator) || '.' || strip(t.name)
|| ' i=' || strip(i.creator) || '.' || strip(i.name) pa
$@%[db2gp i.dbName $]
, i.*
from iG
join sysibm.sysIndexes i
on iG.qu = i.creator and iG.nm = i.name
join sysibm.sysTables t
on i.tbCreator = t.creator and i.tbName = t.name
)
, ip as
(
select 'ip' ty, qu, nm, $gpCols, pa, ip.*
from i join sysibm.sysIndexPart ip
on ip.ixCreator = qu and ip.ixName = nm
)
, vU (ty, qu, nm, pa, db, lv) as
(
select unicode_str('T'), qu, nm, pa, dbName, 0
from t
union all select unicode_str('V'), qu, nm
, 'v=' || strip(qu) ||'.'|| strip(nm), '', 0
from $cr.tQZ120DdlSel
where ty = 'v'
union all select dType, dCreator, dName, pa || ' ' || dType || '='
|| strip(dCreator) || '.' || strip(dName), db, lv+1
from vU join sysibm.sysViewDep d
on d.bCreator = vU.qu and d.bName = vU.nm -- and d.bType = vU.ty
where lv < 100
)
, vG as
( select qu, nm
-- shortest path, preferrable not starting with view
, replace(substr(
min(case when left(pa, 1) = 'v' then 'v' else 'e' end
|| right('00000' || length(pa), 6) || pa)
, 8), 'V=', 'v=') pa
, max(case when db like 'DSNDB%' then '' else db end) db
from vU
where ty = 'V'
group by ty, qu, nm
)
, vUse ( qu, nm, pa, uQ, uN, lv) as
( -- if db is empty or DSNDB% then search
-- all used views for application db
select qu, nm, pa, qu, nm, 0
from vG
where db = ''
union all select qu, nm, pa, bCreator, bName, lv + 1
from vUse join sysibm.sysViewDep d
on d.dCreator = vUse.uQ and d.dName = vUse.uN
-- and d.bType = 'V' and d.dType = 'V'
where lv < 100
)
, vD (qu, nm, pa, db) as
(
select qu, nm, max(pa)
, max(case when dbName like 'DSNDB%' then '' else dbName end) db
from vUse join sysibm.sysTables t
on vUse.uQ = t.creator and vUse.uN = t.name
-- and t.type = 'V'
group by qu, nm
union all select * from vG
where db <> ''
)
, v as -- all used views for application db
(
select 'v' ty, qu, nm, pa
$@%[db2gp vD.db $]
, t.*
from vD join sysibm.sysTables t
on t.type = 'V' and t.creator = qu and t.name = nm
)
, eTs ($colE8) as
(
select ty, qu, nm, pa, 'p', 'type',
case when type = 'O' then 'lob'
when type = 'P' then 'XML'
when type = ' 'and partitions = 0
and segSize = 0 then 'simple'
when type = ' ' then 'classic'
when type = 'L' then 'large'
else 'type='||type
end, 'UTS'
from ts
where type not in ('G', 'R')
union all select ty, qu, nm, pa, 'p', 'status',
case when status = 'C' then 'partitioning undefined'
when status in ('P' ,'S') then 'check pending'
when status = 'T' then 'no table'
else 'status=' || status end
end, ''
from ts
where status <> 'A'
union all select ty, qu, nm, pa, 'n', 'name', name
, 'S' || substr(dbName, 3, 4) || '%'
from ts, nmC1
where gpOpt = 'x' and 'DX0G' <> dbSys
and left(name, 4) <> 'S' || substr(dbName, 3, 3)
union all select ty, qu, nm, pa, 'p', 'tables', char(nTables), '1'
from ts
where nTables <> 1
union all select ty, qu, nm, pa, 'p', 'partitioned', 'no', 'yes'
from ts
where gpOpt = 'x' and partitions = 0
union all select ty, qu, nm, pa, 'p', 'partitions',
char(partitions), '<=200'
from ts
where partitions > 200
union all select ty, qu, nm, pa, 'o', 'bufferPool', bPool, 'bp2...'
from ts
where bPool not in('BP2', 'BP8K1', 'BP16K1', 'BP32K')
union all select ty, qu, nm, pa, 'o', 'bufferPool', bPool, 'bp2...'
from (select ts.*
, case when strip(name) like '%H' then 'BP32K'
else 'BP2' end bp
from ts
where gpOpt = 'x' ) ts
where bPool <> bp
union all select ty, qu, nm, pa, 'p', 'encoding',
encoding_scheme, 'EBCDIC'
from ts
where encoding_scheme <> 'E'
union all select ty, qu, nm, pa, 'o', 'close', closeRule, 'Y'
from ts
where closeRule <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'dssize', char(dsSize), '16GB'
from ts
where dssize <> 16777216 and partitions <> 0 and type <> 'G'
union all select ty, qu, nm, pa, 'oq', 'dssize', char(dsSize), '4GB'
from ts
where dssize <> 4194304 and partitions <> 0 and type = 'G'
union all select ty, qu, nm, pa, 'o', 'erase', eraseRule, 'N'
from ts
where eraseRule <> 'N'
union all select ty, qu, nm, pa, 'p', 'implicit', implicit, 'N'
from ts
where implicit <> 'N'
union all select ty, qu, nm, pa, 'o', 'lockmax',
char(lockmax), 'system'
from ts
where lockMax <> -1
union all select ty, qu, nm, pa
, case when lockRule = 'P' then 'o' else 'oq' end
, 'locksize', lockRule, 'any'
from ts
where lockRule <> 'A'
union all select ty, qu, nm, pa, 'o', 'logged', log, 'Y'
from ts
where log <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'maxrows', char(maxrows),'255'
from ts
where maxrows <> 255 and type <> 'O'
union all select ty, qu, nm, pa, 'pq', 'membercluster',
member_cluster, mbrClu
from ts join std on ts.gpOpt = std.std
where member_cluster <> mbrClu
union all select ty, qu, nm, pa, 'o', 'segsize', char(segsize), '64'
from ts
where segsize not in (0, 64)
union all select ty, qu, nm, pa, 'p', 'clone', clone, 'N'
from ts
where clone <> 'N'
union all select ty, qu, nm, pa, 'p', 'maxParts',
char(maxPartitions), '10'
from ts
where type = 'G' and maxPartitions <> 10
union all select ty, qu, nm, pa, 'p'
, case when organizationType = 'H' then 'hash'
else 'organizationType='||organizationType end
,'organization', 'default'
from ts
where organizationType <> ' '
)
, eTP ($colE8) as
(
select ty, qu, nm, pa, 'o', 'compress', compress, 'Y'
from tp
where tp.compress <> 'Y'
union all select ty, qu, nm, pa, 'oq', 'storName',storName,'GSMS?'
from tp
where (gp <> 'XB' and storName <> 'GSMS')
or (gp = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, pa, 'o', 'priQty', char(pQty), '-1'
from tp
where pqty <> -1
union all select ty, qu, nm, pa, 'o', 'secQty', char(sQty), '-1'
from tp
where sqty <> -1
union all select ty, qu, nm, pa
, case when freePage >= 10 then 'o' else 'oq' end
, 'freePage', char(freepage), '0'
from tp
where freepage <> 0
union all select ty, qu, nm, pa
, case when pctFree <= 10 then 'o' else 'oq' end
, 'pctFree', char(pctFree), char(pFree)
from tp join std on tp.gpOpt = std.std
where pctFree <> pFree and tsTy <> 'O'
union all select ty, qu, nm, pa, 'o', 'gbpCache',
gbpCache, 'changed'
from tp
where gbpCache <> ' '
)
, eT ($colE8) as
(
select ty, qu, nm, pa, 'n', 'creator', qu, gpCr
from t
where not (qu = gpCr
or (gpCr <> 'BUA' and gpCr || '00'
= translate(qu, '999999999', '012345678'))
or (gp = 'VV' and qu = 'VDPS2'))
union all select ty, qu, nm, pa, 'n', 'name', nm, tbPre || '%'
from ( select t.*
, case when gpOpt = 'x' then 'XB' || substr(tsName, 2,6)
when gp = 'VV' then 'TVV'
else 'T' || gp || substr(tsName, 2, 4) end tbPre
from t, nmC1 ) t
where left(name, length(tbPre)) <> tbPre
union all select ty, qu, nm, pa, 'p', 'tableStatus',
case when tableStatus = 'C' then 'clone'
when tableStatus = 'L' then 'auxilary index oder table fehlt'
when tableStatus = 'P' then 'primary index fehlt'
when tableStatus = 'R' then 'index auf Row ID fehlt'
when tableStatus = 'U' then 'index auf unique key fehlt'
when tableStatus = 'V' then 'Fehler interne ViewDarstellung'
else 'tableStatus=' || tableStatus
end, 'ok'
from t
where tablestatus <> ' '
union all select ty, qu, nm, pa, 'p', 'append', append, app
from t join std on t.gpOpt = std.std
where append <> app
union all select ty, qu, nm, pa, 'p', 'audit', auditing, 'no'
from t
where auditing <> ' '
union all select ty, qu, nm, pa, 'o', 'dataCapture',
dataCapture, 'changes'
from t
where dataCapture <> 'Y'
union all select ty, qu, nm, pa, 'o', 'restrictDrop',
clustertype, 'Y'
from t
where clusterType <> 'Y'
union all select ty, qu, nm, pa, 'p', 'volatile',
split_rows, 'Y'
from t
where gpOpt = 'x' and split_rows <> 'Y'
union all select ty, qu, nm, pa, 'p', 'volatile',
split_rows, 'not'
from t
where gpOpt <> 'x' and split_rows <> ''
union all select ty, qu, nm, pa, 'p', 'clusterIndex', 'no',
'ifIndexes'
from t
where (select max(clustering) from sysibm.sysIndexes i
where i.tbCreator = t.qu and i.tbName = t.nm)
not in ('Y', case when 1=1 then 'x' else null end)
union all select ty, qu, nm, pa, 'p', 'partitioning',
'indexBased', 'tableBased'
from t join sysibm.sysTablespace ts
on t.dbName = ts.dbName and t.tsName = ts.name
and ts.partitions > 0 and ts.type <> 'G'
and partKeyColNum < 1
union all select ty, qu, nm, pa, 'a', 'primKeyConst',
'no', 'yes'
from t
where keyColumns = 0 and gpOpt <> 'x'
)
, eC ($colE8) as
(
select ty, qu, nm, pa, 'p', 'colType', colType,
'CSallowed'
from c
where coltype not in('INTEGER', 'SMALLINT', 'FLOAT', 'CHAR'
, 'VARCHAR', 'DECIMAL', 'DATE', 'TIME', 'TIMESTMP'
, 'ROWID', 'BIGINT', 'BINARY')
union all select ty, qu, nm, pa, 'p', 'hidden', hidden, 'N'
from c
where hidden <> 'N'
)
, eI (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'n', 'creator', qu, tbCreator
from i
where creator <> tbCreator
union all select ty, qu, nm, pa, 'n', 'name', nm, sIx || '%'
from (select i.*
, case when gpOpt='x' then 'I' || substr(tbName, 3, 6)
else 'I' || strip(substr(tbName, 2, 6)) end sIx
from i) i
where left(name, length(sIx)) <> sIx and qu <> 'VDPS2'
union all select ty, qu, nm, pa, 'p', 'extensionType',
case when ix_extension_type = 'S' then 'index on expression'
else ix_extension_type end, 'no'
end
from i
where ix_extension_type <> ''
union all select ty, qu, nm, pa, 'o', 'bufferpool', bPool, 'BP1'
from i
where bPool <> 'BP1'
union all select ty, qu, nm, pa, 'o', 'close', closeRule, 'Y'
from i
where closeRule <> 'Y'
union all select ty, qu, nm, pa, 'o', 'compress', compress, 'N'
from i
where compress <> 'N'
union all select ty, qu, nm, pa, 'o', 'copy', copy, 'N'
from i
where copy <> 'N'
union all select ty, qu, nm, pa, 'o', 'erase', eraseRule, 'N'
from i
where eraseRule <> 'N'
union all select ty, qu, nm, pa, 'pq', 'padded', padded, 'N'
from i
where padded not in (' ', 'N')
union all select ty, qu, nm, pa, 'a', 'partitioned', 'no', 'yes'
from i join sysibm.sysTables t
on i.tbCreator = t.creator and i.tbName = t.name
join sysibm.sysTableSpace ts
on ts.dbName = t.dbName and t.tsName = ts.name
and ts.partitions > 0 and ts.type <> 'G'
join sysibm.sysIndexPart ip
on ip.ixCreator = i.creator and ip.ixName = i.name
and ip.partition = 0
union all select ty, qu, nm, pa, 'pq',
'piecesize', char(piecesize), '2GB,4GB'
from i
where piecesize not in(2097152, 4194304)
and indexType not in ('D', 'P')
)
, eIP ($colE8) as
(
select ty, qu, nm, pa, 'oq', 'storName'
, storName, 'GSMS?'
from ip
where ( gp <> 'XB' and storName <> 'GSMS')
or (gp = 'XB' and storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4'))
union all select ty, qu, nm, pa, 'o', 'priQty', char(pQty), '-1'
from ip
where pqty <> -1
union all select ty, qu, nm, pa, 'o', 'secQty', char(sQty), '-1'
from ip
where sqty <> -1
union all select ty, qu, nm, pa
, case when freePage >= 10 then 'o' else 'oq' end
, 'freePage', char(freePage), '0'
from ip
where freepage <> 0
union all select ty, qu, nm, pa
, case when pctFree <= 15 then 'o' else 'oq' end
, 'pctFree', char(pctFree), '10'
from ip
where pctFree <> 10
union all select ty, qu, nm, pa, 'o', 'gbpCache', gbpCache, 'changed'
from ip
where gbpCache <> ' '
union all select ty, qu, nm, pa, 'p', 'partitioned', 'no', 'yes'
from ip
where gpOpt = 'x' and partition = 0
)
, eV (ty, qu, nm, pa, cat, att, val, std) as
(
select ty, qu, nm, pa, 'n', 'creator', qu,
'tbCr | ' || gpCr
from v, nmCn
where not( qu = nmCn.cr
or ( nmCn.cr = left(qu, length(nmCn.cr))
and nmCn.cr99 = translate(substr(qu
, 1+length(nmCn.cr)), '999999999', '012345678')) )
union all select ty, qu, nm, pa, 'n', 'name',nm, 'V' ||strip(gp)||'%'
from v
where left(nm, 1) <> 'V'
or (gp <> '' and substr(nm, 2, 2) <> gp)
)
, eU ($colE8) as
(
select * from eTs
union all select * from eTp
group by $cols8
union all select * from eT
union all select * from eC
union all select * from eI
union all select * from eIp
group by $cols8
union all select * from eV
)
$/cte/
$@proc $@=/txt/
select ty, qu, nm, cat
, case when cat in ('s', 'sb') and val = '' then ' '
when cat in ('s', 'sb') then ' rows=' || val
|| ', space=' || std || 'B'
when length(val) = 0 then att || '='''', not std=' || std
when val = '' then att || '='' '', not std='|| std
else att || '=' || strip(val) || ', not std='|| std
end err
, pa
$/txt/
$@proc $@=/outTxt/
, outTxt as
(
$@txt
from uO
)
$/outTxt/
$proc $@=/db2gp/
$arg dbNa
, case -- when gp <> '' then gp
when $dbNa like 'DXB%' then 'XB'
when $dbNa like 'XB%' then 'XB'
else left($dbNa, 2)
end gp
, case when -- gp in ('XB', 'DXB') or
$dbNa like 'DXB%'
or $dbNa like 'XB%' then 'BUA'
when right(strip($dbNa), 2) in ('1A' , '1T')
then 'OA' || right(strip($dbNa), 2)
else 'OA1P'
end gpCr
, case when $dbNa like 'XB%' then 'x' -- elar family db
else '' end gpOpt
$/db2gp/
$proc $@=/cteC8/ $** cte with 8 columns
$@cte
,
u ($cols8) as
(
select ty, qu, nm, cat, att, val, std, pa from eU
union all select ty, qu, nm, 's', '', '', '', pa from ts
union all select ty, qu, nm, 's', '', '', '', pa from t
union all select ty, qu, nm, 's', '', '', '', pa from i
union all select ty, qu, nm, 's', '', '', '', pa from v
)
$@cteUO
$/cteC8/
$proc $@=/cteUO/
, uO0 ($colO8) as
(
select ty, cat
, cast(strip(qu) as varchar(8))
, cast(strip(nm) as varchar(50))
, cast(att as varchar(12))
, cast(rTrim(val) as varchar(50))
, cast(strip(std) as varchar(20))
, cast(strip(pa) as varchar(1000))
from u
)
, uO ($colO8) as
(
select *
from uO0
group by $colO8
order by pa, att, cat
)
$/cteUO/
$@proc genJobs $@/genJobs/
$@for li $@[
$=rz =- word($li, 1)
$=jn =- A540769 || right($rz, 1)
if $rz \== sysvar(sysnode) then $@=[
//${rz}SUB EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD SUBSYS=(CSM,'SYSTEM=$rz,SYSOUT=(A,INTRDR)')
//SYSUT1 DD DATA,DLM='}}'
$]
$@=[
//${jn} JOB (CP00,KE50),'DB2 REO',
// CLASS=M,MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL
$]
$@do wx=2 to words($li) $@=/dbSy/
$=dbSy =- word($li, wx)
$=p3 =- substr($dbSy, 3, 1)
$=path =- if(wordPos($dbSy, 'DBAF DBTF')>0, 'OA1'$p3, 'OA1P')
//${dbSy}SQL EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSTSIN DD *
DSN SYSTEM($dbSy)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSIN DD *
$@%[genDdl $path$]
$/dbSy/
if $rz \== sysvar(sysnode) then
$$ }}
$]
$/genJobs/
$#out 20160617 13:15:02
sqlCode 0: set current path = 'OA1P'
sqlCode 0, 4 rows inserted: insert into OA1P.tQZ120DdlSel (ty, qu, nm) sel...
sqlCode 0, 1 rows inserted: insert into OA1P.tQZ120DdlSel (ty, qu, nm) val...
TY CAT VAL PA
QUALIFIERNAME ATT STD
TY QUALIFIERNAME C ATT VAL STD PA
t OA1P.TQZ040BINDPGM s db=DSNDB...
t OA1P.TQZ040BINDPGM o dataCapture changes db=DSNDB...
t OA1P.TQZ040BINDPGM n name TQZ040BINDPGM TDSYSTS% db=DSNDB...
t OA1P.TQZ040BINDPGM a primKeyConst no yes db=DSNDB...
t OA1P.TQZ040BINDPGM o restrictDrop Y db=DSNDB...
v OA1P.VQZ040BINDOV s db=DSNDB...
v OA1P.VQZ040BIND s db=DSNDB...
v OA1P.VQZ040REBIND s db=DSNDB...
v OA1P.VQZ040BINDPGM s db=DSNDB...
v OA1P.VQZ040BINDRDL s db=DSNDB...
v OA1P.VQZ040BINDCQ s db=DSNDB...
v OA1P.VQZ040BINDRZ s db=DSNDB...
v OA1P.VQZ040BINDDBP s db=DSNDB...
t OA1P.TQZ120DDLSEL s db=DSNDB...
t OA1P.TQZ120DDLSEL o dataCapture changes db=DSNDB...
t OA1P.TQZ120DDLSEL n name TQZ120DDLSEL TDSYSTS% db=DSNDB...
t OA1P.TQZ120DDLSEL a primKeyConst no yes db=DSNDB...
t OA1P.TQZ120DDLSEL o restrictDrop Y db=DSNDB...
v OA1P.VQZ120DDLCHEC8 s db=DSNDB...
v OA1P.VQZ120DDLCHECK s db=DSNDB...
v OA1P.VQZ120DDLCHESUM s db=DSNDB...
v OA1P.VQZ120DDLCHESU8 s db=DSNDB...
v OA1P.VQZ120DDLCHES8 s db=DSNDB...
v OA1P.VQZ120DDLCHESZ s db=DSNDB...
ts QZ01A1P.A006A s db=QZ01A...
ts QZ01A1P.A006A p encoding U EBCDIC db=QZ01A...
t OA1P.TQZ006GBGRTSSTATS s db=QZ01A...
t OA1P.TQZ006GBGRTSSTATS n name TQZ006GBGRTSSTATS TQZ006A% db=QZ01A...
t OA1P.TQZ006GBGRTSSTATS o restrictDrop Y db=QZ01A...
i OA1P.IQZ006A1 s db=QZ01A...
i OA1P.IQZ006A1 n name IQZ006A1 IQZ006G% db=QZ01A...
i OA1P.IQZ006A2 s db=QZ01A...
i OA1P.IQZ006A2 n name IQZ006A2 IQZ006G% db=QZ01A...
v OA1P.VQZ006GBGRENZE s db=QZ01A...
ts QZ01A1P.A050A s db=QZ01A...
ts QZ01A1P.A050A p encoding U EBCDIC db=QZ01A...
tp QZ01A1P.A050A o pctFree 10 5 db=QZ01A...
ts QZ01A1P.A050A p type classic UTS db=QZ01A...
t OA1P.TQZ050CMD s db=QZ01A...
t OA1P.TQZ050CMD p append Y N db=QZ01A...
t OA1P.TQZ050CMD p clusterIndex no ifIndexes db=QZ01A...
t OA1P.TQZ050CMD n name TQZ050CMD TQZ050A% db=QZ01A...
t OA1P.TQZ050CMD a primKeyConst no yes db=QZ01A...
t OA1P.TQZ050CMD o restrictDrop Y db=QZ01A...
i OA1P.IQZ050A1 s db=QZ01A...
i OA1P.IQZ050A1 n name IQZ050A1 IQZ050C% db=QZ01A...
i OA1P.IQZ050A2 s db=QZ01A...
i OA1P.IQZ050A2 n name IQZ050A2 IQZ050C% db=QZ01A...
48 rows fetched: with std (std, mbrClu, app, pFree) as ( select ' ', ' ', ...
$#out 20160617 13:14:31
$#out 20160617 13:13:32
$#out 20160617 10:42:26
$#out 20160617 10:38:13
$#out 20160617 10:37:05
$#out 20160616 21:34:06
$#out 20160616 08:37:26
$#out 20160616 08:37:07
$#out 20160612 12:24:35