CODEWITHSUNDEEP
db2ibm-midrange
David A Beamer
David A Beamer

Reputation: 87

DB2 syscolumns does not return all columns

When running a simple catalog query:

SELECT * FROM SYSCOLUMNS

I get back only a few hundred rows. This is DB2 7.1 on an AS400. There are dozens of schemas, more than a thousand tables in total. I expect this query to return several thousand rows, not a few hundred. The rows I do get back are only from my test schema and that of one other developer.

I am new to DB2 (20+ years in Oracle), so this is puzzling. The IBM doc says that SELECT is granted to PUBLIC on SYSCOLUMNS, so I should get everything, right? I don't know what the token "SYSCOLUMNS" is pointing to, a view, or local table (which would explain things). In my environment, every query to the database requires SCHEMA_NAME.TABLE_NAME, so this SYSCOLUMNS (with no schema name) is already an exception.

thanks in advance,

db

Upvotes: 2

Views: 2562

Answers (1)

dmc
dmc

Reputation: 2684

The answer will depend on what naming mode you're using.

  • *SQL mode: You qualify tables as SCHEMA.TABLE
  • *SYS mode: You qualify tables as SCHEMA/TABLE

(Behind the scenes, the schema maps to a library and the table maps to a file.)

In *SQL mode, if you don't specify a schema, the value of CURRENT SCHEMA is used. By default your CURRENT SCHEMA is your user profile, but you can change it like so:

SET CURRENT SCHEMA = SOMELIB

In *SYS mode, if you don't specify a schema, the library list is used to resolve the table name to a particular file.

Anyway, I'm going to guess you're in *SQL naming mode and your current schema is set to your test schema. Querying the un-qualified SYSCOLUMNS will give you the columns in your current schema.

I would try the following, which should widen the scope to all columns on the system:

SELECT * FROM QSYS2.SYSCOLUMNS

Upvotes: 5

Related Questions