DB2 Query to retrieve all table names for a given schema

For Db2 for Linux, Unix and Windows (i.e. Db2 LUW) or for Db2 Warehouse use the SYSCAT.TABLES catalog view. E.g.

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE '%CUR%' AND TYPE = 'T'

Which is a SQL statement that will return all standard tables in all schema that contains the substring CUR. From a Db2 command line you could also use a CLP command e.g. db2 list tables for all | grep CUR to similar effect

This page describes the columns in SYSCAT.TABLES including the different values for the TYPE column.

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

Other commonly used catalog views incude

SYSCAT.COLUMNS      Lists the columns in each table, view and nickname
SYSCAT.VIEWS        Full SQL text for view and materialized query tables
SYSCAT.KEYCOLUSE    Column that are in PK, FK or Uniuqe constraints

In Db2 LUW it is considered bad practice to use the SYSIBM catalog tables (which the SYSCAT catalog views select thier data from). They are less consistent as far as column names go, are not quite as easy to use, are not documented and are more likely to change between versions.

This page has a list of all the catalog views Road map to the catalog views

For Db2 for z/OS, use SYSIBM.TABLES which is described here. E.g.

SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE OWNER LIKE '%CUR%' AND TYPE = 'T'

For Db2 for i (i.e. iSeries aka AS/400) use QSYS2.SYSTABLES which is described here

SELECT TABLE_OWNER, TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA LIKE '%CUR%' AND TABLE_TYPE = 'T'

For DB2 Server for VSE and VM use SYSTEM.SYSCATALOG which is described here DB2 Server for VSE and VM SQL Reference

SELECT CREATOR, TNAME FROM SYSTEM.SYSCATALOG WHERE TABLETYPE = 'R'

select name from sysibm.systables 
where name like '%ISP%' 
and type = 'T'

This is my working solution:

select tabname as table_name
from syscat.tables
where tabschema = 'schema_name' -- put schema name here
      and type = 'T'
order by tabname

SELECT
  name
FROM
  SYSIBM.SYSTABLES
WHERE
    type = 'T'
  AND
    creator = 'MySchema'
  AND
    name LIKE 'book_%';

IN db2warehouse I found that "owner" doesn't exist, so I describe table syscat.systables and try using CREATOR instead and it works.

db2 "select NAME from sysibm.systables where CREATOR = '[SCHEMANAME]'and type = 'T'"