DBACOCKPIT and missing View SYSFIELDS

[christoph_ostrop]

we are on NW-7.03/7.31 SP03 with Kernel 720_EXT on IBM-i V7R1

DBACOCKPIT shows under => Diagnostics => missing Tables and Indexes

Looking at DB2 for i - sometimes a View is in User-defined Collections, sometimes not,

and in this case, the View is missing in Collection R3EREDATA

it seems, that this View is (in some cases) automatically created in user-defined Collections,

maybe when working with IBM i Access for Windows (OpsNav) on this Collection ?

- how to create this missing View in SAP-DB-Collections ?

[dorothea_stein · Accepted solution]

Hello Mr. Ostrop,

SYSFIELDS is a system catalog view that is new in IBM i 7.1. After upgrading, it will thus show up as a view that exists on DB but not in DDIC. SAP Note 1587209 has a fix that will correct the display (== ignore this discrepancy).

Best regards,

Dorothea Stein

[christoph_ostrop · Accepted solution]

Dorothea Stein wrote:
Hello Mr. Ostrop,
SYSFIELDS is a system catalog view that is new in IBM i 7.1. After upgrading, it will thus show up as a view that exists on DB but not in DDIC. SAP Note 1587209 has a fix that will correct the display (== ignore this discrepancy).
Best regards,
Dorothea Stein

in this mentioned sap-note 1587209 there is no hint and no code-correction for NW-7.03/NW-7.31 ?

and

i think, this is not a SAP DDIC Problem,

because in one of our IBM i  (someone has created this View in the SAP-DB-Collection,

and then), there is no entry of missing views in DBACOCKPIT.

[christoph_ostrop · Accepted solution]

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 ???

PS:

for all, who are looking for the solution: (create the missing views)

CALL PGM(QSYS2/QSQXRLF) PARM(CRT collectionName)

[dorothea_stein · Accepted solution]

Hello Mr. Ostrop,

I'm very sorry, in yesterday's rush I actually misread your post (by implying my anticipation about what the question would be ).

The (real?) answer to your question is as follows: All SQL schemata being newly created in IBM i 7.1 will have catalog view SYSFIELDS (plus potential extensions to existing catalogs). All user schemata that have already existed at the time the OS upgrade happened, will not.

The name catalog, however, is misleading, because the ones in the user schemata are really only convenience views to existing global "cross reference files" in QSYS, which are of course correctly maintained all the time. There is also a global version of the catalog views in QSYS2, which is recreated according to their new structure as part of OS upgrades. The update of the views in the user schemata is however left to the user of it.

If you would like to do this, you can run IBM tools:

CALL QSYS2/QSQXRLF PARM(DLT R3<sid>DATA)     ----   delete existing catalog views

CALL QSYS2/QSQXRLF PARM(CRT R3<sid>DATA)    ----    create the catalog views

Or you can run

AS4FIXFILE CHGOWN(*NO) SYSTBL(*YES) ENDJRN(*NO) STRJRN(*NO)

from the SAP kernel to achieve the same (newer versions of AS4FIXFILE only).

As long as you only upgrade your OS without upgrading SAP, the non-existence of certain catalog views should only be a cosmetic issue, since SAP does not rely on them.

As soon as we do, we'd make sure that we upgrade the catalog views as part of the SAP upgrade.

Hope I got it right this time...

Best regards,

Dorothea Stein

[christoph_ostrop · Accepted solution]

Dorothea Stein wrote:
Hello Mr. Ostrop,
I'm very sorry, in yesterday's rush I actually misread your post (by implying my anticipation about what the question would be ).
The (real?) answer to your question is as follows: All SQL schemata being newly created in IBM i 7.1 will have catalog view SYSFIELDS (plus potential extensions to existing catalogs). All user schemata that have already existed at the time the OS upgrade happened, will not.
The name catalog, however, is misleading, because the ones in the user schemata are really only convenience views to existing global "cross reference files" in QSYS, which are of course correctly maintained all the time. There is also a global version of the catalog views in QSYS2, which is recreated according to their new structure as part of OS upgrades. The update of the views in the user schemata is however left to the user of it.
If you would like to do this, you can run IBM tools:
CALL QSYS2/QSQXRLF PARM(DLT R3<sid>DATA)     ----   delete existing catalog views
CALL QSYS2/QSQXRLF PARM(CRT R3<sid>DATA)    ----    create the catalog views
Or you can run 
AS4FIXFILE CHGOWN(*NO) SYSTBL(*YES) ENDJRN(*NO) STRJRN(*NO)
from the SAP kernel to achieve the same (newer versions of AS4FIXFILE only).
As long as you only upgrade your OS without upgrading SAP, the non-existence of certain catalog views should only be a cosmetic issue, since SAP does not rely on them. 
As soon as we do, we'd make sure that we upgrade the catalog views as part of the SAP upgrade.
Hope I got it right this time...
Best regards,
Dorothea Stein

yes, Dorothea, thank you, that's what I expected.