× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 18-Nov-2015 17:10 -0600, Rob wrote:
<<SNIP>>

So if I have it correct ...
The DB2 Database is a FILE,

Either a system as a single partition, a logical partition (LPAR), or an iASP [group] might be considered to be the "DB2 Database"; at least as far as the DB2 for i [as the DBMS] is concerned. In each of those effective partitioning, there might be a number of Library (*LIB) objects, all within the single library QSYS as the container of those LIB objects. The terms Library and SCHEMA are synonyms.

Possibly a source of confusion could be: A common term referenced for the DB2 for i, is the Data Base File (DBF) *FILE object; perhaps written instead as, a Database File, or a Database *FILE object. So with the DB2 for i, the row-data resides [in member(s) of] Database Files.

which contains SCHEMAS,

So as mentioned above, the library named QSYS contains all SCHEMAS. And that library QSYS is also exposed as a SCHEMA. But that library QSYS is reserved for use by the OS, so should only be referred to for read-only purposes [as effectively is the case for any library name beginning with Q, and some libraries starting with SYS; a list of names that has grown in number].

which contains TABLES.

The Library is a container for most\all external object types, of which the *FILE object type is just one. Thus a SCHEMA is [as is the Library], a container for a TABLE, because a TABLE is of the type *FILE; i.e. the TABLE is a subset of the Database File *FILE object type.


So I have a list of all Schemas... and I used this query:
SELECT TABLE_SCHEM FROM SYSIBM.SQLSCHEMAS

Also known as, a list of all Libraries.


But when I try to get a list of Tables for a Schema... I get NO JOY!

I have tried:

SELECT TABLE_NAME FROM SYSTABLES
WHERE TABLE_SCHEMA = schema_name

What was the declaration and value assigned for the variable schema_name? Or was that reference to "schema_name" an allusion to a literal\constant specification, and if so, what was the actual literal value specified? And what was the implicit qualification for the unqualified table-reference SYSTABLES; i.e. what was the CURRENT SCHEMA?


SELECT TABLE_NAME FROM SYSTABLES

Again, no qualification; the table-reference would be qualified implicitly with the CURRENT SCHEMA.


SELECT * FROM SYSIBM.SYSTABLES
WHERE OWNER = 'MySchema' AND TYPE = 'T'

This time the table-reference was library-qualified, but QSYS2 is what I would have expected for the name SYSTABLES, whereas from SYSIBM I would have expected a table-reference of SQLTABLES. However, I do not recall that both column names OWNER and TYPE are available in either.?

The OWNER is a User Profile (*USRPRF) object type name, not a SCHEMA [aka Library (*LIB) object type] name. Also the literal value is case-sensitive [the value delimited with apostrophes], and object names not delimited by a double-quote character when created, are implicitly folded to upper-case, so the literal selection often will need to be specified as upper-cased for references to an object name.


SELECT TABLE_NAME FROM MySCHEMA.SYSTABLES

For a SQL catalog view created into a specific named SCHEMA [e.g. in library MYSCHEMA], the literal specification of the schema-name will have been included in an equal predicate of the WHERE clause; i.e. the predicate DBXLIB = 'MYSCHEMA ' would be included, which is the equivalent to the SYSTEM_TABLE_SCHEMA='MYSCHEMA'

As such, the selection of table names is limited to only those /tables/ in the library named MYSCHEMA. Note: although TABLES appears in the name, the selection is not so limited; i.e. non-SQL /relational/ files as well as other non-INDEX SQL files are included in the row data, so any query like the one shown [qualified table-reference for SYSTABLES] should _always_ include the name SYSTABLES as output else there is clearly an issue to be resolved.


Nothing Works!

Sadly, "No joy" and "Nothing works" are quite nebulous. Should the reader infer all of those query requests returned an empty result-set, or something different? Without specifics, a reader can only guess :-(

Per a prior comment, the following query should always yield _a row_ with the value 'SYSTABLES', along with a number of other rows as well; if instead the result is an empty-set, then there is an issue to be investigated and resolved:

SELECT TABLE_NAME
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'QSYS2'
-- optionally to ease locating a value: ORDER BY TABLE_NAME


Back to google I go....


Given this newsgroup\forum is for web development, and given neither general SQL queries nor the [query of] SQL catalogs are specifically web-related as topics, a better place to post a question about such a topic might be midrange-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.