Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: How to know All the Views and Aliases for a Specific Table

    I would like to know all the views and aliases that are created for a table 'JFBT02' with a single Query

    View is created with another name for the table as JFBV02 similary Alias as JFBA02. Can anyone help me please

    I tried the below Query

    SELECT * FROM SYSIBM.SYSTABLES WHERE NAME LIKE '%B02%'

    but the above query fetched tables and views like SABA02 not just for JFBT02
    I want to know only for a single table

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you can run db2look command. You can run a select on syscat.views where text like '%tabname%'.

    For aliases select from syscat.tables where base_tabname = 'Tabname'
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Nov 2007
    Posts
    6
    Try This query..SELECT * FROM SYSIBM.SYSTABLES WHERE TBNAME='your table name'.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by saidulu
    Try This query..SELECT * FROM SYSIBM.SYSTABLES WHERE TBNAME='your table name'.
    And he will get one record back ALL the time. And it will be for his table.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Neharao
    I would like to know all the views and aliases that are created for a table 'JFBT02' with a single Query
    Code:
    SELECT RTRIM(DCREATOR) || '.' || RTRIM(DNAME) || ' (' || DTYPE || ')' as referer
    FROM   SYSIBM.SYSVIEWDEP
    WHERE  BNAME = 'JFBT02' AND BCREATOR = USER
    (I'm assuming you are using DB2 v8 on z/OS.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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