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

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
    446 Posts
    ACCEPTED ANSWER

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

    ‏2012-10-18T18:52:40Z  in response to admin33544
    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
      ACCEPTED ANSWER

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

      ‏2012-10-19T09:10:41Z  in response to krmilligan
      > 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
        250 Posts
        ACCEPTED ANSWER

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

        ‏2012-10-19T09:40:45Z  in response to admin33544
        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
          ACCEPTED ANSWER

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

          ‏2012-10-19T09:51:19Z  in response to B.Hauser
          > 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
            ACCEPTED ANSWER

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

            ‏2012-10-19T10:44:03Z  in response to admin33544
            for all, who are looking for the solution: (create the missing views)

            CALL PGM(QSYS2/QSQXRLF) PARM(CRT collection)
            • krmilligan
              krmilligan
              446 Posts
              ACCEPTED ANSWER

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

              ‏2012-10-19T13:48:52Z  in response to admin33544
              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.