DB2 syscolumns does not return all columns

Question

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

@Gilbert: on my system that returned no rows. Admittedly mine a V5R4 box... — dmc
Interesting. If I SELECT * FROM SYSIBM.COLUMNS I get the same number of rows as I do using QSYS2.SYSCOLUMNS. — dmc

dmc dmc · Accepted Answer · 2015-01-16T16:43:46

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

DB2 syscolumns does not return all columns

1 Answers