Nested Stored Procedure Commits

I hope somebody can help me.
I have a stored procedure that calls nested Stored Procedures inside a Loop.
My problem is that the Stored Procedures in all runs over 4 days and I need a way to count the Completed rows already processed
, and as you know the Commit statement is not allowed inside DDL's.
I have tried Dynamic SQL calls to the Stored Procedures with Commits but the Commit fails at run time in the DSQL.
I also tried to use the Firebird SQL SAVEPOINT ( A.k.a Nested Transactions ) but I must be doing something wrong because that Also Fails at Runtime.
I hope Somebody could shed some Light. Thanx

4 days?

Is that alright?

You might want to speed that up and fix this problem altogether.

How are you using the savepoints? As far as I can tell - they're different from "commit".


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Four days is the fastest I could get it,
I have optimized the stored procedures as best I could ( Indexes taking out DSQL etc ) it started out running a whole week, It loops through around 10 mil records doing calcs etc.
( data is dumped from Medical Systems. )

I declare my SavePoints with SAVEPOINT <Identifier> inside Dynamic SQL but The Procedure Fails when trying to Run it someting about :
SQL error code = -902
Wrong Request Type

I will gladly try UDF's If that is the only other way.
Somehow Writing to File the Number of records processed, if that is even possible , I am using Multiple Linux Servers to cut down on turnover time.
( I mean what is the Security issues to take into consideration writing to File From Firebird etc. I would really be glad if somebody could Point me In the right Direction in that Regard ) .

Thanx for reply.

Firstly, I would recommend you to try to optimize your procedures and hardware/software infrastructure. With a little bit of tuning on good commodity hardware Firebird easily can do a million updates in a second and a few millions reads.

Also I would break this batch process into some smaller pieces executed in separate transactions (you don't want to loose 4-days work due to some minor mistake, right?).

Anyways, you can use make your procedure selectable returning rows as progress goes. Also you can use generators or implement UDF to write progress to file or put it into attachment-specific variable.

Yes you are right I have broken it up into manageable pieces and Now at least I can see where the bottle necks are.
I used ISQL -I file -T / commands and using commits after each Block and it is actually turning out quite well.
I would still like to ask if someone knows how to use the DEBUG command in Firebird and/or writing to the default logging files?!? anyway thanx for the help