Monday, July 01, 2013

List the tables in DB2 LUW & z/OS



List the tables in DB2 LUW


list tables for all
list tables for schema <schema_name>

or

select * from syscat.tables ;

select * from syscat.tables
where TABSCHEMA = '<schema_name>'
and TYPE= '<table_type>' ;

You can add more conditions as you want.

Type of object.

    A = Alias
    G = Created temporary table
    H = Hierarchy table
    L = Detached table
    N = Nickname
    S = Materialized query table
    T = Table (untyped)
    U = Typed table
    V = View (untyped)
    W = Typed view


List the tables in z/OS (Mainframe)


In z/OS (mainframe) short command will not work, you have to query on system table.

select * from sysibm.systables
where owner = '<schema_name>' and
type = '<table_type>';