Re: SQL views missing after Cumulative PTF's applied

[Robert Clay <zreclay@xxxxxxxxx>]

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