Kbase 17368: How to fix Esql application that causes bi file to grow
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
How to fix Esql application that causes bi file to grow
Fixing Application (Report Builder) to avoid growing BI file
The problem underlying this bug seems to be that the SQL
Engine (which Report Builder uses to obtain data and
metaschema information) always has a transaction open.
While a transaction is open, other users that attempt to
access the same database force the BI file to grow. Report
Builder operates by executing dynamic SQL queries and
fetching result data, and it never used to close any
transaction after it fetched data. This meant that Report
Builder was essentially always putting other clients at
risk for expanding the BI file. Even when Report Builder
opens a new report, the first thing it does is execute
several queries against the schema, and fetch schema
information--so from the moment the user opens a report,
Report Builder has a transaction open, even though the
user doesn't see anything to indicate that the schema
queries are going on.
What Report Builder does:
1) When the user opens a report in Report Builder, it
executes several metaschema queries to get ready (verify
that database fields defined in the report are still there,
etc).
One problem users encountered was that if the report was
opened for a long duration, ie. when developing a report,
there was a transaction left open from the last metaschema
query. Because Report Builder had fetched data during the
transaction and because the transaction stayed open, other
queries against the database caused the BI file to grow.
What we did to address this problem: we added a call to
roll back the transaction after we had finished with each
metaschema query.
2) When the user says "print the report", Report Builder
executes the big query that actually fetches the data that
goes into the report. Also, the user can say "Print
Preview", and Report Builder displays data from the report
on the user's monitor; the user can page through the report
by clicking on "next page" and "previous page" buttons.
In either case, even when the user was done with the query,
Report Builder did nothing to close the transaction. So if
the user printed a report and then kept the report open,
the transaction from the big data fetching would remain
open, which would mean that other transactions could
expand the BI file--even though, again, from the user's
point of view Report Builder wasn't doing anything.
What we did to address this problem: we added a call to
roll back the transaction after we were finished fetching
data for the report. In the case where the user says
"Print", this would be after we had finished sending all
data to the printer. In the case where the user says
"Print Preview", this would be after the user had finished
browsing through the report on-screen and closed the Print
Preview window.
Note that this solution is not perfect: it is possible that
Report Builder will be "in-the-middle" of the query
(leaving the transaction open) for long periods of time,
and as long as Report Builder is "in-the-middle" of the
query, other applications might force expansion of the BI
file. The most likely situation in which this could occur
is when the user says "Print Preview", looks at some pages
on the screen, and walks away without closing the Print
Preview window. As far as Report Builder is concerned, the
user is not finished with the query yet, so Report Builder
cannot close the transaction until the user closes the
Print Preview window. Depending on how long it is before
the Print preview screen is closed, this could mean that
the BI file is exposed to this problem for many hours.
Technical details of what we did to fix the problem, and
extrapolation to other applications:
Report Builder is written in C and uses the ESQL interface.
The code contains embedded calls to the various ESQL API
routines. Report Builder calls these routines directly in
C, rather than using the SQL Preprocessor, but an
application that uses the Preprocessor can fix the BI file
problem using analogous EXEC SQL statements in its C code
(see below).
Here is a typical sequence of ESQL API calls from Report
Builder:
sqllogin
sqlconn (connect to a database)
sqldynprep (prepare a dynamic query)
sqldynexec (execute the dynamic query)
sqldynftch (fetch a row of the result)
sqldynftch (fetch a row of the result)
...
sqldynclose (close the query)
sqldynprep (prepare the next query)
sqldynexec
sqldynftch
sqldynftch
...
sqldynclose
... (and so on)
What we did to address the BI file problem was insert calls
to "sqlrollback" after the login, the connection and the
close. So now, the series of calls looks like:
sqllogin
sqlrollback (*****ADDED*****)
sqlconn (connect to a database)
sqlrollback (*****ADDED*****)
sqldynprep (prepare a dynamic query)
sqldynexec (execute the dynamic query)
sqldynftch (fetch a row of the result)
sqldynftch (fetch a row of the result)
...
sqldynclose (close the query)
sqlrollback (*****ADDED*****)
sqldynprep (prepare the next query)
sqldynexec
sqldynftch
sqldynftch
...
sqldynclose
sqlrollback (*****ADDED*****)
... (and so on)
Note that it wouldn't make sense to insert sqlrollback
calls between the "sqldynprep" and "sqldynclose" calls,
because we would be shutting off the query before we were
done with it. This, in fact, is the "window of
opportunity" for problems as noted above: for example, if
the user told Report Builder to do a Print Preview, Report
Builder would call sqldynprep, sqldynexec and sqldynftch,
but would not call sqldynclose until the user closed the
Print Preview window.
For applications written with SQL embedded in the C code
(i.e. for applications which use the SQL Preprocessor), the
corresponding code might look like this:
sqllogin
EXEC SQL ROLLBACK WORK
sqlconn (connect to a database)
EXEC SQL ROLLBACK WORK
EXEC SQL PREPARE...
EXEC SQL EXECUTE
EXEC SQL FETCH
...
EXEC SQL CLOSE
EXEC SQL ROLLBACK WORK
...
B.Mau
10/28/97
96-09-06-015
Progress Software Technical Support Note # 17368