how to query db2 database
To query for a result containing all tables in the system, we need to use the SYSIBM.SYSTABLES table, which is a catalog containing a record for every existing view, alias, or table.
Note: Since SYSIBM.SYSTABLES contains a row for each of the three object types (view, alias, table), from now on throughout this little tutorial we’ll refer to each row as a potential object, rather than specifying all three object types that are possible.
Like all normal tables in DB2, SYSIBM.SYSTABLES has a number of columns that can be queried to retrieve all kinds of useful data:
- name, of course, contains the name of the object.
- creator is the schema of the object.
- type is the type of object and is represented by a single character. A value of T indicates the object is a table, V for view, A for alias, and so on.
- dbname is the name of the database that contains the object, if applicable.
Querying the SYSIBM.SYSTABLES Table
Now that we know where we need to look, we can actually execute some code and get our table list.
At the most basic level, we can simply perform a query to lookup all tables by ensuring the type column is equal to T:
SELECT * FROM SYSIBM.SYSTABLES WHERE type = 'T';
Depending on the size of your database, this could return far too many records that you may not be interested in.
If desired, we can narrow down our search to a particular schema (or owner) by filtering against the creator column:
SELECT * FROM SYSIBM.SYSTABLES WHERE type = 'T' AND creator = 'MySchema';
Note: Depending on your version of IBM DB2, you may need to compare the appropriate schema against the owner column rather than the creator column. If the above doesn’t behave as expected, try owner instead.
To filter results down even further, you may also wish to look for tables with a wildcard search of a few characters in the table name. This can be particularly useful for a collection of tables that represent a particular system and use a namespace prefix of some kind.