Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Unanswered: display tables in a tablespace in DB2 8.1

    I need to display all tables in a tablespace. I can get the tablespace for a single table if I use syscat.tables and specify the table name in the WHERE clause. I tried to reverse the query and display all the tables for a tablespace using the same syscat.tables, but the output comes out empty. Is there a way to display all the tables for a tablespace in DB2?

    SELECT *
    FROM syscat.tables
    WHERE tabname like table1
    This works. I get all the info for table1 including the name of tablespace1 which is related to it.

    SELECT tabname
    FROM syscat.tables
    WHERE tblspaces like tablespace1
    This returns only column headings

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you will get the same info from syscat.tables when querying by the tablespace name for all tables in that tablespace as you do when you query on just a table name.
    Dave

  3. #3
    Join Date
    Dec 2014
    Posts
    3
    Yes, but the problem was that I needed to use upper case when typing the tablespace name. SQL sometimes is case sensitive. Also, the LIKE operator works better when using wildcards. Once I made those changes I got all of the table names for a specific tablespace.

  4. #4
    Join Date
    Dec 2014
    Posts
    3
    Yes, but the problem was that I needed to use upper case when typing the tablespace name. SQL sometimes is case sensitive. Also, the LIKE operator works better when using wildcards. Once I made those changes I got all of the table names for a specific tablespace.

  5. #5
    Join Date
    Dec 2014
    Posts
    8
    You may join tablespaces view with table view.

    --First you get id of tablespace with a simple select:

    select * from syscat.tablespaces;


    --Lets say you want tablespace that has id=4

    select S.tbspace, T.tbspaceid, T.tabschema, T.tabname from syscat.tables T join syscat.tablespaces S on T.tbspaceid=S.tbspaceid where T.tbspaceid=4;

  6. #6
    Join Date
    Sep 2012
    Posts
    177
    Hi,

    If you need to select the tables created under the tablespace 'XXXXXXX' execute the below query.

    select TABSCHEMA, TABNAME, TBSPACEID, TBSPACE from syscat.tables where 'XXXXXXX'

    Thanks,
    laxman.

  7. #7
    Join Date
    Sep 2012
    Posts
    177
    select TABSCHEMA, TABNAME, TBSPACEID, TBSPACE from syscat.tables where TBSPACE='XXXXXXX'

  8. #8
    Join Date
    Dec 2014
    Posts
    8
    Quote Originally Posted by laxman babu View Post
    select TABSCHEMA, TABNAME, TBSPACEID, TBSPACE from syscat.tables where TBSPACE='XXXXXXX'
    laxman's answer is better. I missed the tbspace column in syscat.tables and went from longer way. No join needed.

Tags for this Thread

Posting Permissions

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