Svenson's  Bulletin Board Forum Index Svenson's Bulletin Board
Bulletin Board for boat building and model airplane building enthusiasts.
 
AlbumAlbum   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL Statements to list dependencies of objects.

 
Post new topic   Reply to topic    Svenson's Bulletin Board Forum Index -> DB2
View previous topic :: View next topic  
Author Message
craig
Site Admin


Joined: 19 Feb 2006
Posts: 40

PostPosted: Tue Feb 28, 2006 6:39 am    Post subject: SQL Statements to list dependencies of objects. Reply with quote

SELECT rtrim(t.tabschema), rtrim(t.tabname), t.status, t.type,
rtrim(t.tbspace),
rtrim(t.index_tbspace), rtrim(t.long_tbspace), rtrim(t.datacapture),
rtrim(t.partition_mode), rtrim(t.log_attribute), t.pctfree,
t.append_mode,
rtrim(t.locksize), t.volatile, t.npages, rtrim(t.const_checked),
rtrim(t.remarks), rtrim(c.colname), c.colno, rtrim(c.typeschema),
rtrim(c.typename), c.length, c.scale, rtrim(c.default), c.nulls,
c.codepage, c.logged, c.compact, rtrim(p.dl_features),
rtrim(c.special_props), c.keyseq, c.partkeyseq,
rtrim(c.target_typeschema),
rtrim(c.target_typename), rtrim(c.scope_tabschema),
rtrim(c.scope_tabname),
rtrim(c.source_tabschema), rtrim(c.source_tabname),
rtrim(c.remarks),
c.inline_length, c.generated, c.text, s.seqname, s.start,
s.increment,
s.cache, s.minvalue, s.maxvalue, s.cycle, s.order, t.droprule,
t.compression, c.compress, rtrim(t.property) FROM syscat.tables t
JOIN syscat.columns c ON t.tabname = c.tabname and t.tabschema =
c.tabschema
LEFT OUTER JOIN sysibm.syscolproperties p
ON c.tabname = p.tabname and c.tabschema = p.tabschema and c.colname
= p.colname
LEFT OUTER JOIN syscat.colidentattributes d
ON d.tabname = t.tabname AND d.tabschema = t.tabschema
LEFT OUTER JOIN syscat.sequences s
ON s.seqid = d.seqid WHERE ( ( t.tabname = 'DATABASES' and
t.tabschema = 'DBLOGGR' ))
ORDER BY c.tabschema, c.tabname, c.colno FOR FETCH ONLY

SELECT rtrim(t.tabschema), rtrim(t.tabname), t.type, rtrim(t.constname),
rtrim(t.remarks), rtrim(k.colname), k.colseq
FROM syscat.tabconst t, syscat.keycoluse k
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
and (t.type = 'P' OR t.type = 'U')
AND t.constname = k.constname
AND t.tabschema = k.tabschema
AND t.tabname = k.tabname
ORDER BY t.tabschema, t.tabname, t.type, t.constname, k.colseq FOR
FETCH ONLY

SELECT rtrim(t.tabschema), rtrim(t.tabname), t.type, rtrim(t.constname),
rtrim(t.remarks), rtrim(r.reftabschema), rtrim(r.reftabname),
rtrim(r.refkeyname), r.deleterule, r.updaterule, rtrim(fk.colname),
fk.colseq, rtrim(pk.colname), pk.colseq, t.enforced,
t.enablequeryopt
FROM syscat.tabconst t, syscat.references r, syscat.keycoluse fk,
syscat.keycoluse pk
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
and t.type = 'F' AND t.constname = r.constname
AND r.constname = fk.constname AND r.refkeyname = pk.constname
AND t.tabschema = r.tabschema AND t.tabname = r.tabname
AND r.tabschema = fk.tabschema AND r.tabname = fk.tabname
AND r.reftabschema = pk.tabschema AND r.reftabname = pk.tabname
AND fk.colseq = pk.colseq
ORDER BY t.tabschema, t.tabname, fk.constname, fk.colseq FOR FETCH
ONLY

SELECT rtrim(t.tabschema), rtrim(t.tabname), t.type, rtrim(t.constname),
rtrim(t.remarks), c.text, rtrim(c.qualifier), rtrim(c.func_path),
t.enforced, t.enablequeryopt
FROM syscat.tabconst t, syscat.checks c
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
and t.constname = c.constname AND t.tabschema = c.tabschema
AND t.tabname = c.tabname AND c.type = 'C'
ORDER BY t.tabschema, t.tabname, t.constname FOR FETCH ONLY

SELECT rtrim(t.tabschema), rtrim(t.tabname), rtrim(t.colname), t.dimension
FROM syscat.coluse t WHERE ( ( t.tabname = 'DATABASES' and t.tabschema =
'DBLOGGR' ))
ORDER BY t.tabschema, t.tabname, t.dimension DESC ,t.colseq FOR
FETCH ONLY

SELECT sysibm.rtrim(u.grantor), sysibm.rtrim(u.grantee),
CASE granteetype WHEN 'U' then '1' WHEN 'G' then '2' end,
sysibm.rtrim(u.tabschema), sysibm.rtrim(u.tabname),
CASE (sysibm.max(CASE u.controlauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.selectauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.insertauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.updateauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.alterauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.deleteauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.indexauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1
else 0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end||
CASE (sysibm.max(CASE u.refauth WHEN 'G' THEN 3 WHEN 'Y' THEN 1 else
0 end))
WHEN 3 THEN '3' WHEN 1 THEN '1' else '0' end
FROM syscat.tabauth u, syscat.tables t
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
AND u.tabschema = t.tabschema AND u.tabname = t.tabname
AND t.type IN('T', 'S', 'U')
GROUP BY u.grantor, u.grantee, u.granteetype, u.tabschema, u.tabname

UNION
SELECT sysibm.rtrim(c.grantor), sysibm.rtrim(c.grantee),
CASE c.granteetype WHEN 'U' then '1' WHEN 'G' then '2' end,
sysibm.rtrim(c.tabschema), sysibm.rtrim(c.tabname),'00000000'
FROM syscat.tables t, syscat.colauth c
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
AND t.tabschema = c.tabschema AND t.tabname = c.tabname AND t.type
IN('T', 'S', 'U') AND c.tabschema||c.tabname
NOT IN (
SELECT y.grantee||y.granteetype FROM syscat.tabauth y,
syscat.tables t
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema =
'DBLOGGR' ))
AND y.tabschema = t.tabschema AND y.tabname = t.tabname AND
t.type
IN('T', 'S', 'U')
) ORDER BY 4, 5, 2, 3

SELECT sysibm.rtrim(grantor), sysibm.rtrim(grantee),
CASE granteetype WHEN 'U' then '1' WHEN 'G' then '2' end,
sysibm.rtrim(tabschema), sysibm.rtrim(tabname),
sysibm.rtrim(colname),
case privtype||grantable WHEN 'RG' then '03' WHEN 'RN' then '01'
WHEN 'UG'
then '30' WHEN 'UN' then '10' else '00' end
FROM syscat.colauth t
WHERE ( ( t.tabname = 'DATABASES' and t.tabschema = 'DBLOGGR' ))
ORDER BY tabschema, tabname, t.colname, t.grantee, t.granteetype FOR
FETCH ONLY

SELECT rtrim(tabschema), rtrim(tabname)
FROM syscat.tables
WHERE type = 'A' AND base_tabschema = 'DBLOGGR'
AND base_tabname = 'DATABASES' ORDER BY tabschema, tabname FOR FETCH
ONLY

SELECT rtrim(indschema), rtrim(indname), rtrim(indextype), user_defined,
system_required
FROM SYSCAT.INDEXES
WHERE tabschema = 'DBLOGGR' AND tabname = 'DATABASES'
ORDER BY indschema, indname FOR FETCH ONLY

SELECT distinct rtrim(d.pkgschema), rtrim(d.pkgname), hex(p.unique_id),
sysibm.rtrim(p.pkgversion) FROM syscat.packagedep d, syscat.packages p
WHERE d.bschema = 'DBLOGGR' AND d.bname = 'DATABASES' AND d.btype =
'T'
AND d.pkgschema = p.pkgschema AND d.pkgname = p.pkgname
AND p.unique_id = d.unique_id AND (d.pkgschema, d.pkgname)
NOT IN (
SELECT procschema, sysibm.substr(implementation, 1,
((sysibm.posstr(implementation,'!') - 1 )))
FROM syscat.procedures WHERE language = 'SQL'
) ORDER BY 1,2 FOR FETCH ONLY

SELECT rtrim(P.PROCSCHEMA), rtrim(P.PROCNAME), rtrim(P.SPECIFICNAME),
rtrim(P.LANGUAGE), rtrim(PP.TYPESCHEMA), rtrim(PP.TYPENAME), PP.LENGTH,
PP.SCALE, PP.CODEPAGE, rtrim(PP.AS_LOCATOR),
rtrim(PP.TARGET_TYPESCHEMA),
rtrim(PP.TARGET_TYPENAME), rtrim(PP.PARM_MODE), PP.ORDINAL
FROM SYSCAT.PROCEDURES P LEFT OUTER JOIN SYSCAT.PROCPARMS PP
ON P.PROCSCHEMA = PP.PROCSCHEMA AND P.SPECIFICNAME =
PP.SPECIFICNAME
WHERE (P.PROCSCHEMA,
SYSIBM.SUBSTR(P.IMPLEMENTATION,1,((SYSIBM.POSSTR(P.IMPLEMENTATION,'!') -
1))))
IN (
SELECT D.PKGSCHEMA, D.PKGNAME FROM SYSCAT.PACKAGEDEP D
WHERE D.BTYPE = 'T' AND D.BSCHEMA = 'DBLOGGR' AND D.BNAME =
'DATABASES'
) AND P.LANGUAGE = 'SQL' ORDER BY 1,2,3,14 FOR FETCH ONLY

SELECT distinct rtrim(r.tabschema), rtrim(r.tabname), t.type, ' '
FROM syscat.references r, syscat.tables t
WHERE r.reftabschema = 'DBLOGGR' AND r.reftabname = 'DATABASES'
AND r.tabschema = t.tabschema AND r.tabname = t.tabname
UNION ALL SELECT distinct rtrim(d.tabschema), rtrim(d.tabname),
d.dtype, t.property FROM syscat.tabdep d, syscat.tables t
WHERE (d.dtype = 'S' OR d.dtype = 'T') AND (d.btype = 'T' OR d.btype
= 'S')
AND d.bschema = 'DBLOGGR' AND d.bname = 'DATABASES'
AND d.tabschema = t.tabschema
AND d.tabname = t.tabname ORDER BY 1, 2 FOR FETCH ONLY

SELECT rtrim(trigschema), rtrim(trigname) FROM syscat.triggers
WHERE tabschema = 'DBLOGGR' AND tabname = 'DATABASES'
UNION SELECT rtrim(trigschema), rtrim(trigname)
FROM syscat.trigdep WHERE btype = 'T' AND bschema = 'DBLOGGR'
AND bname = 'DATABASES' ORDER BY 1, 2 FOR FETCH ONLY

SELECT rtrim(viewschema), rtrim(viewname), dtype
FROM syscat.viewdep WHERE (dtype = 'V' OR dtype = 'W') AND btype = 'T'
AND bschema = 'DBLOGGR' AND bname = 'DATABASES' ORDER BY 1, 2 FOR
FETCH ONLY

SELECT rtrim(i.indschema), rtrim(i.indname), i.uniquerule, i.colcount,
rtrim(i.tabschema), rtrim(i.tabname), rtrim(i.indextype), i.pctfree,
i.minpctused, i.reverse_scans, rtrim(c.colname), c.colseq,
c.colorder,
rtrim(i.remarks), i.system_required, i.user_defined, t.type ,
rtrim(x.ieschema), rtrim(x.iename), x.iearguments FROM
syscat.indexes i,
syscat.indexcoluse c, syscat.tables t ,sysibm.sysindexes x
WHERE ( ( i.indschema = 'DBLOGGR' AND i.indname = 'DATABASES_U1' ))
AND i.indschema = c.indschema AND i.indname = c.indname
AND i.tabschema = t.tabschema AND i.tabname = t.tabname
AND i.indschema = x.creator AND i.indname = x.name
ORDER BY i.indschema, i.indname, c.colseq FOR FETCH ONLY

SELECT sysibm.rtrim(grantee), CASE granteetype WHEN 'U' then '1'
WHEN 'G' then '2' end, sysibm.rtrim(i.indschema),
sysibm.rtrim(i.indname), CASE (sysibm.max(CASE controlauth WHEN 'G'
THEN 3
WHEN 'Y' THEN 1 else 0 end)) WHEN 3 THEN '3'
WHEN 1 THEN '1' else '0' end
FROM syscat.indexauth i
WHERE ( ( i.indschema = 'DBLOGGR' AND i.indname = 'DATABASES_U1' ))
GROUP BY indschema, indname, grantee, granteetype
ORDER BY indschema, indname, grantee, granteetype FOR FETCH ONLY
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Svenson's Bulletin Board Forum Index -> DB2 All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group