Partial select

gjain@alfalfa.csci.csc.com (Gajendra Jain) writes:
> 
> We are facing an awkward problem at customer site.
> 
> Job: This is a batch process written in embedded sql. It does lots of select
> and update for each of the table in the database. It runs perfectly fine. We
> have never come across any problem with this batch process till recently.
> 
> But we have come across a problem recently which is not yet solved. In the 
> middle of the batch process, for one of the table, the select went half way 
> through and it did partial operation for this particular table. It did'nt 
> report any error, whereas it should have. It went further doing operation 
> with other tables. It completed the process successfully.
> 
> 
> Question : Is it possible for INGRES to not report any error message for some
> error conditions. 

I've not seen or heard of this for years.

It may be that Ingres thinks it worked OK. This may point to a corrupt 
table structure (especially it its just started happening and happens 
consistently).

Is the select using an index structure ? How do you know that all the rows 
are not being returned ?

Try using the same SQL statement in isql and see what's returned. If you're 
getting the same partial results as your program, but a table scan SQL 
proves that there are more rows which should have been returned, then it's 
probably a corrupt index.

Otherwise, talk to Tech Support.

Good luck,

-- 
Graham Parsons
Principal Consultant
Forest Consulting Limited

 


> But . . . In the middle of the batch process, for one of the table, the select
> went half way through and it did partial operation for this particular table.
> It did'nt report any error, whereas it should have. It went further doing
> operation with other tables. It completed the process successfully. 
 
Meaning it ran the rest of the process to completion, with the other
selects and updates operating as they should. 
 
 
> Question : Is it possible for INGRES to not report any error message for some  
> error conditions. 
 
Certainly within the realm of possibility. I haven't seen it for years,
however. 
 
> My guess is, it might have faced problem with disk space and  
> and did not report any error. 
 
Negative. Disk space problems are reported _big_ and _loud_ and it is darn
hard to ignore them, let alone not find them when you're looking for them.
It says things like "unable to put record" and other frightening stuff. 
 
> Whereas it  stopped this select and went further  
> executing another statement. Remember we are checking for error conditions
> after each of the statement.  
 
So let's assume that Ingres was under the impression it did fine. This can
be confirmed by looking at errlog.log. If you're got a time log showing
when the partial operation took place (which I'll bet you do) check out the
portion of errlog.log that corresponds.  
 
 
If this is a cursor, being used to update other tables, you might want to
try running just the select that feeds the cursor, through isql. That will
tell you what the select itself is returning. 
 
The other thing to look for with cursors and 3GL programs, is commit
statements lying around. If the program took an unexpected execution path,
and did a commit, the cursor will be closed, and the next read will come up
empty.  
 
-- 
James Bullock - Rare Bird Enterprises 
620 Park Avenue # 171 
Rochester, NY. 
716-242-4824 
716-244-9072 (Fax)
Ingres Q & A
To William's Home Page
Email William