Would you mind quantifying slowly? Care posting the DDL?
Also how fast is it if you run this procedure:
CREATE PROCEDURE p() BEGIN END;
DB2 isn't optimized towards "trivial stored procedures" (maybe Oracle is,
...?)
My usual answer would be to use an SQL (table) Function instead of trivial
procedures.
Problem is that for semantic reasons NEXT VALUE is not allowed in
functions.
Also what is the CACHE of the sequence?

This procedure:
CREATE SEQUENCE s
$
CREATE PROCEDURE p(OUT v INT)
BEGIN
VALUES NEXT VALUE FOR s INTO v;
END
$
CALL p(?)
$

Will cause two statements to execute:
1. CALL execution
2. Load procedure (if it's not already in cache => package cache size!!)
3. VALUES execution

Doing the VALUES INTO directly will take at most half the time....

Cheers
Serge

"Isman Vicki " <db2udbdba-ezmlmshield-x46014086.[Email address protected]
wrote on 07/29/2005 09:11:07 AM:

> Running DB2 8.2 (fixpack 8) on AIX 5.3, we're seeing a VERY SIMPLE stored
> procedure that just returns the next value from a sequence run slowly. Is
> there anything special in terms of configuration that might make stored
> procedures run faster?
>
> Also if anybody's worked with DB2 and Informatica and has any tips, that
> would be helpful.
>
> We're working with some Oracle/Informatica bigot consultants who are not
> being nice about this.
>
> Thanks,
> Vicki
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


DB2 & UDB email list listserv db2-l LazyDBA home page