Subject Re: [IBO] Paradox to IB/IBO
Author Helen Borrie
At 12:26 PM 23/02/2004 -0500, you wrote:
>IBO (4.3.A) and Interbase (open source 6.0.2.0) with Delphi 5 . Converting
>some apps from Paradox to IB using IBO.
>
>1. In one app I want to automatically back up the database at 3AM. I have
>a process working within the app which starts and waits for a gbak (backup
>and restore) batch job to run and complete. With the 'old' Paradox
>database, all files were closed, backed up and re-opened so everything was
>up to date. Is this something I need to be concerned about with IBO and
>Interbase? If I set the IBO database connection to false before the backup
>will that 'close' the gdb??

As Jason said, with just backup you don't need to, because the IB backup
(gbak) is a "hot" backup.

Since you are doing this at 3 a.m., having users close all instances of
your application before they go home will ensure that everyone's work gets
committed and into the backup. gbak works within a snapshot transaction,
so it doesn't see any uncommitted work.

I *hope* your batch file isn't restoring with gbak -r. In that case, your
backup routine is horribly risky if any users are logged in, or could log
in. It's one of the few "possible causes of corruption" with IB. If you
are doing this, you should stop doing it, like NOW.

Also, it can happen that a restore encounters a problem when it is
rebuilding the database. In that case, the restore will abend and you are
left with a file which is only part of a database.

Always restore with gbak -c to a different filename and log in to the "new"
database to ensure the backup is OK. Once you're happy with it, switch the
names, zip up the old database and store it somewhere, and carry on with
the restored db.

btw, if the backup is OK, there is no need to run with a restored version
every time you back up. The backup process itself does a lot of
housekeeping in the live database (as long as you don't use the -g
switch). Running on a restored database is something you can schedule to
do periodically - say, monthly or quarterly, depending on the amount of
work the database handles.

>2. The Paradox app used dbiSaveChanges after dataset posts. Is this a
>concern with IBO, Interbase?

No, the dbi methods don't work with transactional dbm systems. They are
for desktop dbs like Paradox, that update the database files directly from
memory images.

>Does the default IBO transaction management take care of this?

The answer is - yes it does - but it doesn't mean you should ignore
transactions. "Updating the database" is a four-step process in Delphi and IB:

1. Transaction starts
2. (simple model - application selects some rows and fills the local buffers).

3. Editing cycle begins (this repeats)
a. User edits, inserts or deletes a row in the program.
Nothing in the database yet.
b. User Posts the change.
Often happens automatically when user moves to another row.
A temporary image of the new row (known as a "record version")
is written into the database.
Other transactions can't see it yet.
This cycle repeats until....

4. User commits transaction.
At this point,
-- if the commit succeeds, all the posted rows become
permanent in the database
-- if ANY row fails, all fail, and the transaction is in an
unfinished state. The application has to catch the
exception and resolve the transaction (roll it back).

Everything happens (has to happen) inside a transaction.

>(Not yet using explicit transaction control).

It's not essential to begin with, as long as you are testing all along the
line to make sure things actually happen.

The default ReadCommitted isolation level + Autocommit true provides
familiarity with the Paradox model. It follows the Post with an immediate
CommitRetaining.

CommitRetaining *does* make your updates permanent but it doesn't release
any resources on the server. For your early attention will be to take
explicit control of your Autocommit cycle and intervene periodically with a
"hard" call to Commit. This is essential - otherwise the transaction
inventory on the server will grow and grow until it is commanding the
lion's share of the server's memory.

CommitRetaining with no hard commits also prevents background garbage
collection from proceeding normally, so your db will fill up with obsolete
record versions ("garbage") that can't be cleaned up because of all these
users with Autocommit operating. That increases the number of pages that
have to be searched to find the requested rows.

Both problems cause response time to degrade progressively. The memory
consumption problem will eventually cause the system to "freeze" or, on
lower-spec'd systems, the server will crash.

Helen