Search code examples
ibm-midrangedb2-400

List of System21 Tables?

I am about to start working with a System21 database and I am looking to find a file definition list. From reading the GEAC System21 IBM Handbook I found a few snippets like:

  • OEP40 = Sales Order Header
  • INP35 = Inventory Item Master

Is there a master list anywhere?

Solution

  • The System i Database Catalog maintains a list of all tables / files on the system.

    You can SQL query the catalog objects to get a list of tables, views, indexes, procedures, functions, etc.. The catalog objects are stored in library / schema QSYS2 and use a SYS object name prefix. To get a list of tables for a schema, run this query:

    select * from QSYS2.SYSTABLES where TABLE_SCHEMA = 'supply your schema name here'

    To get a list of most of the catalog objects, run this query:

    select * from QSYS2.SYSTABLES where TABLE_SCHEMA = 'QSYS2' and TABLE_NAME like 'SYS%'

    As I recall, some of the catalog objects are SQL views instead of tables, so to find those, run this query:

    select * from QSYS2.SYSVIEWS where OBJECT_SCHEMA = 'QSYS2' and OBJECT_NAME like 'SYS%'