Topic
2 replies Latest Post - ‏2012-11-09T14:44:55Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic Not able to see tables in database administration perspective

‏2012-11-08T19:53:28Z |
Can anyone tell me if there are any grants necessary to allow a user to see a list of tables in the database administration perspective? One of our people connects to a subsystem and brings up the database administration perspective. He is able to click on "Databases" and see a list of all the database names. He is able to click on "Table Spaces" and see a list of all the table space names. But when he clicks on "Tables", he doesn't see a list of table names. I have SYSADM and can see the list of table names when using data studio on that same pc. We're using Data Studio 3.1, and are connecting to a version 10 subsystem on z/os.
Updated on 2012-11-09T14:44:55Z at 2012-11-09T14:44:55Z by SystemAdmin
  • loicjulien
    loicjulien
    96 Posts
    ACCEPTED ANSWER

    Re: Not able to see tables in database administration perspective

    ‏2012-11-08T22:42:46Z  in response to SystemAdmin
    Hi Mike,

    Here is some help:

    1/ You will need SELECT on at least SYSIBM.SYSTABLES

    2/ For reference, you can check if the user can run the following query with our SQL Editor.
    SELECT CREATOR, NAME, TYPE, REMARKS, LABEL, EDPROC, VALPROC, AUDITING,
    DATACAPTURE, ENCODING_SCHEME, PARTKEYCOLNUM, SPLIT_ROWS, STATUS,
    CREATEDBY, CLUSTERTYPE, APPEND, VERSIONING_SCHEMA, VERSIONING_TABLE,
    TSNAME, DBNAME, STATSTIME, CARDF, CONTROL, NUM_DEP_MQTS, PARENTS,
    CHILDREN
    FROM SYSIBM.SYSTABLES
    WHERE TYPE IN ('T', 'C', 'X', 'H', 'G', 'V', 'M', 'A')
    ORDER BY CREATOR, NAME
    FOR READ ONLY;

    3/ I will attach at the end the several queries run since there are several queries run concurrently from the initial connect to the Tables folder
    selection **
    -- BTW, you can enable the JDBC trace on Connection to get the statements executed.

    4/ If this is not related to privileges, make sure that on connection, the user did not select the checkbox "Retrieve objects created by this user only" (Attaching a screenshot)

    5/ Also, the Error Log view may provide you with additional information. If it doesn't you can attach it to the forum so that I can take a look.

    Hope this helps.

    ~Loic

    **
    SELECT FOREIGNKEY
    FROM SYSIBM.SYSCOLUMNS
    WHERE NAME = 'TEXT' AND TBNAME = 'SYSVIEWS' AND TBCREATOR = 'SYSIBM';

    SELECT FOREIGNKEY
    FROM SYSIBM.SYSCOLUMNS
    WHERE NAME = 'TEXT' AND TBNAME = 'SYSTRIGGERS' AND TBCREATOR = 'SYSIBM';

    SELECT FOREIGNKEY
    FROM SYSIBM.SYSCOLUMNS
    WHERE NAME = 'CREATESTMT' AND TBNAME = 'SYSROUTINES_SRC' AND TBCREATOR = 'SYSIBM';

    SELECT FOREIGNKEY
    FROM SYSIBM.SYSCOLUMNS
    WHERE NAME = 'STMT' AND TBNAME = 'SYSPACKSTMT' AND TBCREATOR = 'SYSIBM';

    WITH
    SCHEMAS_CTE (CREATOR) AS
    (SELECT DISTINCT CREATOR
    FROM SYSIBM.SYSTABLES
    UNION
    SELECT DISTINCT CREATOR
    FROM SYSIBM.SYSINDEXES
    UNION
    SELECT DISTINCT SCHEMA AS CREATOR
    FROM SYSIBM.SYSROUTINES
    UNION
    SELECT DISTINCT CREATOR
    FROM SYSIBM.SYSSYNONYMS
    UNION
    SELECT DISTINCT JARSCHEMA AS CREATOR
    FROM SYSIBM.SYSJAROBJECTS
    UNION
    SELECT DISTINCT OWNER AS CREATOR
    FROM SYSIBM.SYSPACKAGE
    UNION
    SELECT DISTINCT SCHEMA AS CREATOR
    FROM SYSIBM.SYSSEQUENCES)
    SELECT *
    FROM SCHEMAS_CTE
    ORDER BY CREATOR
    FOR READ ONLY;

    SELECT CREATOR, NAME, TYPE, REMARKS, LABEL, EDPROC, VALPROC, AUDITING,
    DATACAPTURE, ENCODING_SCHEME, PARTKEYCOLNUM, SPLIT_ROWS, STATUS,
    CREATEDBY, CLUSTERTYPE, APPEND, VERSIONING_SCHEMA, VERSIONING_TABLE,
    TSNAME, DBNAME, STATSTIME, CARDF, CONTROL, NUM_DEP_MQTS, PARENTS,
    CHILDREN
    FROM SYSIBM.SYSTABLES
    WHERE TYPE IN ('T', 'C', 'X', 'H', 'G', 'V', 'M', 'A')
    ORDER BY CREATOR, NAME
    FOR READ ONLY;

    SELECT CREATOR, NAME, CREATEDBY
    FROM SYSIBM.SYSSYNONYMS
    ORDER BY CREATOR, NAME
    FOR READ ONLY;

    SELECT NAME, TYPE, BPOOL, ENCODING_SCHEME, BPOOL, INDEXBP, STGROUP,
    GROUP_MEMBER, CREATOR
    FROM SYSIBM.SYSDATABASE
    ORDER BY NAME
    FOR READ ONLY;

    SELECT NAME
    FROM SYSIBM.SYSSTOGROUP
    ORDER BY NAME
    FOR READ ONLY;

    WITH
    CONSTRAINTS_CTE (TBCREATOR, TBNAME, NAME, CHECKCONDITION, ENFORCED,
    TYPE, CREATOR, TBOWNER) AS
    (SELECT TBOWNER AS TBCREATOR, TBNAME, CHECKNAME AS NAME, CHECKCONDITION,
    '' AS ENFORCED, '' AS TYPE, TBOWNER AS CREATOR, TBOWNER
    FROM SYSIBM.SYSCHECKS
    UNION
    SELECT CREATOR AS TBCREATOR, TBNAME, RELNAME AS NAME, '' AS CHECKCONDITION,
    ENFORCED, '' AS TYPE, CREATOR, CREATOR AS TBOWNER
    FROM SYSIBM.SYSRELS
    UNION
    SELECT TBCREATOR, TBNAME, CONSTNAME AS NAME, '' AS CHECKCONDITION,
    '' AS ENFORCED, TYPE, TBCREATOR AS CREATOR, TBCREATOR AS TBOWNER
    FROM SYSIBM.SYSTABCONST)
    SELECT *
    FROM CONSTRAINTS_CTE
    ORDER BY TBCREATOR, TBNAME
    FOR READ ONLY;
  • SystemAdmin
    SystemAdmin
    1632 Posts
    ACCEPTED ANSWER

    Re: Not able to see tables in database administration perspective

    ‏2012-11-09T14:44:55Z  in response to SystemAdmin
    Thank you! Once I granted SELECT on SYSIBM.SYSSEQUENCES, everything worked fine.