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.
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.
Comment