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".

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.

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