Consider rolling through the schemas using the DB2_Tables function?
<?php
$conn = db2_connect('*LOCAL', "", "");
$db2Tables = db2_tables($conn, "", 'ZENDSVR6'); //ZENDSVR6 is the schema
name...
$row = db2_fetch_assoc($db2Tables);
echo '<pre>'; print_r($row); echo '</pre>';
Mike Pavlak
Cell: (408)679-1011 Office: (708)233-5880
-----Original Message-----
From: WEB400 [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, November 18, 2015 10:00 PM
To: web400@xxxxxxxxxxxx
Subject: Re: [WEB400] List Tables For A Schema... Not Working
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.
--
Regards, Chuck
--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
list To post a message email: WEB400@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/web400.
midrange.com
