zOs/SQL/TEMPOBJ
------------------------------------------------------------------------00010037
-- alle temporären Objekte aus dem Db2 Catalog anzeigen W.Keller00020037
-- d.h. Objekte mit einem '$' irgendwo im Namen 23.06.0900030037
-- durchsuchte ObjektTypen: db, ts, tb, vw, al, ix 00040037
------------------------------------------------------------------------00050037
with v as 00060028
( 00070028
select '$' v from sysibm.sysdummy1 00080028
) 00090028
, a (type, name, dbTs, created) as 00100035
( 00110028
select 'db', name, name, createdTS 00120032
from sysibm.sysdatabase, v 00130013
where locate(v, name) > 0 00140013
union select 'ts', strip(dbName) || '.' || strip(name), 00150013
strip(dbname) || '.' || strip(name), createdTS 00160033
from sysibm.systableSpace, v 00170013
where locate(v, name) > 0 00180014
union select case when type = 'T' then 'tb' 00190028
when type = 'A' then 'al' 00200028
when type = 'V' then 'vw' 00210028
else 't' || type end, 00220028
strip(creator) || '.' || strip(name), 00230028
strip(dbname) || '.' || strip(tsname), createdTS 00240034
from sysibm.systables, v 00250013
where (locate(v, name) > 0 or locate(v, creator) > 0) 00260023
union select 'ix', strip(i.creator) || '.' || strip(i.name), 00270021
strip(t.dbname) || '.' || strip(t.tsname), 00280031
i.createdTS 00290032
from sysibm.sysIndexes i, sysibm.systables t, v 00300021
where i.tbCreator = t.creator and i.tbName = t.name and 00310021
(locate(v, i.name) > 0 or locate(v, i.creator) > 0) 00320021
) 00330010
select type "type", char(left(name, 35), 35) "name", 00340037
char(left(dbTs, 17), 17) "db.ts", created "created" 00350037
from a 00360036
order by 3 00370030
with ur 00380038