Hi Morgan,

One other approach might be to break down a complex DBP into a series of
simpler ones that are called from a top level procedure. This would allow
you to see each new procedure call through printrules tracing.

This also has an additional advantage relating to locking. When a DBP is
called, IS or IX locks are taken on ALL tables referenced by the procedure
even if the SQL statements that accessed the table are not executed. This
adds a possibly unnecessary overhead and could potentially cause some
contention problems. If your procedure has a SQL statements embedded in IF
statements you could move those SQL statements to their own DBPS and call
the new DBP's from with the IF statements. Thus

IF condition
INSERT into table_1
ELSE
INSERT into table_2
ENDIF

would become

IF condition
CALLPROC table_1_ins
ELSE
CALLPROC table_2_ins
ENDIF

In terms of locking this approach won't have any benefits in the situation
where the same table is referenced in both parts of the IF. e.g

IF condition
INSERT into table_1
ELSE
UPDATE into table_2
ENDIF

However it would still help with the debugging.

Peter Gale
Comprehensive Solutions (US)
Comprehensive Solutions International (UK)
T: +44 (0)1398 341777 M: +44 (0)7831 513181
PGale@Comp-Soln.co.uk www.Comp-Soln.co.uk-----Original Message-----
From: info-ingres-admin@cariboulake.com
[mailto:info-ingres-admin@cariboulake.com] On Behalf Of morgan brickley
Sent: 27 October 2005 12:31
To: info-ingres@cariboulake.com
Subject: [Info-ingres] is there a tool for stepping through stored procs ?

I find that a lot of time is wasted trying to understand why a database
procedure doesn't behave as expected (i.e. it doesn't return an error it
just doesn't do what I was hoping it would)

Is there any tool to allow stepping through the stored procedure line by
line, a sql-debugger if you will, in order to ascertain the path along
which the procedure winds? and perhaps even see the temporary/permanent
effects the proc has on the DB

Morgan.
_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailm...py/info-ingres