Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2007
    Posts
    50

    Unanswered: Sql prepare and the optimizer

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2007
    Posts
    50
    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;

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by ARWinner
    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
    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).

    Code:
    create procedure sp_help
    (
    	IN TableName varchar (255),
    	IN LibraryName varchar(255)
    )
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    	DECLARE sqlstmt VARCHAR(1000);
    	DECLARE cur CURSOR WITH RETURN FOR s1;
    	IF LibraryName = '' THEN
    		SET LibraryName = 'CRMDATA';
    	END IF;
    	SET sqlstmt = '
    	SELECT *
    		FROM qsys2.syscolumns
    		WHERE table_schema LIKE ?
    		AND table_name LIKE ?';
    	SET TableName = UPPER(TableName);
    	PREPARE s1 FROM sqlstmt;
    	OPEN cur USING LibraryName, TableName;
    END;
    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:

    Code:
    	IF LibraryName = '' THEN
    		SET LibraryName = 'CRMDATA';
    	END IF;
    	SET TableName = UPPER(TableName);
    	DECLARE cur CURSOR WITH RETURN FOR
    	SELECT *
    		FROM qsys2.syscolumns
    		WHERE table_schema LIKE LibraryName
    		AND table_name LIKE TableName;
    	OPEN cur;
    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.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Sep 2007
    Posts
    50
    You are a genius! (either that, or I am really dumb).

    Thanks very much. This is perfect.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another alternative:
    Code:
    DECLARE c CURSOR FOR
       SELECT ...
       FROM   ...
       WHERE  ... LIKE ?;
    ...
    OPEN c USING LibraryName;
    And yet another one:
    Code:
    SET LibraryName = ...;
    BEGIN
       DECLARE c CURSOR FOR ...
    END;
    This works because DECLARE statements must be at the beginning of a block - not necessarily at the beginning of the stored procedure body.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •