Good Questions :)

>>The only gotcha I can think of at the moment, is maybe one (or many)
>>of the transactions is still uncommitted, or your transaction you are
>>running select count(*) from was started before all of the insert
>>transactions were committed.

All of the lines are run inside a single transaction. A single SP will by
itself perform many count(*)'s and then perform an insert on either TABLE_A
or DROP_FILE. It is all performed serial, so there is no problem here.
Also, I don't see how it would make a record not processed at all. This
would just create more drops than necessary, etc.

>>What happens when this code raises an exception (duplicate key
>>violation or similar)? Does your application raise this, or does the
>>script stop on these errors?

For this situation, there is no "app" side. I am using IB Workbench to
process the script. I could just as easily use the SQL utility as well.
The WHEN ANY DO code in the SP catches all server side exceptions and then
performs an INSERT into the DROP_FILE table. This does not stop the SP or
any subsequent calls to the SP. You would need to raise an additional
exception within the WHEN ANY DO code to do that. So all exceptions for any
reason are caught and do not stop processing.

>>How have you calculated the original script query count? Are you
>>possibly including blank lines in this count?

Yep. Used MS Access to load up the script file and got myself a record
count where the field was not null. Any blank lines would of been accounted
for.

>>Is it possible for the input to have too many characters to fit into
>>the input parameter of the stored procedure?

Now this has occasionally happened to me. In this case however the script
stops processing and I receive a dialog box asking if I want to ignore or
abort. If I abort, the whole script is aborted and the transaction is rolled
back, if I ignore it then processing continues as normal and that particular
execution of the SP never occured as far as the database is concerned. I
have since stopped this from happening completely by creating the script
with functions that select only the first X amount of characters from the
input string. The only situation unresolved is transliteration errors which
is completely my fault since I left the field with NONE for the character
set. In any case, these errors are few, highly visible, and accounted for.

>>Perhaps you could try and locate the last record you attempted to
>>insert from each script, and see whether there is any script that did
>>not totally complete? When it is inserting hundreds of records per
>>second, you have to be watching closely to see a problem.

Heh. I performed this very same check. Now here is the mystery....

SCRIPT LINE 10045 'EXECUTED'
SCRIPT LINE 10046 'MISSED'
SCRIPT LINE 10047 'EXECUTED'
SCRIPT LINE 10048 'EXECUTED'

The missed lines are distributed randomly throughout the script files. No
clear pattern or indication of why. Line 10045 executed with no errors, no
malformed parameters, no transliteration errors, nothing. Why Line 10046
just did not execute is beyond me. Line 10047 executed just fine, so
clearly 10046 did not stop the whole process, and COMMIT has nothing to do
with it, since if it did ALL 4 lines would not be present in either TABLE_A
or DROP_FILE. 10045,10047, and 10048 are.

Once again, I am stumped :)


-----Original Message-----
From: Adam [mailto:s3057043@...]
Sent: Tuesday, February 15, 2005 2:53 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Absolute Mystery




Hi Edwin,

The only gotcha I can think of at the moment, is maybe one (or many)
of the transactions is still uncommitted, or your transaction you are
running select count(*) from was started before all of the insert
transactions were committed.

Other possibilities:

What happens when this code raises an exception (duplicate key
violation or similar)? Does your application raise this, or does the
script stop on these errors?

INSERT INTO DROP_FILE(FIELD_1,FIELD_2,FIELD_3)
VALUES(:PASSED_VALUE_A,:PASSED_VALUE_B,:PASSED_VALUE_C);

How have you calculated the original script query count? Are you
possibly including blank lines in this count?

Is it possible for the input to have too many characters to fit into
the input parameter of the stored procedure?

Perhaps you could try and locate the last record you attempted to
insert from each script, and see whether there is any script that did
not totally complete? When it is inserting hundreds of records per
second, you have to be watching closely to see a problem.

Adam


--- In firebird-support@yahoogroups.com, "Edwin A. Epstein, III"
<eepstein@c...> wrote:

> Well I am stumped on this one.
>
> I had 4.2 million lines of a stored procedure that was to be run

from a

> script. I broke it up into several different batches, so it was

not all

> going to be in one transaction.
>
> An example of a line:
>
> EXECUTE PROCEDURE PROC_A('12341234','AB','DJSDADKJJJSJJ');
>
> PROC_A does some lookups and inserts, etc. It also has:
>
> WHEN ANY DO
> BEGIN
> INSERT INTO DROP_FILE(FIELD_1,FIELD_2,FIELD_3)
> VALUES(:PASSED_VALUE_A,:PASSED_VALUE_B,:PASSED_VALUE_C);
> END
>
> So any exception that the procedure throws, deliberate or

otherwise, creates

> a drop record in the DROP_FILE table.
>
> After completing all the batches, all of the results are going to

be in

> either TABLE_A or DROP_FILE. So a count(*) on both tables added

together

> should equal 4.2 million records.
>
> It adds up 450K short. Hmmmmmm
>
> Okay. I checked 20 random lines from each script file. Every file

has the

> majority of the lines processed. I assume this since the data

being passed

> into the procedure is unique and I can find a record in either

TABLE_A or

> DROP_FILE.
>
> Why are some of the lines not being processed in the script file?

The whole

> script file is inside one transaction. So if the whole thing

rolled back I

> would see one script file missing completely. Where are the missing
> records?
>
> Has anybody had anything like this happen to them?

Yahoo! Groups Links