At 11:43 PM 13/03/2005 +0000, you wrote:

>I hope this provides the missing details:
>
>- FB is v1.5.1. Superserver
>- ODBC is v1.2.0.68
>- The PC has 1Gb RAM, 40 GB Disk space, of which 28 free
>
>Here is the connection string:
>
>DRIVER=Firebird/InterBase(r) driver;
>DBNAME=D:\DataCenter\DB\Data\DCDB.FDB; UID=GWC; PWD=******; ROLE=SU
>
>For isql:
>
>connect "D:\DataCenter\DB\Data\DCDB.FDB" user 'gwc' password '******';
>
>The path is stored in registry in the following
>format "..\..\DCDB.FDB", if on the same drive. If on another the full
>path is stored. The application transforms always to full path, incl.
>drive, as you can see above.

1) When you say "the application transforms...", how does it do this? i.e.
does it do it by calls to Windows API? During debug, are you *certain*
that the path that is presented to the Firebird API is exactly the
absolute, hard path?

2) Presumably your application installer writes this Registry key. Why is
it that you can't write the hard path into this key, IN EVERY CASE?

3) How do you check EXACTLY what the connection path is?

>All I have in firebird.log is:
>
>JOYBRINGER (Client) Thu Mar 10 22:31:53 2005
> Guardian starting: D:\Program
>Files\DataCenter\DB\bin\fbserver.exe

Where (exactly) are isql and your custom data viewer located?

Is your application starting its own instances of Guardian and fbserver, as
applications, while a Firebird server is running somewhere else on the
system as a service ?

>
>I guess that the sort files are from the selects from the "temp"
>table. It is not a real temp table, but a table where the raw data
>from the DB2 is initally inserted. Then a human checks and modifies
>some fields, which are again saved in this "temp" table.
>Next, the application generates some additional data (like hash
>values, automatic classification, etc.) - sorry, I cannot reveal all
>details here :(.
>This is done in memory. The modified data is inserted - single record
>each time, updating all four tables (4 or more INSERT statements) and
>commiting the transaction. Yes, commit after each row in the main
>table. Two other tables may have more than one INSERT. ID's as PK's
>are generated BEFORE INSERT from two generators for the main and
>hashes tables.

Relating this back to earlier information...you are iteratively
1) starting a transaction
2) selecting gigabytes of source records from the temp table
3) reading one source record
4) letting the user do some stuff with that source record and writing it
back to the temp table (as yet uncommitted)
5) meanwhile,
a) in memory, massaging some data from the current source record
b) from the application, constructing a series of INSERT statements
populated by data from these memory structures
6) logging something to a log table?
7) finally, committing the whole transaction

Correct ?

If this is not the sequence of events, please try to modify this to
describe what the application is really doing from start to finish of a
transaction.

Also, comment on what is the purpose of Step 4, above?

>Indices: 1 on varchar(20) and 1 on varchar(250) + 3 on int. Charset
>is NONE.

Are these indices in the source table or the target tables?

>I am afraid that I cannot paste here the exact INSERT statements, but
>they are really simple. The data is passed verbatim, not from another
>select. Example:
> INSERT INTO TABLE1 VALUES ('','blah','blah','5');
>Only VARCHAR, CHAR and INT fields are used.
>
>The logs I mention are mine, but they will contain any ODBC error, as
>returned by the driver. As I wrote, there was no such error.

The server returns exceptions to the driver via the API. Data integrity
exceptions (violation of constraints, type mismatches,
overflows, conflicts, etc.) would come back on posting the new record,
i.e. when the request causes a new record to be written to disk. This
record is known only to the transaction that writes it. Communication
exceptions would come back on any request, as long as the client
application remains able to post requests and get something back. If the
application should crash, or the client connection should crash, your ODBC
log will have only the last known communication. The Firebird log, OTOH,
may show evidence of a connection that was lost, provided the server itself
wasn't in a state where it was unable to listen for messages from the
network, i.e. itself crashed.

So let's assume that, from the POV of your internal logging of a live
connection, a single instance of your application appears to complete the
whole of its task without any data integrity errors or lost
connections. Yet something (?) seems to be telling you that the task
*wasn't* completed. The server was interrupted during the last commit, by
something external and destructive. Given that this is Superserver, the
least likely cause will be compromised path syntax. Given that this is
IPServer, the most likely cause is corrupt IPC memory.

Your internal logs might be enhanced greatly for your troubleshooting if
they recorded context information for each log entry: CURRENT_TRANSACTION
and CURRENT_CONNECTION (both BigInts).

>The temp (sort) files are from the same day, but I cannot relate them
>to a given operation, as someone was working with the app all the
>day. I guess they are result from SELECT from the "temp" table. It
>has 2.3 million records and the SELECT has ORDER BY clause.

Then these will be ****enormous***** temp files bridging both memory and
disk. If your program flow is as it appears from your description, you
will be having a furious overlap of operations, with huge queues of demand
on machine resources. Amongst all this, you are using the IPServer
protocol, which is notoriously fragile. You haven't mentioned whether you
have multi-threading in this equation....Risks abound here.

Experiment with development copies of the database, until you're very clear
that you've got it right. With your current architecture, you would be
very unwise to keeping playing Russian roulette with any databases that you
care about. A good place to start will be to abandon the IPServer protocol
and use TCP/IP local loopback (the localhost server) for every connection
to the database, as you indicated elsewhere you would do.

Because you have this source table open to a ravenously hungry UI, you need
to find a selection strategy to minimise the number of source records your
client connection requests at each turn of your conversion procedure. A
human can't read 2.3 million records in a lifetime. A machine is going to
struggle, gasp and finally choke if you tell it to read, sort and fetch 2.3
million records each time the human wants to read one. Presumably, the
human knows what s/he is looking for, or you wouldn't be asking her to look
at all. Provide selectors to populate a WHERE clause. Provide flags to
tag the source records that are no longer of interest...and so on.

./hb