On 2004-12-14,
th**********@gm ail.com scribbled:
I want to list all the user-tables of a DB2 database. I use the
ADO-openSchema method (adSchemaTables ), but I can't filter the
systables out.
Has someone any idea how to select only the user tables? (on a generic
way)
Hi Thijs,
All the system tables, views, etc. exist within the SYSCAT, SYSIBM,
SYSFUN, and SYSSTAT schemas. Additionally, you are not permitted to
create any tables or views within these schemas:
"CREATE TABLE statement
....
table-name
Names the table. The name, including the implicit or explicit
qualifier, must not identify a table, view, nickname, or alias
described in the catalog. The schema name must not be SYSIBM, SYSCAT,
SYSFUN, or SYSSTAT (SQLSTATE 42939)..."
Ergo, if a table or view exists within any of these schemas, it is a
system table.
One final note of caution: I was once in a similar situation, having to
discern which tables were system tables in an application (to filter
them from the list given to end users), and decided to take a more
general approach.
On the basis of the following content from the DB2 SQL Reference:
"CREATE SCHEMA Statement
....
schema-name
Names the schema. The name must not identify a schema already described
in the catalog (SQLSTATE 42710). The name cannot begin with 'SYS'
(SQLSTATE 42939)..."
I decided that, since you cannot even create schemas that begin with
"SYS", it would be enough to check whether the schema for a given table
began with "SYS". If it did, it was a system table. However, this
turned out to be wrong.
Although one cannot _explicitly_ create a schema that begins with SYS,
one can do so _implicitly_. For example, this command will fail:
CREATE SCHEMA SYSTEST;
However, this command succeeds:
CREATE TABLE SYSTEST.TEST (A INTEGER NOT NULL);
HTH,
Dave.
--
Cogito cogito ergo cogito sum
-- Ambrose Bierce
