Re: Command to list all logicals and their key fields for a physical file? -- MIDRANGE-L

What the heck, I wasn't done writing an email.

The files in QSYS look promising, however, I couldn't tell how to determine
what physical file the index was based upon. I did use the QADBIFLD to
display the fields in the file using STRQMQRY.
Just so that I didn't have to write a DSPF screen for it. [?]
SELECT DBIFLD, TRIM(DBITYP) CONCAT
CASE
WHEN DBITYP in ('CHAR','VARCHAR')
THEN '(' CONCAT TRIM(CHAR(DBICLN)) CONCAT ')'
WHEN DBITYP in ('DECIMAL','NUMERIC')
THEN '(' CONCAT TRIM(CHAR(DBINLN)) CONCAT ',' CONCAT
TRIM(CHAR(DBINSC)) CONCAT ')'
ELSE ''
END, DBITXT
FROM QADBIFLD
WHERE DBIFIL = &FILE
AND DBILIB = &LIBRARY
ORDER BY DBIPOS

On Fri, Feb 10, 2012 at 4:19 PM, Michael Schutte <mschutte369@xxxxxxxxx>wrote:

Thanks everyone, I did want a file to read from because I don't like how
the information is display on the OUTPUT(*). And ripping from a report
isn't good.

On Thu, Feb 9, 2012 at 8:15 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 09-Feb-2012 15:16 , Sam_L wrote:

From a wrkobj *all/SYSP* *file on a V5R3 system:

Object Type Library Attribute
SYSPACKAGE *FILE QSYS2 LF
SYSPARMS *FILE QSYS2 PF
SYSPROCS *FILE QSYS2 LF

However, I don't have a powerful profile, so it might be an
authority issue.

Or even more likely, per the V5R3 documentation, those are the only
three of the SQL Catalogs with that prefix [although the possibility
exists that some could have been made available by PTF; e.g. as those
which were delivered in SYSIBM]:
_i iSeries catalog tables and views i_

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogtbls.htm
"The iSeries™ catalog includes the following views and tables in the
QSYS2 schema:

DB2 UDB for Corresponding
iSeries name ANSI/ISO name Description
...
SYSPACKAGE _ _ Information about packages
SYSPARMS PARAMETERS Information about routine parameters
SYSPROCS ROUTINES Information about procedures
...
"

InfoCenter Search [v5r3 above, and v6r1 below]: sql catalog view

As compared to the V6R1 documentation:
IBM i 6.1 Information Center -> Database -> Reference -> SQL reference
-> DB2 for i catalog views
_i IBM® i catalog tables and views i_

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcatalogtbls.htm
"The i catalog includes the views and tables in the QSYS2 schema
displayed in this section. ...
# SYSPACKAGE ...
# SYSPACKAGESTAT ...
# SYSPARMS ...
# SYSPARTITIONINDEXES
The SYSPARTITIONINDEXES view contains one row for every index built over
a table partition or table member. If the table is a distributed table,
the indexes over partitions that reside on other database nodes are not
contained in this catalog view. They are contained in the catalog views
of the other database nodes.
# SYSPARTITIONINDEXSTAT
The SYSPARTITIONINDEXSTAT view contains one row for every index built
over a table partition or table member. If the table is a distributed
table, the indexes over partitions that reside on other database nodes
are not contained in this catalog view. They are contained in the
catalog views of the other database nodes.
# SYSPARTITIONMQTS
The SYSPARTITIONMQTS view contains one row for every materialized table
built over a table partition or table member. If the table is a
distributed table, the materialized tables over partitions that reside
on other database nodes are not contained in this catalog view. They are
contained in the catalog views of the other database nodes.
# SYSPARTITIONSTAT
The SYSPARTITIONSTAT view contains one row for every table partition or
table member. If the table is a distributed table, the partitions that
reside on other database nodes are not contained in this catalog view.
They are contained in the catalog views of the other database nodes.
# SYSPROCS ...
# SYSPROGRAMSTAT
The SYSPROGRAMSTAT view contains one row for each program, service
program, and module that contains SQL statements.
...
"

Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.