Re: List Tables For A Schema... Not Working

It is possible that Rob is looking at non-IBM i material, about the other flavors of DB2 - the LUW or z flavors. Those would be likely to speak more about catalogs, I would guess. His original sentence suggests this - "...with DB2 SQL" - that is not, as you point out, specific enough.

On other systems, it is possible that a catalog is a more-important entity - especially because DB2 is installed, it is not an integral part of the system - the latter might be why we don't think of catalogs, as you say, everything is within the "automatic" catalog that is the system itself.

Cheers
Vern

On 11/19/2015 2:57 PM, CRPence wrote:

On 19-Nov-2015 12:00 -0600, Rob wrote:

On 2015-11-19 11:48, CRPence wrote:

On 19-Nov-2015 11:15 -0600, Rob wrote:

Now since I am getting good with DB2 SQL...

And using the System i Navigator... according to IBM

Columns are grouped by Table
Tables are grouped by Schema
Schemas are contained within a Catalog

A "Catalog". Really? Where do they come with this stuff [and those
who would regurgitate such info]? Odd how much strange stuff that
is offered up by, or at least attributed to, the so-called "IBM".
IMNSHO, a response of "within a Directory", "within a Cabinet", or
"within a Senate" would have been just as accurate and meaningful,
with respect to the DB2 for IBM i ;-)

Open System I Navigator... choose your database... Run SQL Script

SELECT * FROM SYSIBM.TABLES

You get:
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME....

SELECT * FROM SYSIBM.COLUMNS

You get:
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME...

Sure. But my point was that _from a DB2 for i perspective_, the term CATALOG is almost meaningless, as contrasted with having said the CURRENT SERVER [which is whence the data for TABLE_CATALOG is derived], which is the local database connection, which is the Relational Database (RDB) Directory Entry (RDBDIRE) for *LOCAL that defines the "Relational database name".

I would expect that anyone trying to find information about the "catalog" in the TABLE_CATALOG sense, within the documentation of the DB2 for i SQL, will be hard-pressed to find anything that could explain the origin of the term; nothing other than the column label of: "Relational database name". Worse, such a person is likely to become confused when searching the term, because predominantly the term /catalog/ will be seen used to refer to the VIEWs from which information about the entities on the current server can be queried. At least the docs discuss what a SCHEMA is, within the context of the DB2 for i, in contrast with the usage of the term by most other RDBMS; there is AFaIK, *no* similar discussion of the term /catalog/, so only those familiar with non-DB2 for i would be generally well informed of what is the meaning.

So my implication was, that by "IBM" telling someone who is using the DB2 for i, that the SCHEMAS are entities\components of a CATALOG, is little better than having suggested to that interested party that the SCHEMAS are entities\components of a CABINET; i.e. the term CABINET will have a similarly useful meaning [almost none], within the context of the DB2 for i, as does the term CATALOG, at least with the effective meaning as /container/ of SCHEMAS. IOW, talking to most long-term DB2 for i users, and any mention that there is a CATALOG that contains the SCHEMAS, and they will almost certainly conflate the term with the Catalog VIEWs; and many probably would be adamant, insisting that instead the library QSYS contains SCHEMAS, because the catalog is just row-data.

If the "IBM" were inclined to use effective column-names to describe the situation, then they could have offered instead, an explanation using only column-names: that each COLUMN_NAME is grouped within each TABLE_NAME which is grouped within each TABLE_SCHEMA which is grouped within each TABLE_CATALOG which is derived from a CATALOG_NAME [or just as accurately, derived from the special register CURRENT SERVER].