show all tables in DB2 using the LIST command

Question

This is embarrassing, but I can't seem to find a way to list the names of the tables in our DB2 database. Here is what I tried:

root@VO11555:~# su - db2inst1
root@VO11555:~# . ~db2inst1/sqllib/db2profile
root@VO11555:~# LIST ACTIVE DATABASES

We receive this error: SQL1092N "ROOT" does not have the authority to perform the requested command or operation.

The DB2 version number follows.

root@VO11555:~# db2level
DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack
"1".
Product is installed at "/opt/db2V9.7".

Kittle

shouldn't it be "db2inst1@VO11555" starting at line two? – Striction

Couldn't you also do this from the green screen with wrkobj command? Not a programming answer but it will show you – Eyesore

Answer 1

To get a list of tables for the current database in DB2 -->

Connect to the database:

db2 connect to DATABASENAME user USER using PASSWORD

Run this query:

db2 LIST TABLES

This is the equivalent of SHOW TABLES in MySQL.

You may need to execute 'set schema myschema' to the correct schema before you run the list tables command. By default upon login your schema is the same as your username - which often won't contain any tables. You can use 'values current schema' to check what schema you're currently set to.

Ate

Thanks for the command. I am new to using the IBM DB2 Express-C environment that I've built on my Turnkey LINUX VM. – Marci

You should also try running db2 LIST TABLES FOR ALL in case some of the tables are in different schemas. – Happ

Answer 2

Connect to the database:

db2 connect to <database-name>

List all tables:

db2 list tables for all

To list all tables in selected schema, use:

db2 list tables for schema <schema-name>

To describe a table, type:

db2 describe table <table-schema.table-name>

credit http://onewebsql.com/blog/list-all-tables

Upmost

Answer 3

select * from syscat.tables where type = 'T'

you may want to restrict the query to your tabschema

Striction

Answer 4

I'm using db2 7.1 and SQuirrel. This is the only query that worked for me.

select * from SYSIBM.tables where table_schema = 'my_schema' and table_type = 'BASE TABLE';

Lida

Answer 5

Run this command line on your preferred shell session:

db2 "select tabname from syscat.tables where owner = 'DB2INST1'"

Maybe you'd like to modify the owner name, and need to check the list of current owners?

db2 "select distinct owner from syscat.tables"

Tresa

Answer 6

have you installed a user db2inst2, i think, i remember, that db2inst1 is very administrative

Striction

Answer 7

Run this command line on your preferred shell session: $ db2 connect to $ db2 LIST TABLES

Kiele

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Semasiology