Re: informix and web logic
- From: "Art S. Kagel" <kagel@xxxxxxxxxxxxx>
- Date: Wed, 20 Jul 2005 10:21:46 -0400
tomcaml@xxxxxxxxx wrote:
hello
Hi. My guess is that their apps are reusing prepared statements/cursors that are initialized when the connection pool is established. Fine so far. However, when you create the new empty database and set up stats for it, as you say, the tables are all empty or nearly so. This means that the query plans developed and stored with the prepared statements/cursors are likely specifying sequential scans. That means that every row in each table accessed is being locked at least briefly during the scan. If you can have the apps print the ISAM code when they get the sqlcode -244 you can be sure because that will specify whether it was a table or index read that failed and why (say for a lock or data problem, etc.).
The obvious fix, as already indicated would be to set lock mode to wait in every connection so that each waits for transient locks to clear for several seconds before erroring out.
But this only fixes the symptom, not the cause which is a poor query plan reducing concurrency. There remains the problem that as the tables grow those sequential scans will begin to bog things down. The solution is to run update stats on the OLD database when it is full, ie right after you rename it, then copy the sysdistrib records from the old database to the new one (making sure to adjust for any changes in the tabid of specific tables). Then when the apps attach they will be using data distributions developed for a full database. Under some circumstances those end-of-day stats may also be less than ideal at the beginning of the day, then you can export the records from an update stats taken in the middle of the load cycle when everything is half full and save them to restore the next day, perhaps running another update stats and bounce the connections towards the end of the day if that give less than satisfactory results towards the end of the day.
I know, I normally beat up on folk who muck with the system catalog records, but sysdistrib is safe. The data kept there is not echo'd anywhere else in the server (no secret copies on sysmaster pages). This is a patch I've done in similar situations and it works like a charm.
Art S. Kagel
we are running infx 9.40 FC3 on HP-UX 11i. Developers use web logic to process batch files (JDBC drivers) - and they use connection pooling. when i create a new DB in dev from Prod data, i start from scratch and then rename DB's, for example DB1 becomes DB1_old and the DB1_new is renamed to DB1, and then developers use the DB1 database. last thing i do is run update stats on each table , proc and view and the whole db. most tables are empty of data
what happens is they go to write to tables in the new DB and their application returns an error of -244 "Could not do a physical-order read to fetch next row. "
they ask me to re-run update stats and they reset their server
connections to the database. has anyone run into this? it does not seem
that the update stats really change anything for them but rather the
re-setting of their connections....any help is appreciated
Thanks Tom
.
- Follow-Ups:
- Re: informix and web logic
- From: tomcaml@xxxxxxxxx
- Re: informix and web logic
- References:
- informix and web logic
- From: tomcaml@xxxxxxxxx
- informix and web logic
- Prev by Date: Re: How do export 4js report to PDF format report??
- Next by Date: Re: IDS 7.31 UD8 on Linux SUSE SLES 9 ?
- Previous by thread: informix and web logic
- Next by thread: Re: informix and web logic
- Index(es):
Relevant Pages
|
