Hello everybody ;-)

I have a major question of correctness and performance (IDS 7.31 on AIX
4.somthing). (If you cant work out what I am on about there are some made
up examples at the end of the post!)

Work I am invovlved in managing data in an extreamly large informix
database. (Over 300 tables with the largest having just over 40 million
rows) We use SPL on a daily basis to massage the data and it has up to
this point been written as one large transaction (incl the create/execute
and drop procedure calls)The Procs contain a large amount of business
logic and perfom complex calculations.

I have suggested that this is BAD (Getting a lot of sysprocplan locks and
huge 20 hour+ sql runs) and have suggested that the procedures should be
being created out of transaction and the procedures should contain BEGIN
/ COMMIT and ROLLBACK as required we should also be using correct
exception handling (My initial proving spl's took a 20 hour run down to 4
hours)

Now i have alway prided my self on non-code dupe and compact routines (in
any language) and as such my move back into informix was no exception and
I produce my spl in nice tight code with a main proc calling sub procs.

Now My problem!!! We have brought a machine down to its knees today it
dropped to single thread processing and had no memmory left! It had a
main proc with a foreach with hold and a transaction was began and
commited within the foreach loop. The foreach recursively called a sub
procedure that did the main body of the work.

Now I believe its due to a recursive call on a sub procedure (I have read
that we shouldnt do that! as we end up with mutex locking madness) And I
believe we didnt have the problem before because the recursive proc call
was in the same transaction so once created was locked for the duration)

How should we write our SPL to balance performance for reliability?
I am convinced we should base the transactions on data specific commit
points and we can overcome our memmory problems by not calling sub
procedures and/or an environment setting such as SET OPTIMIZATION LOW
OR have we reached the end of our search for enlightenment and we should
be using esql/c?

Regards John Berry

Ok Now the examples

1. Old style....

DATABASE x;

BEGIN;

SET DEBUG FILE TO .trc;

CREATE PROCEDURE x()

DEFINE xx LIKE b.col;

FOREACH x FOR

Do somthing;

Call Sub_proc();

TRACE 'Somthing happening';
END FOREACH;
END PROCEDURE;

EXECUTE PROCEDURE x();

DROP PROCEDURE x;

2. New Style...

DATABASE x;

SET DEBUG FILE TO .trc;

CREATE PROCEDURE x()

DEFINE xx LIKE b.col;
DEFINE ISAMERR INT;
DEFINE SQLERR INT;
DEFINE ERRTXT CHAR(500);

FOREACH x WITH HOLD FOR

ON EXCEPTION
SET SQLERR,ISAMERR,ERRTXT

TRACE ISAMERR||' '||SQLERR||' '||TRIM(ERRTXT);

ROLLBACK;
END EXCEPTION;

BEGIN;
Do somthing;

Call Sub_proc();

TRACE 'Somthing happening';
COMMIT
END FOREACH;
END PROCEDURE;

EXECUTE PROCEDURE x();

DROP PROCEDURE x;