Problem with view in large SQL

Problem with view in large SQL

Post by Neil » Tue, 19 Sep 2000 04:00:00

I decided to fix a design flaw in a system I'm working on to split a
database table into 2. I created a view which joined them back together in a
way that was indistinguishable from the original.

Most of the system worked unchanged, however a few of the most horrendously
complex SQLs with some old fashioned syntax


etc... involving 9 or more tables produced bad results when the table was
replaced with the view.

Is there any "known problem" I should be aware of concerning this? Has
anyone experienced this & found an easy fix?

I really don't want to attempt to rewrite those SQLs because of the testing
load that would add to the project. So the alternative is to go back to the
old inefficient table structure. I would really appreciate any suggestions
which would allow me to avoid doing that.




Problem with view in large SQL

Post by Serge Riela » Wed, 20 Sep 2000 04:00:00


Can you post the old table structure, the new one and one of the queries
that misbehaves (with enough DDL to get it parsed)?
The Version of DB2 is helpfull, too :-)

Would be interesting to see where it goes of...
One reason that comes to mind is that DB2 could now be sharing thw view
rather than redoing the join everytime. This could lead to system temp
tables and failure to push down some predicates.


Visit the DB2 UDB and DB2 Connect Online Support site at:


1. Problem with a large view (SQL error -264 / ISAM -131)


We have Informix 7.31 UC4 (OWS) installed on a HP 11.11 platform. We have a
view that is extremely large. When I  select from the view, I execute a SQL
i.e., ,

SELECT COUNT(*) FROM my_view, I get the following error :

-264 Could not write a temporary file.
-131 ISAM error: no free disk space.

When I do onstat -d it appears that all of available free space on a
tempdbspace is being used up. Almost 750 MB.

The strange thing is this view worked fine prior to our moving to HP 11.11
(we had HP 10.2). Our database has buffered logging and the underlying
tables had updates statistics run.

Is there any setting on the view definition, that I can change to make this
statement not use the tempdbspace, since it worked on the HP 10.2 platform.
The underlying SQL is very large and since it worked on HP 10.2, I want to
ensure that the settings are correct, before trying to re-write the SQL.

Thanks in advance.

2. reducing Tempdb size?

3. A problem with large views


5. stored proc. concurrent access

6. Problem with a large view (worked on 7.30 but not on 7.31)

7. Converting SQL Server 2000 Database to SQL Server 7?

8. Large Query Causes Large Problem

9. SQL 7.0 views vs SQL 2000 views

10. Reference needed - Current Large (Largest) SQL 7.0 Database?

11. Can't view Very Large Dimensoin

12. Large VIEWS