A “classic” usage of generators is to ensure unique, sequential numbers for – well, anything in your application other than the row IDs discussed above. When you have an application that is transferring data to some other system, you can use generators to safely identify a single transfer by labeling it with a generated value. This greatly helps tracking down problems with interfaces between two systems (and, unlike most of the following, this does work safely and exactly).

Imagine you just built a fantastic new feature into your database with a stored procedure. Now you update your customer's systems and some time later you'd like to know if the users really use this feature and how often. Simple: make a special generator that only gets incremented in that SP and you're there... with the restriction that you can't know the number of transactions that were rolled back after or while your SP executed. So in this case you at least know how often users tried to use your SP :-)

You could further refine this method by using two generators: One gets incremented at the very start of the SP, another at the very end just before the EXIT. This way you can count how many attempts to use the SP were succesful: if both generators have the same value, then none of the calls to the SP failed etc. Of course you then still don't know how many times the transaction(s) invoking your SP were actually committed.

There is the known problem with InterBase and Firebird that a SELECT COUNT(*) (with no Where clause) from a really large table can take quite a while to execute, since the server must count "by hand" how many rows there are in the table at the time of the request. In theory, you could easily solve this problem with generators:

This works beautifully and makes a “full” record count needless – just get the current generator value. I stressed the “in theory” here because the whole thing goes down the drain when any Insert statements fail, because as said those generators are beyond transaction control. Inserts can fail because of constraints (Unique Key violations, NOT NULL fields being NULL, etc.) or other metadata restrictions, or simply because the transaction that issued the Insert gets rolled back. You have no rows in the table and still your Insert counter climbs.

So it depends – when you know the rough percentage of Inserts that fail (you can kinda get a “feeling” for this), and you're only interested in an estimation of the record count, then this method can be useful even though it's not exact. From time to time you can do a “normal” record count and set the generator to the exact value (“re-synchronize” the generator), so the error can be kept rather small.

There are situations when customers can happily live with an info like “there are about 2.3 million records” instantly at a mouseclick, but would shoot you if they have to wait 10 minutes or more to see that there are precisely 2.313.498.229 rows...

When you have SPs that e.g. generate report outputs on large tables and/or complex joins, they can take quite a while to execute. Generators can be helpful here in two ways: they can provide you with a “progress counter” which you can poll periodically from the client side while the SP runs, and they can be used to stop it:

CREATE GENERATOR gen_spTestProgress;
CREATE GENERATOR gen_spTestStop;

set term ^;

CREATE PROCEDURE spTest (...)
AS
BEGIN
  (...)
  for select <lots of data taking lots of time>
  do begin
    GEN_ID(gen_spTestProgress,1);

    IF (GEN_ID(gen_spTestStop,0)>0) THEN Exit;

    (...normal processing here...)
  end
END^

Just a rough sketch, but you should get the idea. From the client, you can do a GEN_ID(gen_spTestProgress,0) asynchronously to the actual row fetching (e.g. in a different thread), to see how many rows were processed, and display the value in some sort of progress window. And you can do a GEN_ID(gen_spTestStop,1) to cancel the SP at any time from the “outside”.

Although this can be very handy, it has a strong limitation: it's not multi-user safe. If the SP would run simultaneously in two transactions, they would mess up the progress generator – they would both increment the same counter at the same time so the result would be useless. Even worse, incrementing the stop generator would immediately stop the SP in both transactions. But for e.g. monthly reports that are generated by a single module run in batch mode, this can be acceptable – as usual, it depends on your needs.

If you want to use this technique and allow users to trigger the SP at any time, you must make sure by other means that the SP can not be run twice. Thinking about this, I had the idea to use another generator for that: let's call this one gen_spTestLocked (assuming the initial value of 0 of course):

CREATE GENERATOR gen_spTestProgress;
CREATE GENERATOR gen_spTestStop;
CREATE GENERATOR gen_spTestLocked;

set term ^;

CREATE PROCEDURE spTest (...)
AS
DECLARE VARIABLE lockcount INTEGER;
BEGIN
  lockcount = GEN_ID(gen_spTestLocked,1); 
    /* very first step: increment the locking generator */

  if (lockcount=1) then /* _we_ got the lock, continue */
  begin
    (..."normal" procedure body here...)
  end

  lockcount = GEN_ID(gen_spTestLocked,-1); /* undo the increment */

  /* make sure the gen is reset at the very end even when an exception
     happens inside the “normal” procedure body: */
     
  WHEN ANY DO
    lockcount = GEN_ID(spTestLocked,-1); /* undo the increment */
  exit;
END^

Note: I'm not yet 100% sure this is absolutely multi-user safe, but it looks rock solid – as long as no EXIT occurs in the normal procedure body, for then the SP would stop and quit, leaving the generator incremented. The WHEN ANY clause handles exceptions, but not normal EXITs. Then you'd have to decrement it by hand – but you could decrement the generator just before the EXIT to avoid this. Given the right precautions, I can't make up any situation where this mechanism could fail... If you can – let us know!