Re: SQL views missing after Cumulative PTF's applied

Followup:

IBM could offer no assistance, advising that they “never” delete views when applying PTFs. Further, they advised that there is no audit trail to begin an investigation because we do not journal deletions for these particular database objects.

I thought (hoped) that it was just a fluke but this past weekend we installed the current batch of group PTFs and it happened AGAIN: missing views! More this time than last time.

Last time, I was able to tell which views were missing by querying QSYS2.SYSTABLES...WHERE table_type = 'V' AND system_table <> 'Y'.

I think that it is strange that the views show up in SYSTABLES and yet don't really exist as objects.

We were able to re-create the views but it's just a hassle.

The one thing that these five views have in common is that they each utilize UNION with values based on SYSIBM.SYSDUMMY1 (don't ask).

I see the need to start auditing on these objects, at least for deletions. Then, maybe I'll have something for IBM to begin an investigation.

I'm really surprised that no one else has experienced this issue.

Robert


"Contrariwise, if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic."--Tweedledee
On 04/30/2013 2:47 PM, Robert Clay wrote:

V6.1.0 LPAR


We recently (April 21, 2013) installed Cumulative PTF fix pack C3058610 and related Groups, HIPERs, etc.Here are our current *INSTALLED levels:

PTF Group LevelStatusText

SF9962610Installed 6.1 ELECTRONIC SERVICES GROUP PTF

SF9961013058Installed CUMULATIVE PTF PACKAGE C3058610

SF99609144Installed GROUP HIPER

SF9960840Installed GROUP SECURITY

SF996054Installed HARDWARE AND RELATED PTFS

SF9960129Installed DB2 FOR IBM I

SF9956223Installed JAVA

SF9935626Installed PRINT PTFS

SF9935415Installed TCP/IP PTF

SF991446Installed PERFORMANCE TOOLS

SF9911530Installed IBM HTTP SERVER FOR I

* * * * *E N DO FL I S T I N G* * * * *

Now, we are finding that there are SQL views that have mysteriously disappeared and must be re-created.We use a mix of DDS-defined files/logicals and DDL-described tables/indexes/views and we are unaware of any way to tell which ones could be missing until they are found to be gone.

Has anyone else had this issue?Is there anything else we can do to determine what needs to be re-created?

Thanks in advance,

Robert Clay