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



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.