V6R1M0 upgrade crisis:
My former employ (I'm semi-retired) had a crisis after a V5R4M0 upgrade to V6R1M0...Some DB2 changes had "tightened" up some rules apparently and many views they had created had issues
with nulls in comparisons and some other issues that won't be detailed here. Bottom line...Syntax
and/or some statement re-configuring had to be done to fix the views.
The problem: These views had dependent views, and views using views, and just some really bad awful SQL syntax. (The SQL must have been generated by some client tool because I don't think anyone could personally author such incredible chaos.) Doesn't matter the views had to be rebuilt.
Doh...! You can't just go and drop the view that's bad. No-no...That would drop all dependent views
and holy crap now you've got problems. Dropping a child view, drops the parent! Yippee another enhancement so useful to whom or what I don't know?
Here's what we did. Retrieved the SQL statements for all views in the library via Navigator. Fortunately I still had it installed on my client. Dropped all the views in the library. Corrected the syntax where needed or where the views where "broken".
Run the script in the Navigator to re-create all the views. My issue? The SQL retrieved of course was not in the order of dependencies they had...So the script would crap-out because a view needed a dependent view that wasn't created yet. So edit, cut the failing CREATE VIEW statement, paste it to the bottom of the script, and re-start at the next statement. This went on for several hours until we finally had all views re-created.
My beef...There's got to be a better way? I thought I recalled a post here some time ago about
views w/dependent views, and views upon views and views of views. Anyone remember? I couldn't find it. But the order of dependency and re-creation is so critical when you have dependent views like that. And hacking the SQL script for 2 hours was no fun.
What I've done in the past is to uncheck the stop on error option in navigator and just keep running the script until everything is created. This means that you have to have generated the short names, since for some reason the generator uses short names for view references within views.
You know that probably would have saved us some time. But I did end up with a script of CREATE VIEW statements in the proper order of creation. I forgot to mention that ALL of the previously used short-name's used in
CREATE VIEW statements had to be changed to use the long-name's in double quotes, because obviously when re-creating all these views...The short-name's changed. Another gotcha. Now I strongly suggest that if possible...DO NOT create views that use other "views". If performance is the issue (and the only reason I can think of for using views in other views)...Save yourself, keep it simple and use the SELECT statements used in views instead.