Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    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:

    SELECT
    SUBSTR(Y.CREATOR,1,10) as CREATOR,
    SUBSTR(Y.NAME,1,8) as NAME,
    SUBSTR(Y.DBNAME,1,10) AS DBNAME,
    CREATEDTS,
    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.
    Last edited by DB_N00b; 09-02-16 at 07:49.

  2. #2
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    quick and dirty

    Hi DB_N00b,

    this might help for a start:

    SELECT
    SUBSTR(TAB.CREATOR,1,10) as CREATOR,
    SUBSTR(TAB.NAME,1,30) as NAME,
    SUBSTR(AUTH.GRANTEE,1,10) AS GRANTEE,
    AUTH.SELECTAUTH
    FROM
    SYSIBM.SYSTABLES TAB, SYSIBM.SYSTABAUTH AUTH
    WHERE
    TAB.NAME=AUTH.TTNAME
    AND
    TAB.CREATOR=AUTH.TCREATOR
    AND
    -- SYSIBM IS FOR TESTING
    TAB.CREATOR IN ('FISPROD','HLCPROD','DB2PROD','SYSIBM')
    AND
    -- FOR YOU 'INFAPRDA'
    AUTH.GRANTEE = 'PUBLIC'
    AND
    -- FOR YOU 'N'
    AUTH.SELECTAUTH='Y'
    ORDER BY NAME ASC
    -- FOR TESTING
    FETCH FIRST 10 ROWS ONLY
    FOR READ ONLY WITH UR
    ;

    Good luck
    db2dp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •