473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

filter systables from ado recordset adSchemaTables (repost)

Hello,
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)
Regards,
Thijs

Nov 12 '05 #1
1 3733
On 2004-12-14, th**********@gmail.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
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: thijs.kupers | last post by:
Hello, 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...
0
by: thijs.kupers | last post by:
Hello, 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...
0
by: thijs.kupers | last post by:
Hello, 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...
3
by: Gorilla | last post by:
I bound my package with EXPLAIN(YES), and it's got the following static SQL in it: EXEC SQL SELECT CARDF, RECLENGTH INTO :CARDF,:RECLENGTH FROM SYSIBM.SYSTABLES WHERE NAME = :TBNAME AND...
8
by: dick | last post by:
I am just trying to print/report the results of a "filter by selection" which is done by right-clicking a form, filling in values, and "applying the filter." I have searched the newsgroups, and...
2
by: Patrick Gonzalez | last post by:
Is it possible to use the built-in filter functions (ie. "Filter by Selection", etc..) when a form's recordsource has been set to a recordset object during the load event. When I try to do this, I...
2
by: Thall | last post by:
Hey Gurus - I've seen a few solutions to this problem, but none of which I can do without a little help. Here's the situation The following code loops thru a sales report, using the sales rep ID...
11
by: jason.teen | last post by:
Hi, I dont seem to be able to get this Filter right on my RecordSet for some reason. I have this code: Dim rs As Recordset Set rs = CurrentDb().OpenRecordset("SELECT DISTINCT Product,
0
by: Troels Arvin | last post by:
Hello, I have a user who regularly runs a query which blocks a lot of other queries. The query is initiated from SSIS (SQL Server Integration Services) on another host. The DB2 instance is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.