Unanswered: Talking with Sysibm Tables on DB2 z/OS
Dear DB2 colleagues,
I want to get an overview of all tables existing to specific schemas (schema1, schema2, schema3
and where a group that i specifiy does have no select authorization.
Can you help me on extending my query?
I want to get those informations from sysibm tables.
thank you for your support in advance.
this is what i prepared:
SUBSTR(Y.CREATOR,1,10) as CREATOR,
SUBSTR(Y.NAME,1,8) as NAME,
SUBSTR(Y.DBNAME,1,10) AS DBNAME,
SUBSTR(Y.OWNER,1,10) AS OWNER
SUBSTR(X.GRANTEE,1,10) AS GRANTEE
FROM SYSIBM.SYSTABLES Y, SYSIBM.SYSTABAUTH X
where (Y.OWNER = 'SCHEMA1'
OR Y.OWNER = 'SCHEMA2'
OR Y.OWNER = SCHEMA3')
AND X.grantee = 'USER1'
and Y.TYPE ='T'
ORDER BY NAME ASC;
By the way I found out that the authorization information is listed in sysibm.systabauth.
SUBSTR(TAB.CREATOR,1,10) as CREATOR,
SUBSTR(TAB.NAME,1,30) as NAME,
SUBSTR(AUTH.GRANTEE,1,10) AS GRANTEE,
SYSIBM.SYSTABLES TAB, SYSIBM.SYSTABAUTH AUTH
-- SYSIBM IS FOR TESTING
TAB.CREATOR IN ('FISPROD','HLCPROD','DB2PROD','SYSIBM')
-- FOR YOU 'INFAPRDA'
AUTH.GRANTEE = 'PUBLIC'
-- FOR YOU 'N'
ORDER BY NAME ASC
-- FOR TESTING
FETCH FIRST 10 ROWS ONLY
FOR READ ONLY WITH UR