Does the PREPARE statement affect the performance in a stored procedure?
Does the optimizer run everytime or is it just as performant as any select in a sp?
Also, is there any way to see the estimated execution plan for a query? (like the one in MsSQL) ?

Cheers,
Daniel

1) I am not sure what you are asking here. Using a PREPARE in a Stored Procedure is a method to have dynamic SQL in the SP. All dynamic SQL has to be run through the optimizer every time it is presented as new to the DB. Static SQL on the other hand is only optimized once, when it is bound to the DB. So using dynamic over static SQL does incur a performance hit.

2) The optimizer runs every time that dynamic SQL is presented as new to the DB. What I mean by this is that there is a dynamic SQL cache in DB2, and if the SQL is not there it is new and so the optimizer has to run.

3) Yes, you can EXPLAIN the SQL. You can do this three ways. One is to use the EXPLAIN statement (See the manual). The second is to use the Visual Explain tool. This can be found in the" Command Editor". The third is to use db2expln program to explain static SQL located in packages in the DB.

HTH

Andy

Thanks mate. That is exactly what I wanted to know.
Regarding point 1, is there anything I can do to avoid it? I take it all declares need to take part on the first part of the SP before any other code. My SP needs to return a cursor (to return a result set) and I need to do some checks before the select that gets the results. Is there anything I can do to avoid doing something like:
DECLARE cur CURSOR WITH RETURN FOR s1;
....
....
PREPARE s1 FROM sqlstmt;
OPEN cur;

My answer is probably yes. A lot depends on what you are trying to accomplish. I have numerous SPs that use all static SQL and work like that. Posting the SP and a description of what you are trying to accomplish would net you a better answer.

Andy

If your stored procedure returns a result set, you must declare and open a cursor because cursor are the only way to access values in a DB2 database - whether from a procedure or an application does not matter. The PREPARE is only needed/applicable for dynamic SQL.

Usually, you need dynamic SQL if you don't know which tables are to be queried or if the predicates may change depending on the input parameters of the procedure. In fact, a procedure is just like a regular application: if you know your queries, you can use static SQL and don't have the optimization hit. Otherwise, you have to stick to dynamic SQL. Note that there is a good discussion on static vs. dynamic SQL in the manuals (at least for LUW).

Ok, here is an example: this is a simple stored proc that is supposed to give information about tables/views (somehow like the sp_help sp in mssql).

I need to declare the cursor cur in the declarations section (so before I get the chance to define the SELECT query). In between I need to have some statements (alter some of the parameters).
If I would do something like:

It fails to compile and it doesn't like the declare cursor statement. From what I've read (in here: www.redbooks.ibm.com/abstracts/sg246503.html) I understand that all declarations need to sit in a block at the beginning. The only way I found around this issue is to use dynamic sql as above.

Please let me know if there is any other better way to do it.

Try it like this:

DECLARE cur CURSOR WITH RETURN FOR
SELECT *
FROM qsys2.syscolumns
WHERE table_schema LIKE LibraryName
AND table_name LIKE TableName;

IF LibraryName = '' THEN
SET LibraryName = 'CRMDATA';
END IF;
SET TableName = UPPER(TableName);
OPEN cur;


Andy

You are a genius! (either that, or I am really dumb).

Thanks very much. This is perfect.

I am no genius, I just have lots of experience trying to avoid dynamic SQL in stored procedures, i.e I have just done this sort of thing alot.

Andy

Another alternative:
And yet another one:
This works because DECLARE statements must be at the beginning of a block - not necessarily at the beginning of the stored procedure body.