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

I'm with Buck - basically use the other catalog views, the ones in QSYS2. They never mention the catalog, which is defined in SQL-ish terms as the place where the schemas reside and where you have some metadata.

On IBM i, the catalog is equivalent, I'd say, to a relational database entry - and that is usually the system itself, unless you have independent IASPs, which can have their own entry in the RDBDIRE list.

Chuck's response surprised me and is not his usual amazingly helpful style. Fact is, we hardly ever think of catalogs. We work with schemas (libraries in IBM i parlance), then down through tables and views and indexes and all, then columns

You mention using Navigator - you may notice that in the main app, there is a Databases piece, and if you open that up, you will see 1 or more names - there will be at least 1, which is the name of the system you are connecting to.

No mention of catalogs there, but that list of names, which is at a higher level than schemas, is what is probably considered the catalogs.

On IBM i, if those ARE equivalent to catalogs, the is only one per machine - the machine itself. Or IASP itself, which is like a mini-LPAR.

I'd say again with Buck, forget about the SYSIBM stuff for the most part - it has fewer tables and views in it than the DB2 for i catalog metadata.

In that metadata, to confuse things some, there is a view QSYS2/SYSCATALOGS - and this does list the names one sees in WRKRDBDIRE command screen.

Some of the views are found in both SYSIBM and QSYS2 - COLUMNS and TABLES and Views (Oh, My!), for example. But there are also some only in the library (schema) that can be considered ODBC and JDBC catalog views, according to a PDF from the database folks that I'm looking at.

The PDF is located at

https://www.ibm.com/developerworks/community/wikis/basic/anonymous/api/wiki/beb2d3aa-565f-41f2-b8ed-55a791b93f4f/page/2d8b432e-08ec-4c4b-bc5d-d4db253f148c/attachment/4070e002-9f7c-4d72-b541-243bc7d6a2c3/media/DB2fori_Catalog_Views_dW_14x8Landscape.pdf

If it's wrapped, copy all of it into a browser to get the PDF.

HTH
Vern

On 11/19/2015 12:00 PM, Rob wrote:

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...


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 ;-)

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus