Objects missing in database (views)

[Former Member]

As information/background, I have been away from SAP and basis for about four years. Due to personnel changes here, I am now returning to it. While I was away, our SAP system was upgraded from version 4.0B to “SAP R/3 ENTERPRISE 47X110”. I was not a part of the upgrade and have no details as to how it was accomplished.

At this point, we have two SAP “systems”: DEV and PRD and they are on separate partitions of an i5 520. Both are running on OS/i5 V5R3M5 with mostly updated CUMe packages, etc, and PASE is installed. Both of these “systems” use the 6.20 kernel at patch level 1598. We know that this is an OLD kernel and we need to upgrade. I am currently planning the kernel upgrade to 6.40.

In our PRD instance, using transaction DB02 I have been seeing that we are missing four views in the database:

  Views
    SYSTRIGCOL    Obligatory Base object is missing in DB
    SYSTRIGDEP    Obligatory Base object is missing in DB
    SYSTRIGGERS   Obligatory Base object is missing in DB
    SYSTRIGUPD    Obligatory Base object is missing in DB

Is this a big deal? Should I worry about it or just continue with my kernel-upgrade planning?

If needed, can I just retrieve the source from the DEV instance, modify the source, and create the views in PRD?

I have confirmed that I can retrieve the DDL source from DEV via iSeries Navigator. It would then just be a matter of copying it, modifying it for PRD, and then executing it for PRD.

Thanks in advance for any advice.

Robert

[dorothea_stein · Accepted solution]

Hi Robert,

no this isn't critical during daily business, at least not in base release 6.20.

You are missing a couple of system catalog views providing SQL trigger information on a schema basis. (They are views on top of the systemwide cross reference files.)

I've seen this happening if you restore a library from an old OS/400 release (that did not know about those views) to a newer release.

SAP does not use triggers in 6.20 during daily business that's why it is not critical.

It's easy to fix though: You may consider removing all system catalog logical files from your R3<sid>DATA schema (WRKOBJ, 4) according to the following list:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogtbls.htm#catalogtbls .

The following command will recreate them:

     CALL QSYS2/QSQXRLF PARM(CRT R3<sid>DATA)

Hope that helps!

Best regards,

Dorothea

[Former Member · Accepted solution]

Dorothea,

Thanks for the insight.

If I chose to remove "...all system catalog logical files from your R3<sid>DATA schema..." and then recreate them as you suggest, would the system need to be in a restricted state?

At the very least, I would think that the PRD instance would need to be stopped.

Can you elaborate?

Thanks!

Robert

[dorothea_stein · Accepted solution]

Hi Robert,

yes, I'm sorry, I forgot to mention that the SAP system should be down during that time. However, the machine does not need to be in restricted state, since you are not recollecting cross reference data, but only creating database views on top of already existing data in the cross reference files (QSYS.QADBxxx).

The purpose of this is, for one, to provide a more user friendly view on that data, and secondly to project on objects within a single schema R3<sid>DATA.

Best regards,

Dorothea

[Former Member · Accepted solution]

Dorothea,

Thanks for the update.

One more thing:

Instead of using WRKOBJ,4 to delete the existing views, could I use CALL QSYS2/QSQXRLF PARM( DLT 'R3PRDDATA') ?

Because this is our production instance, downtime is difficult to schedule.

I am thinking about just adding (one time) the two commands to the end of our weekly full system backup (when the SAP instance is down anyway):

CALL QSYS2/QSQXRLF PARM( DLT 'R3PRDDATA')

CALL QSYS2/QSQXRLF PARM( CRT 'R3PRDDATA')

Your thoughts?

Robert

[dorothea_stein · Accepted solution]

Hi Robert,

whew, I was actually not aware that a delete parameter exists as well, or too lazy to look for it. Certainly, that's a much more elegant way to achieve this. Thanks for the hint!

Best regards,

Dorothea

[Former Member · Accepted solution]

Dorothea,

Sounds like I have a solution and an implementation plan, then.

I've awarded your points.

Thanks for your help!

Robert

[Former Member]

I need to revisit this thread because I'm preparing to correct this error of the missing SQL views.

After I run the commands to rebuild the SQL views, should I run AS4FIXFILE to correct the authorizations? I don't see any way to specify a specific object on the command (just the data library) so I'm assuming that this will affect the ENTIRE data library. I suppose that I should count on that running several hours, then?

What about journaling? Should I end journaling as a parameter of AS4FIXFILE (if, indeed, I need to run it)?

If AS4FIXFILE isn't the answer, then please advise how to handle the authorizations.

Any help is greatly appreciated.

Robert

[Former Member]

No need to run as4fixfile, recreating the catalog is not a big thing and is quick. It doesn't really seem to touch the rest of the database.

Pat

[Former Member]

As follow-up, I retrieved the DDL statements for these views and checked the tables referenced (QSYS.QADBXTRIGD, QSYS.QADBXTRIGC, and QSYS.QADBXTRIGB) in PRD. There are no entries in these tables that reference any R3 data.

That being said, logically speaking, it shouldn't matter that the views do not exist. However, creating them would make the error disappear.

So, my question still stands. Should I create the missing views?

Thanks!

Robert