Topic
  • 6 replies
  • Latest Post - ‏2012-10-19T13:48:52Z by krmilligan
admin33544
admin33544
4 Posts

Pinned topic missing system catalog view SYSFIELDS in user-defined collections

‏2012-10-18T14:15:18Z |
the view SYSFIELDS exists in library/collection QSYS2,
and
sometimes in some (but not in all) user defined collections.

some applications (e.g. SAP) are showing that a view in the DB-collection is missing.
what program or hint exists to create this missing view SYSFIELDS in user-collections?
or
do i have to run a CMD: DSPFD QSYS2/SYSFIELDS
and copy the create-SQL-Statement like:

CREATE VIEW SYSFIELDS (TABLE_SCHEMA FOR "DBNAME", TABLE_NAME FOR "TB
NAME", COLUMN_NAME FOR "NAME", ORDINAL_POSITION FOR "COLNO", DATA_TY
PE FOR "COLTYPE", "LENGTH", CHARACTER_MAXIMUM_LENGTH FOR "CHARLEN",
CHARACTER_OCTET_LENGTH FOR "CHARBYTE", NUMERIC_SCALE FOR "SCALE", NU
MERIC_PRECISION FOR "PRECISION", NUMERIC_PRECISION_RADIX FOR "RADIX"
, "CCSID", DATETIME_PRECISION FOR "DATPRC", FIELD_PROC FOR "FLDPROC"
, "PARMLIST", "EXITPARM", SYSTEM_COLUMN_NAME FOR "SYS_CNAME", SYSTEM
_TABLE_NAME FOR "SYS_TNAME", SYSTEM_TABLE_SCHEMA FOR "SYS_DNAME") AS
SELECT DBILB2, DBILFI, DBILFL, INTEGER(DBIPOS), TRIM(DBXSTYP), INTE
GER(DBXSFLN), INTEGER(DBXSCNC), INTEGER(DBXSCLN), INTEGER(DBXSNSC),
INTEGER(DBXSNLN), INTEGER(DBXSRDX), DBXSCCC, INTEGER(DBXSDLN), DBXSF
PN, DBXSEPARML, DBXSIPARML, DBIFLD, DBIFIL, DBILIB FROM QSYS.QADBIFL
D LEFT OUTER JOIN QSYS.QADBXSFLD ON DBILIB=DBXSFLIB AND DBIFIL=DBXSF
FILE AND DBIFLD=DBXSFCOL WHERE DBIREL = 'Y' AND DBIATR 'IX' AND D
BXSFPN IS NOT NULL AND DBILIB = '< user-def-collection >'
Updated on 2012-10-19T13:48:52Z at 2012-10-19T13:48:52Z by krmilligan
  • krmilligan
    krmilligan
    450 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-18T18:52:40Z  
    When you state that some applications show SYSFIELDS is missing - what does that mean? I'm assuming that the missing view is not causing any errors because these views are really not required.

    As you've determined by looking at the definition of the SYSFIELDS view, the view in user-defined schemas just returns a subset of the catalog data.

    Were the user-defined schemas missing the SYSFIELDS view created before the IBM i 7.1 release was installed.
  • admin33544
    admin33544
    4 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-19T09:10:41Z  
    When you state that some applications show SYSFIELDS is missing - what does that mean? I'm assuming that the missing view is not causing any errors because these views are really not required.

    As you've determined by looking at the definition of the SYSFIELDS view, the view in user-defined schemas just returns a subset of the catalog data.

    Were the user-defined schemas missing the SYSFIELDS view created before the IBM i 7.1 release was installed.
    > krmilligan wrote:
    > When you state that some applications show SYSFIELDS is missing - what does that mean? I'm assuming that the missing view is not causing any errors because these views are really not required.
    >
    > As you've determined by looking at the definition of the SYSFIELDS view, the view in user-defined schemas just returns a subset of the catalog data.
    >
    > Were the user-defined schemas missing the SYSFIELDS view created before the IBM i 7.1 release was installed.
    CREATE COLLECTION (on V5R4 or V6R1),
    these system-catalog-views are created in each collection:
    SYSCHKCST
    SYSCOLUMNS
    SYSCST
    SYSCSTCOL
    SYSCSTDEP
    SYSINDEXES
    SYSKEYCST
    SYSKEYS
    SYSPACKAGE
    SYSREFCST
    SYSTABDEP
    SYSTABLES
    SYSTRIGCOL
    SYSTRIGDEP
    SYSTRIGGER
    SYSTRIGUPD
    SYSVIEWDEP
    SYSVIEWS

    CREATE COLLECTION (on V7R1),
    these system-catalog-views are created in each collection:
    SYSCHKCST
    SYSCOLUMNS
    SYSCST
    SYSCSTCOL
    SYSCSTDEP
    SYSFIELDS <==== new with V7R1, but missing in collections created with older IBM-i/OS and updated to V7R1
    SYSINDEXES
    SYSKEYCST
    SYSKEYS
    SYSPACKAGE
    SYSREFCST
    SYSTABDEP
    SYSTABLES
    SYSTRIGCOL
    SYSTRIGDEP
    SYSTRIGGER
    SYSTRIGUPD
    SYSVIEWDEP
    SYSVIEWS

    so the system-objects look different and there is a gap (View SYSFIELDS missing),
    is this the way IBM provides IBM-i/OS Updates ???
  • B.Hauser
    B.Hauser
    256 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-19T09:40:45Z  
    > krmilligan wrote:
    > When you state that some applications show SYSFIELDS is missing - what does that mean? I'm assuming that the missing view is not causing any errors because these views are really not required.
    >
    > As you've determined by looking at the definition of the SYSFIELDS view, the view in user-defined schemas just returns a subset of the catalog data.
    >
    > Were the user-defined schemas missing the SYSFIELDS view created before the IBM i 7.1 release was installed.
    CREATE COLLECTION (on V5R4 or V6R1),
    these system-catalog-views are created in each collection:
    SYSCHKCST
    SYSCOLUMNS
    SYSCST
    SYSCSTCOL
    SYSCSTDEP
    SYSINDEXES
    SYSKEYCST
    SYSKEYS
    SYSPACKAGE
    SYSREFCST
    SYSTABDEP
    SYSTABLES
    SYSTRIGCOL
    SYSTRIGDEP
    SYSTRIGGER
    SYSTRIGUPD
    SYSVIEWDEP
    SYSVIEWS

    CREATE COLLECTION (on V7R1),
    these system-catalog-views are created in each collection:
    SYSCHKCST
    SYSCOLUMNS
    SYSCST
    SYSCSTCOL
    SYSCSTDEP
    SYSFIELDS <==== new with V7R1, but missing in collections created with older IBM-i/OS and updated to V7R1
    SYSINDEXES
    SYSKEYCST
    SYSKEYS
    SYSPACKAGE
    SYSREFCST
    SYSTABDEP
    SYSTABLES
    SYSTRIGCOL
    SYSTRIGDEP
    SYSTRIGGER
    SYSTRIGUPD
    SYSVIEWDEP
    SYSVIEWS

    so the system-objects look different and there is a gap (View SYSFIELDS missing),
    is this the way IBM provides IBM-i/OS Updates ???
    Not all catalog views are included in schemas created with either the CREATE SCHEMA or the CREATE COLLECTION statement. For example none of the statistic views like SYSTABLESTAT or SYSPARTITIONSTAT is included.

    Birgitta
  • admin33544
    admin33544
    4 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-19T09:51:19Z  
    • B.Hauser
    • ‏2012-10-19T09:40:45Z
    Not all catalog views are included in schemas created with either the CREATE SCHEMA or the CREATE COLLECTION statement. For example none of the statistic views like SYSTABLESTAT or SYSPARTITIONSTAT is included.

    Birgitta
    > B.Hauser wrote:
    > Not all catalog views are included in schemas created with either the CREATE SCHEMA or the CREATE COLLECTION statement. For example none of the statistic views like SYSTABLESTAT or SYSPARTITIONSTAT is included.
    >
    > Birgitta

    it may be,
    but my question was about the View SYSFIELDS and the above CREATE COLLECTION i executed today,
    so with V7R1, SYSFIELDS exists in new created collections
    but in collections created with older OS-Vers. and then updated to V7R1, this View is missing.
  • admin33544
    admin33544
    4 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-19T10:44:03Z  
    > B.Hauser wrote:
    > Not all catalog views are included in schemas created with either the CREATE SCHEMA or the CREATE COLLECTION statement. For example none of the statistic views like SYSTABLESTAT or SYSPARTITIONSTAT is included.
    >
    > Birgitta

    it may be,
    but my question was about the View SYSFIELDS and the above CREATE COLLECTION i executed today,
    so with V7R1, SYSFIELDS exists in new created collections
    but in collections created with older OS-Vers. and then updated to V7R1, this View is missing.
    for all, who are looking for the solution: (create the missing views)

    CALL PGM(QSYS2/QSQXRLF) PARM(CRT collection)
  • krmilligan
    krmilligan
    450 Posts

    Re: missing system catalog view SYSFIELDS in user-defined collections

    ‏2012-10-19T13:48:52Z  
    for all, who are looking for the solution: (create the missing views)

    CALL PGM(QSYS2/QSQXRLF) PARM(CRT collection)
    As pointed out earlier, "missing" views from a user-defined schema should not cause any problems. The vast majority of customers and applications don't even use these views, they reference the master catalog views in QSYS2.

    Also, the collection term has been deprecated so we're encourage users to use the Schema term and CREATE SCHEMA statement instead.