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